C#编程学习27: C#操作Excel从入门到精通

C#编程学习27: C#操作Excel从入门到精通

2019-07-15 23:14:33 小薛引路 阅读数 371更多

分类专栏: C# C#编程学习

版权声明:本文为博主原创文章,遵循 CC 4.0 BY-SA 版权协议,转载请附上原文出处链接和本声明。

本文链接:https://blog.csdn.net/m1m2m3mmm/article/details/96010380

目录

1 添加名称空间引用

2 Excel应用的创建与销毁

2.1 创建Application并销毁

2.2 杀死Excel进程

3 打开Excel数据表

3.1 由Excel应用打开和关闭数据表的两种方式

3.2 打开数据表的工作表

3.3添加数据表

3.3 对数据表的操作

3.4 思维导图

4 对单元格的操作

4.1 获取单元格的信息

4.2 设置单元格的值

4.3 合并单元格

4.4 将DataTable数据添加到数据表

5 excel与Datatable的数据交换

5.1 将Excel的数据读入到DataTable中

5.2 将DataTable数据输出到Excel中

5.3 海量DataTable导出到Excel

6 Excel数据快速写出方法

6.1 将DataTable的数据写入到数组中,然后在整体输出

6.2 将Grid的数据写出到Excel中

6.3 WPS的操作

7 参考博文



1 添加名称空间引用


 
  1. //用到的名空间

  2. using Excel = Microsoft.Office.Interop.Excel;

  3. using System.Reflection;

  4. using Microsoft.Office.Core;//使用Nothing

  5. using System.Runtime.InteropServices;//导入dll

2 Excel应用的创建与销毁

2.1 创建Application并销毁


 
  1. //创建excel应用程序

  2. Excel.Application myApp = new Excel.Application();

  3.  
  4. //处理代码

  5.  
  6. //关闭应用程序

  7. myApp.Quit();

  8. System.Runtime.InteropServices.Marshal.ReleaseComObject(myApp);

  9. myApp = null;

2.2 杀死Excel进程

方法一:


 
  1. public class PublicMethod

  2. {

  3. [DllImport("User32.dll", CharSet = CharSet.Auto)]

  4. public static extern int GetWindowThreadProcessId(IntPtr hwnd, out int ID);

  5. public static void Kill(Microsoft.Office.Interop.Excel.Application excel)

  6. {

  7. IntPtr t = new IntPtr(excel.Hwnd);//得到这个句柄,具体作用是得到这块内存入口

  8.  
  9. int k = 0;

  10. GetWindowThreadProcessId(t, out k); //得到本进程唯一标志k

  11. System.Diagnostics.Process p = System.Diagnostics.Process.GetProcessById(k); //得到对进程k的引用

  12. p.Kill(); //关闭进程k

  13. }

  14.  
  15. }

方法二:


 
  1. //创建进程对象

  2. Process[] ExcelProcess = Process.GetProcessesByName("Excel");

  3. //关闭进程

  4. foreach (Process p in ExcelProcess)

  5. {

  6. p.Kill();

  7. }

3 打开Excel数据表

2019年上半年居民收入和消费支出情况数据为例,开展本博文的相关实验

3.1 由Excel应用打开和关闭数据表的两种方式

object missing = System.Reflection.Missing.Value;//设置object的默认值,需要添加名称空间using System.Reflection;

(1) open方式--打开已有文件


 
  1. //打开实验数据

  2. string str = @"E:\C#\Example200\LearnExcel\LearnExcel\data.xlsx";

  3. Excel.Workbook wb = myApp.Workbooks.Open(str);

  4. Excel.WorkShee ws = myApp.WoekSheets.Add();

  5.  
  6.  
  7.  
  8. //....

  9.  
  10. ws.Save();

  11. //关闭数据表

  12. wb.Close();

  13.  
  14. myApp.Quit();

  15. myApp = null;

  16. System.Runtime.InteropServices.Marshal.ReleaseComObject(myApp);

