public static void test1() { NpoiHelper np = new NpoiHelper(); DataTable dt1 = np.ReadExcel(AppDomain.CurrentDomain.BaseDirectory + "1测试数据.xls", 2).Tables[0];//读2003格式数据 DataSet ds1 = new DataSet(); ds1.Tables.Add(dt1.Copy()); ds1.AcceptChanges(); string SaveFileName = "output1.xls"; np.CreateExcel2003(SaveFileName, "sheet001", ds1, 0);//写2003格式数据 } public static void test2() { NpoiHelper np = new NpoiHelper(); DataTable dt1 = np.ReadExcel(AppDomain.CurrentDomain.BaseDirectory + "2测试数据.xlsx", 2).Tables[0];//读2007格式数据 DataSet ds1 = new DataSet(); ds1.Tables.Add(dt1.Copy()); ds1.AcceptChanges();
string SaveFileName = "output2.xlsx"; np.CreateExcel2007(SaveFileName, "sheet001", ds1, 0);//写2007格式数据 Console.ReadKey(); }
using System; using System.Collections.Generic; using System.Text; using System.IO; using NPOI.HSSF.UserModel; using System.Data; using System.Collections; using System.Drawing; using NPOI.HSSF.Util; using NPOI.SS.UserModel; using NPOI.HPSF; using NPOI.XSSF.UserModel;
namespace Tools.Common { /// <summary> /// Excel文件到DataSet的转换类 /// </summary> public class NpoiHelper { #region 读取Excel文件内容转换为DataSet /// <summary> /// 读取Excel文件内容转换为DataSet,列名依次为 "c0"……c[columnlength-1] /// </summary> /// <param name="FileName">文件绝对路径</param> /// <param name="startRow">数据开始行数(1为第一行)</param> /// <param name="ColumnDataType">每列的数据类型</param> /// <returns></returns> public DataSet ReadExcel(string FileName, int startRow, params NpoiDataType[] ColumnDataType) { int ertime = 0; int intime = 0; DataSet ds = new DataSet("ds"); DataTable dt = new DataTable("dt"); DataRow dr; StringBuilder sb = new StringBuilder(); using (FileStream stream = new FileStream(@FileName, FileMode.Open, FileAccess.Read)) { IWorkbook workbook = WorkbookFactory.Create(stream);//使用接口,自动识别excel2003/2007格式 ISheet sheet = workbook.GetSheetAt(0);//得到里面第一个sheet int j; IRow row; #region ColumnDataType赋值 if (ColumnDataType.Length <= 0) { row = sheet.GetRow(startRow - 1);//得到第i行 ColumnDataType = new NpoiDataType[row.LastCellNum]; for (int i = 0; i < row.LastCellNum; i++) { ICell hs = row.GetCell(i); ColumnDataType[i] = GetCellDataType(hs); } } #endregion for (j = 0; j < ColumnDataType.Length; j++) { Type tp = GetDataTableType(ColumnDataType[j]); dt.Columns.Add("c" + j, tp); } for (int i = startRow - 1; i <= sheet.PhysicalNumberOfRows; i++) { row = sheet.GetRow(i);//得到第i行 if (row == null) continue; try { dr = dt.NewRow();
for (j = 0; j < ColumnDataType.Length; j++) { dr["c" + j] = GetCellData(ColumnDataType[j], row, j); } dt.Rows.Add(dr); intime++; } catch (Exception er) { ertime++; sb.Append(string.Format("第{0}行出错:{1}\r\n", i + 1, er.Message)); continue; } } ds.Tables.Add(dt); } if (ds.Tables[0].Rows.Count == 0 && sb.ToString() != "") throw new Exception(sb.ToString()); return ds; } #endregion Color LevelOneColor = Color.Green; Color LevelTwoColor = Color.FromArgb(201, 217, 243); Color LevelThreeColor = Color.FromArgb(231, 238, 248); Color LevelFourColor = Color.FromArgb(232, 230, 231); Color LevelFiveColor = Color.FromArgb(250, 252, 213);
#region 从DataSet导出到MemoryStream流2003 /// <summary> /// 从DataSet导出到MemoryStream流2003 /// </summary> /// <param name="SaveFileName">文件保存路径</param> /// <param name="SheetName">Excel文件中的Sheet名称</param> /// <param name="ds">存储数据的DataSet</param> /// <param name="startRow">从哪一行开始写入,从0开始</param> /// <param name="datatypes">DataSet中的各列对应的数据类型</param> public bool CreateExcel2003(string SaveFileName, string SheetName, DataSet ds, int startRow, params NpoiDataType[] datatypes) { try { if (startRow < 0) startRow = 0; HSSFWorkbook wb = new HSSFWorkbook(); wb = new HSSFWorkbook(); DocumentSummaryInformation dsi = PropertySetFactory.CreateDocumentSummaryInformation(); dsi.Company = "pkm"; SummaryInformation si = PropertySetFactory.CreateSummaryInformation(); si.Title = si.Subject = "automatic genereted document"; si.Author = "pkm"; wb.DocumentSummaryInformation = dsi; wb.SummaryInformation = si; ISheet sheet = wb.CreateSheet(SheetName); //sheet.SetColumnWidth(0, 50 * 256); //sheet.SetColumnWidth(1, 100 * 256); IRow row; ICell cell; DataRow dr; int j; int maxLength = 0; int curLength = 0; object columnValue; DataTable dt = ds.Tables[0]; if (datatypes.Length < dt.Columns.Count) { datatypes = new NpoiDataType[dt.Columns.Count]; for (int i = 0; i < dt.Columns.Count; i++) { string dtcolumntype = dt.Columns[i].DataType.Name.ToLower(); switch (dtcolumntype) { case "string": datatypes[i] = NpoiDataType.String; break; case "datetime": datatypes[i] = NpoiDataType.Datetime; break; case "boolean": datatypes[i] = NpoiDataType.Bool; break; case "double": datatypes[i] = NpoiDataType.Numeric; break; default: datatypes[i] = NpoiDataType.String; break; } } }
#region 创建表头 row = sheet.CreateRow(0);//创建第i行 ICellStyle style1 = wb.CreateCellStyle();//样式 IFont font1 = wb.CreateFont();//字体
font1.Color = HSSFColor.WHITE.index;//字体颜色 font1.Boldweight = (short)FontBoldWeight.BOLD;//字体加粗样式 //style1.FillBackgroundColor = HSSFColor.WHITE.index;//GetXLColour(wb, LevelOneColor);// 设置图案色 style1.FillForegroundColor = HSSFColor.GREEN.index;//GetXLColour(wb, LevelOneColor);// 设置背景色 style1.FillPattern = FillPatternType.SOLID_FOREGROUND; style1.SetFont(font1);//样式里的字体设置具体的字体样式 style1.Alignment = HorizontalAlignment.CENTER;//文字水平对齐方式 style1.VerticalAlignment = VerticalAlignment.CENTER;//文字垂直对齐方式 row.HeightInPoints = 25; for (j = 0; j < dt.Columns.Count; j++) { columnValue = dt.Columns[j].ColumnName; curLength = Encoding.Default.GetByteCount(columnValue.ToString()); maxLength = (maxLength < curLength ? curLength : maxLength); int colounwidth = 256 * maxLength; sheet.SetColumnWidth(j, colounwidth); try { cell = row.CreateCell(j);//创建第0行的第j列 cell.CellStyle = style1;//单元格式设置样式
try { cell.SetCellType(CellType.STRING); cell.SetCellValue(columnValue.ToString()); } catch { }
} catch { continue; } } #endregion
#region 创建每一行 for (int i = startRow; i < ds.Tables[0].Rows.Count; i++) { dr = ds.Tables[0].Rows[i]; row = sheet.CreateRow(i + 1);//创建第i行 for (j = 0; j < dt.Columns.Count; j++) { columnValue = dr[j]; curLength = Encoding.Default.GetByteCount(columnValue.ToString()); maxLength = (maxLength < curLength ? curLength : maxLength); int colounwidth = 256 * maxLength; sheet.SetColumnWidth(j, colounwidth); try { cell = row.CreateCell(j);//创建第i行的第j列 #region 插入第j列的数据 try { NpoiDataType dtype = datatypes[j]; switch (dtype) { case NpoiDataType.String: { cell.SetCellType(CellType.STRING); cell.SetCellValue(columnValue.ToString()); } break; case NpoiDataType.Datetime: { cell.SetCellType(CellType.STRING); cell.SetCellValue(columnValue.ToString()); } break; case NpoiDataType.Numeric: { cell.SetCellType(CellType.NUMERIC); cell.SetCellValue(Convert.ToDouble(columnValue)); } break; case NpoiDataType.Bool: { cell.SetCellType(CellType.BOOLEAN); cell.SetCellValue(Convert.ToBoolean(columnValue)); } break; case NpoiDataType.Richtext: { cell.SetCellType(CellType.FORMULA); cell.SetCellValue(columnValue.ToString()); } break; } } catch { cell.SetCellType(CellType.STRING); cell.SetCellValue(columnValue.ToString()); } #endregion
} catch { continue; } } } #endregion
//using (FileStream fs = new FileStream(@SaveFileName, FileMode.OpenOrCreate))//生成文件在服务器上 //{ // wb.Write(fs); //} //string SaveFileName = "output.xls"; using (FileStream fs = new FileStream(@SaveFileName, FileMode.OpenOrCreate, FileAccess.Write))//生成文件在服务器上 { wb.Write(fs); Console.WriteLine("文件保存成功!" + SaveFileName); }
return true; } catch (Exception er) { Console.WriteLine("文件保存成功!" + SaveFileName); return false; }
} #endregion
#region 从DataSet导出到MemoryStream流2007 /// <summary> /// 从DataSet导出到MemoryStream流2007 /// </summary> /// <param name="SaveFileName">文件保存路径</param> /// <param name="SheetName">Excel文件中的Sheet名称</param> /// <param name="ds">存储数据的DataSet</param> /// <param name="startRow">从哪一行开始写入,从0开始</param> /// <param name="datatypes">DataSet中的各列对应的数据类型</param> public bool CreateExcel2007(string SaveFileName,string SheetName, DataSet ds, int startRow, params NpoiDataType[] datatypes) { try { if (startRow < 0) startRow = 0; XSSFWorkbook wb = new XSSFWorkbook(); ISheet sheet = wb.CreateSheet(SheetName); //sheet.SetColumnWidth(0, 50 * 256); //sheet.SetColumnWidth(1, 100 * 256); IRow row; ICell cell; DataRow dr; int j; int maxLength = 0; int curLength = 0; object columnValue; DataTable dt = ds.Tables[0]; if (datatypes.Length < dt.Columns.Count) { datatypes = new NpoiDataType[dt.Columns.Count]; for (int i = 0; i < dt.Columns.Count; i++) { string dtcolumntype = dt.Columns[i].DataType.Name.ToLower(); switch (dtcolumntype) { case "string": datatypes[i] = NpoiDataType.String; break; case "datetime": datatypes[i] = NpoiDataType.Datetime; break; case "boolean": datatypes[i] = NpoiDataType.Bool; break; case "double": datatypes[i] = NpoiDataType.Numeric; break; default: datatypes[i] = NpoiDataType.String; break; } } }
#region 创建表头 row = sheet.CreateRow(0);//创建第i行 ICellStyle style1 = wb.CreateCellStyle();//样式 IFont font1 = wb.CreateFont();//字体
font1.Color = HSSFColor.WHITE.index;//字体颜色 font1.Boldweight = (short)FontBoldWeight.BOLD;//字体加粗样式 //style1.FillBackgroundColor = HSSFColor.WHITE.index;//GetXLColour(wb, LevelOneColor);// 设置图案色 style1.FillForegroundColor = HSSFColor.GREEN.index;//GetXLColour(wb, LevelOneColor);// 设置背景色 style1.FillPattern = FillPatternType.SOLID_FOREGROUND; style1.SetFont(font1);//样式里的字体设置具体的字体样式 style1.Alignment = HorizontalAlignment.CENTER;//文字水平对齐方式 style1.VerticalAlignment = VerticalAlignment.CENTER;//文字垂直对齐方式 row.HeightInPoints = 25; for (j = 0; j < dt.Columns.Count; j++) { columnValue = dt.Columns[j].ColumnName; curLength = Encoding.Default.GetByteCount(columnValue.ToString()); maxLength = (maxLength < curLength ? curLength : maxLength); int colounwidth = 256 * maxLength; sheet.SetColumnWidth(j, colounwidth); try { cell = row.CreateCell(j);//创建第0行的第j列 cell.CellStyle = style1;//单元格式设置样式
try { //cell.SetCellType(CellType.STRING); cell.SetCellValue(columnValue.ToString()); } catch { }
} catch { continue; } } #endregion
#region 创建每一行 for (int i = startRow; i < ds.Tables[0].Rows.Count; i++) { dr = ds.Tables[0].Rows[i]; row = sheet.CreateRow(i + 1);//创建第i行 for (j = 0; j < dt.Columns.Count; j++) { columnValue = dr[j]; curLength = Encoding.Default.GetByteCount(columnValue.ToString()); maxLength = (maxLength < curLength ? curLength : maxLength); int colounwidth = 256 * maxLength; sheet.SetColumnWidth(j, colounwidth); try { cell = row.CreateCell(j);//创建第i行的第j列 #region 插入第j列的数据 try { NpoiDataType dtype = datatypes[j]; switch (dtype) { case NpoiDataType.String: { //cell.SetCellType(CellType.STRING); cell.SetCellValue(columnValue.ToString()); } break; case NpoiDataType.Datetime: { // cell.SetCellType(CellType.STRING); cell.SetCellValue(columnValue.ToString()); } break; case NpoiDataType.Numeric: { //cell.SetCellType(CellType.NUMERIC); cell.SetCellValue(Convert.ToDouble(columnValue)); } break; case NpoiDataType.Bool: { //cell.SetCellType(CellType.BOOLEAN); cell.SetCellValue(Convert.ToBoolean(columnValue)); } break; case NpoiDataType.Richtext: { // cell.SetCellType(CellType.FORMULA); cell.SetCellValue(columnValue.ToString()); } break; } } catch { //cell.SetCellType(HSSFCell.CELL_TYPE_STRING); cell.SetCellValue(columnValue.ToString()); } #endregion
} catch { continue; } } } #endregion
//using (FileStream fs = new FileStream(@SaveFileName, FileMode.OpenOrCreate))//生成文件在服务器上 //{ // wb.Write(fs); //} //string SaveFileName = "output.xlsx"; using (FileStream fs = new FileStream(SaveFileName, FileMode.OpenOrCreate, FileAccess.Write))//生成文件在服务器上 { wb.Write(fs); Console.WriteLine("文件保存成功!" + SaveFileName); } return true; } catch (Exception er) { Console.WriteLine("文件保存失败!" + SaveFileName); return false; }
} #endregion
private short GetXLColour(HSSFWorkbook workbook, System.Drawing.Color SystemColour) { short s = 0; HSSFPalette XlPalette = workbook.GetCustomPalette(); HSSFColor XlColour = XlPalette.FindColor(SystemColour.R, SystemColour.G, SystemColour.B); if (XlColour == null) { if (NPOI.HSSF.Record.PaletteRecord.STANDARD_PALETTE_SIZE < 255) { if (NPOI.HSSF.Record.PaletteRecord.STANDARD_PALETTE_SIZE < 64) { //NPOI.HSSF.Record.PaletteRecord.STANDARD_PALETTE_SIZE= 64; //NPOI.HSSF.Record.PaletteRecord.STANDARD_PALETTE_SIZE += 1; XlColour = XlPalette.AddColor(SystemColour.R, SystemColour.G, SystemColour.B); } else { XlColour = XlPalette.FindSimilarColor(SystemColour.R, SystemColour.G, SystemColour.B); } s = XlColour.GetIndex(); } } else s = XlColour.GetIndex(); return s; }
#region 读Excel-根据NpoiDataType创建的DataTable列的数据类型 /// <summary> /// 读Excel-根据NpoiDataType创建的DataTable列的数据类型 /// </summary> /// <param name="datatype"></param> /// <returns></returns> private Type GetDataTableType(NpoiDataType datatype) { Type tp = typeof(string);//Type.GetType("System.String") switch (datatype) { case NpoiDataType.Bool: tp = typeof(bool); break; case NpoiDataType.Datetime: tp = typeof(DateTime); break; case NpoiDataType.Numeric: tp = typeof(double); break; case NpoiDataType.Error: tp = typeof(string); break; case NpoiDataType.Blank: tp = typeof(string); break; } return tp; } #endregion
#region 读Excel-得到不同数据类型单元格的数据 /// <summary> /// 读Excel-得到不同数据类型单元格的数据 /// </summary> /// <param name="datatype">数据类型</param> /// <param name="row">数据中的一行</param> /// <param name="column">哪列</param> /// <returns></returns> private object GetCellData(NpoiDataType datatype, IRow row, int column) {
switch (datatype) { case NpoiDataType.String: try { return row.GetCell(column).DateCellValue; } catch { try { return row.GetCell(column).StringCellValue; } catch { return row.GetCell(column).NumericCellValue; } } case NpoiDataType.Bool: try { return row.GetCell(column).BooleanCellValue; } catch { return row.GetCell(column).StringCellValue; } case NpoiDataType.Datetime: try { return row.GetCell(column).DateCellValue; } catch { return row.GetCell(column).StringCellValue; } case NpoiDataType.Numeric: try { return row.GetCell(column).NumericCellValue; } catch { return row.GetCell(column).StringCellValue; } case NpoiDataType.Richtext: try { return row.GetCell(column).RichStringCellValue; } catch { return row.GetCell(column).StringCellValue; } case NpoiDataType.Error: try { return row.GetCell(column).ErrorCellValue; } catch { return row.GetCell(column).StringCellValue; } case NpoiDataType.Blank: try { return row.GetCell(column).StringCellValue; } catch { return ""; } default: return ""; } } #endregion
#region 获取单元格数据类型 /// <summary> /// 获取单元格数据类型 /// </summary> /// <param name="hs"></param> /// <returns></returns> private NpoiDataType GetCellDataType(ICell hs) { NpoiDataType dtype; DateTime t1; string cellvalue = "";
switch (hs.CellType) { case CellType.BLANK: dtype = NpoiDataType.String; cellvalue = hs.StringCellValue; break; case CellType.BOOLEAN: dtype = NpoiDataType.Bool; break; case CellType.NUMERIC: dtype = NpoiDataType.Numeric; cellvalue = hs.NumericCellValue.ToString(); break; case CellType.STRING: dtype = NpoiDataType.String; cellvalue = hs.StringCellValue; break; case CellType.ERROR: dtype = NpoiDataType.Error; break; case CellType.FORMULA: default: dtype = NpoiDataType.Datetime; break; } if (cellvalue != "" && DateTime.TryParse(cellvalue, out t1)) dtype = NpoiDataType.Datetime; return dtype; } #endregion
#region 测试代码
#endregion }
#region 枚举(Excel单元格数据类型) /// <summary> /// 枚举(Excel单元格数据类型) /// </summary> public enum NpoiDataType { /// <summary> /// 字符串类型-值为1 /// </summary> String, /// <summary> /// 布尔类型-值为2 /// </summary> Bool, /// <summary> /// 时间类型-值为3 /// </summary> Datetime, /// <summary> /// 数字类型-值为4 /// </summary> Numeric, /// <summary> /// 复杂文本类型-值为5 /// </summary> Richtext, /// <summary> /// 空白 /// </summary> Blank, /// <summary> /// 错误 /// </summary> Error } #endregion
}
|
请发表评论