export DataTable To Excel(C)

 

 

 

static DataTable GetTable()

{

DataTable table = new DataTable(); // New data table.

table.Columns.Add("Dosage", typeof(int)); // Add five columns.

table.Columns.Add("Drug", typeof(string));

table.Columns.Add("Patient", typeof(string));

table.Columns.Add("Date", typeof(DateTime));

 

table.Rows.Add(15, "Abilify", "xxx", DateTime.Now); // Add five data rows.

table.Rows.Add(40, "Accupril", "yyy", DateTime.Now);

table.Rows.Add(40, "Accutane", "zzz", DateTime.Now);

table.Rows.Add(20, "Aciphex", "zyy", DateTime.Now);

table.Rows.Add(45, "Actos", "xxxy", DateTime.Now);

 

return table; // Return reference.

}

 

private void exportDataTableToExcel(DataTable dt, string filePath)

{

// Excel file Path

string myFile = filePath;

 

//System.Data.DataRow dr = default(System.Data.DataRow);

 

int colIndex = 0;

int rowIndex = 0;

 

// Open the file and write the headers

StreamWriter fs = new StreamWriter(myFile, false);

 

fs.WriteLine("<? xml version=\"1.0\"?>");

fs.WriteLine("<?mso-application progid=\"Excel.Sheet\"?>");

fs.WriteLine("<ss:Workbook xmlns:ss=\"urn:schemas-microsoft-com:office:spreadsheet\">");

 

// Create the styles for the worksheet

fs.WriteLine(" <ss:Styles>");

// Style for the column headers

fs.WriteLine(" <ss:Style ss:ID=\"1\">");

fs.WriteLine(" <ss:Font ss:Bold=\"1\" ss:Color=\"#FFFFFF\"/>");

fs.WriteLine(" <ss:Alignment ss:Horizontal=\"Center\" ss:Vertical=\"Center\" " + "ss:WrapText=\"1\"/>");

fs.WriteLine(" <ss:Interior ss:Color=\"#254117\" ss:Pattern=\"Solid\"/>");

fs.WriteLine(" </ss:Style>");

// Style for the column information

fs.WriteLine(" <ss:Style ss:ID=\"2\">");

fs.WriteLine(" <ss:Alignment ss:Vertical=\"Center\" ss:WrapText=\"1\"/>");

fs.WriteLine(" </ss:Style>");

// Style for the column headers

fs.WriteLine(" <ss:Style ss:ID=\"3\">");

fs.WriteLine(" <ss:Font ss:Bold=\"1\" ss:Color=\"#FFFFFF\"/>");

fs.WriteLine(" <ss:Alignment ss:Horizontal=\"Center\" ss:Vertical=\"Center\" " + "ss:WrapText=\"1\"/>");

fs.WriteLine(" <ss:Interior ss:Color=\"#736AFF\" ss:Pattern=\"Solid\"/>");

fs.WriteLine(" </ss:Style>");

fs.WriteLine(" </ss:Styles>");

 

// Write the worksheet contents

fs.WriteLine("<ss:Worksheet ss:Name=\"Sheet1\">");

fs.WriteLine(" <ss:Table>");

 

fs.WriteLine(" <ss:Row>");

foreach (DataColumn dc in dt.Columns)

{

fs.WriteLine(string.Format(" <ss:Cell ss:StyleID=\"1\">" + "<ss:Data ss:Type=\"String\">{0}</ss:Data></ss:Cell>", dc.ColumnName ));

}

 

fs.WriteLine(" </ss:Row>");

 

object cellText = null;

 

// Write contents for each cell

foreach (DataRow dr in dt.Rows)

{

rowIndex = rowIndex + 1;

colIndex = 0;

fs.WriteLine(" <ss:Row>");

foreach (DataColumn dc in dt.Columns)

{

cellText = dr[dc];

// Check for null cell and change it to empty to avoid error

if (cellText == null ) cellText = "";

fs.WriteLine(string.Format(" <ss:Cell ss:StyleID=\"2\">" +

"<ss:Data ss:Type=\"String\">{0}</ss:Data></ss:Cell>", cellText));

colIndex = colIndex + 1;

}

fs.WriteLine(" </ss:Row>");

}

 

fs.WriteLine(" <ss:Row>");

fs.WriteLine(" </ss:Row>");

 

// Close up the document

fs.WriteLine(" </ss:Table>");

fs.WriteLine("</ss:Worksheet>");

fs.WriteLine("</ss:Workbook>");

fs.Close();

}

 

Example:

exportDataTableToExcel (GetTable(),"C:\\PatientDetails.xls");

will write the content of data table with Formatting Styles. 

转载于:https://www.cnblogs.com/ChenYilong/p/3625057.html