(2) Add() -- 先创建表格之后再保存到指定路径的方法


 
  1. //打开实验数据

  2. string str = @"E:\C#\Example200\LearnExcel\LearnExcel\data.xlsx";

  3. Excel.Workbook wb = myApp.Workbooks.Add(true);

  4. Excel.WorkShee ws = myApp.WorkSheets.Add();

  5.  
  6.  
  7.  
  8. //....

  9.  
  10. ws.SaveAs(str);

  11. //关闭数据表

  12. wb.Close();

  13.  
  14. myApp.Quit();

  15. myApp = null;

  16. System.Runtime.InteropServices.Marshal.ReleaseComObject(myApp);

3.2 打开数据表的工作表


 
  1. //根据索引获取感兴趣的数据表

  2. Excel.Worksheet ws = wb.Worksheets[1];//sheet的索引从1开始

  3. //获取工作表的名称

  4. string wsName = ws.Name;

  5. //数据表的行数

  6. int wsRows = ws.Rows.Count;

  7. //数据表的列数

  8. int wsColumns = ws.Columns.Count;

  9. //数据表的有效数据行数

  10. int wsUsedRows = ws.UsedRange.Rows.Count;

  11. //数据表的有效数据列数

  12. int wsUsedColumns = ws.UsedRange.Columns.Count;

3.3添加数据表

先判断是否存在同名的数据表,不存在再创建


 
  1. for (int i = 1; i < openwb.Worksheets.Count; i++) //循环sheet工作表

  2. {

  3. string sheet = ((Worksheet)openwb.Worksheets[i]).Name;

  4. sheets.Add(sheet);

  5. }

  6. //--------------------------------定义新增Excel工作表名称------------------------------

  7. string addsheet = "新增工作表";

  8. if (sheets.Contains(addsheet)) //判断Excel中是否存在该工作表

  9. {

  10. Console.WriteLine("新增工作表已存在");

  11. }

  12. else //没有则新增该工作表

  13. {

  14. ws = (Worksheet)openwb.Worksheets.Add(missing, missing, 1, missing); //添加新的Excel工作表

  15. ws.Name = addsheet;

  16. openwb.Save();//保存Excel文件

  17. App.DisplayAlerts = false;//不显示提示对话框

  18. //App.Visible = true;

  19. }

3.3 对数据表的操作

字体相关的设置

属性 功能
Size 字号的大小
Bold 是否加粗
Italic 是否倾斜
colorIndex 文字的颜色
SubScript 是否下标
Superscript 是否上标
Color 字体颜色

行高列宽设置:

使用ColumnWidth和RowHeight两个属性设置


 
  1. ws.Rows[1, Missing.Value].Delete(Excel.XlDeleteShiftDirection.xlShiftUp);//删除第一行

  2. ws.Cells[2, 1].HorizontalAlignment = XlVAlign.xlVAlignCenter;//垂直居中

  3. ws.Cells[2, 1].HorizontalAlignment = XlHAlign.xlHAlignCenter;//水平居中

  4. ws.Rows[1, Missing.Value].Font.Bold = true;//设置是否粗体

  5. ws.Cells[1, "A"].Font.Size = 8;//设置字体大小

  6. ws.Rows[1, Missing.Value].Interior.ColorIndex = 3;//设置第一行为红色

  7. ws.get_Range("A1", "P1").Borders.LineStyle = 1;//设置表格的线宽

  8.  
  9. //删除数据表的第一行第一个元素(下边数据上移)

  10. ws.Cells[1, 1].Delete(Excel.XlDeleteShiftDirection.xlShiftUp);

  11. //删除工作表第一行第三列(右侧单元格左移)

  12. ws.Cells[1, 3].Delete(Excel.XlDeleteShiftDirection.xlShiftToLeft);

  13.  
  14. ws.Rows[3, Missing.Value].RowHeight = 5;//设置行高

  15. ws.Rows[3, Missing.Value].ColumnWidth = 5;//设置列宽

  16. ws.get-Range("A1").EntireColumn.NumberFormat = "@";//将A1列设置为【文本】格式

3.4 思维导图

4 对单元格的操作

4.1 获取单元格的信息


 
  1. Excel.Range rang = (Excel.Range)ws.Cells[2, 2];//单元格B2

  2.  
  3. rang.EntireColumn.AutoFit();//自动列宽

  4.  
  5. string content = rang.Text;//该单元格文本

  6.  
  7. double height = rang.Height;//单元格的高度

  8.  
  9. double width = rang.Width;//单元格的宽度

