C#批量导入Excel或WPS实现方法

批量导入导出Excel,WPS,TXT,可按照系统是否安装OFFICE、WPS情况选择,导出是自动选择。

上效果图C#批量导入Excel或WPS实现方法

  1       /// <summary>
  2         /// //获得当前你选择的Excel Sheet的所有名字
  3         /// </summary>
  4         /// <param name="filePath">文件路径</param>
  5         /// <param name="ExcelType">1 Microsoft Excel 2 WPS Excel</param>
  6         /// <returns></returns>
  7         public static string[] GetExcelSheetNames(string filePath, int ExcelType)
  8         {
  9             try
 10             {
 11                 if (ExcelType == 1)
 12                 {
 13                     Microsoft.Office.Interop.Excel.ApplicationClass excelApp = new Microsoft.Office.Interop.Excel.ApplicationClass();
 14                     Microsoft.Office.Interop.Excel.Workbooks wbs = excelApp.Workbooks;
 15                     Microsoft.Office.Interop.Excel.Workbook wb = wbs.Open(filePath, Type.Missing, Type.Missing, Type.Missing, Type.Missing,
 16                     Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing,
 17                     Type.Missing, Type.Missing, Type.Missing, Type.Missing);
 18                     int count = wb.Worksheets.Count;
 19                     string[] names = new string[count];
 20                     for (int i = 1; i <= count; i++)
 21                     {
 22                         names[i - 1] = ((Microsoft.Office.Interop.Excel.Worksheet)wb.Worksheets[i]).Name;
 23                     }
 24                     return names;
 25                 }
 26                 else
 27                 {
 28                     ET.Application etApp;
 29                     ET.workbook etbook;
 30                     etApp = new ET.Application();
 31                     etbook = new ET.workbook();
 32                     etbook = (ET.workbook)etApp.Workbooks.Open(filePath, null, null, null, null, null, null, null, null, null, null, null, null);
 33                     int count = etbook.Worksheets.Count;
 34                     string[] names = new string[count];
 35                     for (int i = 1; i <= count; i++)
 36                     {
 37                         names[i - 1] = ((ET.Worksheet)etbook.Worksheets[i]).Name;
 38                     }
 39                     return names;
 40                 }
 41             }
 42             catch
 43             {
 44                 return null;
 45             }
 46         }
 47 
 48         //引用命名空间   using Microsoft.Office.Interop.Excel;WPS的ET
 49         /// <summary>
 50         /// DataGridView导出文件到Microsoft Excel WPS Excel 文本文档
 51         /// </summary>
 52         /// <param name="gridView"></param>
 53         /// <param name="excludeColumnsIndexList">排除列"0,1,2"</param>
 54         /// <param name="fileName"></param>
 55         /// <param name="sheetName"></param>
 56         public static void ExportDataGridViewAsExcel(DataGridView gridView, string excludeColumnsIndexList,string fileName, string sheetName)
 57         {
 58             //导出到execl   
 59             try
 60             {
 61                 SaveFileDialog saveFileDialog = new SaveFileDialog();
 62                 saveFileDialog.Filter = "Microsoft Excel(*.xls)|*.xls|WPS Excel(*.xls)|*.xls|文本文档(*.txt)|*.txt";
 63                 saveFileDialog.FileName = fileName; //dataGridView1.Rows[0].Cells[0].Value.ToString() + "幸运榜单_" + DateTime.Now.ToString("yyyymmddhhmmss") + "";
 64                 saveFileDialog.FilterIndex = 0;
 65                 saveFileDialog.RestoreDirectory = true;
 66                 saveFileDialog.CreatePrompt = true;
 67                 saveFileDialog.Title = "导出文件保存为";
 68                 // saveFileDialog.ShowDialog();
 69                 if (saveFileDialog.ShowDialog() == DialogResult.OK)
 70                 {
 71                     string strName = saveFileDialog.FileName;
 72                     if (strName.Length != 0)
 73                     {
 74                         int rowscount = gridView.Rows.Count;
 75                         int colscount = gridView.Columns.Count;
 76                         if (rowscount <= 0)
 77                         {
 78                             MessageBox.Show("无数据需要保存 ", "信息", MessageBoxButtons.OK, MessageBoxIcon.Information);
 79                             return;
 80                         }
 81                         if (colscount <= 0)
 82                         {
 83                             MessageBox.Show("无数据需要保存 ", "信息", MessageBoxButtons.OK, MessageBoxIcon.Information);
 84                             return;
 85                         }
 86                         if (saveFileDialog.FilterIndex == 1)
 87                         {
 88                             if (rowscount > 65536)
 89                             {
 90                                 MessageBox.Show("行太多(最大限制为 65536行),不能保存! ", "信息", MessageBoxButtons.OK, MessageBoxIcon.Information);
 91                                 return;
 92                             }
 93                             if (colscount > 255)
 94                             {
 95                                 MessageBox.Show("列太多(最大限制为 255列), 不能保存! ", "信息", MessageBoxButtons.OK, MessageBoxIcon.Information);
 96                                 return;
 97                             }
 98                             try
 99                             {
100                                 //this.toolStripProgressBar1.Visible = true;
101                                 System.Reflection.Missing miss = System.Reflection.Missing.Value;
102                                 Microsoft.Office.Interop.Excel.ApplicationClass excel = new Microsoft.Office.Interop.Excel.ApplicationClass();
103                                 excel.Application.Workbooks.Add(true); ;
104                                 excel.Visible = false;//若是true,则在导出的时候会显示EXcel界面。
105                                 if (excel == null)
106                                 {
107                                     MessageBox.Show("Microsoft Excel无法启动!", "错误", MessageBoxButtons.OK, MessageBoxIcon.Error);
108                                     return;
109                                 }
110                                 Microsoft.Office.Interop.Excel.Workbooks books = (Microsoft.Office.Interop.Excel.Workbooks)excel.Workbooks;
111                                 Microsoft.Office.Interop.Excel.Workbook book = (Microsoft.Office.Interop.Excel.Workbook)(books.Add(miss));
112                                 Microsoft.Office.Interop.Excel.Worksheet sheet = (Microsoft.Office.Interop.Excel.Worksheet)book.ActiveSheet;
113                                 sheet.Name = sheetName;
114                                 string[,] datas = new string[rowscount + 1, colscount];
115                                 int m = 0, n = 0;
116                                 //生成列名称   这里i是从1开始的 因为我第0列是个隐藏列ID  没必要写进去
117                                 for (int i = 0; i < gridView.ColumnCount; i++)
118                                 {
119                                     if (("," + excludeColumnsIndexList + ",").IndexOf("," + i.ToString() + ",") == 0)
120                                     {
121                                         continue;
122                                     }
123                                     if (gridView.Columns[i].Visible)
124                                     {
125                                         datas[0, m] = gridView.Columns[i].HeaderText.ToString();
126                                         m++;
127                                     }
128                                     
129                                 }
130                                 //填充数据
131                                 for (int i = 0; i < gridView.RowCount; i++)
132                                 {
133                                     //j也是从1开始  原因如上  每个人需求不一样
134                                     n = 0;
135                                     for (int j = 0; j < gridView.ColumnCount; j++)
136                                     {
137                                         if (("," + excludeColumnsIndexList + ",").IndexOf("," + j.ToString() + ",") == 0)
138                                         {
139                                             continue;
140                                         }
141                                         if (gridView.Columns[j].Visible)
142                                         {
143                                             if (gridView[j, i].Value.GetType() == typeof(string))
144                                             {
145                                                 datas[i+1, n] = "" + gridView[j, i].Value.ToString();
146                                             }
147                                             else
148                                             {
149                                                 datas[i+1, n] = gridView[j, i].Value.ToString();
150                                             }
151                                             n++;
152                                         }
153                                     }
154                                     //this.toolStripProgressBar1.Value += 100 / gridView.RowCount;
155                                 }
156                                 int exportRowCount = rowscount + 1;
157                                 Microsoft.Office.Interop.Excel.Range range = sheet.get_Range("A1", IndexToColumn(colscount) + exportRowCount.ToString());
158                                 range.Value2 = datas;
159 
160                                 sheet.Columns.EntireColumn.AutoFit(); // Automatically change the column width
161 
162                                 book.SaveAs(strName, Microsoft.Office.Interop.Excel.XlFileFormat.xlWorkbookNormal, miss, miss, miss,
163                                         miss, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlShared, miss, miss, miss,
164                                         miss, miss);
165                                 //sheet.SaveAs(strName, miss, miss, miss, miss, miss, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange, miss, miss, miss);
166                                 book.Close(false, miss, miss);
167                                 books.Close();
168                                 excel.Quit();
169                                 System.Runtime.InteropServices.Marshal.ReleaseComObject(sheet);
170                                 System.Runtime.InteropServices.Marshal.ReleaseComObject(book);
171                                 System.Runtime.InteropServices.Marshal.ReleaseComObject(books);
172                                 System.Runtime.InteropServices.Marshal.ReleaseComObject(excel);
173 
174                                 GC.Collect();
175                             }
176                             catch (Exception ex)
177                             {
178                                 MessageBox.Show(ex.Message, "导出失败,\n1、如果未安装Microsoft Excel,请导出时尝试选择保存到WPS Excel或者文本文档!\n2、请确保导出到的目标文件未打开,否则程序占用可能导致失败。\n3、如已关闭还提示错误,请尝试在任务管理器结束Excel程序。");
179                                 return;
180                             }
181 
182                         }
183                         else if (saveFileDialog.FilterIndex == 2)
184                         {
185                             if (rowscount > 65536)
186                             {
187                                 MessageBox.Show("行太多(最大限制为 65536行),不能保存! ", "信息", MessageBoxButtons.OK, MessageBoxIcon.Information);
188                                 return;
189                             }
190                             if (colscount > 255)
191                             {
192                                 MessageBox.Show("列太多(最大限制为 255列), 不能保存! ", "信息", MessageBoxButtons.OK, MessageBoxIcon.Information);
193                                 return;
194                             }
195                             try
196                             {
197                                 ET.Application etApp;
198                                 //获取工作表表格
199                                 etApp = new ET.Application();
200                                 etApp.Workbooks.Add(Type.Missing);
201                                 etApp.Visible = false;
202                                 if (etApp == null)
203                                 {
204                                     MessageBox.Show("WPS Excel无法启动!", "错误", MessageBoxButtons.OK, MessageBoxIcon.Error);
205                                     return;
206                                 }
207                                 System.Reflection.Missing miss = System.Reflection.Missing.Value;
208                                 ET.Workbooks books = (ET.Workbooks)etApp.Workbooks;
209                                 ET.workbook book = (ET.workbook)(books.Add(miss));
210                                 ET.Worksheet sheet = (ET.Worksheet)book.ActiveSheet;
211                                 sheet.Name = sheetName;
212                                 string[,] datas = new string[rowscount + 1, colscount];
213                                 int m = 0, n = 0;
214                                 //生成列名称   这里i是从1开始的 因为我第0列是个隐藏列ID  没必要写进去
215                                 for (int i = 0; i < gridView.ColumnCount; i++)
216                                 {
217                                     if (("," + excludeColumnsIndexList + ",").IndexOf("," + i.ToString() + ",") == 0)
218                                     {
219                                         continue;
220                                     }
221                                     if (gridView.Columns[i].Visible)
222                                     {
223                                         datas[0, m] = gridView.Columns[i].HeaderText.ToString();
224                                         m++;
225                                     }
226 
227                                 }
228                                 //填充数据
229                                 for (int i = 0; i < gridView.RowCount; i++)
230                                 {
231                                     //j也是从1开始  原因如上  每个人需求不一样
232                                     n = 0;
233                                     for (int j = 0; j < gridView.ColumnCount; j++)
234                                     {
235                                         if (("," + excludeColumnsIndexList + ",").IndexOf("," + j.ToString() + ",") == 0)
236                                         {
237                                             continue;
238                                         }
239                                         if (gridView.Columns[j].Visible)
240                                         {
241                                             if (gridView[j, i].Value.GetType() == typeof(string))
242                                             {
243                                                 datas[i + 1, n] = "" + gridView[j, i].Value.ToString();
244                                             }
245                                             else
246                                             {
247                                                 datas[i + 1, n] = gridView[j, i].Value.ToString();
248                                             }
249                                             n++;
250                                         }
251                                     }
252                                     //this.toolStripProgressBar1.Value += 100 / gridView.RowCount;
253                                 }
254                                 int exportRowCount = rowscount + 1;
255                                 ET.Range range = sheet.get_Range("A1", IndexToColumn(colscount) + exportRowCount.ToString());
256                                 range.Value2 = datas;
257                                 sheet.Columns.EntireColumn.AutoFit(); // Automatically change the column width
258                                 book.SaveAs(strName, ET.XlFileFormat.xlWorkbookNormal, miss, miss, miss, miss, ET.ETSaveAsAccessMode.etShared, miss, miss, miss, miss);
259                                 book.Close(false, miss, miss);
260                                 books.Close();
261                                 etApp.Quit();
262                                 System.Runtime.InteropServices.Marshal.ReleaseComObject(sheet);
263                                 System.Runtime.InteropServices.Marshal.ReleaseComObject(book);
264                                 System.Runtime.InteropServices.Marshal.ReleaseComObject(books);
265                                 System.Runtime.InteropServices.Marshal.ReleaseComObject(etApp);
266 
267                                 GC.Collect();
268                             }
269                             catch (Exception ex)
270                             {
271                                 MessageBox.Show("导出失败,\n1、如果未安装WPS Excel,请导出时尝试选择保存到Microsoft Excel或者文本文档!\n2、请确保导出到的目标文件未打开,否则程序占用可能导致失败。\n3、如已关闭还提示错误,请尝试在任务管理器结束Excel程序。\n系统消息:" + ex.Message, "错误提示");
272                                 return;
273                             }
274 
275                         }
276                         else if (saveFileDialog.FilterIndex == 3)
277                         {
278 
279 
280                             FileStream fs = new FileStream(strName, FileMode.OpenOrCreate, FileAccess.Write);
281                             StreamWriter sw = new StreamWriter(fs);
282                             sw.BaseStream.Seek(0, SeekOrigin.End);
283                             string str = "";
284                             int n = 0;
285                             for (int i = 0; i < gridView.ColumnCount; i++)
286                             {
287                                 if (gridView.Columns[i].Visible)
288                                 {
289                                     n++;
290                                     if (n == 1)
291                                     {
292                                         str += gridView.Columns[i].HeaderText.ToString();
293                                     }
294                                     else
295                                     {
296                                         str += "," + gridView.Columns[i].HeaderText.ToString();
297                                     }
298                                 }
299                             }
300                             sw.WriteLine(str);
301 
302                             //填充数据
303                             for (int i = 0; i < gridView.RowCount; i++)
304                             {
305                                 str = "";
306                                 int m = 0;
307                                 //j也是从1开始  原因如上  每个人需求不一样
308                                 for (int j = 0; j < gridView.ColumnCount; j++)
309                                 {
310                                     if (gridView.Columns[j].Visible)
311                                     {
312                                         m++;
313                                         if (m == 1)
314                                         {
315                                             str += gridView[j, i].Value.ToString();
316                                         }
317                                         else
318                                         {
319                                             str += "," + gridView[j, i].Value.ToString();
320                                         }
321                                     }
322 
323                                 }
324                                 sw.WriteLine(str);
325                                 //this.toolStripProgressBar1.Value += 100 / gridView.RowCount;
326                             }
327 
328                             sw.Flush();
329                             sw.Close();
330                             fs.Close();
331 
332 
333                         }
334                         MessageBox.Show("数据已经成功导出!", "提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
335                         return;
336                         //this.toolStripProgressBar1.Value = 0;
337 
338                         //System.Diagnostics.Process.Start(strName);
339                     }
340                 }
341             }
342             catch (Exception ex)
343             {
344                 MessageBox.Show("导出失败,\n1、如果未安装Microsoft Excel或者WPS Excel,请导出时尝试选择保存到文本文档!\n2、请确保导出到的目标文件未打开,否则程序占用可能导致失败。\n3、如已关闭还提示错误,请尝试在任务管理器结束Excel程序。\n系统消息:" + ex.Message, "错误提示");
345                 return;
346             }
347         }
348         //引用命名空间   using Microsoft.Office.Interop.Excel;WPS的ET
349         /// <summary>
350         /// DataTable导出文件到Microsoft Excel WPS Excel 文本文档
351         /// </summary>
352         /// <param name="dt"></param>
353         /// <param name="fileName"></param>
354         /// <param name="sheetName"></param>
355         public static void ExportDataTableAsExcel(System.Data.DataTable dt, string fileName, string sheetName)
356         {
357             //导出到execl   
358             try
359             {
360                 SaveFileDialog saveFileDialog = new SaveFileDialog();
361                 saveFileDialog.Filter = "Microsoft Excel(*.xls)|*.xls|WPS Excel(*.xls)|*.xls|文本文档(*.txt)|*.txt";
362                 saveFileDialog.FileName = fileName; //dataGridView1.Rows[0].Cells[0].Value.ToString() + "幸运榜单_" + DateTime.Now.ToString("yyyymmddhhmmss") + "";
363                 saveFileDialog.FilterIndex = 0;
364                 saveFileDialog.RestoreDirectory = true;
365                 saveFileDialog.CreatePrompt = true;
366                 saveFileDialog.Title = "导出文件保存为";
367                 // saveFileDialog.ShowDialog();
368                 if (saveFileDialog.ShowDialog() == DialogResult.OK)
369                 {
370                     string strName = saveFileDialog.FileName;
371                     if (strName.Length != 0)
372                     {
373                         int rowscount = dt.Rows.Count;
374                         int colscount = dt.Columns.Count;
375                         if (rowscount <= 0)
376                         {
377                             MessageBox.Show("无数据需要保存 ", "信息", MessageBoxButtons.OK, MessageBoxIcon.Information);
378                             return;
379                         }
380 
381                         if (colscount <= 0)
382                         {
383                             MessageBox.Show("无数据需要保存 ", "信息", MessageBoxButtons.OK, MessageBoxIcon.Information);
384                             return;
385                         }
386                         if (saveFileDialog.FilterIndex == 1)
387                         {
388 
389                             if (rowscount > 65536)
390                             {
391                                 MessageBox.Show("行太多(最大限制为 65536行),不能保存! ", "信息", MessageBoxButtons.OK, MessageBoxIcon.Information);
392                                 return;
393                             }
394                             if (colscount > 255)
395                             {
396                                 MessageBox.Show("列太多(最大限制为 255列), 不能保存! ", "信息", MessageBoxButtons.OK, MessageBoxIcon.Information);
397                                 return;
398                             }
399                             try
400                             {
401 
402                                 //this.toolStripProgressBar1.Visible = true;
403                                 System.Reflection.Missing miss = System.Reflection.Missing.Value;
404                                 Microsoft.Office.Interop.Excel.ApplicationClass excel = new Microsoft.Office.Interop.Excel.ApplicationClass();
405                                 excel.Application.Workbooks.Add(true); ;
406                                 excel.Visible = false;//若是true,则在导出的时候会显示EXcel界面。
407                                 if (excel == null)
408                                 {
409                                     MessageBox.Show("Microsoft Excel无法启动!", "错误", MessageBoxButtons.OK, MessageBoxIcon.Error);
410                                     return;
411                                 }
412                                 Microsoft.Office.Interop.Excel.Workbooks books = (Microsoft.Office.Interop.Excel.Workbooks)excel.Workbooks;
413                                 Microsoft.Office.Interop.Excel.Workbook book = (Microsoft.Office.Interop.Excel.Workbook)(books.Add(miss));
414                                 Microsoft.Office.Interop.Excel.Worksheet sheet = (Microsoft.Office.Interop.Excel.Worksheet)book.ActiveSheet;
415                                 sheet.Name = sheetName;
416 
417                                 string[,] datas = new string[rowscount + 1, colscount];
418                                 //生成列名称   这里i是从1开始的 因为我第0列是个隐藏列ID  没必要写进去
419                                 for (int i = 0; i < dt.Columns.Count; i++)
420                                 {
421                                     datas[0, i] = dt.Columns[i].ColumnName.Trim();
422                                 }
423 
424                                 //填充数据
425                                 for (int i = 0; i < dt.Rows.Count; i++)
426                                 {
427                                     //j也是从1开始  原因如上  每个人需求不一样
428                                     for (int j = 0; j < dt.Columns.Count; j++)
429                                     {
430 
431                                         if (dt.Rows[i][j].GetType() == typeof(string))
432                                         {
433                                             datas[i + 1, j] = "" + dt.Rows[i][j].ToString();
434                                         }
435                                         else
436                                         {
437                                             datas[i + 1, j] = dt.Rows[i][j].ToString();
438                                         }
439 
440 
441                                     }
442                                     //this.toolStripProgressBar1.Value += 100 / gridView.RowCount;
443                                 }
444                                 int exportRowCount = rowscount + 1;
445                                 Microsoft.Office.Interop.Excel.Range range = sheet.get_Range("A1", IndexToColumn(colscount) + exportRowCount.ToString());
446                                 range.Value2 = datas;
447 
448                                 sheet.Columns.EntireColumn.AutoFit(); // Automatically change the column width
449 
450                                 book.SaveAs(strName, Microsoft.Office.Interop.Excel.XlFileFormat.xlWorkbookNormal, miss, miss, miss,
451                                         miss, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlShared, miss, miss, miss,
452                                         miss, miss);
453 
454                                 //sheet.SaveAs(strName, miss, miss, miss, miss, miss, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange, miss, miss, miss);
455                                 book.Close(false, miss, miss);
456                                 books.Close();
457                                 excel.Quit();
458                                 System.Runtime.InteropServices.Marshal.ReleaseComObject(sheet);
459                                 System.Runtime.InteropServices.Marshal.ReleaseComObject(book);
460                                 System.Runtime.InteropServices.Marshal.ReleaseComObject(books);
461                                 System.Runtime.InteropServices.Marshal.ReleaseComObject(excel);
462 
463                                 GC.Collect();
464                             }
465                             catch (Exception ex)
466                             {
467                                 MessageBox.Show("导出失败,\n1、如果未安装Microsoft Excel,请导出时尝试选择保存到WPS Excel或者文本文档!\n2、请确保导出到的目标文件未打开,否则程序占用可能导致失败。\n3、如已关闭还提示错误,请尝试在任务管理器结束Excel程序。\n系统消息:" + ex.Message, "错误提示");
468                                 return;
469                             }
470                         }
471                         if (saveFileDialog.FilterIndex == 2)
472                         {
473 
474                             if (rowscount > 65536)
475                             {
476                                 MessageBox.Show("行太多(最大限制为 65536行),不能保存! ", "信息", MessageBoxButtons.OK, MessageBoxIcon.Information);
477                                 return;
478                             }
479                             if (colscount > 255)
480                             {
481                                 MessageBox.Show("列太多(最大限制为 255列), 不能保存! ", "信息", MessageBoxButtons.OK, MessageBoxIcon.Information);
482                                 return;
483                             }
484                             try
485                             {
486                                 ET.Application etApp;
487                                 //获取工作表表格
488                                 etApp = new ET.Application();
489                                 etApp.Workbooks.Add(Type.Missing);
490                                 etApp.Visible = false;
491                                 if (etApp == null)
492                                 {
493                                     MessageBox.Show("WPS Excel无法启动!", "错误", MessageBoxButtons.OK, MessageBoxIcon.Error);
494                                     return;
495                                 }
496                                 System.Reflection.Missing miss = System.Reflection.Missing.Value;
497                                 ET.Workbooks books = (ET.Workbooks)etApp.Workbooks;
498                                 ET.workbook book = (ET.workbook)(books.Add(miss));
499                                 ET.Worksheet sheet = (ET.Worksheet)book.ActiveSheet;
500                                 sheet.Name = sheetName;
501 
502                                 string[,] datas = new string[rowscount + 1, colscount];
503                                 //生成列名称   这里i是从1开始的 因为我第0列是个隐藏列ID  没必要写进去
504                                 for (int i = 0; i < dt.Columns.Count; i++)
505                                 {
506                                     datas[0, i] = dt.Columns[i].ColumnName.Trim();
507                                 }
508 
509                                 //填充数据
510                                 for (int i = 0; i < dt.Rows.Count; i++)
511                                 {
512                                     //j也是从1开始  原因如上  每个人需求不一样
513                                     for (int j = 0; j < dt.Columns.Count; j++)
514                                     {
515 
516                                         if (dt.Rows[i][j].GetType() == typeof(string))
517                                         {
518                                             datas[i + 1, j] = "" + dt.Rows[i][j].ToString();
519                                         }
520                                         else
521                                         {
522                                             datas[i + 1, j] = dt.Rows[i][j].ToString();
523                                         }
524                                     }
525                                     //this.toolStripProgressBar1.Value += 100 / gridView.RowCount;
526                                 }
527                                 int exportRowCount = rowscount + 1;
528                                 ET.Range range = sheet.get_Range("A1", IndexToColumn(colscount) + exportRowCount.ToString());
529                                 range.Value2 = datas;
530                                 sheet.Columns.EntireColumn.AutoFit(); // Automatically change the column width
531                                 book.SaveAs(strName, ET.XlFileFormat.xlWorkbookNormal, miss, miss, miss, miss, ET.ETSaveAsAccessMode.etShared, miss, miss, miss, miss);
532                                 book.Close(false, miss, miss);
533                                 books.Close();
534                                 etApp.Quit();
535                                 System.Runtime.InteropServices.Marshal.ReleaseComObject(sheet);
536                                 System.Runtime.InteropServices.Marshal.ReleaseComObject(book);
537                                 System.Runtime.InteropServices.Marshal.ReleaseComObject(books);
538                                 System.Runtime.InteropServices.Marshal.ReleaseComObject(etApp);
539 
540                                 GC.Collect();
541                             }
542                             catch (Exception ex)
543                             {
544                                 MessageBox.Show("导出失败,\n1、如果未安装WPS Excel,请导出时尝试选择保存到Microsoft Excel或者文本文档!\n2、请确保导出到的目标文件未打开,否则程序占用可能导致失败。\n3、如已关闭还提示错误,请尝试在任务管理器结束Excel程序。\n系统消息:" + ex.Message, "错误提示");
545                                 return;
546                             }
547                         }
548                         else if (saveFileDialog.FilterIndex == 2)
549                         {
550 
551 
552                             FileStream fs = new FileStream(strName, FileMode.OpenOrCreate, FileAccess.Write);
553                             StreamWriter sw = new StreamWriter(fs);
554                             sw.BaseStream.Seek(0, SeekOrigin.End);
555                             string str = "";
556                             for (int i = 0; i < dt.Columns.Count; i++)
557                             {
558                                 if (i == 0)
559                                 {
560                                     str += dt.Columns[i].ColumnName.Trim();
561                                 }
562                                 else
563                                 {
564                                     str += "," + dt.Columns[i].ColumnName.Trim();
565                                 }
566                             }
567                             sw.WriteLine(str);
568 
569                             //填充数据
570                             for (int i = 0; i < dt.Rows.Count; i++)
571                             {
572                                 str = "";
573                                 //j也是从1开始  原因如上  每个人需求不一样
574                                 for (int j = 0; j < dt.Columns.Count; j++)
575                                 {
576                                     if (j == 0)
577                                     {
578                                         str += dt.Rows[i][j].ToString();
579                                     }
580                                     else
581                                     {
582                                         str += "," + dt.Rows[i][j].ToString();
583                                     }
584 
585                                 }
586                                 sw.WriteLine(str);
587                                 //this.toolStripProgressBar1.Value += 100 / gridView.RowCount;
588                             }
589 
590                             sw.Flush();
591                             sw.Close();
592                             fs.Close();
593 
594 
595                         }
596                         MessageBox.Show("数据已经成功导出!", "提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
597                         //this.toolStripProgressBar1.Value = 0;
598 
599                         //System.Diagnostics.Process.Start(strName);
600                     }
601                 }
602             }
603             catch (Exception ex)
604             {
605                 MessageBox.Show(ex.Message, "导出失败,\n1、如果未安装Microsoft Excel或者WPS Excel,请导出时尝试选择保存到文本文档!\n2、请确保导出到的目标文件未打开,否则程序占用可能导致失败。\n3、如已关闭还提示错误,请尝试在任务管理器结束Excel程序。");
606             }
607         }

 

C#批量导入Excel或WPS实现方法

上一篇:对TControl和TWinControl相同与不同之处的深刻理解


下一篇:Win7 VS2010下开发的CS软件,安装到XP下无法运行或者桌面快捷方式无法显示图标的问题解决方法