1 protected void ExportExcel(System.Data.DataTable dt)
2 {
3 if (dt == null || dt.Rows.Count == 0) return;
4 Microsoft.Office.Interop.Excel.Application xlsxApp = new Application();
5 if (xlsxApp == null) return;
6 //System.Globalization.CultureInfo CurrentCI = System.Threading.Thread.CurrentThread.CurrentCulture;
7 //System.Threading.Thread.CurrentThread.CurrentCulture = new System.Globalization.CultureInfo("en-US");
8 Microsoft.Office.Interop.Excel.Workbooks workbooks = xlsxApp.Workbooks;
9 Microsoft.Office.Interop.Excel.Workbook workbook = workbooks.Add(Microsoft.Office.Interop.Excel.XlWBATemplate.xlWBATWorksheet);
10 Microsoft.Office.Interop.Excel.Worksheet worksheet = (Microsoft.Office.Interop.Excel.Worksheet)workbook.Worksheets[1];
11 Microsoft.Office.Interop.Excel.Range range;
12 long totalCount = dt.Rows.Count;
13 long rowRead = 0;
14 float percent = 0;
15 for (int i = 0; i < dt.Columns.Count; i++)
16 {
17 worksheet.Cells[1, i + 1] = dt.Columns[i].ColumnName;
18 range = (Microsoft.Office.Interop.Excel.Range)worksheet.Cells[1, i + 1];
19 range.Interior.ColorIndex = 15;
20 }
21 for (int r = 0; r < dt.Rows.Count; r++)
22 {
23 for (int i = 0; i < dt.Columns.Count; i++)
24 {
25 try
26 {
27 worksheet.Cells[r + 2, i + 1] = dt.Rows[r][i].ToString();
28 }
29 catch (Exception)
30 {
31 worksheet.Cells[r + 2, i + 1] = dt.Rows[r][i].ToString().Replace("=", "");
32 throw;
33 }
34 }
35 rowRead++;
36 percent = ((float)(100 * rowRead)) / totalCount;
37 }
38 ////在Excel的指定位置加入图片
39 //worksheet.Shapes.AddPicture("C:\\Users\\mengmeng.zhao\\Desktop\\1.jpg",
40 // Microsoft.Office.Core.MsoTriState.msoFalse, Microsoft.Office.Core.MsoTriState.msoTrue, 100, 200, 200, 300);
41 ////在Excel的指定位置加入文本框,和里面的内容.
42 //worksheet.Shapes.AddTextEffect(Microsoft.Office.Core.MsoPresetTextEffect.msoTextEffect1,
43 // "123456", "Red", 15, Microsoft.Office.Core.MsoTriState.msoFalse, Microsoft.Office.Core.MsoTriState.msoTrue, 150, 200);
44
45 xlsxApp.Visible = true;
46 }