4.2 设置单元格的值


 
  1. //设置单元格的值

  2. ws.Cells[2, 3] = "null";

4.3 合并单元格


 
  1. //合并单元格

  2. Excel.Range mergeRange = ws.get_Range("A1", "b2");

  3. mergeRange.Merge();

4.4 将DataTable数据添加到数据表


 
  1. DataTable dt = new DataTable();

  2. //添加表头

  3. dt.Columns.Add("姓名");

  4. dt.Columns.Add("年龄");

  5. dt.Columns.Add("性别");

  6. //添加数据项

  7. dt.Rows.Add("姓名", "年龄", "性别");

  8. dt.Rows.Add("张三", "23", "男");

  9. dt.Rows.Add("李思", "12", "女");

  10. dt.Rows.Add("张琴", "33", "女");

  11. dt.Rows.Add("王高", "62", "男");

  12. dt.Rows.Add("郑涛", "56", "男");

  13. int rowIndex = 1;

  14. foreach (DataRow row in dt.Rows)

  15. {

  16. for (int colIndex = 0; colIndex < dt.Columns.Count; colIndex++)

  17. {

  18. ws.Cells[rowIndex, colIndex + 1] = row[colIndex].ToString();

  19. }

  20. rowIndex++;

  21. }

 

5 excel与Datatable的数据交换

5.1 将Excel的数据读入到DataTable中


 
  1. /// <summary>

  2.         /// 从excel文件读取内容

  3.         /// </summary>

  4.         /// <param name="fileName">excel文件名</param>

  5.         /// <returns>获取读取数据的表</returns>

  6.         static public DataTable ImportFromExcel(string fileName)

  7.         {

  8.             Excel.Application excelApp = null;

  9.             Excel.Workbooks wbks = null;

  10.             Excel._Workbook wbk = null;

  11.             try

  12.             {

  13.                 excelApp = new Excel.Application();

  14.                 excelApp.Visible = false;//是打开不可见

  15.                 wbks = excelApp.Workbooks;

  16.                 wbk = wbks.Add(fileName);

  17.                 object Nothing = Missing.Value;

  18.                 Excel._Worksheet whs;

  19.                 whs = (Excel._Worksheet)wbk.Sheets[2];//获取第一张工作表

  20.                 whs.Activate();

  21.                 DataTable dt = new DataTable(whs.Name);

  22.                 //读取excel表格的列标题

  23.                 int col_count=whs.UsedRange.Columns.Count;

  24.                 for (int col = 1; col <= col_count; col++)

  25.                 {

  26.                     dt.Columns.Add(((Excel.Range)whs.Cells[1,col]).Text.ToString());

  27.                 }

  28.                 //读取数据

  29.                 for (int row = 2; row <= whs.UsedRange.Rows.Count; row++)

  30.                 {

  31.                     DataRow dr = dt.NewRow();

  32.                     for (int col = 1; col < col_count; col++)

  33.                     {

  34.                         dr[col - 1] = ((Excel.Range)whs.Cells[row, col]).Text.ToString();

  35.                     }

  36.                     dt.Rows.Add(dr);

  37.                 }

  38.                 return dt;

  39.             }

  40.             catch (Exception e)

  41.             {

  42.                throw e;

  43.             }

  44.             finally

  45.             {

  46.                 //wbks.Close();//关闭工作簿

  47.                 excelApp.Quit();//关闭excel应用程序

  48.                 System.Runtime.InteropServices.Marshal.ReleaseComObject(excelApp);//释放excel进程

  49.                 excelApp = null;

  50.             }

  51.         }

