c#使用开源控件读卡excel

因工作关系，使用c#读写excel文件，试过myxls, 与NPOI这两个组件，读写excel文件都没问题，不过都读不了excel2007格式的，目前还未找到开源能读excel2007的。

如果只是读或者写，这两个组件都可以。但是如果是读写一起进行，那myxls组件在保存时就会有问题，需要先读出原来的excel文件，再删除，然后再保存到这个文件里。这样不合理。npoi没这个问题，先这个文件里读出来之后，可以再保存回这个文件，在此说明。

使用npoi读写excel

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; namespace SyncTool { class CExcel { public string m_LastError = ""; private CellType GetCellType( string type ) { CellType cell = CellType.STRING; if (type == "System.Double" || type=="System.Int32" ) { cell = CellType.NUMERIC; } return cell; } private void SetCellValue(Cell cell, string type, string data) { CellType cellType = CellType.STRING; if (type == "System.Double" || type == "System.Int32" || type=="System.Decimal") { cellType = CellType.NUMERIC; cell.SetCellType(cellType); cell.SetCellValue(double.Parse(data)); } else { cell.SetCellType(cellType); cell.SetCellValue(data); } } public bool DatatableToExcel( ref DataTable dtExcel, string excelPath ) { try { HSSFWorkbook hssfworkbook = new HSSFWorkbook(); create a entry of DocumentSummaryInformation DocumentSummaryInformation dsi = PropertySetFactory.CreateDocumentSummaryInformation(); dsi.Company = "Austec"; hssfworkbook.DocumentSummaryInformation = dsi; create a entry of SummaryInformation SummaryInformation si = PropertySetFactory.CreateSummaryInformation(); si.Subject = "图书发退库"; 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 Sheet sheet = hssfworkbook.CreateSheet(dtExcel.TableName); int cellCount= dtExcel.Columns.Count; Row column = sheet.CreateRow(0); for( int j=0; j<cellCount; j++) { Cell cell = column.CreateCell(j); cell.SetCellType(GetCellType(dtExcel.Columns[j].DataType.ToString())); cell.SetCellValue(dtExcel.Columns[j].ColumnName); sheet.SetColumnWidth(j, 23 * 256); } for( int i=0; i<dtExcel.Rows.Count; i++) { Row row = sheet.CreateRow(i+1); for( int j=0; j<cellCount; j++) { Cell cell = row.CreateCell(j); SetCellValue(cell, dtExcel.Columns[j].DataType.ToString(), dtExcel.Rows[i][j].ToString()); } } //Write the stream data of workbook to the root directory FileStream file = new FileStream(excelPath, FileMode.Create); hssfworkbook.Write(file); file.Close(); return true; } catch (System.Exception ex) { m_LastError = "读取Excel失败:" + ex.Message; return false; } } /// <summary> /// 读取单元格的数据，并转化成字符串值 /// </summary> /// <param name="cell"></param> /// <returns></returns> private string GetCellValue(Cell cell) { string value = ""; if(cell != null) { switch (cell.CellType ) { case CellType.STRING: value = cell.StringCellValue; break; case CellType.NUMERIC: 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"></param> /// <param name="dtExcel"></param> /// <returns></returns> public bool ReadExcelToDataTable(string excelPath, string sheetName, string tableName, string fieldNameList, out DataTable dtExcel) { dtExcel = new DataTable(tableName); sheetName = sheetName.Trim(); try { FileStream file= new FileStream(excelPath, FileMode.Open); HSSFWorkbook workbook = new HSSFWorkbook(file);//创建工作簿对象 NPOI.SS.UserModel.Sheet sheet = null; if (sheetName.Length == 0) { sheet = workbook.GetSheetAt(0); } else { sheet = workbook.GetSheet(sheetName); } int sheetHaveHeader = 0; int cellCount = 0; //创建表标题 if (fieldNameList.Trim().Length>0 ) { string[] fieldList = fieldNameList.Split(','); foreach ( string field in fieldList ) { dtExcel.Columns.Add(new DataColumn(field.Trim())); } cellCount = fieldList.Length; } else { sheetHaveHeader = 1; NPOI.SS.UserModel.Row 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); } } //逐个读取单元格，这里就不能正确读取到 for (int i = (sheet.FirstRowNum + sheetHaveHeader); i <= sheet.LastRowNum; i++) { NPOI.SS.UserModel.Row 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)); } dtExcel.Rows.Add(dataRow); } file.Close(); workbook = null; sheet = null; return true; } catch (System.Exception ex) { m_LastError = "读取Excel失败:" + ex.Message; return false; } } public bool DataTableInsertIntoExcel(string excelPath, string sheetName, ref DataTable dtExcel) { try { bool bFindXlsFile = File.Exists(excelPath); FileStream file = null; HSSFWorkbook workbook = null; if (bFindXlsFile) { file = new FileStream(excelPath, FileMode.Open, FileAccess.Read); workbook = new HSSFWorkbook(file);//创建工作簿对象 file.Close(); } else { workbook = new HSSFWorkbook(); } NPOI.SS.UserModel.Sheet sheet = null; sheet= workbook.GetSheet(sheetName); if (sheet == null) { sheet = workbook.CreateSheet(sheetName); } int cellCount = dtExcel.Columns.Count; Row column = sheet.CreateRow(0); for (int j = 0; j < cellCount; j++) { Cell cell = column.CreateCell(j); cell.SetCellType(GetCellType(dtExcel.Columns[j].DataType.ToString())); cell.SetCellValue(dtExcel.Columns[j].ColumnName); sheet.SetColumnWidth(j, 23 * 256); } for (int i = 0; i < dtExcel.Rows.Count; i++) { Row row = sheet.CreateRow(i + 1); for (int j = 0; j < cellCount; j++) { Cell cell = row.CreateCell(j); SetCellValue(cell, dtExcel.Columns[j].DataType.ToString(), dtExcel.Rows[i][j].ToString()); } } //Write the stream data of workbook to the root directory file = new FileStream(excelPath, FileMode.OpenOrCreate, FileAccess.ReadWrite); workbook.Write(file); file.Close(); workbook = null; sheet = null; return true; } catch (System.Exception ex) { m_LastError = "读取Excel失败:" + ex.Message; return false; } } } }

使用myxls读写excel

private bool ReadExcel( string csFilePath, out DataTable dt ) { // 建立表名 dt = new DataTable("client"); string[] strFields = { "name", "code", "letter" }; foreach (string field in strFields) { DataColumn col = new DataColumn(field); dt.Columns.Add(field); } if ( !File.Exists(csFilePath)) { m_LastError = "未找到客户代码文件!"; return false; } /// 读取Excel文件 XlsDocument xls = new XlsDocument(csFilePath); Worksheet sheet = xls.Workbook.Worksheets[0]; for (ushort i = 1; i < sheet.Rows.Count; i++) { DataRow row = dt.NewRow(); for (ushort j = 1; j <=2 ; j++) //sheet.Rows[j].CellCount { row[j-1] = sheet.Rows[i].GetCell(j).Value.ToString().Trim(); } row[2] = CLetterCode.GetChineseLetterCode(row[0].ToString()); dt.Rows.Add(row); } return true; } public bool SaveDBtoExcel(DataSet ds, string xlsPath, string xlsName) { //XlsDocument xls = new XlsDocument(); //xls.FileName = xlsName; //XF xf = xls.NewXF();// 格式对象 //xf.Font.FontName = "宋体"; xf.HorizontalAlignment = org.in2bits.MyXls.HorizontalAlignments.Centered;//左右居中 xf.VerticalAlignment = org.in2bits.MyXls.VerticalAlignments.Centered;//上线居中 xf.BottomLineStyle = ushort.Parse("2");//下边线粗 xf.BottomLineColor = org.in2bits.MyXls.Colors.Default0C;//下边线颜色 //xf.Font.Height = 260;//自体高度，也是大小 //foreach (DataTable dt in ds.Tables) //{ // Worksheet sheet = xls.Workbook.Worksheets.Add(dt.TableName); // for (ushort n = 1; n <= dt.Columns.Count; n++) // { // ColumnInfo colInfo = new ColumnInfo(xls, sheet); // colInfo.ColumnIndexStart = n; // colInfo.ColumnIndexEnd = n; // if (n < dt.Columns.Count) // { // colInfo.Width = 15 * 256; // } // else // { // colInfo.Width = 23 * 256; // } // sheet.AddColumnInfo(colInfo); // } // Cells cells = sheet.Cells; // for (ushort i = 0; i < dt.Rows.Count; i++) // { // for (ushort j = 0; j < dt.Columns.Count; j++) // { // Cell cell = cells.AddValueCellXF(i + 1, j + 1, dt.Rows[i][j].ToString(), xf); // } // } //} //xls.Save(xlsPath, true); return true; }