using NPOI.HSSF.UserModel; using NPOI.HPSF; using NPOI.POIFS.FileSystem; /// <summary> /// Excel生成操作类 /// </summary> public class NPOIHelpers { #region 导出Excel文件 /// <summary> /// 导出Excel文件 /// </summary> /// <param name="dt">DataTable数据源</param> /// <param name="filepath">保存的文件路径</param> /// <param name="sheetname">Excel文件中Sheet名</param> public static void ExportExcel(DataTable dt, string filepath, string sheetname) { HSSFWorkbook excel = new HSSFWorkbook(); DataSet ds = new DataSet(); ds.Tables.Add(dt.Copy()); string[] sheetnames = { sheetname }; InsertRecord(excel, ds, sheetnames); using (MemoryStream ms = new MemoryStream()) { excel.Write(ms); ms.Flush(); using (FileStream fs = new FileStream(filepath, FileMode.Create, FileAccess.Write)) { byte[] data = ms.ToArray(); fs.Write(data, 0, data.Length); fs.Flush(); } } } public static void ExportExcel(DataSet ds, string filepath, string resultTableName, string settingTableName) { string[] sheetname = { resultTableName, settingTableName }; HSSFWorkbook excel = new HSSFWorkbook(); InsertRecord2(excel, ds, sheetname); using (MemoryStream ms = new MemoryStream()) { excel.Write(ms); ms.Flush(); using (FileStream fs = new FileStream(filepath, FileMode.Create, FileAccess.Write)) { byte[] data = ms.ToArray(); fs.Write(data, 0, data.Length); fs.Flush(); } } } /// <summary> /// 导出Excel文件 /// </summary> /// <param name="ds">DataSet数据源</param> /// <param name="filepath">保存的文件路径</param> /// <param name="sheetname">Excel文件中Sheet名</param> public static void ExportExcel(DataSet ds, string filepath, string[] sheetname) { HSSFWorkbook excel = new HSSFWorkbook(); InsertRecord(excel, ds, sheetname); using (MemoryStream ms = new MemoryStream()) { excel.Write(ms); ms.Flush(); using (FileStream fs = new FileStream(filepath, FileMode.Create, FileAccess.Write)) { byte[] data = ms.ToArray(); fs.Write(data, 0, data.Length); fs.Flush(); } } } /// <summary> /// 插入数据行 /// </summary> /// <param name="excel">Excel对象</param> /// <param name="ds">数据源</param> /// <param name="sheetname">Sheet表名</param> private static void InsertRecord(HSSFWorkbook excel, DataSet ds, string[] sheetname) { int j=0; int k=0; try { for (int i = 0; i < ds.Tables.Count; i++) { HSSFSheet sheet = (HSSFSheet)excel.CreateSheet(sheetname[i]); DataTable dt = ds.Tables[i]; #region 字段标题 HSSFRow fieldName = (HSSFRow)sheet.CreateRow(0); for (j = 0; j < dt.Columns.Count; j++) { fieldName.CreateCell(j).SetCellValue(dt.Columns[j].ColumnName); } #endregion #region 数据行 for (j = 0; j < dt.Rows.Count; j++) { HSSFRow row = (HSSFRow)sheet.CreateRow(j + 1); for (k = 0; k < dt.Columns.Count; k++) { HSSFCell cell = (HSSFCell)row.CreateCell(k); System.Type rowType = dt.Rows[j][k].GetType(); string drValue = dt.Rows[j][k].ToString().Trim(); switch (rowType.ToString()) { case "System.String"://字符串类型 drValue = drValue.Replace("&", "&"); drValue = drValue.Replace(">", ">"); drValue = drValue.Replace("<", "<"); cell.SetCellValue(drValue); break; case "System.DateTime"://日期类型 DateTime dateV; DateTime.TryParse(drValue, out dateV); cell.SetCellValue(drValue); //格式化显示 HSSFCellStyle cellStyle = (HSSFCellStyle)excel.CreateCellStyle(); HSSFDataFormat format = (HSSFDataFormat)excel.CreateDataFormat(); cellStyle.DataFormat = format.GetFormat("yyyy-mm-dd hh:mm:ss"); cell.CellStyle = cellStyle; break; case "System.Boolean"://布尔型 bool boolV = false; bool.TryParse(drValue, out boolV); cell.SetCellValue(boolV); break; case "System.Int16"://整型 case "System.Int32": case "System.Int64": case "System.Byte": int intV = 0; int.TryParse(drValue, out intV); cell.SetCellValue(intV); break; case "System.Decimal"://浮点型 case "System.Double": double doubV = 0; double.TryParse(drValue, out doubV); cell.SetCellValue(doubV); break; default: cell.SetCellValue(""); break; } } } #endregion } } catch (Exception ex) { //BIStone.Common.CatchLog.WriteErrorLog(ex+"Row:"+j+"Column:"+k); } } /// <summary> /// 插入数据行 /// </summary> /// <param name="excel">Excel对象</param> /// <param name="ds">数据源</param> /// <param name="sheetname">Sheet表名</param> private static void InsertRecord2(HSSFWorkbook excel, DataSet ds, string[] sheetname) { for (int i = 0; i < ds.Tables.Count; i++) { HSSFSheet sheet = (HSSFSheet)excel.CreateSheet(sheetname[i]); DataTable dt = ds.Tables[i]; #region 字段标题 HSSFRow fieldName = (HSSFRow)sheet.CreateRow(0); for (int j = 0; j < dt.Columns.Count; j++) { if (i == 0) { fieldName.CreateCell(j).SetCellValue(GetQuestionLabel(ds.Tables[1], dt.Columns[j].ColumnName)); } else { fieldName.CreateCell(j).SetCellValue(dt.Columns[j].ColumnName); } } #endregion #region 数据行 for (int j = 0; j < dt.Rows.Count; j++) { HSSFRow row = (HSSFRow)sheet.CreateRow(j + 1); for (int k = 0; k < dt.Columns.Count; k++) { HSSFCell cell = (HSSFCell)row.CreateCell(k); System.Type rowType = dt.Rows[j][k].GetType(); string drValue = dt.Rows[j][k].ToString().Trim(); switch (rowType.ToString()) { case "System.String"://字符串类型 drValue = drValue.Replace("&", "&"); drValue = drValue.Replace(">", ">"); drValue = drValue.Replace("<", "<"); cell.SetCellValue(drValue); break; case "System.DateTime"://日期类型 DateTime dateV; DateTime.TryParse(drValue, out dateV); cell.SetCellValue(drValue); //格式化显示 HSSFCellStyle cellStyle = (HSSFCellStyle)excel.CreateCellStyle(); HSSFDataFormat format = (HSSFDataFormat)excel.CreateDataFormat(); cellStyle.DataFormat = format.GetFormat("yyyy-mm-dd hh:mm:ss"); cell.CellStyle = cellStyle; break; case "System.Boolean"://布尔型 bool boolV = false; bool.TryParse(drValue, out boolV); cell.SetCellValue(boolV); break; case "System.Int16"://整型 case "System.Int32": case "System.Int64": case "System.Byte": int intV = 0; int.TryParse(drValue, out intV); cell.SetCellValue(intV); break; case "System.Decimal"://浮点型 case "System.Double": double doubV = 0; double.TryParse(drValue, out doubV); cell.SetCellValue(doubV); break; default: cell.SetCellValue(""); break; } } } #endregion } } /// <summary> /// 得到问题题干 /// </summary> /// <param name="dt">Setting表</param> /// <param name="qtag">问题其他</param> /// <returns>问题题干</returns> private static string GetQuestionLabel(DataTable dt, string qtag) { DataRow[] drs = dt.Select("QTag='" + qtag + "'"); if (drs.Length > 0) { return drs[0]["DataSetVar"].ToString(); } else { return qtag; } } #endregion #region 读Excel文件 /// <summary> /// 读取Excel文件 /// </summary> /// <param name="filepath">文件路径</param> /// <param name="sheetname">Sheet表名</param> /// <returns>DataTable</returns> public static DataTable ReadExcel(string filepath, string sheetname) { DataTable dt = new DataTable(); HSSFWorkbook hssfworkbook; using (FileStream file = new FileStream(filepath, FileMode.Open, FileAccess.Read)) { hssfworkbook = new HSSFWorkbook(file); } HSSFSheet sheet = (HSSFSheet)hssfworkbook.GetSheet(sheetname); System.Collections.IEnumerator rows = sheet.GetRowEnumerator(); if (sheet.LastRowNum > 1) { //读取表头 HSSFRow headerRow = (HSSFRow)sheet.GetRow(0); HSSFRow firstRow = (HSSFRow)sheet.GetRow(1); int cellCount = headerRow.LastCellNum; for (int i = 0; i < cellCount; i++) { DataColumn dc = new DataColumn(); HSSFCell cell = (HSSFCell)headerRow.GetCell(i); if (cell != null) dc.ColumnName = cell.ToString(); else dc.ColumnName = "col" + i; cell = (HSSFCell)firstRow.GetCell(i); try { switch (cell.CellType) { case CellType.BOOLEAN: dc.DataType = Type.GetType("System.Boolean"); break; case CellType.NUMERIC: dc.DataType = Type.GetType("System.Double"); break; default: dc.DataType = Type.GetType("System.String"); if (cell.CellStyle.DataFormat == 164) { dc.DataType = Type.GetType("System.DateTime"); } break; } } catch { } dt.Columns.Add(dc); } //读取数据 for (int i = (sheet.FirstRowNum + 1); i <= sheet.LastRowNum; i++) { try { HSSFRow row = (HSSFRow)sheet.GetRow(i); if (row != null) { DataRow dataRow = dt.NewRow(); for (int j = row.FirstCellNum; j < cellCount; j++) { if (row.GetCell(j) != null) dataRow[j] = row.GetCell(j).ToString(); } dt.Rows.Add(dataRow); } } catch { } } } return dt; } /// <summary> /// 得到Excel文件所有Sheet名 /// </summary> /// <param name="filepath">文件路径</param> /// <returns>String[]</returns> public static string[] GetSheetNames(string filepath) { HSSFWorkbook hssfworkbook; using (FileStream file = new FileStream(filepath, FileMode.Open, FileAccess.Read)) { hssfworkbook = new HSSFWorkbook(file); } string[] sheetnames = new string[hssfworkbook.NumberOfSheets]; for (int i = 0; i < hssfworkbook.NumberOfSheets; i++) { sheetnames[i] = hssfworkbook.GetSheetAt(i).SheetName; } return sheetnames; } #endregion public static void ExportExcels(DataSet ds, string filepath, string[] sheetname, string settingTableName) { HSSFWorkbook excel = new HSSFWorkbook(); InsertRecord2s(excel, ds, sheetname); using (MemoryStream ms = new MemoryStream()) { excel.Write(ms); ms.Flush(); using (FileStream fs = new FileStream(filepath, FileMode.Create, FileAccess.Write)) { byte[] data = ms.ToArray(); fs.Write(data, 0, data.Length); fs.Flush(); } } } /// <summary> /// 插入数据行 /// </summary> /// <param name="excel">Excel对象</param> /// <param name="ds">数据源</param> /// <param name="sheetname">Sheet表名</param> private static void InsertRecord2s(HSSFWorkbook excel, DataSet ds, string[] sheetname) { for (int i = 0; i < ds.Tables.Count; i++) { HSSFSheet sheet = (HSSFSheet)excel.CreateSheet(sheetname[i]); DataTable dt = ds.Tables[i]; #region 字段标题 int count = ds.Tables.Count - 1; HSSFRow fieldName = (HSSFRow)sheet.CreateRow(0); for (int j = 0; j < dt.Columns.Count; j++) { if (i != count) { fieldName.CreateCell(j).SetCellValue(GetQuestionLabel(ds.Tables[count], dt.Columns[j].ColumnName)); } else { fieldName.CreateCell(j).SetCellValue(dt.Columns[j].ColumnName); } } #endregion #region 数据行 for (int j = 0; j < dt.Rows.Count; j++) { HSSFRow row = (HSSFRow)sheet.CreateRow(j + 1); for (int k = 0; k < dt.Columns.Count; k++) { HSSFCell cell = (HSSFCell)row.CreateCell(k); System.Type rowType = dt.Rows[j][k].GetType(); string drValue = dt.Rows[j][k].ToString().Trim(); switch (rowType.ToString()) { case "System.String"://字符串类型 drValue = drValue.Replace("&", "&"); drValue = drValue.Replace(">", ">"); drValue = drValue.Replace("<", "<"); cell.SetCellValue(drValue); break; case "System.DateTime"://日期类型 DateTime dateV; DateTime.TryParse(drValue, out dateV); cell.SetCellValue(drValue); //格式化显示 HSSFCellStyle cellStyle = (HSSFCellStyle)excel.CreateCellStyle(); HSSFDataFormat format = (HSSFDataFormat)excel.CreateDataFormat(); cellStyle.DataFormat = format.GetFormat("yyyy-mm-dd hh:mm:ss"); cell.CellStyle = cellStyle; break; case "System.Boolean"://布尔型 bool boolV = false; bool.TryParse(drValue, out boolV); cell.SetCellValue(boolV); break; case "System.Int16"://整型 case "System.Int32": case "System.Int64": case "System.Byte": int intV = 0; int.TryParse(drValue, out intV); cell.SetCellValue(intV); break; case "System.Decimal"://浮点型 case "System.Double": double doubV = 0; double.TryParse(drValue, out doubV); cell.SetCellValue(doubV); break; default: cell.SetCellValue(""); break; } } } #endregion } } }
posted on