5.2 将DataTable数据输出到Excel中


 
  1. /// <summary>

  2.         /// 导出数据到excel文件

  3.         /// </summary>

  4.         /// <param name="dt">要导出的数据集</param>

  5.         /// <returns>生成的文件名</returns>

  6.         static public string ExportToExcel(DataTable dt)

  7.         {

  8.             Excel.Application excelApp = null;

  9.             Excel.Workbooks wbks = null;

  10.             Excel._Workbook wbk = null;

  11.             try

  12.             {

  13.                 excelApp = new Excel.Application();

  14.                 excelApp.Visible = false;//是打开不可见

  15.                 wbks = excelApp.Workbooks;

  16.                 wbk = wbks.Add(true);

  17.  
  18.                 String version = excelApp.Version;//获取你使用的excel 的版本号

  19.                 int FormatNum;//保存excel文件的格式

  20.                 if (Convert.ToDouble(version) < 12)//You use Excel 97-2003

  21.                 {

  22.                     FormatNum = -4143;

  23.                 }

  24.                 else//you use excel 2007 or later

  25.                 {

  26.                     FormatNum = 56;

  27.                 }

  28.                 object Nothing = Missing.Value;

  29.                 Excel._Worksheet whs;

  30.                 whs = (Excel._Worksheet)wbk.Sheets[1];//获取第一张工作表

  31.                 whs.Activate();

  32.                 //写入标题行

  33.                 int rowIndex=1;

  34.                 for (int col = 0; col < dt.Columns.Count; col++)

  35.                 {

  36.                     whs.Cells[rowIndex, col+1] = dt.Columns[col].Caption.ToString();

  37.                 }

  38.                 rowIndex++;

  39.                 //写入数据内容

  40.                 foreach (DataRow row in dt.Rows)

  41.                 {

  42.                     for (int colIndex = 0; colIndex < dt.Columns.Count; colIndex++)

  43.                     {

  44.                         whs.Cells[rowIndex, colIndex + 1] = row[colIndex].ToString();

  45.                     }

  46.                     rowIndex++;

  47.                 }

  48.                 excelApp.DisplayAlerts = false;

  49.                 //保存excel文件

  50.                 //wbk.SaveCopyAs(@"D:\test.xls");

  51.                 string newFileName = @"D:\导出的excel文件.xls";

  52.                 wbk.SaveAs(newFileName, FormatNum);

  53.                 //关闭文件

  54.                 wbk.Close(false, Nothing, Nothing);

  55.                 return newFileName;

  56.             }

  57.             catch (Exception e)

  58.             {

  59.                 throw e;

  60.             }

  61.             finally

  62.             {

  63.                 //wbks.Close();//关闭工作簿

  64.                 excelApp.Quit();//关闭excel应用程序

  65.                 System.Runtime.InteropServices.Marshal.ReleaseComObject(excelApp);//释放excel进程

  66.                 excelApp = null;

  67.             }

  68.         }

5.3 海量DataTable导出到Excel

将DataTable中的大量数据导出到Excel表格中。

但每张Excel表单只能有65536行,所以当DataTable数据多于65536行时,Excel要考虑分页功能

