public static int QuickWrite(ExcelSheet excelSheet, string filePath, ExcelVersions version = ExcelVersions.Xls) { using (FileStream fs = File.Open(filePath, FileMode.OpenOrCreate, FileAccess.ReadWrite)) { IWorkbook workbook = OpenWrite(version); ISheet sheet = workbook.CreateSheet(string.IsNullOrEmpty(excelSheet.Name) ? "sheet1" : excelSheet.Name); for (int i = 0; i < excelSheet.Rows.Count; i++) { IRow row = sheet.CreateRow(i); ExcelRow excelRow = excelSheet.Rows[i]; for (int j = 0; j < excelRow.Cells.Count; j++) { ExcelCell excelCell = excelRow.GetCell(j); ICell cell = CreateCell(row, j, excelCell); } } workbook.Write(fs); fs.Close(); return(DotNETCode.SUCCESS); } }
private static IWorkbook OpenWrite(ExcelVersions version) { switch (version) { case ExcelVersions.Xls: { return(new HSSFWorkbook()); } case ExcelVersions.Xlsx: { return(new XSSFWorkbook()); } default: throw new NotSupportedException("不支持的Excel格式"); } }
/// <summary> /// 把ExcelSheet对象保存成Excel文件 /// </summary> /// <param name="excelSheet"></param> /// <param name="filePath"></param> /// <param name="version"></param> /// <returns></returns> public static int Write <TRow>(string filePath, IList <TRow> dataList, ExcelVersions version = ExcelVersions.Xls) where TRow : IExcelRow { List <PropertyAttribute <ExcelColumnAttribute> > properties = Reflections.GetPropertyAttribute <ExcelColumnAttribute>(typeof(TRow)); if (properties.Count == 0) { return(DotNETCode.SUCCESS); } ExcelSheet sheet = new ExcelSheet(); // 先写第一行,相当于是标题 ExcelRow titleRow = new ExcelRow(); IEnumerable <ExcelColumnAttribute> columns = properties.Select(v => v.Attribute); foreach (ExcelColumnAttribute column in columns) { titleRow.AddCell(column.Name, ExcelCellTypes.String); } sheet.AddRow(titleRow); foreach (TRow row in dataList) { ExcelRow excelRow = new ExcelRow(); foreach (PropertyAttribute <ExcelColumnAttribute> property in properties) { // 当前要写入的列 object value = property.Property.GetValue(row, null); excelRow.AddCell(value, property.Attribute.Type); } sheet.AddRow(excelRow); } return(QuickWrite(sheet, filePath, version)); }
/// <summary> /// 读取excel文件获得DataReader /// </summary> /// <param name="strExcelFileName">Excel文件</param> /// <param name="strSheetName">工作薄的名字</param> /// <param name="exVersions">版本</param> /// <returns></returns> public static OleDbDataReader ExcelToDataReader(string strExcelFileName, string strSheetName, ExcelVersions exVersions) { string ConnectString = string.Empty; switch (exVersions) { case ExcelVersions.Excel8: ConnectString = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + strExcelFileName + ";" + "Extended Properties='Excel 8.0;HDR=YES;IMEX=1';"; break; case ExcelVersions.Excel12: ConnectString = "Provider=Microsoft.ACE.OLEDB.12.0;" + "Data Source=" + strExcelFileName + ";" + "Extended Properties='Excel 12.0 Xml;HDR=YES;IMEX=1';"; break; default: break; } string strExcel = "select * from [" + strSheetName + "$]"; OleDbConnection conn = new OleDbConnection(ConnectString); OleDbCommand cmd = new OleDbCommand(strExcel, conn); try { conn.Open(); OleDbDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection); return(reader); } catch (System.Data.OleDb.OleDbException e) { throw new Exception(e.Message); } }
/// <summary> /// 读取excel文件获得DataTable /// </summary> /// <param name="strExcelFileName">目标Excel文件完全路径</param> /// <param name="strSheetName">工作表的名字</param> /// <returns></returns> public static DataTable ExcelToDataTable(string strExcelFileName, string strSheetName, ExcelVersions exVersions) { string ConnectString = string.Empty; switch (exVersions) { case ExcelVersions.Excel8: ConnectString = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + strExcelFileName + ";" + "Extended Properties='Excel 8.0;HDR=YES;IMEX=1';"; break; case ExcelVersions.Excel12: ConnectString = "Provider=Microsoft.ACE.OLEDB.12.0;" + "Data Source=" + strExcelFileName + ";" + "Extended Properties='Excel 12.0 Xml;HDR=YES;IMEX=1';"; break; default: break; } string strExcel = "select * from [" + strSheetName + "$]"; //DataSet ds = new DataSet(); DataTable dt = new DataTable(); using (OleDbConnection conn = new OleDbConnection(ConnectString)) { conn.Open(); OleDbDataAdapter adapter = new OleDbDataAdapter(strExcel, ConnectString); //adapter.Fill(ds, strSheetName); //return ds.Tables[strSheetName]; adapter.Fill(dt); dt.TableName = strSheetName; return(dt); } #region MyRegion //很简单的代码,但是问题就出在连接字符串上面,后面一定要加上Extended Properties='Excel 8.0;HDR=NO;IMEX=1',HDR和IMEX也一定要配合使用, //哈哈,老实说,我也不知道为什么,这样配合的效果最好,这是我艰苦调试的结果.IMEX=1应该是将所有的列全部视为文本,我也有点忘记了. //至于HDR本来只是说是否要出现一行标题头而已,但是结果却会导致某些字段值丢失,所以其实我至今也搞不明白为什么,很可能是驱动的问题... //IMEX=1 解决数字与字符混合时,识别不正常的情况. #endregion }