C# WPS导入导出【V9】版本
一：生成WPS dll
下载WPS专业版本就能得到"WPSOfficePIA.exe",个人版本需要手动生成,有点麻烦。
就在你安装路径下面：XX:\WPS office个人版本\WPS Office\9.1.0.4953\office6
项目引用：Excel.dll、Office.dll,
二：导入导出功能
1 /// <summary> 2 /// 判断是否安装WPS 3 /// </summary> 4 /// <returns></returns> 5 private bool IsWPS() 6 { 7 Type type = Type.GetTypeFromProgID("Ket.Application");//V9版本类型 8 if (type == null)//没有安装V9版本 9 { 10 return false; 11 } 12 else 13 { 14 return true; 15 } 16 } 17 /// <summary> 18 /// 导出WPS ET模板 19 /// </summary> 20 /// <param name="strColumnName">模板列名称</param> 21 /// <param name="strFileName">文件名称</param> 22 /// <param name="strPath">保存路径</param> 23 public void WPSCrateET(string strColumnName, string strFileName, string strPath) 24 { 25 try 26 { 27 28 List<string> strConlumnNames = strColumnName.Split(',').ToList<string>(); 29 List<string> listName = new List<string>(); 30 if (strConlumnNames.Count<=256) 31 { 32 #region WPS ET模板导出 33 //创建App实例 34 Excel.Application etApp = new Excel.Application(); 35 etApp.Visible = false; 36 etApp.UserControl = false; 37 Excel.Workbooks etBooks = etApp.Workbooks; 38 //创建新工作薄 39 Excel.Workbook etBook = etBooks.Add(true); 40 //创建工作表 41 Excel.Worksheet etSheet = etApp.Sheets.Add(Type.Missing, Type.Missing, 1) as Excel.Worksheet; 42 etSheet.Name =strFileName; 43 listName.Add(etSheet.Name); 44 for (int j = 0; j < strConlumnNames.Count; j++) 45 { 46 string strColumnTitleName = GetExcelColumnLabel(j, 1); 47 etSheet.Range[strColumnTitleName].Value = strConlumnNames[j]; 48 49 } 50 //删除多余的表格 51 for (int i = etBook.Worksheets.Count; i > 0; i--) 52 { 53 54 Excel.Worksheet sheet = etBook.Worksheets[i] as Excel.Worksheet; 55 string strNamedelete = sheet.Name; 56 if (!listName.Contains(strNamedelete)) 57 { 58 sheet.Delete(); 59 } 60 } 61 62 etBook.SaveAs(strPath);//保存 63 etApp.Workbooks.Close();//关闭所有Workbook 64 etApp.Quit();//关闭Application 65 System.Runtime.InteropServices.Marshal.ReleaseComObject(etBook);//释放Workbook 66 System.Runtime.InteropServices.Marshal.ReleaseComObject(etApp);//释放Application 67 #endregion 68 } 69 else 70 { 71 throw new Exception("最多256列"); 72 } 73 74 75 } 76 catch (Exception ex) 77 { 78 Aukeys.MsgTool.ShowMessage(ex.Message); 79 } 80 81 } 82 /// <summary> 83 /// 导出WPS ET数据 84 /// </summary> 85 /// <param name="strFileName">文件名称</param> 86 /// <param name="strPath">保存路径</param> 87 /// <param name="dt">数据源</param> 88 public void WPSExportET(string strFileName,string strPath, System.Data.DataTable dtSource) 89 { 90 91 try 92 { 93 if (!IsWPS()) 94 { 95 Aukeys.MsgTool.ShowMessage("请安装WPS"); 96 return; 97 } 98 List<string> listName = new List<string>(); 99 if (dtSource != null && dtSource.Rows.Count > 0) 100 { 101 //创建App实例 102 Excel.Application etApp = new Excel.Application(); 103 etApp.Visible = false; 104 etApp.UserControl = false; 105 Excel.Workbooks etBooks = etApp.Workbooks; 106 //创建新工作薄 107 Excel.Workbook etBook = etBooks.Add(true); 108 //计算当前工作薄中表数量 109 int WorksheetCount = Convert.ToInt32(Math.Ceiling(Convert.ToDouble(dtSource.Rows.Count) / 60000)); 110 for (int i = 0; i < WorksheetCount; i++) 111 { 112 //创建工作表 113 Excel.Worksheet etSheet = etApp.Sheets.Add(Type.Missing, Type.Missing, 1) as Excel.Worksheet; 114 etSheet.Name = "第"+(i+1)+"个" + strFileName; 115 listName.Add(etSheet.Name); 116 //计算要取列名称 117 int indexStartRow = 60000 * ((i+1) - 1); 118 int indexEndRow = 60000 * (i+1); 119 if (dtSource.Rows.Count<indexEndRow) 120 { 121 indexEndRow = dtSource.Rows.Count; 122 } 123 for (int j = indexStartRow; j <= indexEndRow; j++) 124 { 125 for (int y = 0; y < dtSource.Columns.Count; y++) 126 { 127 string strColumnTitleName = GetExcelColumnLabel(y, j + 1); 128 if (j == 0) 129 { 130 131 etSheet.Range[strColumnTitleName].Value = dtSource.Columns[y].ColumnName; 132 } 133 else 134 { 135 etSheet.Range[strColumnTitleName].Value = dtSource.Rows[j-1][y]; 136 } 137 138 } 139 } 140 141 } 142 //删除多余的表格 143 for (int i = etBook.Worksheets.Count; i > 0; i--) 144 { 145 146 Excel.Worksheet sheet = etBook.Worksheets[i] as Excel.Worksheet; 147 string strNamedelete = sheet.Name; 148 if (!listName.Contains(strNamedelete)) 149 { 150 sheet.Delete(); 151 } 152 } 153 etBook.SaveAs(strPath);//保存 154 etApp.Workbooks.Close();//关闭所有Workbook 155 etApp.Quit();//关闭Application 156 System.Runtime.InteropServices.Marshal.ReleaseComObject(etBook);//释放Workbook 157 System.Runtime.InteropServices.Marshal.ReleaseComObject(etApp);//释放Application 158 } 159 else 160 { 161 MessageBox.Show("没有数据,暂时不支持导出"); 162 } 163 } 164 catch (Exception ex) 165 { 166 167 throw ex; 168 } 169 } 170 /// <summary> 171 /// 导入WPS ET 172 /// </summary> 173 /// <param name="strPath"></param> 174 /// <returns></returns> 175 public System.Data.DataTable WPSImportET(string strFileName,System.Data.DataTable dt) 176 { 177 178 try 179 { 180 181 //创建App实例 182 Excel.Application etApp = new Excel.Application(); 183 etApp.Visible = false; 184 etApp.UserControl = false; 185 //创建新工作薄 186 Excel.Workbook etBook = etApp.Workbooks.Open(strFileName); 187 Excel.Worksheet etsheet = etBook.Worksheets[1] as Excel.Worksheet;//获取第一个工作薄 188 //sheet.GetRowEnumerator(); 189 //获取数据区域 190 Excel.Range myRange = etsheet.UsedRange; 191 //获取ET中的行数与列数 192 int rowsCount = myRange.Rows.Count; 193 int colsCount = myRange.Columns.Count; 194 195 dt = GetEtData(dt, myRange, rowsCount, colsCount); 196 etApp.Workbooks.Close();//关闭所有Workbook 197 etApp.Quit();//关闭Application 198 System.Runtime.InteropServices.Marshal.ReleaseComObject(etBook);//释放Workbook 199 System.Runtime.InteropServices.Marshal.ReleaseComObject(etApp);//释放Application 200 201 } 202 catch (Exception ex) 203 { 204 MessageBox.Show(ex.Message); 205 } 206 return dt; 207 } 208 /// <summary> 209 /// 根据活动表获取数据 210 /// </summary> 211 /// <param name="range">表数据</param> 212 /// <param name="rowsCount">行数</param> 213 /// <param name="colsCount">列数</param> 214 /// <returns></returns> 215 private System.Data.DataTable GetEtData(System.Data.DataTable dt,Excel.Range range, int rowsCount, int colsCount) 216 { 217 //创建一个DataTable对象 218 System.Data.DataRow row = null; 219 //填入数据 220 for (int i = 0; i < rowsCount; i++) 221 { 222 row = dt.NewRow(); 223 for (int j = 0; j < colsCount; j++) 224 { 225 Excel.Range column = range.get_Item(i + 1, j + 1) as Excel.Range; 226 string columnText = column.Text as string; 227 row[j] = columnText; 228 } 229 dt.Rows.Add(row); 230 } 231 return dt; 232 } 233 /// <summary> 234 /// 根据列编号获取列别名 235 /// </summary> 236 /// <param name="num">列编号（从0开始）</param> 237 /// <returns></returns> 238 public static string GetExcelColumnLabel(int num,int row) 239 { 240 string temp = ""; 241 double i = Math.Floor(Math.Log(25.0 * (num) / 26.0 + 1) / Math.Log(26)) + 1; 242 if (i > 1) 243 { 244 double sub = num - 26 * (Math.Pow(26, i - 1) - 1) / 25; 245 for (double j = i; j > 0; j--) 246 { 247 temp = temp + (char)(sub / Math.Pow(26, j - 1) + 65); 248 sub = sub % Math.Pow(26, j - 1); 249 } 250 } 251 else 252 { 253 temp = temp + (char)(num + 65); 254 } 255 return temp + row; 256 }