代码在Office 2003 环境下通过。


 
  1. using Excel = Microsoft.Office.Interop.Excel;

  2.  
  3.  
  4. public Excel.Application m_xlApp = null;

  5.  
  6.  
  7. /// <summary>

  8. /// 将DataTable数据导出到Excel表

  9. /// </summary>

  10. /// <param name="tmpDataTable">要导出的DataTable</param>

  11. /// <param name="strFileName">Excel的保存路径及名称</param>

  12. public void DataTabletoExcel(System.Data.DataTable tmpDataTable, string strFileName)

  13. {

  14. if (tmpDataTable == null)

  15. {

  16. return;

  17. }

  18. long rowNum = tmpDataTable.Rows.Count;//行数

  19. int columnNum = tmpDataTable.Columns.Count;//列数

  20. Excel.Application m_xlApp = new Excel.Application();

  21. m_xlApp.DisplayAlerts = false;//不显示更改提示

  22. m_xlApp.Visible = false;

  23.  
  24. Excel.Workbooks workbooks = m_xlApp.Workbooks;

  25. Excel.Workbook workbook = workbooks.Add(Excel.XlWBATemplate.xlWBATWorksheet);

  26. Excel.Worksheet worksheet = (Excel.Worksheet)workbook.Worksheets[1];//取得sheet1

  27.  
  28. try

  29. {

  30. if (rowNum > 65536)//单张Excel表格最大行数

  31. {

  32. long pageRows = 65535;//定义每页显示的行数,行数必须小于65536

  33. int scount = (int)(rowNum / pageRows);//导出数据生成的表单数

  34. if (scount * pageRows < rowNum)//当总行数不被pageRows整除时,经过四舍五入可能页数不准

  35. {

  36. scount = scount + 1;

  37. }

  38. for (int sc = 1; sc <= scount; sc++)

  39. {

  40. if (sc > 1)

  41. {

  42. object missing = System.Reflection.Missing.Value;

  43. worksheet = (Excel.Worksheet)workbook.Worksheets.Add(

  44. missing, missing, missing, missing);//添加一个sheet

  45. }

  46. else

  47. {

  48. worksheet = (Excel.Worksheet)workbook.Worksheets[sc];//取得sheet1

  49. }

  50. string[,] datas = new string[pageRows + 1, columnNum];

  51.  
  52. for (int i = 0; i < columnNum; i++) //写入字段

  53. {

  54. datas[0, i] = tmpDataTable.Columns[i].Caption;//表头信息

  55. }

  56. Excel.Range range = worksheet.get_Range(worksheet.Cells[1, 1], worksheet.Cells[1, columnNum]);

  57. range.Interior.ColorIndex = 15;//15代表灰色

  58. range.Font.Bold = true;

  59. range.Font.Size = 9;

  60.  
  61. int init = int.Parse(((sc - 1) * pageRows).ToString());

  62. int r = 0;

  63. int index = 0;

  64. int result;

  65. if (pageRows * sc >= rowNum)

  66. {

  67. result = (int)rowNum;

  68. }

  69. else

  70. {

  71. result = int.Parse((pageRows * sc).ToString());

  72. }

  73.  
  74. for (r = init; r < result; r++)

  75. {

  76. index = index + 1;

  77. for (int i = 0; i < columnNum; i++)

  78. {

  79. object obj = tmpDataTable.Rows[r][tmpDataTable.Columns[i].ToString()];

  80. datas[index, i] = obj == null ? "" : "'" + obj.ToString().Trim();//在obj.ToString()前加单引号是为了防止自动转化格式

  81. }

  82. System.Windows.Forms.Application.DoEvents();

  83. //添加进度条

  84. }

  85.  
  86. Excel.Range fchR = worksheet.get_Range(worksheet.Cells[1, 1], worksheet.Cells[index + 1, columnNum]);

  87. fchR.Value2 = datas;

  88. worksheet.Columns.EntireColumn.AutoFit();//列宽自适应。

  89. m_xlApp.WindowState = Excel.XlWindowState.xlMaximized;//Sheet表最大化

  90. range = worksheet.get_Range(worksheet.Cells[1, 1], worksheet.Cells[index + 1, columnNum]);

  91. //range.Interior.ColorIndex = 15;//15代表灰色

  92. range.Font.Size = 9;

  93. range.RowHeight = 14.25;

  94. range.Borders.LineStyle = 1;

  95. range.HorizontalAlignment = 1;

  96. }

  97. }

  98. else

  99. {

  100. string[,] datas = new string[rowNum + 1, columnNum];

  101. for (int i = 0; i < columnNum; i++) //写入字段

  102. {

  103. datas[0, i] = tmpDataTable.Columns[i].Caption;

  104. }

  105. Excel.Range range = worksheet.get_Range(worksheet.Cells[1, 1], worksheet.Cells[1, columnNum]);

  106. range.Interior.ColorIndex = 15;//15代表灰色

  107. range.Font.Bold = true;

  108. range.Font.Size = 9;

  109.  
  110. int r = 0;

  111. for (r = 0; r < rowNum; r++)

  112. {

  113. for (int i = 0; i < columnNum; i++)

  114. {

  115. object obj = tmpDataTable.Rows[r][tmpDataTable.Columns[i].ToString()];

  116. datas[r + 1, i] = obj == null ? "" : "'" + obj.ToString().Trim();//在obj.ToString()前加单引号是为了防止自动转化格式

  117. }

  118. System.Windows.Forms.Application.DoEvents();

  119. //添加进度条

  120. }

  121. Excel.Range fchR = worksheet.get_Range(worksheet.Cells[1, 1], worksheet.Cells[rowNum + 1, columnNum]);

  122. fchR.Value2 = datas;

  123.  
  124. worksheet.Columns.EntireColumn.AutoFit();//列宽自适应。

  125. m_xlApp.WindowState = Excel.XlWindowState.xlMaximized;

  126.  
  127. range = worksheet.get_Range(worksheet.Cells[1, 1], worksheet.Cells[rowNum + 1, columnNum]);

  128. //range.Interior.ColorIndex = 15;//15代表灰色

  129. range.Font.Size = 9;

  130. range.RowHeight = 14.25;

  131. range.Borders.LineStyle = 1;

  132. range.HorizontalAlignment = 1;

  133. }

  134. workbook.Saved = true;

  135. workbook.SaveCopyAs(strFileName);

  136. }

  137. catch (Exception ex)

  138. {

  139. MessageBox.Show("导出异常:" + ex.Message, "导出异常", MessageBoxButtons.OK, MessageBoxIcon.Warning);

  140. }

  141. finally

  142. {

  143. EndReport();

  144. }

  145. }

  146.  
  147.  
  148. /// <summary>

  149. /// 退出报表时关闭Excel和清理垃圾Excel进程

  150. /// </summary>

  151. private void EndReport()

  152. {

  153. object missing = System.Reflection.Missing.Value;

  154. try

  155. {

  156. m_xlApp.Workbooks.Close();

  157. m_xlApp.Workbooks.Application.Quit();

  158. m_xlApp.Application.Quit();

  159. m_xlApp.Quit();

  160. }

  161. catch { }

  162. finally

  163. {

  164. try

  165. {

  166. System.Runtime.InteropServices.Marshal.ReleaseComObject(m_xlApp.Workbooks);

  167. System.Runtime.InteropServices.Marshal.ReleaseComObject(m_xlApp.Application);

  168. System.Runtime.InteropServices.Marshal.ReleaseComObject(m_xlApp);

  169. m_xlApp = null;

  170. }

  171. catch { }

  172. try

  173. {

  174. //清理垃圾进程

  175. this.killProcessThread();

  176. }

  177. catch { }

  178. GC.Collect();

  179. }

  180. }

  181. /// <summary>

  182. /// 杀掉不死进程

  183. /// </summary>

  184. private void killProcessThread()

  185. {

  186. ArrayList myProcess = new ArrayList();

  187. for (int i = 0; i < myProcess.Count; i++)

  188. {

  189. try

  190. {

  191. System.Diagnostics.Process.GetProcessById(int.Parse((string)myProcess[i])).Kill();

  192. }

  193. catch { }

  194. }

  195. }

