涉及到程序导出Excel报表,所以就花了两个收集了一下资料,这了这个程序,分享给大家.程序说明:
程序读取 gridView 表格中内容,导入到Excel ,自定义 页眉 页脚和添加页码,以及横向打印.列宽根据列的
多少列自适应,表格高度自适应,自动换行等.使用需要添加 引用 Microsoft.Office.Interop.Excel,代码如下 :出处C# 导出 Excel 自定义输出格式
/// <summary>
/// 将GridView内的内容导出到Excel
/// </summary>
/// <param name="xlsName">Excel文件名</param>
/// <param name="gridView">Gridview</param>
public static void ExportToExl(string xlsName, GridView gridView1)
{
1
2
3 bool flag = true;
4 SaveFileDialog sfd = new SaveFileDialog();
5 //string filename = DateTime.Now.ToString("yyyyMMdd") + "-" + DateTime.Now.Hour.ToString() + DateTime.Now.Minute.ToString();
6 sfd.FileName = xlsName;
7 sfd.Filter = "Excel files (*xls) | *.xls";
8 sfd.RestoreDirectory = true;
9
10 if (sfd.ShowDialog() == DialogResult.OK && sfd.FileName.Trim() != null)
11 {
12 int gridview_column_count = gridView1.Columns.Count;
13 int gridview_row_count = gridView1.RowCount;
14 System.Reflection.Missing miss = System.Reflection.Missing.Value;
15 Microsoft.Office.Interop.Excel.Application xlapp = new Microsoft.Office.Interop.Excel.Application();
16 if (xlapp == null)
17 {
18 MessageBox.Show("无法打开EXcel，请检查Excel是否可用或者是否安装好Excel", "系统提示");
19 return;
20 }
21
22 xlapp.Visible = true;
23
24 xlapp.DisplayAlerts = false;
25 Microsoft.Office.Interop.Excel.Workbooks mBooks = (Microsoft.Office.Interop.Excel.Workbooks)xlapp.Workbooks;
26 Microsoft.Office.Interop.Excel.Workbook mBook = (Microsoft.Office.Interop.Excel.Workbook)mBooks.Add(miss);
27 Microsoft.Office.Interop.Excel.Worksheet mSheet = (Microsoft.Office.Interop.Excel.Worksheet)mBook.ActiveSheet;
28 Microsoft.Office.Interop.Excel.Range mRange = mSheet.get_Range((object)"A1", System.Reflection.Missing.Value);
29
30 //设置对齐方式
31 //mSheet.Cells.HorizontalAlignment = XlHAlign.xlHAlignCenter;
32
33 mSheet.Cells.WrapText = true;
34 // xlapp.Worksheets.page
35
36 ((Microsoft.Office.Interop.Excel.Range)mSheet.Rows["1:1", System.Type.Missing]).RowHeight = 20;
37
38 //设置数据行行高度
39 ((Microsoft.Office.Interop.Excel.Range)mSheet.Rows["2:" + gridView1.RowCount + 1, System.Type.Missing]).RowHeight = 16;
40
41 //设置字体大小（10号字体）
42 // mSheet.Range[mSheet.Cells[1, 1], mSheet.Cells[gridView1.RowCount + 1, gridView1.Columns.Count]].Font.Size = 10;
43
44 Microsoft.Office.Interop.Excel.Range range1 = mSheet.get_Range(mSheet.Cells[1, 1], mSheet.Cells[gridView1.RowCount + 2, gridView1.Columns.Count]);
45 range1.Borders.LineStyle = 1;
46
47 Microsoft.Office.Interop.Excel.Range range2 = mSheet.get_Range(mSheet.Cells[2, 1], mSheet.Cells[gridView1.RowCount + 2,gridview_column_count]);
48 Microsoft.Office.Interop.Excel.Range range3 = mSheet.get_Range(mSheet.Cells[3, 1], mSheet.Cells[gridView1.RowCount + 2, gridview_column_count]);
49 range2.NumberFormat = "@"; 50
51 range2.EntireColumn.ColumnWidth = 112 / gridView1.Columns.Count;
52
53 try
54 {
55
56
57 mSheet.PageSetup.LeftHeader = "入库编号:" + DateTime.Now.ToString("yyyyMMdd") + DateTime.Now.Hour.ToString() + " 入库日期" + DateTime.Now.ToString();
58
59 mSheet.PageSetup.LeftFooter = "操作员 复核员 仓库管理员 会计主管 负责人 ";
60
61 mSheet.PageSetup.RightHeader = "Page &P of &N";
62
63 mSheet.PageSetup.Orientation = Excel.XlPageOrientation.xlLandscape;
64
65 // mSheet.PrintPreview(true);
67 }
68 catch (Exception e)
69 {
70 flag = false;
71 }
72
73
74 mSheet.Cells[1, 1] = xlsName.Replace(DateTime.Now.ToShortDateString(),"");
75
//mSheet.get_Range(mSheet.Cells[1, 1], mSheet.Cells[1, gridView1.Columns.Count]).MergeCells = true;
76 mSheet.get_Range(mSheet.Cells[1, 1], mSheet.Cells[1, gridview_column_count]).MergeCells = true;
77
78 mSheet.get_Range(mSheet.Cells[1, 1], mSheet.Cells[1, gridview_column_count]).HorizontalAlignment = Excel.XlVAlign.xlVAlignCenter;
79 mSheet.get_Range(mSheet.Cells[1, 1], mSheet.Cells[1, gridview_column_count]).Font.Size = 16;
80
81
82 for (int i = 1; i <= gridview_column_count; i++)
83 {
84 mSheet.Cells[2, i] = gridView1.Columns[i - 1].Caption.ToString();
85 }
86
87 object[,] objData = new object[gridview_row_count, gridview_column_count];
88 for (int r = 0; r < gridview_row_count; r++)
89 {
90 for (int col = 0; col < gridview_column_count; col++)
91 {
92 objData[r, col] = gridView1.GetRowCellValue(r, gridView1.Columns[col]).ToString();
93 }
94 }
95
96 try
97 {
98
99
100 range3.Value2 = objData;
101
102 mSheet.get_Range(mSheet.Cells[1, 1], mSheet.Cells[gridview_row_count + 2, gridview_column_count]).Rows.AutoFit();
103 mBook.SaveAs(sfd.FileName, Microsoft.Office.Interop.Excel.XlFileFormat.xlExcel7, miss, miss, miss, miss, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange,
104 miss, miss, miss, miss, miss);
105 if(flag)
106 {
107
108 mSheet.PrintPreview(true);
109 }
110 //return true;
111 }
112
113 catch (Exception ex)
114 {
115 //throw new Exception(ex.Message);
116 }
117
118 finally
119 {
120 //mBook.Close(false, miss, miss);
121 mBooks.Close();
122 xlapp.Quit();
123 System.Runtime.InteropServices.Marshal.ReleaseComObject(mRange);
124 System.Runtime.InteropServices.Marshal.ReleaseComObject(mSheet);
125 System.Runtime.InteropServices.Marshal.ReleaseComObject(mBook);
126 System.Runtime.InteropServices.Marshal.ReleaseComObject(mBooks);
127 System.Runtime.InteropServices.Marshal.ReleaseComObject(xlapp);
128 GC.Collect();
129 if (!flag)
130 {
131 MessageBox.Show("导出成功!未连接打印机,无法进行相关打印设置!");
132 }
133
134 }
135 }
136 else
137 {
138 //return false;
139 }
140 }
代码为本人所写(加整理),转载请表明出处.
这两天对代码进行的优化,这是优化之后的代码 ,修正一些BUG 和增加导出效率
