1 using System;
2 using System;
3 using System.Collections.Generic;
4 using System.Linq;
5 using System.Text;
6 using Excel = Microsoft.Office.Interop.Excel;
7 using System.Collections;
8 using System.Data;
9 using System.Runtime.InteropServices;
10 using System.Reflection;
11
12 namespace BeiJing.ISS.Common
13 {
14 public class Table_ToExcel
15 {
16 public Excel.Application m_xlApp = null;
17
18 #region 外部接口
19 /// <summary>
20 /// 将一个DataTable的数据导出多个Excel文件（每一个Excel文件的数据行数由函数控制）
21
22 /// </summary>
23 /// <param name="tempDataTable">数据源</param>
24 /// <param name="PathFileName">保存excel的路径</param>
25 /// <param name="ExcelRows">每一个Excel文件的行数</param>
26 /// <param name="ExcelVersion">导出Excel的版本(2003,2007)</param>
27 public void u_DataTableToExcel1(DataTable tempDataTable, string filepath, string filename, long ExcelRows, string ExcelVersion)
28 {
29 if (tempDataTable == null)
30 {
31 return;
32 }
33 long rowNum = tempDataTable.Rows.Count;//导出数据的行数
34 int columnNum = tempDataTable.Columns.Count;//导出数据的列数
35 string sFileName = "";
36 if (rowNum > ExcelRows)
37 {
38 long excelRows = ExcelRows;//定义个excel文件显示的行数，最大的行数为65535，不能超过65535
39 int scount = (int)(rowNum / excelRows);//生成excel文件的个数
40 if (scount * excelRows < rowNum)//当总行数不被excelRows整除时，经过四舍五入可能excel的个数不准
41 {
42 scount = scount + 1;
43 }
44 for (int sc = 1; sc <= scount; sc++)
45 {
46 int init = int.Parse(((sc - 1) * excelRows).ToString());
47 sFileName = filepath + sc.ToString();
48 long start = init;
49 long end = sc * excelRows - 1;
50 if (sc == scount)
51 end = rowNum - 1;
52 u_OutExcel(tempDataTable, start, end, filepath, filename, ExcelVersion);
53 }
54 }
55 else
56 {
57 u_OutExcel(tempDataTable, 0, rowNum - 1, filepath, filename, ExcelVersion);
58 }
59 tempDataTable = null;
60 }
61 /// <summary>
62 /// 将一个DataTable的数据导出一个Excel文件:可能包含多个sheet文件，由sheet行数决定 (每一个sheet文件的行数由函数控制)
63 /// </summary>
64 /// <param name="tempDataTable">数据源</param>
65 /// <param name="PathFileName">导出excel的路径</param>
66 /// <param name="SheetRows">excel的文件中sheet的行数</param>
67 /// <param name="ExcelVersion">导出Excel的版本</param>
68 public void u_DataTableToExcel2(DataTable tempDataTable, string Path, string FileName, long SheetRows, string ExcelVersion, bool BudgetStatistics=false)
69 {
70 if (tempDataTable == null)
71 {
72 return;
73 }
74 long rowNum = tempDataTable.Rows.Count;//行数
75 int columnNum = tempDataTable.Columns.Count;//列数
76 Excel.Application m_xlApp = new Excel.Application();
77 m_xlApp.DisplayAlerts = false;//不显示更改提示
78 m_xlApp.Visible = false;
79 m_xlApp.UserControl = true;
80 m_xlApp.EnableLargeOperationAlert = false;
81 m_xlApp.EnableLivePreview = false;
82
83 Excel.Workbooks workbooks = m_xlApp.Workbooks;
84 Excel.Workbook workbook = workbooks.Add(Excel.XlWBATemplate.xlWBATWorksheet);
85 Excel.Worksheet worksheet = (Excel.Worksheet)workbook.Worksheets[1];//取得sheet1
86 try
87 {
88 if (rowNum > SheetRows)//单张Sheet表格最大行数
89 {
90 long sheetRows = SheetRows;//定义每页显示的行数,行数必须小于65536
91 int scount = (int)(rowNum / sheetRows);//导出数据生成的表单数
92 if (scount * sheetRows < rowNum)//当总行数不被sheetRows整除时，经过四舍五入可能页数不准
93 {
94 scount = scount + 1;
95 }
96 for (int sc = 1; sc <= scount; sc++)
97 {
98 if (sc > 1)
99 {
100 object missing = System.Reflection.Missing.Value;
101 worksheet = (Excel.Worksheet)workbook.Worksheets.Add(missing, missing, missing, missing);//添加一个sheet
102 }
103 else
104 {
105 worksheet = (Excel.Worksheet)workbook.Worksheets[sc];//取得sheet1
106 }
107 string[,] datas = new string[sheetRows + 1, columnNum];
108
109 for (int i = 0; i < columnNum; i++) //写入字段
110 {
111 datas[0, i] = tempDataTable.Columns[i].Caption;//表头信息
112 }
113 Excel.Range range = worksheet.Range[worksheet.Cells[1, 1], worksheet.Cells[1, columnNum]];
114 range.Interior.ColorIndex = 15;//15代表灰色
115 range.Font.Bold = true;
116 range.Font.Size = 9;
117 int init = int.Parse(((sc - 1) * sheetRows).ToString());
118 int r = 0;
119 int index = 0;
120 int result;
121 if (sheetRows * sc >= rowNum)
122 {
123 result = (int)rowNum;
124 }
125 else
126 {
127 result = int.Parse((sheetRows * sc).ToString());
128 }
129 for (r = init; r < result; r++)
130 {
131 index = index + 1;
132 for (int i = 0; i < columnNum; i++)
133 {
134 object obj = tempDataTable.Rows[r][tempDataTable.Columns[i].ToString()];
135 datas[index, i] = obj == null ? "" : "'" + obj.ToString().Trim();//在obj.ToString()前加单引号是为了防止自动转化格式
136 }
137 }
138 Excel.Range fchR = worksheet.Range[worksheet.Cells[1, 1], worksheet.Cells[index + 1, columnNum]];
139 fchR.Value2 = datas;
140 worksheet.Columns.EntireColumn.AutoFit();//列宽自适应。
141 m_xlApp.WindowState = Excel.XlWindowState.xlMaximized;//Sheet表最大化
142 range = worksheet.Range[worksheet.Cells[1, 1], worksheet.Cells[index + 1, columnNum]];
143 //range.Interior.ColorIndex = 15;//15代表灰色
144 range.Font.Size = 9;
145 range.RowHeight = 14.25;
146 range.Borders.LineStyle = 1;
147 range.HorizontalAlignment = 1;
148 }
149 }
150 else
151 {
152 string[,] datas = new string[rowNum + 1, columnNum];
153 for (int i = 0; i < columnNum; i++) //写入字段
154 {
155 datas[0, i] = tempDataTable.Columns[i].Caption;
156 }
157 Excel.Range range = worksheet.Range[worksheet.Cells[1, 1], worksheet.Cells[1, columnNum]];
158 range.Interior.ColorIndex = 15;//15代表灰色
159 range.Font.Bold = true;
160 range.Font.Size = 9;
161 int r = 0;
162 for (r = 0; r < rowNum; r++)
163 {
164 if (BudgetStatistics && r == rowNum - 1)//预算表统计导出 最后一行
165 {
166 for (int i = 0; i < columnNum; i++)
167 {
168 if (i == 0)
169 {
170 datas[r + 1, i] = "'总计：";
171 }
172 else
173 {
174 object obj = tempDataTable.Rows[r][tempDataTable.Columns[i].ToString()];
175 if (obj==null || obj == DBNull.Value)
176 {
177 //obj = "0.00";
178 }
179 datas[r + 1, i] = obj == null ? "" : "'" + obj.ToString().Trim();//在obj.ToString()前加单引号是为了防止自动转化格式
180 }
181 }
182 }
183 else
184 {
185 for (int i = 0; i < columnNum; i++)
186 {
187 object obj = tempDataTable.Rows[r][tempDataTable.Columns[i].ToString()];
188 datas[r + 1, i] = obj == null ? "" : "'" + obj.ToString().Trim();//在obj.ToString()前加单引号是为了防止自动转化格式
189 }
190 }
191 }
192 Excel.Range fchR = worksheet.Range[worksheet.Cells[1, 1], worksheet.Cells[rowNum + 1, columnNum]];
193 fchR.Value2 = datas;
194 worksheet.Columns.EntireColumn.AutoFit();//列宽自适应。
195 m_xlApp.WindowState = Excel.XlWindowState.xlMaximized;
196 range = worksheet.Range[worksheet.Cells[1, 1], worksheet.Cells[rowNum + 1, columnNum]];
197 //range.Interior.ColorIndex = 15;//15代表灰色
198 range.Font.Size = 9;
199 range.RowHeight = 14.25;
200 range.Borders.LineStyle = 1;
201 range.HorizontalAlignment = 1;
202 }
203 workbook.Saved = true;
204 switch (ExcelVersion)
205 {
206 case "2003":
207 object ob = System.Reflection.Missing.Value;
208 workbook.SaveAs(Path + FileName, Excel.XlFileFormat.xlExcel7, ob, ob, ob, ob, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange, ob, ob, ob, ob, ob);
209 workbook.Save();
210 //workbook = null;
211 //worksheet = null;
212 //workbooks = null;
213 //m_xlApp.Quit();
214 //m_xlApp = null;
215 workbook.Close(null, null, null);
216 m_xlApp.Workbooks.Close();
217 m_xlApp.Quit();
218 Marshal.ReleaseComObject((object)m_xlApp);
219 Marshal.ReleaseComObject((object)workbook);
220 Marshal.ReleaseComObject((object)worksheet);
221 break;
222 case "2007":
223 workbook.SaveCopyAs(Path + FileName);
224 workbook.Save();
225 //workbook = null;
226 //worksheet = null;
227 //workbooks = null;
228 //m_xlApp.Quit();
229 //m_xlApp = null;
230 workbook.Close(null, null, null);
231 m_xlApp.Workbooks.Close();
232 m_xlApp.Quit();
233 Marshal.ReleaseComObject((object)m_xlApp);
234 Marshal.ReleaseComObject((object)workbook);
235 Marshal.ReleaseComObject((object)worksheet);
236 break;
237 default: break;
238 }
239 KillProcess("EXCEL");//杀死excel进程
240 tempDataTable = null;
241 }
242 catch (Exception ex)
243 {
244 tempDataTable = null;
245 throw new Exception(ex.Message.ToString());
246 }
247 finally
248 {
249 KillProcess("EXCEL");//杀死excel进程
250 tempDataTable = null;
251 }
252 }
253
254 public void u_DataTableToExcel3(DataTable tempDataTable, string TemplatePath, int StartNum, List<SetExcelContent> Eclist, string tableDesigners, string Path, string FileName, long SheetRows, string ExcelVersion)
255 {
256 if (tempDataTable == null)
257 {
258 return;
259 }
260 object missing = Missing.Value;
261 long rowNum = tempDataTable.Rows.Count;//行数
262 int columnNum = tempDataTable.Columns.Count;//列数
263 Excel.Application m_xlApp = new Excel.Application();
264 m_xlApp.DisplayAlerts = false;//不显示更改提示
265 m_xlApp.Visible = false;
266 m_xlApp.UserControl = true;
267 Excel.Workbook workbook = m_xlApp.Workbooks.Open(TemplatePath, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing);
268 Excel.Worksheet worksheet = (Excel.Worksheet)workbook.Sheets.get_Item(1);//取得第一个工作簿
269 Excel.Worksheet sheet = (Excel.Worksheet)workbook.Sheets.get_Item(1);//取得第一个工作簿
270 //sheet.Name = "Sheet1";
271 if (sheet == null)//工作簿中没有工作表
272 return;
273
274 //设置模板中的表头
275 foreach (SetExcelContent ec in Eclist)
276 {
277 sheet.Cells[ec.X, ec.Y] = ec.Content.ToString();
278 }
279
280 try
281 {
282 int cell_r = 0, cell_c = 0;
283 for (int i = 1; i <= rowNum; i++)
284 {
285 cell_r = i + StartNum;
286 int row_index = i - 1;//DataTable的行是从0开始的
287 for (int j = 0; j < columnNum; j++)
288 {
289 cell_c = j + 1;//Excel表格的列
290 object obj = tempDataTable.Rows[row_index][tempDataTable.Columns[j].ToString()];
291 sheet.Cells[cell_r, cell_c] = obj == null ? "" : "'" + obj.ToString().Trim();//在obj.ToString()前加单引号是为了防止自动转化格式
292 }
293 }
294
295 //添加表底信息
296 if (!string.IsNullOrEmpty(tableDesigners))
297 {
298 string [] arrDesigner=tableDesigners.Split(new string [] { "~|~" },StringSplitOptions.RemoveEmptyEntries);
299 sheet.Cells[cell_r + 2, 1] = arrDesigner[0];
300 sheet.Cells[cell_r + 2, 2] = arrDesigner[1];
301 sheet.Cells[cell_r + 2, 3] = arrDesigner[2];
302 sheet.Cells[cell_r + 2, 4] = arrDesigner[3];
303
304 //调整Excel的样式
305 Excel.Range range = sheet.Range[worksheet.Cells[StartNum + 1, 1], sheet.Cells[rowNum + StartNum + 2, columnNum]];//结束的要加上表底
306 range.Borders.LineStyle = 1;//单元格加边框
307 }
308 else
309 {
310 //调整Excel的样式
311 Excel.Range range = sheet.Range[worksheet.Cells[StartNum + 1, 1], sheet.Cells[rowNum + StartNum, columnNum]];
312 range.Borders.LineStyle = 1;//单元格加边框
313 }
314
315 workbook.Saved = true;
316 switch (ExcelVersion)
317 {
318 case "2003":
319 object ob = System.Reflection.Missing.Value;
320 workbook.SaveCopyAs(Path + FileName);
321 workbook.Save();
322 workbook.Close(null, null, null);
323 m_xlApp.Workbooks.Close();
324 m_xlApp.Quit();
325 Marshal.ReleaseComObject((object)m_xlApp);
326 Marshal.ReleaseComObject((object)workbook);
327 Marshal.ReleaseComObject((object)worksheet);
328 break;
329 case "2007":
330 workbook.SaveCopyAs(Path + FileName);
331 workbook.Save();
332 workbook.Close(null, null, null);
333 m_xlApp.Workbooks.Close();
334 m_xlApp.Quit();
335 Marshal.ReleaseComObject((object)m_xlApp);
336 Marshal.ReleaseComObject((object)workbook);
337 Marshal.ReleaseComObject((object)worksheet);
338 break;
339 default: break;
340 }
341 KillProcess("EXCEL");//杀死excel进程
342 tempDataTable = null;
343 }
344 catch (Exception ex)
345 {
346 tempDataTable = null;
347 throw new Exception(ex.Message.ToString());
348 }
349 finally
350 {
351 KillProcess("EXCEL");//杀死excel进程
352 tempDataTable = null;
353 }
354
355 }
356
357 //派发勘察，生成楼层格式表，楼宇格式表
358 public void u_DataTableToExcel33(DataTable tempDataTable, string TemplatePath, int StartNum, string Path, string FileName, long SheetRows, string ExcelVersion)
359 {
360 if (tempDataTable == null)
361 {
362 return;
363 }
364 object missing = Missing.Value;
365 long rowNum = tempDataTable.Rows.Count;//行数
366 int columnNum = tempDataTable.Columns.Count;//列数
367 Excel.Application m_xlApp = new Excel.Application();
368 m_xlApp.DisplayAlerts = false;//不显示更改提示
369 m_xlApp.Visible = false;
370 m_xlApp.UserControl = true;
371 Excel.Workbook workbook = m_xlApp.Workbooks.Open(TemplatePath, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing);
372 Excel.Worksheet worksheet = (Excel.Worksheet)workbook.Sheets.get_Item(1);//取得第一个工作簿
373 Excel.Worksheet sheet = (Excel.Worksheet)workbook.Sheets.get_Item(1);//取得第一个工作簿
374 //sheet.Name = "Sheet1";
375 if (sheet == null)//工作簿中没有工作表
376 return;
377
378 try
379 {
380 int cell_r = 0, cell_c = 0;
381 for (int i = 1; i <= rowNum; i++)
382 {
383 cell_r = i + StartNum;
384 int row_index = i - 1;//DataTable的行是从0开始的
385 for (int j = 0; j < columnNum; j++)
386 {
387 cell_c = j + 1;//Excel表格的列
388 object obj = tempDataTable.Rows[row_index][tempDataTable.Columns[j].ToString()];
389 if (cell_c == 1)
390 {
391 sheet.Cells[cell_r, cell_c] = i;//第一列为序号
392 }
393 else
394 {
395 sheet.Cells[cell_r, cell_c] = obj == null ? "" : "'" + obj.ToString().Trim();//在obj.ToString()前加单引号是为了防止自动转化格式
396 }
397 }
398 }
399
400 //调整Excel的样式
401 Excel.Range range = sheet.Range[worksheet.Cells[StartNum + 1, 1], sheet.Cells[rowNum + StartNum, columnNum]];
402 range.Borders.LineStyle = 1;//单元格加边框
403
404 workbook.Saved = true;
405 switch (ExcelVersion)
406 {
407 case "2003":
408 object ob = System.Reflection.Missing.Value;
409 workbook.SaveCopyAs(Path + FileName);
410 workbook.Save();
411 workbook.Close(null, null, null);
412 m_xlApp.Workbooks.Close();
413 m_xlApp.Quit();
414 Marshal.ReleaseComObject((object)m_xlApp);
415 Marshal.ReleaseComObject((object)workbook);
416 Marshal.ReleaseComObject((object)worksheet);
417 break;
418 case "2007":
419 workbook.SaveCopyAs(Path + FileName);
420 workbook.Save();
421 workbook.Close(null, null, null);
422 m_xlApp.Workbooks.Close();
423 m_xlApp.Quit();
424 Marshal.ReleaseComObject((object)m_xlApp);
425 Marshal.ReleaseComObject((object)workbook);
426 Marshal.ReleaseComObject((object)worksheet);
427 break;
428 default: break;
429 }
430 KillProcess("EXCEL");//杀死excel进程
431 tempDataTable = null;
432 }
433 catch (Exception ex)
434 {
435 tempDataTable = null;
436 throw new Exception(ex.Message.ToString());
437 }
438 finally
439 {
440 KillProcess("EXCEL");//杀死excel进程
441 tempDataTable = null;
442 }
443
444 }
445
446 /// <summary>
447 /// 物料表导出专用
448 /// </summary>
449 public void u_DataTableToExcel4(DataTable tempDataTable, string TemplatePath, int StartNum, List<SetExcelContent> Eclist, string tableDesigners, string Path, string FileName, long SheetRows, string ExcelVersion)
450 {
451 if (tempDataTable == null)
452 {
453 return;
454 }
455 object missing = Missing.Value;
456 long rowNum = tempDataTable.Rows.Count;//行数
457 int columnNum = tempDataTable.Columns.Count;//列数
458 Excel.Application m_xlApp = new Excel.Application();
459 m_xlApp.DisplayAlerts = false;//不显示更改提示
460 m_xlApp.Visible = false;
461 m_xlApp.UserControl = true;
462 Excel.Workbook workbook = m_xlApp.Workbooks.Open(TemplatePath, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing);
463 Excel.Worksheet worksheet = (Excel.Worksheet)workbook.Sheets.get_Item(1);//取得第一个工作簿
464 Excel.Worksheet sheet = (Excel.Worksheet)workbook.Sheets.get_Item(1);//取得第一个工作簿
465 sheet.Name = "Sheet1";
466 if (sheet == null)//工作簿中没有工作表
467 return;
468
469 //设置模板中的表头
470 foreach (SetExcelContent ec in Eclist)
471 {
472 sheet.Cells[ec.X, ec.Y] = ec.Content.ToString();
473 }
474 int count = 0;
475 try
476 {
477 string preDeviceSort = "", curDeviceSort = "";
478
479 int cell_r = 0, cell_c = 0;
480 for (int i = 1; i <= rowNum; i++)
481 {
482 cell_r = i + StartNum;
483 int row_index = i - 1;//DataTable的行是从0开始的
484 if (!string.IsNullOrEmpty(preDeviceSort) && preDeviceSort.Equals(curDeviceSort))//同一个分类
485 {
486 cell_r = cell_r + count;
487 }
488 for (int j = 0; j < columnNum; j++)
489 {
490 cell_c = j + 1;//Excel表格的列
491 object obj = tempDataTable.Rows[row_index][tempDataTable.Columns[j].ToString()];
492 string cellValue = obj == null ? "" : "'" + obj.ToString().Trim();//在obj.ToString()前加单引号是为了防止自动转化格式
493
494 if (tempDataTable.Columns[j].ColumnName.Equals("DeviceSort"))//分类
495 {
496 curDeviceSort = cellValue;
497 if (preDeviceSort != curDeviceSort)//不同分类
498 {
499 sheet.Cells[cell_r, 1] = curDeviceSort;
500
501 sheet.Cells.get_Range("A" + cell_r, "F" + cell_r).MergeCells = true;//合并单元格
502 sheet.Cells.get_Range("A" + cell_r, "F" + cell_r).Font.Size = "12";//设置字体
503 sheet.Cells.get_Range("A" + cell_r, "F" + cell_r).Interior.Color = System.Drawing.Color.LightGray;//设置单元格背景色
504
505 cell_r = cell_r + 1;//另起一行
506
507 count++;//累计不同分类数
508 }
509 else
510 {
511 cell_c = cell_c + 1;//单元格数加1下面才能减
512 }
513 continue;
514 }
515 cell_c = cell_c - 1;
516 sheet.Cells[cell_r, cell_c] = cellValue;//数据的第一行
517 }
518 preDeviceSort = curDeviceSort;
519 }
520
521 //添加表底信息
522 if (!string.IsNullOrEmpty(tableDesigners))
523 {
524 string[] arrDesigner = tableDesigners.Split(new string[] { "~|~" }, StringSplitOptions.RemoveEmptyEntries);
525 sheet.Cells[cell_r + 2, 1] = arrDesigner[0];
526 sheet.Cells[cell_r + 2, 2] = arrDesigner[1];
527 sheet.Cells[cell_r + 2, 3] = arrDesigner[2];
528 sheet.Cells[cell_r + 2, 4] = arrDesigner[3];
529
530 //调整Excel的样式
531 Excel.Range range = sheet.Range[worksheet.Cells[StartNum + 1, 1], sheet.Cells[cell_r + StartNum + 2, columnNum-1]];//结束的要加上表底
532 range.Borders.LineStyle = 1;//单元格加边框
533 }
534 else
535 {
536 //调整Excel的样式
537 Excel.Range range = sheet.Range[worksheet.Cells[StartNum + 1, 1], sheet.Cells[cell_r + StartNum, columnNum-1]];
538 range.Borders.LineStyle = 1;//单元格加边框
539 }
540
541 workbook.Saved = true;
542 switch (ExcelVersion)
543 {
544 case "2003":
545 object ob = System.Reflection.Missing.Value;
546 workbook.SaveCopyAs(Path + FileName);
547 workbook.Save();
548 workbook.Close(null, null, null);
549 m_xlApp.Workbooks.Close();
550 m_xlApp.Quit();
551 Marshal.ReleaseComObject((object)m_xlApp);
552 Marshal.ReleaseComObject((object)workbook);
553 Marshal.ReleaseComObject((object)worksheet);
554 break;
555 case "2007":
556 workbook.SaveCopyAs(Path + FileName);
557 workbook.Save();
558 workbook.Close(null, null, null);
559 m_xlApp.Workbooks.Close();
560 m_xlApp.Quit();
561 Marshal.ReleaseComObject((object)m_xlApp);
562 Marshal.ReleaseComObject((object)workbook);
563 Marshal.ReleaseComObject((object)worksheet);
564 break;
565 default: break;
566 }
567 KillProcess("EXCEL");//杀死excel进程
568 tempDataTable = null;
569 }
570 catch (Exception ex)
571 {
572 tempDataTable = null;
573 throw new Exception(ex.Message.ToString());
574 }
575 finally
576 {
577 KillProcess("EXCEL");//杀死excel进程
578 tempDataTable = null;
579 }
580
581 }
582
583 /// <summary>
584 /// 预算批量导出
585 /// </summary>
586 public void u_DataTableToExcel5(List<BudgetExport> listBudget, string TemplatePath, string Path, string FileName, long SheetRows, string ExcelVersion)
587 {
588 object missing = Missing.Value;
589 Excel.Application m_xlApp = new Excel.Application();
590 m_xlApp.DisplayAlerts = false;//不显示更改提示
591 m_xlApp.Visible = false;
592 m_xlApp.UserControl = true;
593 Excel.Workbook workbook = m_xlApp.Workbooks.Open(TemplatePath, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing);
594 Excel.Worksheet worksheet = null;
595
596 int bookCount = workbook.Sheets.Count;
597
598 DataTable tempDataTable = null;
599 int StartNum = 0;
600 List<SetExcelContent> Eclist=null;
601 string tableDesigners = string.Empty;
602 string SheetName = string.Empty;
603 for (int z = 0; z < listBudget.Count; z++)
604 {
605 BudgetExport be=listBudget[z];
606 tempDataTable = be.tempDataTable;
607 StartNum = be.StartNum;
608 Eclist = be.Eclist;
609 tableDesigners = be.tableDesigners;
610 SheetName = be.SheetName;
611
612 if (tempDataTable == null)
613 {
614 continue;
615 }
616 long rowNum = tempDataTable.Rows.Count;//行数
617 int columnNum = tempDataTable.Columns.Count;//列数
618
619 worksheet = (Excel.Worksheet)workbook.Sheets.get_Item(z+1);//取得第一个工作簿
620 Excel.Worksheet sheet = (Excel.Worksheet)workbook.Sheets.get_Item(z+1);//取得第一个工作簿
621 sheet.Name = SheetName;
622 if (sheet == null)//工作簿中没有工作表
623 return;
624
625 //设置模板中的表头
626 if (Eclist != null && Eclist.Count > 0)//表头不为空
627 {
628 foreach (SetExcelContent ec in Eclist)
629 {
630 sheet.Cells[ec.X, ec.Y] = ec.Content.ToString();
631 }
632 }
633
634 try
635 {
636 int cell_r = 0, cell_c = 0;
637 for (int i = 1; i <= rowNum; i++)
638 {
639 cell_r = i + StartNum;
640 int row_index = i - 1;//DataTable的行是从0开始的
641 for (int j = 0; j < columnNum; j++)
642 {
643 cell_c = j + 1;//Excel表格的列
644 object obj = tempDataTable.Rows[row_index][tempDataTable.Columns[j].ToString()];
645 sheet.Cells[cell_r, cell_c] = obj == null ? "" : "'" + obj.ToString().Trim();//在obj.ToString()前加单引号是为了防止自动转化格式
646 }
647 }
648
649 //添加表底信息
650 if (!string.IsNullOrEmpty(tableDesigners))
651 {
652 string[] arrDesigner = tableDesigners.Split(new string[] { "~|~" }, StringSplitOptions.RemoveEmptyEntries);
653 sheet.Cells[cell_r + 2, 1] = arrDesigner[0];
654 sheet.Cells[cell_r + 2, 2] = arrDesigner[1];
655 sheet.Cells[cell_r + 2, 3] = arrDesigner[2];
656 sheet.Cells[cell_r + 2, 4] = arrDesigner[3];
657
658 //调整Excel的样式
659 Excel.Range range = sheet.Range[worksheet.Cells[StartNum + 1, 1], sheet.Cells[rowNum + StartNum + 2, columnNum]];//结束的要加上表底
660 range.Borders.LineStyle = 1;//单元格加边框
661 }
662 else
663 {
664 //调整Excel的样式
665 Excel.Range range = sheet.Range[worksheet.Cells[StartNum + 1, 1], sheet.Cells[rowNum + StartNum, columnNum]];
666 range.Borders.LineStyle = 1;//单元格加边框
667 }
668 }
669 catch (Exception ex)
670 {
671 tempDataTable = null;
672 throw new Exception(ex.Message.ToString());
673 }
674 finally
675 {
676 tempDataTable = null;
677 }
678 }
679
680 workbook.Saved = true;
681 switch (ExcelVersion)
682 {
683 case "2003":
684 object ob = System.Reflection.Missing.Value;
685 workbook.SaveCopyAs(Path + FileName);
686 workbook.Save();
687 workbook.Close(null, null, null);
688 m_xlApp.Workbooks.Close();
689 m_xlApp.Quit();
690 Marshal.ReleaseComObject((object)m_xlApp);
691 Marshal.ReleaseComObject((object)workbook);
692 if (worksheet != null)
693 {
694 Marshal.ReleaseComObject((object)worksheet);
695 }
696 break;
697 case "2007":
698 workbook.SaveCopyAs(Path + FileName);
699 workbook.Save();
700 workbook.Close(null, null, null);
701 m_xlApp.Workbooks.Close();
702 m_xlApp.Quit();
703 Marshal.ReleaseComObject((object)m_xlApp);
704 Marshal.ReleaseComObject((object)workbook);
705 if (worksheet != null)
706 {
707 Marshal.ReleaseComObject((object)worksheet);
708 }
709 break;
710 default: break;
711 }
712 KillProcess("EXCEL");//杀死excel进程
713 tempDataTable = null;
714 }
715
716 /// <summary>
717 /// 按项目-信源统计
718 /// </summary>
719 public void u_DataTableToExcel6(DataTable tempDataTable, string Path, string FileName, long SheetRows, string ExcelVersion, bool Statistics = false)
720 {
721 if (tempDataTable == null)
722 {
723 return;
724 }
725 object missing = Missing.Value;
726 long rowNum = tempDataTable.Rows.Count;//行数
727 int columnNum = tempDataTable.Columns.Count;//列数
728 Excel.Application m_xlApp = new Excel.Application();
729 m_xlApp.DisplayAlerts = false;//不显示更改提示
730 m_xlApp.Visible = false;
731 m_xlApp.UserControl = true;
732
733 Excel.Workbooks workbooks = m_xlApp.Workbooks;
734 Excel.Workbook workbook = workbooks.Add(Excel.XlWBATemplate.xlWBATWorksheet);
735 Excel.Worksheet sheet = (Excel.Worksheet)workbook.Worksheets[1];//取得sheet1
736 sheet.Name = "Sheet1";
737 if (sheet == null)//工作簿中没有工作表
738 return;
739
740 try
741 {
742 string SerialNum = "", StationNum = "", StationName = "", lastSerialNum = "", lastStationNum = "", lastStationName = "";
743
744 for (int i = 0; i < columnNum; i++) //写入字段
745 {
746 sheet.Cells[1, i + 1] = tempDataTable.Columns[i].Caption;
747 }
748 Excel.Range range = sheet.Range[sheet.Cells[1, 1], sheet.Cells[1, columnNum]];
749 range.Interior.ColorIndex = 15;//15代表灰色
750 range.Font.Bold = true;
751 range.Font.Size = 9;
752
753 int cell_r = 0, cell_c = 0;
754 int sameCount = 0;
755 bool firstMergeFlag = true;
756 for (int i = 1; i <= rowNum; i++)
757 {
758 cell_r = i + 1;//表头算1列
759 int row_index = i - 1;//DataTable的行是从0开始的
760 for (int j = 0; j < columnNum; j++)
761 {
762 cell_c = j + 1;//Excel表格的列
763 object obj = tempDataTable.Rows[row_index][tempDataTable.Columns[j].ToString()];
764 sheet.Cells[cell_r, cell_c] = obj == null ? "" : "'" + obj.ToString().Trim();//在obj.ToString()前加单引号是为了防止自动转化格式
765
766 if (j < 3)
767 {
768 if (tempDataTable.Columns[j].Caption == "序号")
769 {
770 SerialNum = obj.ToString().Trim();
771 }
772 else if (tempDataTable.Columns[j].Caption == "站号")
773 {
774 StationNum = obj.ToString().Trim();
775 }
776 else if (tempDataTable.Columns[j].Caption == "站名")
777 {
778 StationName = obj.ToString().Trim();
779 }
780 }
781 }
782 if (SerialNum != lastSerialNum && StationNum != lastStationNum && StationName != lastStationName)//不一样时才赋值
783 {
784 if (cell_r > 2)
785 {
786 int startRowIndex = 0;
787 int endRowIndex = 0;
788 if (firstMergeFlag)//第一次合并
789 {
790 startRowIndex = 2;
791 endRowIndex = startRowIndex + sameCount;
792 }
793 else
794 {
795 startRowIndex = cell_r - sameCount-1;
796 endRowIndex = cell_r-1;
797 }
798 //合并单元格
799 //sheet.Cells.get_Range("A" + startRowIndex, "A" + endRowIndex).MergeCells = true;
800 //sheet.Cells.get_Range("B" + startRowIndex, "B" + endRowIndex).MergeCells = true;
801 //sheet.Cells.get_Range("C" + startRowIndex, "C" + endRowIndex).MergeCells = true;
802
803 range = sheet.Cells.get_Range("A" + startRowIndex, "A" + endRowIndex);//get_Range("起始单元格，如A1","结束单元格，如G10"); 即表示一块区域
804 range.Clear();
805 range.Font.Size = 12;//设置字体大小
806 range.Font.Bold = true;//设置字体加粗
807 range.Merge(0);//单元格合并
808 range.Value = "'" + lastSerialNum;//设置单元格内容
809 range.Borders.LineStyle = 1;//加边框
810 range.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
811 range.VerticalAlignment = Excel.XlVAlign.xlVAlignCenter;
812 range = null;
813 range = sheet.Cells.get_Range("B" + startRowIndex, "B" + endRowIndex);
814 range.Clear();
815 range.Font.Size = 12;//设置字体大小
816 range.Font.Bold = true;//设置字体加粗
817 range.Merge(0);//单元格合并
818 range.Value = "'" + lastStationNum;//设置单元格内容
819 range.Borders.LineStyle = 1;//加边框
820 range.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
821 range.VerticalAlignment = Excel.XlVAlign.xlVAlignCenter;
822 range = null;
823 range = sheet.Cells.get_Range("C" + startRowIndex, "C" + endRowIndex);
824 range.Clear();
825 range.Font.Size = 12;//设置字体大小
826 range.Font.Bold = true;//设置字体加粗
827 range.Merge(0);//单元格合并
828 range.Value = "'" + lastStationName;//设置单元格内容
829 range.Borders.LineStyle = 1;//加边框
830 range.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
831 range.VerticalAlignment = Excel.XlVAlign.xlVAlignCenter;
832 range = null;
833
834 sameCount = 0;
835 firstMergeFlag = false;
836 }
837
838 lastSerialNum = SerialNum;
839 lastStationNum = StationNum;
840 lastStationName = StationName;
841 }
842 else
843 {
844 if (firstMergeFlag && string.IsNullOrEmpty(lastSerialNum) && string.IsNullOrEmpty(lastStationNum) && string.IsNullOrEmpty(lastStationName))
845 {
846 lastSerialNum = SerialNum;
847 lastStationNum = StationNum;
848 lastStationName = StationName;
849 }
850 if (cell_r > 2 && SerialNum == lastSerialNum && StationNum == lastStationNum && StationName == lastStationName)
851 {
852 sameCount++;//序号，站号，站名相同的个数
853 }
854 else
855 {
856 //处理空值合并
857 if (cell_r > 2)
858 {
859 int startRowIndex = 0;
860 int endRowIndex = 0;
861 if (firstMergeFlag)//第一次合并
862 {
863 startRowIndex = 2;
864 endRowIndex = startRowIndex + sameCount;
865 }
866 else
867 {
868 startRowIndex = cell_r - sameCount - 1;
869 endRowIndex = cell_r - 1;
870 }
871 //合并单元格
872 //sheet.Cells.get_Range("A" + startRowIndex, "A" + endRowIndex).MergeCells = true;
873 //sheet.Cells.get_Range("B" + startRowIndex, "B" + endRowIndex).MergeCells = true;
874 //sheet.Cells.get_Range("C" + startRowIndex, "C" + endRowIndex).MergeCells = true;
875
876 range = sheet.Cells.get_Range("A" + startRowIndex, "A" + endRowIndex);//get_Range("起始单元格，如A1","结束单元格，如G10"); 即表示一块区域
877 range.Clear();
878 range.Font.Size = 12;//设置字体大小
879 range.Font.Bold = true;//设置字体加粗
880 range.Merge(0);//单元格合并
881 range.Value = "'" + lastSerialNum;//设置单元格内容
882 range.Borders.LineStyle = 1;//加边框
883 range.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
884 range.VerticalAlignment = Excel.XlVAlign.xlVAlignCenter;
885 range = null;
886 range = sheet.Cells.get_Range("B" + startRowIndex, "B" + endRowIndex);
887 range.Clear();
888 range.Font.Size = 12;//设置字体大小
889 range.Font.Bold = true;//设置字体加粗
890 range.Merge(0);//单元格合并
891 range.Value = "'" + lastStationNum;//设置单元格内容
892 range.Borders.LineStyle = 1;//加边框
893 range.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
894 range.VerticalAlignment = Excel.XlVAlign.xlVAlignCenter;
895 range = null;
896 range = sheet.Cells.get_Range("C" + startRowIndex, "C" + endRowIndex);
897 range.Clear();
898 range.Font.Size = 12;//设置字体大小
899 range.Font.Bold = true;//设置字体加粗
900 range.Merge(0);//单元格合并
901 range.Value = "'" + lastStationName;//设置单元格内容
902 range.Borders.LineStyle = 1;//加边框
903 range.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
904 range.VerticalAlignment = Excel.XlVAlign.xlVAlignCenter;
905 range = null;
906
907 sameCount = 0;
908 firstMergeFlag = false;
909 }
910
911 lastSerialNum = SerialNum;
912 lastStationNum = StationNum;
913 lastStationName = StationName;
914 }
915 }
916
917 if (cell_r == (rowNum + 1) && sameCount > 0)//最后一行，还有未合并的
918 {
919 int startRowIndex = cell_r - sameCount;
920 int endRowIndex = cell_r;
921
922 //合并单元格
923 //sheet.Cells.get_Range("A" + startRowIndex, "A" + endRowIndex).MergeCells = true;
924 //sheet.Cells.get_Range("B" + startRowIndex, "B" + endRowIndex).MergeCells = true;
925 //sheet.Cells.get_Range("C" + startRowIndex, "C" + endRowIndex).MergeCells = true;
926
927 range = sheet.get_Range("A" + startRowIndex, "A" + endRowIndex);//get_Range("起始单元格，如A1","结束单元格，如G10"); 即表示一块区域
928 range.Clear();
929 range.Font.Size = 12;//设置字体大小
930 range.Font.Bold = true;//设置字体加粗
931 range.Merge(0);//单元格合并
932 range.Value = "'" + lastSerialNum;//设置单元格内容
933 range.Borders.LineStyle = 1;//加边框
934 range.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
935 range.VerticalAlignment = Excel.XlVAlign.xlVAlignCenter;
936 range = null;
937 range = sheet.get_Range("B" + startRowIndex, "B" + endRowIndex);
938 range.Clear();
939 range.Font.Size = 12;//设置字体大小
940 range.Font.Bold = true;//设置字体加粗
941 range.Merge(0);//单元格合并
942 range.Value = "'" + lastStationNum;//设置单元格内容
943 range.Borders.LineStyle = 1;//加边框
944 range.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
945 range.VerticalAlignment = Excel.XlVAlign.xlVAlignCenter;
946 range = null;
947 range = sheet.get_Range("C" + startRowIndex, "C" + endRowIndex);
948 range.Clear();
949 range.Font.Size = 12;//设置字体大小
950 range.Font.Bold = true;//设置字体加粗
951 range.Merge(0);//单元格合并
952 range.Value = "'" + lastStationName;//设置单元格内容
953 range.Borders.LineStyle = 1;//加边框
954 range.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
955 range.VerticalAlignment = Excel.XlVAlign.xlVAlignCenter;
956 range = null;
957
958 sameCount = 0;
959 }
960 if (Statistics && cell_r == (rowNum + 1))//最后一行是汇总信息合并单元格
961 {
962 sheet.Cells.get_Range("A" + cell_r, "F" + cell_r).MergeCells = true;
963 }
964 }
965
966 //调整Excel的样式
967 range = sheet.Range[sheet.Cells[1, 1], sheet.Cells[rowNum + 1, columnNum]];
968 range.Borders.LineStyle = 1;//单元格加边框
969
970 sheet.Columns.EntireColumn.AutoFit();//列宽自适应。
971
972 workbook.Saved = true;
973 switch (ExcelVersion)
974 {
975 case "2003":
976 object ob = System.Reflection.Missing.Value;
977 workbook.SaveCopyAs(Path + FileName);
978 workbook.Save();
979 workbook.Close(null, null, null);
980 m_xlApp.Workbooks.Close();
981 m_xlApp.Quit();
982 Marshal.ReleaseComObject((object)m_xlApp);
983 Marshal.ReleaseComObject((object)workbook);
984 Marshal.ReleaseComObject((object)sheet);
985 break;
986 case "2007":
987 workbook.SaveCopyAs(Path + FileName);
988 workbook.Save();
989 workbook.Close(null, null, null);
990 m_xlApp.Workbooks.Close();
991 m_xlApp.Quit();
992 Marshal.ReleaseComObject((object)m_xlApp);
993 Marshal.ReleaseComObject((object)workbook);
994 Marshal.ReleaseComObject((object)sheet);
995 break;
996 default: break;
997 }
998 KillProcess("EXCEL");//杀死excel进程
999 tempDataTable = null;
1000 }
1001 catch (Exception ex)
1002 {
1003 tempDataTable = null;
1004 throw new Exception(ex.Message.ToString());
1005 }
1006 finally
1007 {
1008 KillProcess("EXCEL");//杀死excel进程
1009 tempDataTable = null;
1010 }
1011 }
1012
1013 //调用
1014 //int excelCount = sheet.UsedRange.CurrentRegion.Rows.Count;//获得记录的行数
1015 //MergeCell(ref sheet, 2, excelCount, "A");//合并单元格
1016 //MergeCell(ref sheet, 2, excelCount, "B");//合并单元格
1017 //MergeCell(ref sheet, 2, excelCount, "C");//合并单元格
1018
1019 /// <summary>
1020 /// 合并指定EXCEL的单元格
1021 /// </summary>
1022 /// <param name="mySheet">指定的EXCEL工作表</param>
1023 /// <param name="startLine">起始行</param>
1024 /// <param name="recCount">总行数</param>
1025 /// <param name="col">要合并的列</param>
1026 private void MergeCell(ref Microsoft.Office.Interop.Excel.Worksheet mySheet, int startLine, int recCount, string col)
1027 {
1028 //获得起始行合并列单元格的填充内容
1029 string qy1 = mySheet.get_Range(col + startLine.ToString(), col + startLine.ToString()).Text.ToString();
1030
1031 Microsoft.Office.Interop.Excel.Range rg1;
1032 string strtemp = "";
1033 bool endCycle = false;
1034
1035 //从起始行到终止行做循环
1036 for (int i = startLine; i <= recCount + startLine - 1 && !endCycle; )
1037 {
1038 for (int j = i + 1; j <= recCount + startLine - 1; j++)
1039 {
1040 rg1 = mySheet.get_Range(col + j.ToString(), col + j.ToString());//获得下一行的填充内容
1041 strtemp = rg1.Text.ToString().Trim();
1042 //最后一行时，标记循环结束
1043 if (j == recCount + startLine - 1)
1044 endCycle = true;
1045 if (strtemp.Trim() == qy1.Trim())//内容等于初始内容
1046 {
1047 rg1 = mySheet.get_Range(col + i.ToString(), col + j.ToString());//选取上条合并位置和当前行的合并区域
1048 rg1.ClearContents();//清空要合并的区域
1049 rg1.MergeCells = true;
1050 if (col == "A")
1051 mySheet.Cells[i, 1] = qy1;
1052 else if (col == "B")
1053 mySheet.Cells[i, 2] = qy1;
1054 else if (col == "C")
1055 mySheet.Cells[i, 3] = qy1;
1056 }
1057 else//内容不等于初始内容
1058 {
1059 i = j;//i获取新值
1060 qy1 = mySheet.get_Range(col + j.ToString(), col + j.ToString()).Text.ToString();
1061 break;
1062 }
1063 }
1064 }
1065 }
1066
1067 //室分类需求网建反馈情况统计
1068 public void u_DataTableToExcel7(DataTable tempDataTable, string TemplatePath, int percent, string Path, string FileName, long SheetRows, string ExcelVersion)
1069 {
1070 if (tempDataTable == null)
1071 {
1072 return;
1073 }
1074 object missing = Missing.Value;
1075 long rowNum = tempDataTable.Rows.Count;//行数
1076 int columnNum = tempDataTable.Columns.Count;//列数
1077 Excel.Application m_xlApp = new Excel.Application();
1078 m_xlApp.DisplayAlerts = false;//不显示更改提示
1079 m_xlApp.Visible = false;
1080 m_xlApp.UserControl = true;
1081 Excel.Workbook workbook = m_xlApp.Workbooks.Open(TemplatePath, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing);
1082 Excel.Worksheet worksheet = (Excel.Worksheet)workbook.Sheets.get_Item(1);//取得第一个工作簿
1083 if (worksheet == null)//工作簿中没有工作表
1084 return;
1085
1086 try
1087 {
1088 worksheet.Cells[2, 4] = "非0前" + percent.ToString() + "%";
1089
1090 for (int i = 0; i < rowNum; i++)
1091 {
1092 for (int j = 1; j < columnNum; j++)
1093 {
1094 object obj = tempDataTable.Rows[i][tempDataTable.Columns[j].ToString()];
1095 worksheet.Cells[i + 3, j + 1] = obj == null ? "" : "'" + obj.ToString().Trim();//在obj.ToString()前加单引号是为了防止自动转化格式
1096 }
1097 }
1098
1099 workbook.Saved = true;
1100 switch (ExcelVersion)
1101 {
1102 case "2003":
1103 object ob = System.Reflection.Missing.Value;
1104 workbook.SaveCopyAs(Path + FileName);
1105 workbook.Save();
1106 workbook.Close(null, null, null);
1107 m_xlApp.Workbooks.Close();
1108 m_xlApp.Quit();
1109 Marshal.ReleaseComObject((object)m_xlApp);
1110 Marshal.ReleaseComObject((object)workbook);
1111 Marshal.ReleaseComObject((object)worksheet);
1112 break;
1113 case "2007":
1114 workbook.SaveCopyAs(Path + FileName);
1115 workbook.Save();
1116 workbook.Close(null, null, null);
1117 m_xlApp.Workbooks.Close();
1118 m_xlApp.Quit();
1119 Marshal.ReleaseComObject((object)m_xlApp);
1120 Marshal.ReleaseComObject((object)workbook);
1121 Marshal.ReleaseComObject((object)worksheet);
1122 break;
1123 default: break;
1124 }
1125 KillProcess("EXCEL");//杀死excel进程
1126 tempDataTable = null;
1127 }
1128 catch (Exception ex)
1129 {
1130 tempDataTable = null;
1131 throw new Exception(ex.Message.ToString());
1132 }
1133 finally
1134 {
1135 KillProcess("EXCEL");//杀死excel进程
1136 tempDataTable = null;
1137 }
1138
1139 }
1140
1141 /// <summary>
1142 /// 泰和 楼宇格式表生成
1143 /// </summary>
1144 public void u_DataTableToExcel8(DataTable dt, string TemplatePath, string Path, string FileName, long SheetRows, string ExcelVersion)
1145 {
1146 object missing = Missing.Value;
1147 Excel.Application m_xlApp = new Excel.Application();
1148 m_xlApp.DisplayAlerts = false;//不显示更改提示
1149 m_xlApp.Visible = false;
1150 m_xlApp.UserControl = true;
1151 Excel.Workbook workbook = m_xlApp.Workbooks.Open(TemplatePath, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing);
1152 Excel.Worksheet worksheet = null;
1153
1154 int bookCount = workbook.Sheets.Count;
1155
1156 int StartNum = 3;//从第三行开始 第一行、第二行为列填写要求说明
1157 string SheetName = "楼宇格式表";
1158 long rowNum = dt.Rows.Count;//行数
1159 int columnNum = dt.Columns.Count;//列数
1160
1161 Excel.Worksheet sheet = (Excel.Worksheet)workbook.Sheets.get_Item(1);//取得第一个工作簿
1162 sheet.Name = SheetName;
1163 if (sheet == null)//工作簿中没有工作表
1164 return;
1165
1166 for (int z = 0; z < dt.Rows.Count; z++)
1167 {
1168 try
1169 {
1170 sheet.Cells[z + StartNum, 1] = z + 1;//第一列 序号列
1171 sheet.Cells[z + StartNum, 2] = dt.Rows[z][0] == null ? "" : "'" + dt.Rows[z][0].ToString().Trim();//楼宇名称列 在obj.ToString()前加单引号是为了防止自动转化格式
1172 sheet.Cells[z + StartNum, 6] = dt.Rows[z][1] == null ? "" : "'" + dt.Rows[z][1].ToString().Trim();//所属分公司列
1173 sheet.Cells[z + StartNum, 10] = dt.Rows[z][2] == null ? "" : "'" + dt.Rows[z][2].ToString().Trim();//经度
1174 sheet.Cells[z + StartNum, 11] = dt.Rows[z][3] == null ? "" : "'" + dt.Rows[z][3].ToString().Trim();//纬度
1175
1176 }
1177 catch (Exception ex)
1178 {
1179 dt = null;
1180 throw new Exception(ex.Message.ToString());
1181 }
1182 finally
1183 {
1184
1185 }
1186 }
1187
1188 workbook.Saved = true;
1189 switch (ExcelVersion)
1190 {
1191 case "2003":
1192 object ob = System.Reflection.Missing.Value;
1193 workbook.SaveCopyAs(Path + FileName);
1194 workbook.Save();
1195 workbook.Close(null, null, null);
1196 m_xlApp.Workbooks.Close();
1197 m_xlApp.Quit();
1198 Marshal.ReleaseComObject((object)m_xlApp);
1199 Marshal.ReleaseComObject((object)workbook);
1200 if (worksheet != null)
1201 {
1202 Marshal.ReleaseComObject((object)worksheet);
1203 }
1204 break;
1205 case "2007":
1206 workbook.SaveCopyAs(Path + FileName);
1207 workbook.Save();
1208 workbook.Close(null, null, null);
1209 m_xlApp.Workbooks.Close();
1210 m_xlApp.Quit();
1211 Marshal.ReleaseComObject((object)m_xlApp);
1212 Marshal.ReleaseComObject((object)workbook);
1213 if (worksheet != null)
1214 {
1215 Marshal.ReleaseComObject((object)worksheet);
1216 }
1217 break;
1218 default: break;
1219 }
1220 KillProcess("EXCEL");//杀死excel进程
1221 dt = null;
1222 }
1223
1224 #endregion
1225 #region 内部接口
1226 //作用将dt的（startindex到endindex的数据导出到filename）---用于将海量数据导出到多个excel文件
1227 private void u_OutExcel(DataTable dt, long startindex, long endindex, string filepath, string filename, string ExcelVersion)
1228 {
1229 long columnNum = dt.Columns.Count;
1230 long excelRows = endindex - startindex - 1;
1231 Excel.Application m_xlApp = new Excel.Application();
1232 m_xlApp.DisplayAlerts = false;//不显示更改提示
1233 m_xlApp.Visible = false;
1234 Excel.Workbooks workbooks = m_xlApp.Workbooks;
1235 Excel.Workbook workbook = workbooks.Add(Excel.XlWBATemplate.xlWBATWorksheet);
1236 Excel.Worksheet worksheet = (Excel.Worksheet)workbook.Worksheets[1];//取得sheet1
1237 try
1238 {
1239 string[,] datas = new string[excelRows + 1, columnNum];
1240 for (int i = 0; i < columnNum; i++) //写入表头字段
1241 {
1242 string sTitle = dt.Columns[i].ColumnName;
1243 datas[0, i] = sTitle;//表头信息
1244 }
1245 Excel.Range range = worksheet.get_Range(worksheet.Cells[1, 1], worksheet.Cells[1, columnNum]);
1246 range.Interior.ColorIndex = 15;//15代表灰色
1247 range.Font.Bold = true;
1248 range.Font.Size = 9;
1249 int r = 0;
1250 int row = 0;
1251 for (r = Convert.ToInt32(startindex); r < endindex - 1; r++)
1252 {
1253 row++;
1254 for (int i = 0; i < columnNum; i++)
1255 {
1256 string sname = dt.Columns[i].ToString().Trim();
1257 object obj = dt.Rows[r][sname];
1258 datas[row, i] = obj == null ? "" : "'" + obj.ToString().Trim();//在obj.ToString()前加单引号是为了防止自动转化格式
1259 }
1260 }
1261
1262 Excel.Range fchR = worksheet.get_Range(worksheet.Cells[1, 1], worksheet.Cells[excelRows + 1, columnNum]);
1263 fchR.Value2 = datas;
1264 worksheet.Columns.EntireColumn.AutoFit();//列宽自适应。
1265 m_xlApp.WindowState = Excel.XlWindowState.xlMaximized;
1266 range = worksheet.get_Range(worksheet.Cells[1, 1], worksheet.Cells[excelRows + 1, columnNum]);
1267 //range.Interior.ColorIndex = 15;//15代表灰色
1268 range.Font.Size = 9;
1269 range.RowHeight = 14.25;
1270 range.Borders.LineStyle = 1;//1边框为实线 0为excel样式
1271 range.HorizontalAlignment = 1;
1272 workbook.Saved = true;
1273 switch (ExcelVersion)
1274 {
1275 case "2003":
1276 object ob = System.Reflection.Missing.Value;
1277 workbook.SaveAs(filepath + filename, Excel.XlFileFormat.xlExcel7, ob, ob, ob, ob, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange, ob, ob, ob, ob, ob);
1278 break;
1279 case "2007":
1280 workbook.SaveCopyAs(filepath + filename);
1281 break;
1282 default: break;
1283 }
1284 KillProcess("EXCEL");//杀死excel进程
1285 }
1286 catch (Exception ex)
1287 {
1288 KillProcess("EXCEL");
1289 throw new Exception(ex.Message.ToString());
1290 }
1291 finally
1292 {
1293 workbook.Close(null, null, null);
1294 m_xlApp.Workbooks.Close();
1295 m_xlApp.Quit();
1296 Marshal.ReleaseComObject((object)m_xlApp);
1297 Marshal.ReleaseComObject((object)workbook);
1298 Marshal.ReleaseComObject((object)worksheet);
1299 KillProcess("EXCEL");
1300 }
1301
1302 }
1303 //关闭进程
1304 private void KillProcess(string processName)
1305 {
1306 //System.Runtime.InteropServices.Marshal.ReleaseComObject(obj)
1307
1308 System.Diagnostics.Process myproc = new System.Diagnostics.Process();
1309 try
1310 {
1311 foreach (System.Diagnostics.Process thisproc in System.Diagnostics.Process.GetProcessesByName(processName))
1312 {
1313 thisproc.Kill();
1314 GC.Collect();
1315 }
1316 }
1317 catch
1318 {
1319
1320 }
1321 }
1322 #endregion
1323 }
1324
1325 public class SetExcelContent
1326 {
1327 public int X{set;get;}
1328 public int Y{set;get;}
1329 public string Content{set;get;}
1330
1331 public SetExcelContent()
1332 {
1333 }
1334
1335 public SetExcelContent(int _x,int _y,string _content)
1336 {
1337 this.X=_x;
1338 this.Y=_y;
1339 this.Content=_content;
1340 }
1341 }
1342
1343 //预算导出类
1344 public class BudgetExport
1345 {
1346 public DataTable tempDataTable { get; set; }
1347 public int StartNum { get; set; }
1348 public List<SetExcelContent> Eclist { get; set; }
1349 public string tableDesigners { get; set; }
1350 public string SheetName { get; set; }
1351
1352 public BudgetExport()
1353 {
1354
1355 }
1356
1357 public BudgetExport(DataTable _tempDataTable, int _StartNum, List<SetExcelContent> _Eclist,
1358 string _tableDesigners, string _SheetName)
1359 {
1360 this.tempDataTable = _tempDataTable;
1361 this.StartNum = _StartNum;
1362 this.Eclist = _Eclist;
1363 this.tableDesigners = _tableDesigners;
1364 this.SheetName = _SheetName;
1365 }
1366 }
1367 }