6 Excel数据快速写出方法

6.1 将DataTable的数据写入到数组中,然后在整体输出


 
  1. //快速写入(先写入数组,然后一次性将数组写入到EXCEL中)

  2. private void CopyDataToSheet(System.Data.DataTable Table, _Worksheet Sheet)

  3. {

  4. int colCount, rowCount;

  5. colCount = Table.Columns.Count;

  6. rowCount = Table.Rows.Count;

  7. Range range;

  8.  
  9. //写入标题行

  10. range = Sheet.get_Range("A1", Missing.Value);

  11. range = range.get_Resize(1, colCount);

  12. object[,] headerData = new object[1, colCount];

  13. for (int iCol = 0; iCol < colCount; iCol++)

  14. {

  15. headerData[0, iCol] = Table.Columns[iCol].ColumnName;

  16. }

  17. range.set_Value(Missing.Value, headerData);

  18.  
  19. //写入数据行

  20. range = Sheet.get_Range("A2", Missing.Value);

  21. range = range.get_Resize(rowCount, colCount);

  22. object[,] cellData = new object[rowCount, colCount];

  23. for (int iRow = 0; iRow < rowCount; iRow++)

  24. {

  25. for (int iCol = 0; iCol < colCount; iCol++)

  26. {

  27. cellData[iRow, iCol] = Table.Rows[iRow][iCol].ToString();

  28. }

  29. }

  30. range.set_Value(Missing.Value, cellData);

  31. }

