NPOI ADO office wps 四种方式 读excel表格
来源：互联网 发布：广场舞制作软件 编辑：程序博客网 时间：2024/04/29 08:58
有的客户装了不同的excel程序，所以做了四种excel文件的读写，在一定程度上可以适应各种情况的客户。
npoi ：对公式，某些日期兼容的不好，速度很快，不需要装excel程序
ado:：对某些日期格式兼容的不好，速度较快
office：支持office2013
wps：支持wps2016
测试：在wps与excel都安装的情况下，调用 wps sdk时，有些打开确是excel进程
源代码下载地址：http://download.csdn.net/detail/liangzhonglin/9546566
wps
using System;using System.Collections.Generic;using System.Text;using System.IO;using System.Data;using System.Runtime.InteropServices;using System.Diagnostics;using Excel;namespace ExcelLib{ public class CExcel { public string Error = ""; [DllImport("User32.dll", CharSet = CharSet.Auto)] public static extern int GetWindowThreadProcessId(IntPtr hwnd, out int ID); /// <summary> /// 强制关闭当前Excel进程 /// </summary> public static void Kill(IntPtr intPtr) { try { Process[] ps = Process.GetProcesses(); int ExcelID = 0; GetWindowThreadProcessId(intPtr, out ExcelID); //得到本进程唯一标志k foreach (Process p in ps) { string procName = p.ProcessName.ToLower(); if (procName.Equals("excel")|| procName.Equals("et")) { if (p.Id == ExcelID) { p.Kill(); } } } } catch { //不做任何处理 } } public bool DatatableToExcel(ref System.Data.DataTable dtExcel, string excelPath) { Error = "不支持此接口!"; return false; } #region 读取excel文件 public bool ReadExcelToDataTable(string excelPath, string sheetName, string tableName, string fieldNameList, out System.Data.DataTable dtExcel) { Application objApp = null; Workbook objWorkBook = null; Worksheet objWorkSheet = null; IntPtr wpsPtr = IntPtr.Zero; dtExcel = null; try { //获取工作表表格 object type = System.Reflection.Missing.Value; objApp = new Application(); objApp.Visible = false; objApp.ScreenUpdating = false; objApp.DisplayAlerts = false; objWorkBook = (Workbook)objApp.Workbooks.Open(excelPath, type, type, type, type, type, type, type, type, type, type, type, type, type, type); if (objApp.Workbooks.Count == 0) { throw new Exception("excel文件中没有sheet表"); } wpsPtr = new IntPtr(objApp.Hwnd); //获得指定表 if (sheetName.Length == 0) { objWorkSheet = (Worksheet)objWorkBook.Worksheets.get_Item(1); } else { objWorkSheet = (Worksheet)objWorkBook.Worksheets.get_Item(sheetName); } //取表名赋值到dt TableName dtExcel = new System.Data.DataTable(tableName); //获取数据区域 int row = objWorkSheet.UsedRange.Rows.Count; int col = objWorkSheet.UsedRange.Columns.Count; int recordIndex = 1; if (fieldNameList.Length > 0) { string[] fieldList = fieldNameList.Split(','); for (int i = 0; i < fieldList.Length; i++) { dtExcel.Columns.Add(fieldList[i]); } } else { for (int c = 1; c <= col; c++) { dtExcel.Columns.Add(new DataColumn((String)((Excel.Range)objWorkSheet.Cells[1, c]).Text)); } recordIndex = 2; } for (int r = recordIndex; r <= row; r++) { DataRow newRow = dtExcel.NewRow(); for (int c = 1; c <= col; c++) { newRow[c - 1] = ((Excel.Range)objWorkSheet.Cells[r, c]).Text; } dtExcel.Rows.Add(newRow); } return true; } catch (Exception ex) { Error = "读取excel失败:" + ex.Message; return false; } finally { if (objWorkBook != null) { objWorkBook.Close(true, null, null); objWorkBook = null; } if (objApp != null) { objApp.Quit(); objApp = null; } // 杀死WPS线程 try { if (wpsPtr != IntPtr.Zero) { Kill(wpsPtr); } } catch (Exception) { } System.GC.Collect(); GC.WaitForPendingFinalizers(); } } public bool ReadExcelToDataTable(string excelPath, string sheetName, string tableName, int getMaxRecord, out System.Data.DataTable dtExcel) { dtExcel = null; Error = "不支持此接口!"; return false; } #endregion }}office
using System;using System.Collections.Generic;using System.Text;using System.IO;using System.Data;using System.Data.OleDb;using System.Runtime.InteropServices;using System.Diagnostics;namespace ExcelLib{ public class CExcel { public string Error = ""; [DllImport("User32.dll", CharSet = CharSet.Auto)] public static extern int GetWindowThreadProcessId(IntPtr hwnd, out int ID); /// <summary> /// 强制关闭当前Excel进程 /// </summary> public static void Kill(IntPtr intPtr) { try { Process[] ps = Process.GetProcesses(); int ExcelID = 0; GetWindowThreadProcessId(intPtr, out ExcelID); //得到本进程唯一标志k foreach (Process p in ps) { string procName = p.ProcessName.ToLower(); if (procName.Equals("excel") || procName.Equals("et")) { if (p.Id == ExcelID) { p.Kill(); } } } } catch { //不做任何处理 } } public bool DatatableToExcel(ref DataTable dtExcel, string excelPath) { Error = "不支持此接口!"; return false; } #region 读取excel文件 public bool ReadExcelToDataTable(string excelPath, string sheetName, string tableName, string fieldNameList, out DataTable dtExcel) { Microsoft.Office.Interop.Excel.ApplicationClass excel = null; dtExcel = null; IntPtr excelPtr = IntPtr.Zero; try { excel = new Microsoft.Office.Interop.Excel.ApplicationClass(); excelPtr = new IntPtr(excel.Hwnd); excel.Visible = false; excel.ScreenUpdating = false; excel.DisplayAlerts = false; excel.Workbooks.Add(excelPath); if (excel.Workbooks.Count == 0) { throw new Exception("excel文件中没有sheet表"); } //获得指定表 Microsoft.Office.Interop.Excel.Worksheet worksheet = (Microsoft.Office.Interop.Excel.Worksheet)excel.Worksheets[1]; //取表名赋值到dt TableName dtExcel = new System.Data.DataTable(worksheet.Name); worksheet.Columns.EntireColumn.AutoFit(); int row = worksheet.UsedRange.Rows.Count; int col = worksheet.UsedRange.Columns.Count; for (int c = 1; c <= col; c++) { dtExcel.Columns.Add(new DataColumn((String)((Microsoft.Office.Interop.Excel.Range)worksheet.Cells[1, c]).Text)); } for (int r = 2; r <= row; r++) { DataRow newRow = dtExcel.NewRow(); for (int c = 1; c <= col; c++) { newRow[c - 1] = ((Microsoft.Office.Interop.Excel.Range)worksheet.Cells[r, c]).Text; } dtExcel.Rows.Add(newRow); } worksheet = null; return true; } catch (Exception ex) { Error = "读取excel失败:" + ex.Message; return false; } finally { if (excel != null) { excel.Quit(); excel = null; } // 杀死excel线程 try { if ( excelPtr!= IntPtr.Zero) { Kill(excelPtr); } } catch (Exception) { } System.GC.Collect(); } } public bool ReadExcelToDataTable(string excelPath, string sheetName, string tableName, int getMaxRecord, out DataTable dtExcel) { dtExcel = null; Error = "不支持此接口!"; return false; } #endregion }}
ado
using System;using System.Collections.Generic;using System.Text;using System.IO;using System.Data;using System.Data.OleDb;namespace ExcelLib{ public class CExcel { public string Error = ""; public bool DatatableToExcel(ref DataTable dtExcel, string excelPath) { Error = "不支持此接口!"; return false; } #region 读取excel文件 public bool ReadExcelToDataTable(string excelPath, string sheetName, string tableName, string fieldNameList, out DataTable dtExcel) { dtExcel = null; OleDbConnection conn = null; try { string defaultSheetName = sheetName; // "[Sheet1$]"; string strConn = string.Format("Provider=Microsoft.Ace.OleDb.12.0;Data Source={0};Extended Properties='Excel 12.0;HDR=Yes'", excelPath); // string strConn = string.Format("Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties='Excel 8.0;HDR=Yes'", excelPath); if (sheetName.Length == 0) { // 取默认的sheet表名 conn = new OleDbConnection(strConn); conn.Open(); System.Data.DataTable sheetNames = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" }); conn.Close(); conn = null; defaultSheetName = sheetNames.Rows[0]["TABLE_NAME"].ToString(); } DataSet ds = new DataSet(); string sqlTabel = string.Format("select * from [{0}]", defaultSheetName); OleDbDataAdapter oada = new OleDbDataAdapter(sqlTabel, strConn); oada.Fill(ds); dtExcel = ds.Tables[0]; dtExcel.TableName = tableName; if (fieldNameList.Length > 0) { string[] fieldList = fieldNameList.Split(','); for (int i = 0; i < dtExcel.Columns.Count; i++) { dtExcel.Columns[i].ColumnName = fieldList[i]; } } return true; } catch (Exception ex) { Error = "读取excel失败:" + ex.Message; return false; } finally { if (conn != null) { conn.Close(); conn = null; } } } public bool ReadExcelToDataTable(string excelPath, string sheetName, string tableName, int getMaxRecord, out DataTable dtExcel) { dtExcel = null; Error = "不支持此接口!"; return false; } #endregion }}
npoi
using System;using System.Collections.Generic;using System.Text;using NPOI.HSSF.UserModel;using NPOI.HPSF;using NPOI.POIFS.FileSystem;using System.IO;using System.Data;using NPOI;using NPOI.SS.UserModel;using NPOI.XSSF.UserModel;namespace ExcelLib{ public class CExcel { public string Error = ""; #region 生成excel private CellType GetCellType(string type) { CellType cell = CellType.String; if (type == "System.Double") { cell = CellType.Numeric; } return cell; } public bool DatatableToExcel(ref DataTable dtExcel, string excelPath) { try { if (excelPath == null) { throw new Exception("未指定生成路径"); } bool bResult = true; string ext = Path.GetExtension(excelPath); switch (ext.ToLower()) { case ".xls": bResult= DatatableToExcel2003(ref dtExcel, excelPath); break; case ".xlsx": bResult= DatatableToExcel2007(ref dtExcel, excelPath); break; default: throw new Exception("保存文件名不正确"); } return bResult; } catch (System.Exception ex) { Error = "生成excel失败" + ex.Message; return false; } } private bool DatatableToExcel2007(ref DataTable dtExcel, string excelPath) { try { XSSFWorkbook workbook = new XSSFWorkbook(); ////create a entry of DocumentSummaryInformation //DocumentSummaryInformation dsi = PropertySetFactory.CreateDocumentSummaryInformation(); //dsi.Company = "Partner"; //hssfworkbook..DocumentSummaryInformation = dsi; ////create a entry of SummaryInformation //SummaryInformation si = PropertySetFactory.CreateSummaryInformation(); //si.Subject = "FrontLink"; //hssfworkbook.SummaryInformation = si; //here, we must insert at least one sheet to the workbook. otherwise, Excel will say 'data lost in file' //So we insert three sheet just like what Excel does ISheet sheet = workbook.CreateSheet(dtExcel.TableName); int cellCount = dtExcel.Columns.Count; IRow column = sheet.CreateRow(0); for (int j = 0; j < cellCount; j++) { column.CreateCell(j).SetCellType(GetCellType(dtExcel.Columns[j].DataType.ToString())); column.CreateCell(j).SetCellValue(dtExcel.Columns[j].ColumnName); } for (int i = 0; i < dtExcel.Rows.Count; i++) { IRow row = sheet.CreateRow(i + 1); for (int j = 0; j < cellCount; j++) { row.CreateCell(j).SetCellType(GetCellType(dtExcel.Columns[j].DataType.ToString())); row.CreateCell(j).SetCellValue(dtExcel.Rows[i][j].ToString()); } } for (int i = 0; i < dtExcel.Columns.Count; i++) { // sheet.AutoSizeColumn(i, false);// 此方法导致内存占用100%,导出速度慢 sheet.SetColumnWidth(i, 9000); } //Write the stream data of workbook to the root directory FileStream file = new FileStream(excelPath, FileMode.Create); workbook.Write(file); file.Close(); return true; } catch (System.Exception ex) { Error = "生成Excel失败:" + ex.Message; return false; } } private bool DatatableToExcel2003(ref DataTable dtExcel, string excelPath) { try { HSSFWorkbook workbook = new HSSFWorkbook(); //create a entry of DocumentSummaryInformation DocumentSummaryInformation dsi = PropertySetFactory.CreateDocumentSummaryInformation(); dsi.Company = "Partner"; workbook.DocumentSummaryInformation = dsi; //create a entry of SummaryInformation SummaryInformation si = PropertySetFactory.CreateSummaryInformation(); si.Subject = "FrontLink"; workbook.SummaryInformation = si; //here, we must insert at least one sheet to the workbook. otherwise, Excel will say 'data lost in file' //So we insert three sheet just like what Excel does ISheet sheet = workbook.CreateSheet(dtExcel.TableName); int cellCount = dtExcel.Columns.Count; IRow column = sheet.CreateRow(0); for (int j = 0; j < cellCount; j++) { column.CreateCell(j).SetCellType(GetCellType(dtExcel.Columns[j].DataType.ToString())); column.CreateCell(j).SetCellValue(dtExcel.Columns[j].ColumnName); } for (int i = 0; i < dtExcel.Rows.Count; i++) { IRow row = sheet.CreateRow(i + 1); for (int j = 0; j < cellCount; j++) { row.CreateCell(j).SetCellType(GetCellType(dtExcel.Columns[j].DataType.ToString())); row.CreateCell(j).SetCellValue(dtExcel.Rows[i][j].ToString()); } } for (int i = 0; i < dtExcel.Columns.Count; i++) { //sheet.AutoSizeColumn(i, false); // 此方法导致内存占用100%,导出速度慢 sheet.SetColumnWidth(i, 9000); } //Write the stream data of workbook to the root directory FileStream file = new FileStream(excelPath, FileMode.Create); workbook.Write(file); file.Close(); return true; } catch (System.Exception ex) { Error = "生成Excel失败:" + ex.Message; return false; } } #endregion #region 读取excel文件 /// <summary> /// 读取单元格的数据，并转化成字符串值 /// </summary> /// <param name="cell"></param> /// <returns></returns> private string GetCellValue(ICell cell) { string value = ""; if (cell != null) { string format = cell.CellStyle.GetDataFormatString(); switch (cell.CellType) { case CellType.String: value = cell.StringCellValue; break; case CellType.Numeric: if (format != null && (format.IndexOf("m") > 0 || format.IndexOf("d") > 0)) { if (format == "m/d/yy") { DateTime dt=cell.DateCellValue; value = dt.ToString("yyyy-MM-dd"); } else { //DateTime dt = DateTime.FromOADate(cell.NumericCellValue); value = cell.ToString(); } } else { value = cell.NumericCellValue.ToString(); } break; case CellType.Boolean: value = cell.BooleanCellValue.ToString(); break; case CellType.Formula: value = cell.CellFormula; break; case CellType.Blank: value = ""; break; default: break; } } return value; } /// <summary> /// 读取Excel文件内容到表里 /// </summary> /// <param name="excelPath"></param> /// <param name="sheetName"></param> /// <param name="tableName"></param> /// <param name="fieldNameList">fieldNameList有值，为指定的字段，为“”；取excel的第一行做为列值；为null，取excel的列名</param> /// <param name="getMaxRecord">取值范围：-1取全部的值；>0取指定的值</param> /// <param name="dtExcel"></param> /// <returns></returns> private bool LocalReadExcelToDataTable(string excelPath, string sheetName, string tableName, string fieldNameList, int getMaxRecord, out DataTable dtExcel) { dtExcel = new DataTable(tableName); FileStream file = null; try { sheetName = sheetName.Trim(); file = new FileStream(excelPath, FileMode.Open); IWorkbook workbook = WorkbookFactory.Create(file); //HSSFWorkbook workbook = new HSSFWorkbook(file);//创建工作簿对象 NPOI.SS.UserModel.ISheet sheet = null; if (sheetName.Length == 0) { sheet = workbook.GetSheetAt(0); } else { sheet = workbook.GetSheet(sheetName); } int sheetHaveHeader = 0; int cellCount = 0; //创建表标题 if (fieldNameList == null) { NPOI.SS.UserModel.IRow headerRow = sheet.GetRow(0); cellCount = headerRow.LastCellNum; //读取并生成标题行，这里能成功执行 for (int i = headerRow.FirstCellNum; i < cellCount; i++) { DataColumn column = new DataColumn(i.ToString()); dtExcel.Columns.Add(column); } } else if (fieldNameList.Trim().Length > 0) { string[] fieldList = fieldNameList.Split(','); foreach (string field in fieldList) { dtExcel.Columns.Add(new DataColumn(field.Trim())); } cellCount = fieldList.Length; sheetHaveHeader = 1; } else { sheetHaveHeader = 1; NPOI.SS.UserModel.IRow headerRow = sheet.GetRow(0); cellCount = headerRow.LastCellNum; //读取并生成标题行，这里能成功执行 for (int i = headerRow.FirstCellNum; i < cellCount; i++) { DataColumn column = new DataColumn(GetCellValue(headerRow.GetCell(i, MissingCellPolicy.RETURN_BLANK_AS_NULL))); dtExcel.Columns.Add(column); } } bool bFilter = getMaxRecord >= 0; //逐个读取单元格，这里就不能正确读取到 for (int i = (sheet.FirstRowNum + sheetHaveHeader); i <= sheet.LastRowNum; i++) { if (bFilter) { // 只取指定的记录数 if (dtExcel.Rows.Count >= getMaxRecord) { break; } } NPOI.SS.UserModel.IRow row = sheet.GetRow(i); DataRow dataRow = dtExcel.NewRow(); for (int j = row.FirstCellNum; j < cellCount; j++) { dataRow[j] = GetCellValue(row.GetCell(j, MissingCellPolicy.RETURN_BLANK_AS_NULL)).Replace("'", "’"); } dtExcel.Rows.Add(dataRow); } //for (int i = 0; i < dtExcel.Columns.Count; i++) //{ // // sheet.AutoSizeColumn(i, false); // 此方法导致内存占用100%,导出速度慢 // sheet.SetColumnWidth(i, 9000); //} workbook = null; sheet = null; return true; } catch (System.Exception ex) { Error = "读取Excel失败:" + ex.Message; return false; } finally { if (file != null) { file.Close(); } } } public bool ReadExcelToDataTable(string excelPath, string sheetName, string tableName, string fieldNameList, out DataTable dtExcel) { return LocalReadExcelToDataTable(excelPath, sheetName, tableName, fieldNameList, -1, out dtExcel); } public bool ReadExcelToDataTable(string excelPath, string sheetName, string tableName, int getMaxRecord, out DataTable dtExcel) { return LocalReadExcelToDataTable(excelPath, sheetName, tableName, null, getMaxRecord, out dtExcel); } #endregion }}
1 0
- NPOI ADO office wps 四种方式 读excel表格
- 金山WPS、微软Office EXCEL表格通用C++接口
- NPOI操作Excel表格
- NPOI Excel表格处理
- vb6.0 office excel 和wps共存，创建正确的excel对象以便操作excel表格
- vb.net office excel 和wps共存，创建正确的excel对象以便操作excel表格
- 天易26----java导出excel表格(支持wps和office excel)
- NPOI导出到Excel表格
- NPOI 读写Excel、WORD 免安装Office
- 使用NPOI编辑Office（Excel）
- ADO操作Excel表格
- ADO操作Excel表格
- NPOI方式 导出Excel表头
- Office EXCEL 表格如何设置某个单元格是选择项，如何设置二级下拉菜单 WPS版
- NPOI读写Excel 或 Microsoft.Office.Interop.Excel 读取excel
- Asp.net NPOI导入导出Excel表格
- Npoi Excel表格叫做工作表
- NPOI处理excel表格的问题
- android--1--SDK命令行搭建并运行Android
- AJAX跨域访问的实现
- AVL树
- 进程篇上之初识进程（2）
- neuoj-wanghang走迷宫-状态压缩记忆化搜索
- NPOI ADO office wps 四种方式 读excel表格
- Java反射之Field用法
- 第五章 PL/SQL
- python+OpenCV 特征点检测
- JS缓冲运动
- 火车站、汽车站乘车建议
- Scrapy的架构初探
- hibernate的WARN错误HHH000223问题解决
- Android Fragment 的使用
