/// <summary> /// 读Excel-根据NpoiDataType创建的DataTable列的数据类型 /// </summary> /// <param name="datatype"></param> /// <returns></returns> private Type GetDataTableType(NpoiDataType datatype) { Type tp = typeof(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); }
private static void SetCellValue(string value, ref ICell cell, NpoiDataType type) { switch (type) { //字串沒有格式 case NpoiDataType.String: if (!String.IsNullOrWhiteSpace(value)) { cell.SetCellValue(value); } break; //轉日期 case NpoiDataType.Date: if (!String.IsNullOrWhiteSpace(value)) { cell.SetCellValue(Convert.ToDateTime(value)); } break; //轉數字 case NpoiDataType.Number: if (!String.IsNullOrWhiteSpace(value)) { cell.SetCellValue(Convert.ToDouble(value)); } break; //不會發生; default: break; } }
/// <summary> /// /// </summary> /// <param name="row"></param> /// <param name="column"></param> /// <returns></returns> public static object GetCellData(IRow row, int column) { try { ICell hs = row.GetCell(column); NpoiDataType datatype = GetCellDataType(hs); //NpoiDataType.Blank; object obj = row.GetCell(column) ?? null; if (datatype == NpoiDataType.Datetime) { string v = ""; try { v = row.GetCell(column).StringCellValue; } catch (Exception e1) { v = row.GetCell(column).DateCellValue.ToString("yyyy-MM-dd hh:mm:ss"); } if (v != "") { try { obj = row.GetCell(column).DateCellValue.ToString("yyyy-MM-dd hh:mm:ss"); } catch (Exception e2) { obj = Convert.ToDateTime(v).ToString("yyyy-MM-dd hh:mm:ss"); } } else { obj = DBNull.Value; } } else if (datatype == NpoiDataType.Numeric) { obj = DBNull.Value; try { //if (row.GetCell(column).StringCellValue != "") obj = row.GetCell(column).NumericCellValue; } catch (Exception e3) { obj = row.GetCell(column).StringCellValue; } } else if (datatype == NpoiDataType.String && hs.CellType == CellType.Formula) { obj = row.GetCell(column).StringCellValue; } return(obj); } catch (Exception e22) { return(""); } }
/// <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(""); } }
/// <summary> /// 读Excel-得到不同数据类型单元格的数据 /// </summary> /// <param name="datatype">数据类型</param> /// <param name="row">数据中的一行</param> /// <param name="column">哪列</param> /// <returns></returns> private static object GetCellData(NpoiDataType datatype, IRow row, int column) { object obj = row.GetCell(column) ?? null; if (datatype == NpoiDataType.Datetime) { string v = ""; try { v = row.GetCell(column).StringCellValue; } catch (Exception e1) { v = row.GetCell(column).DateCellValue.ToString("yyyy-MM-dd hh:mm:ss"); } if (v != "") { try { obj = row.GetCell(column).DateCellValue.ToString("yyyy-MM-dd hh:mm:ss"); } catch (Exception e2) { obj = Convert.ToDateTime(v).ToString("yyyy-MM-dd hh:mm:ss"); } } else { obj = DBNull.Value; } } if (datatype == NpoiDataType.Numeric) { obj = DBNull.Value; try { //if (row.GetCell(column).StringCellValue != "") obj = row.GetCell(column).NumericCellValue; } catch (Exception e3) { //Errors.WriteError(obj.ToString() + "!" + e3.ToString()); obj = row.GetCell(column).StringCellValue; } } return(obj); }
/// <summary> /// 設定欄位值、屬性 /// </summary> /// <param name="value"></param> /// <param name="cell"></param> /// <param name="dataType"></param> private static void SetCellValue(string value, ref ICell cell, NpoiDataType dataType) { switch (dataType) { case NpoiDataType.String: if (!string.IsNullOrWhiteSpace(value)) { cell.SetCellValue(value); } break; case NpoiDataType.Int: if (!string.IsNullOrWhiteSpace(value)) { cell.SetCellValue(Convert.ToDouble(value)); } break; case NpoiDataType.Double: if (!string.IsNullOrWhiteSpace(value)) { cell.SetCellValue(Convert.ToDouble(value)); } break; case NpoiDataType.DateTime: if (!string.IsNullOrWhiteSpace(value)) { cell.SetCellValue(Convert.ToDateTime(value)); } break; case NpoiDataType.Bool: if (!string.IsNullOrWhiteSpace(value)) { cell.SetCellValue(Convert.ToBoolean(value)); } break; default: break; } }
/// <summary> /// 将excel中的数据导入到DataTable中 /// </summary> /// <param name="sheetName">excel工作薄sheet的名称</param> /// <param name="isFirstRowColumn">第一行是否是DataTable的列名</param> /// <returns>返回的DataTable</returns> public DataTable ExcelToDataTable(string sheetName, bool isFirstRowColumn) { ISheet sheet = null; DataTable data = new DataTable(); int startRow = 0; NpoiDataType[] ColumnDataType = null; try { using (FileStream stream = new FileStream(@fileName, FileMode.Open, FileAccess.Read)) { workbook = WorkbookFactory.Create(stream);//使用接口,自动识别excel2003/2007格式 if (sheetName != null) { sheet = workbook.GetSheet(sheetName); } else { sheet = workbook.GetSheetAt(0); } if (sheet != null) { IRow firstRow = sheet.GetRow(0); int cellCount = firstRow.LastCellNum; //一行最后一个cell的编号 即总的列数 if (isFirstRowColumn) { for (int i = firstRow.FirstCellNum; i < cellCount; ++i) { DataColumn column = new DataColumn(firstRow.GetCell(i).StringCellValue); data.Columns.Add(column); } startRow = sheet.FirstRowNum + 1; } else { startRow = sheet.FirstRowNum; } //最后一列的标号 int rowCount = sheet.LastRowNum; for (int i = startRow; i <= rowCount; ++i) { IRow row = sheet.GetRow(i); if (row == null) { continue; //没有数据的行默认是null } if (ColumnDataType == null) { ColumnDataType = new NpoiDataType[cellCount]; for (int j = 0; j < cellCount; j++) { ICell hs = row.GetCell(j); if (hs != null) { ColumnDataType[j] = GetCellDataType(hs); } } } DataRow dataRow = data.NewRow(); for (int j = row.FirstCellNum; j < cellCount; ++j) { ICell hs = row.GetCell(j); if (row.GetCell(j) != null) //同理,没有数据的单元格都默认是null { if (hs.CellType == CellType.Numeric && HSSFDateUtil.IsCellDateFormatted(hs)) { dataRow[j] = HSSFDateUtil.GetJavaDate(hs.NumericCellValue).ToString("HH:mm:ss"); } else { dataRow[j] = hs; } //dataRow[j] = row.GetCell(j).ToString(); } //dataRow[j] = GetCellData(ColumnDataType[j], row, j); } data.Rows.Add(dataRow); } } } return(data); } catch (Exception ex) { Console.WriteLine("Exception: " + ex.Message); return(null); } }
/// <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, int sheetCount, params NpoiDataType[] ColumnDataType) { int ertime = 0; int intime = 0; DataSet ds = new DataSet("ds"); StringBuilder sb = new StringBuilder(); using (FileStream stream = new FileStream(@FileName, FileMode.Open, FileAccess.Read)) { IWorkbook workbook = WorkbookFactory.Create(stream);//使用接口,自动识别excel2003/2007格式 for (int c = 0; c < sheetCount; c++) { ISheet sheet = workbook.GetSheetAt(c);//得到里面第一个sheet DataTable dt = new DataTable(string.Format("dt{0}", c)); DataRow dr; 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[c].Rows.Count == 0 && sb.ToString() != "") { throw new Exception(sb.ToString()); } } } return(ds); }
/// <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 = NPOI.HSSF.Util.HSSFColor.Green.Index; //GetXLColour(wb, LevelOneColor);// 设置背景色 style1.FillPattern = FillPattern.SolidForeground; 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) { Console.WriteLine("文件保存失败!" + SaveFileName); return(false); } }