6.2 将Grid的数据写出到Excel中


 
  1. public static void OfficeExcel(Type type, SourceGrid.Grid grid, List<int> rowsStr, List<int> colsStr)

  2.         {

  3.             dynamic _app = new Microsoft.Office.Interop.Excel.Application();

  4.             dynamic _workbook;

  5.             _workbook = _app.Workbooks.Add(true);

  6.             _Worksheet objSheet;

  7.             objSheet = _workbook.ActiveSheet;

  8.             Range range;

  9.             try

  10.             {

  11.                 range = objSheet.get_Range("A1", Missing.Value);

  12.                 range = range.get_Resize(rowsStr.Count, colsStr.Count);

  13.                 object[,] saRet = new object[rowsStr.Count, colsStr.Count];

  14.                 for (int iRow = 0; iRow < rowsStr.Count; iRow++)

  15.                 {

  16.                     int row = rowsStr[iRow];

  17.                     for (int iCol = 0; iCol < colsStr.Count; iCol++)

  18.                     {

  19.                         int col = colsStr[iCol];

  20.                         saRet[iRow, iCol] = grid[row, col].Value;

  21.                     }

  22.                 }

  23.                 range.set_Value(Missing.Value, saRet);

  24.                 _app.Visible = true;

  25.                 _app.UserControl = true;

  26.             }

  27.             catch (Exception theException)

  28.             {

  29.                 String errorMessage;

  30.                 errorMessage = "Error: ";

  31.                 errorMessage = String.Concat(errorMessage, theException.Message);

  32.                 errorMessage = String.Concat(errorMessage, " Line: ");

  33.                 errorMessage = String.Concat(errorMessage, theException.Source);

  34.                 MessageBox.Show(errorMessage, "Error");

  35.             }

  36.         }

6.3 WPS的操作

