DataTableToExcel 的方法

   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 }
【推广】 免费学中医，健康全家人
原文地址：https://www.cnblogs.com/wutianyu/p/3803872.html