1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
|
using System; using System.Data; using System.IO; using System.Runtime.InteropServices; using System.Threading; using System.Windows.Forms; using Microsoft.Office.Core; using Excel = Microsoft.Office.Interop.Excel; namespace WindowsFormsApplication1 { public partial class Form1 : Form { private bool isStartPrint = false ; public Form1() { InitializeComponent(); } private void button1_Click( object sender, EventArgs e) { //string OriginalPath = Path.GetDirectoryName(Application.ExecutablePath) + "\\IMS.accdb"; ////初始化数据库 //DataBase.OleDbObject.InitDatabase(OriginalPath); //DataBase.OleDbObject.OpenDb(); //int k=DataBase.OleDbObject.Select("select * from ElementInventory").Rows.Count; //初始化要写入Excel的数据 DataTable dt = new DataTable(); for ( int i = 0; i < 11; i++) { DataColumn dc = new DataColumn(); dc.ColumnName = "name" + i; dc.DataType = typeof ( string ); dt.Columns.Add(dc); } for ( int i = 0; i < 30; i++) { DataRow dr = dt.NewRow(); if (i % 2 == 0) { dr[0] = "测试用例" + i + "号,开始测试是否自动换行,以及页面高度自适应。" ; } else { dr[0] = "测试用例" + i + "号" ; } for ( int j = 1; j < 11; j++) { dr[j] = j; } dt.Rows.Add(dr); } if (!isStartPrint) { Thread th = new Thread( delegate () { DataTabletoExcel(dt, "测试.xlsx" ); }); th.Start(); isStartPrint = true ; } else { MessageBox.Show( "打印程序已启用,请稍后……" , "提示" , MessageBoxButtons.OK, MessageBoxIcon.Information); } //DataTabletoExcel(dt, "测试.xlsx"); } public void DataTabletoExcel(System.Data.DataTable dtTemp, string strFileName) { int rowNum = dtTemp.Rows.Count; //先得到dtTemp的行数 int columnNum = dtTemp.Columns.Count; //列数 Excel.Application ExcelApp = new Excel.Application(); //声明一个应用程序类实例 //ExcelApp.DefaultFilePath = ""; //默认文件路径导出excel的路径还是在参数strFileName里设置 //ExcelApp.DisplayAlerts = true; //ExcelApp.SheetsInNewWorkbook = 1;///返回或设置 Microsoft Excel 自动插入到新工作簿中的工作表数目。 Excel.Workbook worksBook = ExcelApp.Workbooks.Add(); //创建一个新工作簿 Excel.Worksheet workSheet = (Excel.Worksheet)worksBook.Worksheets[1]; //在工作簿中得到sheet。 if (workSheet == null ) { System.Diagnostics.Debug.WriteLine( "ERROR: worksheet == null" ); return ; } workSheet.Name = "测试Sheet1" ; //工作表的名称 workSheet.Cells.WrapText = true ; //设置所有列的文本自动换行 workSheet.Cells.EntireRow.AutoFit(); //设置所有列自动调整行高 #region 绘制列 ///自定义方法,向sheet中绘制列 RangeMark(workSheet, "A1" , "A2" , "合并竖列1" ); RangeMark(workSheet, "B1" , "B2" , "合并竖列2" ); RangeMark(workSheet, "C1" , "C2" , "合并竖列3" ); RangeMark(workSheet, "D1" , "D2" , "合并竖列4" ); RangeMark(workSheet, "E1" , "E2" , "合并竖列5" ); RangeMark(workSheet, "F1" , "H1" , "合并横行1" ); RangeMark(workSheet, "F2" , "F2" , "合并横行1.1" ); RangeMark(workSheet, "G2" , "G2" , "合并横行1.2" ); RangeMark(workSheet, "H2" , "H2" , "合并横行1.3" ); RangeMark(workSheet, "I1" , "K1" , "合并横行2" ); RangeMark(workSheet, "I2" , "J2" , "合并横行2.1" ); RangeMark(workSheet, "K2" , "K2" , "合并横行2.2" ); #endregion //将DataTable中的数据导入Excel中 for ( int i = 0; i < rowNum; i++) { for ( int j = 0; j < columnNum; j++) { workSheet.Cells[i + 3, j + 1] = dtTemp.Rows[i][j].ToString(); //文本 } } ///保存路径 string filePath = @"C:\Users\Admin\Desktop\" + strFileName; if (File.Exists(filePath)) { try { File.Delete(filePath); } catch (Exception) { } } ExcelApp.Visible = true ; //------------------------打印页面相关设置-------------------------------- workSheet.PageSetup.PaperSize = Excel.XlPaperSize.xlPaperA4; //纸张大小 workSheet.PageSetup.Orientation = Excel.XlPageOrientation.xlLandscape; //页面横向 //workSheet.PageSetup.Zoom = 75; //打印时页面设置,缩放比例百分之几 workSheet.PageSetup.Zoom = false ; //打印时页面设置,必须设置为false,页高,页宽才有效 workSheet.PageSetup.FitToPagesWide = 1; //设置页面缩放的页宽为1页宽 workSheet.PageSetup.FitToPagesTall = false ; //设置页面缩放的页高自动 workSheet.PageSetup.LeftHeader = "Nigel" ; //页面左上边的标志 workSheet.PageSetup.CenterFooter = "第 &P 页,共 &N 页" ; //页面下标 workSheet.PageSetup.PrintGridlines = true ; //打印单元格网线 workSheet.PageSetup.TopMargin = 1.5 / 0.035; //上边距为2cm(转换为in) workSheet.PageSetup.BottomMargin = 1.5 / 0.035; //下边距为1.5cm workSheet.PageSetup.LeftMargin = 2 / 0.035; //左边距为2cm workSheet.PageSetup.RightMargin = 2 / 0.035; //右边距为2cm workSheet.PageSetup.CenterHorizontally = true ; //文字水平居中 //------------------------打印页面设置结束-------------------------------- ///http://blog.csdn.net/wanmingtom/article/details/6125599 worksBook.PrintPreview(); //打印预览 //worksBook.PrintOutEx(); //直接打印 //worksBook.Close(); //关闭工作空间 //ExcelApp.Quit(); //退出程序 //workSheet.SaveAs(filePath); //另存表 KillProcess(ExcelApp); //杀掉生成的进程 isStartPrint = false ; //打印完毕 GC.Collect(); //垃圾回收机制 } /// <summary> /// 引用Windows句柄,获取程序PID /// </summary> /// <param name="Hwnd"></param> /// <param name="PID"></param> /// <returns></returns> [DllImport( "User32.dll" )] public static extern int GetWindowThreadProcessId(IntPtr Hwnd, out int PID); /// <summary> /// 杀掉生成的进程 /// </summary> /// <param name="AppObject">进程程对象</param> private static void KillProcess(Excel.Application AppObject) { int Pid = 0; IntPtr Hwnd = new IntPtr(AppObject.Hwnd); System.Diagnostics.Process p = null ; try { GetWindowThreadProcessId(Hwnd, out Pid); p = System.Diagnostics.Process.GetProcessById(Pid); if (p != null ) { p.Kill(); p.Dispose(); } } catch (Exception ex) { System.Diagnostics.Debug.WriteLine( "进程关闭失败!异常信息:" + ex); } } /// <summary> /// 创建表头单元格,包括合并单元格 /// </summary> /// <param name="workSheet">工作表</param> /// <param name="startCell">单元格起始格编号</param> /// <param name="endCell">单元格结束编号</param> /// <param name="strText">单元格名称</param> private static bool RangeMark(Excel.Worksheet workSheet, string startCell, string endCell, string strText) { //创建一个区域对象。第一个参数是开始格子号,第二个参数是终止格子号。比如选中A1——D3这个区域。 Excel.Range range = (Excel.Range)workSheet.get_Range(startCell, endCell); if (range == null ) { System.Diagnostics.Debug.WriteLine( "ERROR: range == null" ); return false ; } range.Merge(0); //合并方法,0的时候直接合并为一个单元格 range.Font.Size = 16; //字体大小 range.Font.Name = "黑体" ; //字体 range.WrapText = true ; //文本自动换行 range.EntireRow.AutoFit(); //自动调整行高 //range.RowHeight = 20; //range.EntireColumn.AutoFit(); //自动调整列宽 range.ColumnWidth = 15; range.HorizontalAlignment = XlVAlign.xlVAlignCenter; //横向居中 range.Value = strText; //合并单元格之后,设置其中的文本 range.Interior.ColorIndex = 20; //填充颜色 range.Cells.Borders.LineStyle = 1; //设置单元格边框的粗细 return true ; } } } |
以上就是C#操作excel打印的示例的详细内容,更多关于c# 打印的资料请关注服务器之家其它相关文章!
原文链接:https://www.cnblogs.com/pilgrim/p/6013207.html