需要注意的是在此引用了wps的 Kingsoft ET 2.0 Object Library(WPS需要用到 ET.dll 和 KSO.dll),在电脑中下载了wsp之后在引用的COM中引用。我的开发环境中在其它地方用到Microsoft.Office.Interop.Excel.dll,因此在声明Excel的时候默认是office的,WPS需要加上ET引用。


 
  1. using System;

  2. using System.Collections.Generic;

  3. using System.Linq;

  4. using System.Text;

  5. using System.Windows.Forms;

  6. using SCFBaseLib;

  7. using TYYW.AGTJ.Common;

  8. using System.Drawing;

  9. using System.Reflection;

  10. using Microsoft.Office.Interop.Excel;

  11.  
  12. //以上是所需要的引用

  13.  
  14. #region 导出SourceGrid数据(最新版,批量快速输出)

  15.         /// <summary>

  16.         /// 导出SourceGrid数据

  17.         /// </summary>

  18.         /// <param name="grid">SourceGrid</param>

  19.         /// <param name="rowsStr">需要导出的行</param>

  20.         /// <param name="colsStr">需要导出的列</param>

  21.  
  22.         //Excel导出的时候有两种软件插件可以使用(一种是office一种wps),因为各个插件的dll使用的方法不一样,因此要判断用户安装了哪个软件。

  23.         public static void NewExportSourceGridCell(SourceGrid.Grid grid, List<int> rowsStr, List<int> colsStr)

  24.         {

  25.  
  26.             //个人做的是政府项目,讲究国产化,在这里我先判断用户是否安装了wps。

  27.             string excelType = "wps";

  28.             Type type;

  29.             type = Type.GetTypeFromProgID("ET.Application");//V8版本类型

  30.             if (type == null)//没有安装V8版本

  31.             {

  32.                 type = Type.GetTypeFromProgID("Ket.Application");//V9版本类型

  33.                 if (type == null)//没有安装V9版本

  34.                 {

  35.                     type = Type.GetTypeFromProgID("Kwps.Application");//V10版本类型

  36.                     if (type == null)//没有安装V10版本

  37.                     {

  38.                         type = Type.GetTypeFromProgID("EXCEL.Application");//MS EXCEL类型

  39.                         excelType = "office";

  40.                         if (type == null)

  41.                         {

  42.                             ModuleBaseUserControl.ShowError("检测到您的电脑上没有安装office或WSP软件,请先安装!");

  43.                             return;//没有安装Office软件

  44.                         }

  45.                     }

  46.                 }

  47.             }

  48.             if (excelType == "wps")

  49.             {

  50.                 WpsExcel(type, grid, rowsStr, colsStr);

  51.             }

  52.             else

  53.             {

  54.                 OfficeExcel(type, grid, rowsStr, colsStr);

  55.             }

  56.         }

  57.  
  58.  
  59.  
  60.         //安装了wps

  61.  
  62.  
  63.        

  64. public static void WpsExcel(Type type, SourceGrid.Grid grid, List<int> rowsStr, List<int> colsStr)

  65.         {

  66.             dynamic _app = Activator.CreateInstance(type);  //根据类型创建App实例

  67.             dynamic _workbook;  //声明一个文件

  68.             _workbook = _app.Workbooks.Add(Type.Missing); //创建一个Excel

  69.             ET.Worksheet objSheet; //声明Excel中的页

  70.             objSheet = _workbook.ActiveSheet;  //创建一个Excel

  71.             ET.Range range; 

  72.             try

  73.             {

  74.                 range = objSheet.get_Range("A1", Missing.Value);

  75.                 object[,] saRet = new object[rowsStr.Count, colsStr.Count];  //声明一个二维数组

  76.                 for (int iRow = 0; iRow < rowsStr.Count; iRow++)  //把sourceGrid中的数据组合成二维数组

  77.                 {

  78.                     int row = rowsStr[iRow];

  79.                     for (int iCol = 0; iCol < colsStr.Count; iCol++)

  80.                     {

  81.                         int col = colsStr[iCol];

  82.                         saRet[iRow, iCol] = grid[row, col].Value;

  83.                     }

  84.                 }

  85.                 range.set_Value(ET.ETRangeValueDataType.etRangeValueDefault, saRet);  //把组成的二维数组直接导入range

  86.                 _app.Visible = true;

  87.                 _app.UserControl = true;

  88.             }

  89.             catch (Exception theException)

  90.             {

  91.                 String errorMessage;

  92.                 errorMessage = "Error: ";

  93.                 errorMessage = String.Concat(errorMessage, theException.Message);

  94.                 errorMessage = String.Concat(errorMessage, " Line: ");

  95.                 errorMessage = String.Concat(errorMessage, theException.Source);

  96.                 MessageBox.Show(errorMessage, "Error");

  97.             }

  98.         }

  99.  
  100.         //安装了office

  101. public static void OfficeExcel(Type type, SourceGrid.Grid grid, List<int> rowsStr, List<int> colsStr)

  102.         {

  103.             dynamic _app = new Microsoft.Office.Interop.Excel.Application();

  104.             dynamic _workbook;

  105.             _workbook = _app.Workbooks.Add(true);

  106.             _Worksheet objSheet;

  107.             objSheet = _workbook.ActiveSheet;

  108.             Range range;

  109.             try

  110.             {

  111.                 range = objSheet.get_Range("A1", Missing.Value);

  112.                 range = range.get_Resize(rowsStr.Count, colsStr.Count);

  113.                 object[,] saRet = new object[rowsStr.Count, colsStr.Count];

  114.                 for (int iRow = 0; iRow < rowsStr.Count; iRow++)

  115.                 {

  116.                     int row = rowsStr[iRow];

  117.                     for (int iCol = 0; iCol < colsStr.Count; iCol++)

  118.                     {

  119.                         int col = colsStr[iCol];

  120.                         saRet[iRow, iCol] = grid[row, col].Value;

  121.                     }

  122.                 }

  123.                 range.set_Value(Missing.Value, saRet);

  124.                 _app.Visible = true;

  125.                 _app.UserControl = true;

  126.             }

  127.             catch (Exception theException)

  128.             {

  129.                 String errorMessage;

  130.                 errorMessage = "Error: ";

  131.                 errorMessage = String.Concat(errorMessage, theException.Message);

  132.                 errorMessage = String.Concat(errorMessage, " Line: ");

  133.                 errorMessage = String.Concat(errorMessage, theException.Source);

  134.                 MessageBox.Show(errorMessage, "Error");

  135.             }

  136.         }

  137. #endregion

7 参考博文

专栏1:C#实战开发历程

专栏2:C#读写Excel文件(6篇博文)

博文3:C#报表数据批量快速导出到Excel(百万级数据秒级内完成)

博文4:C#将DataTable海量数据导出到Excel

 

 

 

posted @ 2019-11-07 09:49  grj001  阅读(249)  评论(0编辑  收藏  举报