/// <summary> /// 导出到EXCEL /// </summary> /// <param name="dtSource"></param> /// <returns></returns> public static byte[] ExportToExcel(DataTable dtSource) { HSSFWorkbook workbook = NPOIHelper.InitializeBlankWorkbook("www.danfoss.com", "danfoss", "danfoss export file"); HSSFSheet sheet = (HSSFSheet)workbook.CreateSheet(); //设置单元格显示格式 HSSFCellStyle dateStyle = (HSSFCellStyle)workbook.CreateCellStyle(); dateStyle.DataFormat = HSSFDataFormat.GetBuiltinFormat("m/d/yy"); HSSFCellStyle intStyle = (HSSFCellStyle)workbook.CreateCellStyle(); intStyle.DataFormat = HSSFDataFormat.GetBuiltinFormat("0"); HSSFCellStyle doubleStyle = (HSSFCellStyle)workbook.CreateCellStyle(); doubleStyle.DataFormat = HSSFDataFormat.GetBuiltinFormat("0.00"); HSSFCellStyle strStyle = (HSSFCellStyle)workbook.CreateCellStyle(); strStyle.DataFormat = HSSFDataFormat.GetBuiltinFormat("@"); HSSFCellStyle genStyle = (HSSFCellStyle)workbook.CreateCellStyle(); genStyle.DataFormat = HSSFDataFormat.GetBuiltinFormat("General"); HSSFCellStyle headStyle = (HSSFCellStyle)workbook.CreateCellStyle(); headStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center; HSSFFont font = (HSSFFont)workbook.CreateFont(); font.FontHeightInPoints = 10; font.Boldweight = 700; headStyle.SetFont(font); //设置列宽 同列标题宽度 int[] arrColWidth = new int[dtSource.Columns.Count]; foreach (DataColumn item in dtSource.Columns) { arrColWidth[item.Ordinal] = Encoding.GetEncoding(936).GetBytes(item.ColumnName.ToString()).Length; } //for (int i = 0; i < dtSource.Rows.Count; i++) //{ // for (int j = 0; j < dtSource.Columns.Count; j++) // { // int intTemp = Encoding.GetEncoding(936).GetBytes(dtSource.Rows[i][j].ToString()).Length; // if (intTemp > arrColWidth[j]) // { // arrColWidth[j] = intTemp; // } // } //} //设置列格式 HSSFCellStyle[] arrColStyle = new HSSFCellStyle[dtSource.Columns.Count]; foreach (DataColumn column in dtSource.Columns) { switch (column.DataType.ToString()) { case "System.DateTime": //日期类型 arrColStyle[column.Ordinal] = dateStyle; //日期格式化显示 m/d/yy break; case "System.Int16": //整型 case "System.Int32": case "System.Int64": arrColStyle[column.Ordinal] = intStyle; break; case "System.Decimal": //浮点数 case "System.Double": arrColStyle[column.Ordinal] = doubleStyle; break; case "System.String": //字符串类型 arrColStyle[column.Ordinal] = strStyle; break; case "System.Byte": case "System.Boolean": //布尔型 case "System.DBNull": //空值处理 default: arrColStyle[column.Ordinal] = genStyle; break; } } int rowIndex = 0; foreach (DataRow row in dtSource.Rows) { #region 超出行数则新建表,填充列头,样式 if (rowIndex == 65535 || rowIndex == 0) { if (rowIndex != 0) { sheet = (HSSFSheet)workbook.CreateSheet(); } #region 列头及样式 { HSSFRow headerRow = (HSSFRow)sheet.CreateRow(0); foreach (DataColumn column in dtSource.Columns) { headerRow.CreateCell(column.Ordinal).SetCellValue(column.ColumnName); headerRow.GetCell(column.Ordinal).CellStyle = headStyle; //设置列宽,列格式 sheet.SetColumnWidth(column.Ordinal, (arrColWidth[column.Ordinal] + 4) * 256); sheet.SetDefaultColumnStyle(column.Ordinal, arrColStyle[column.Ordinal]); } } #endregion rowIndex = 1; } #endregion #region 填充内容 HSSFRow dataRow = (HSSFRow)sheet.CreateRow(rowIndex); foreach (DataColumn column in dtSource.Columns) { HSSFCell newCell = (HSSFCell)dataRow.CreateCell(column.Ordinal); if (row[column].GetType() == typeof(DBNull)) { continue; } switch (column.DataType.ToString()) { case "System.String": //字符串类型 newCell.SetCellValue((string)row[column]); //newCell.CellStyle = strStyle; break; case "System.DateTime": //日期类型 newCell.SetCellValue((DateTime)row[column]); //newCell.CellStyle = dateStyle;//日期格式化显示 m/d/yy break; case "System.Boolean": //布尔型 newCell.SetCellValue((bool)row[column]); break; case "System.Int16": //整型 newCell.SetCellValue((Int16)row[column]); //newCell.CellStyle = intStyle; break; case "System.Int32": newCell.SetCellValue((Int32)row[column]); //newCell.CellStyle = intStyle; break; case "System.Int64": newCell.SetCellValue((Int64)row[column]); //newCell.CellStyle = intStyle; break; case "System.Byte": newCell.SetCellValue((Byte)row[column]); //newCell.CellStyle = intStyle; break; case "System.Decimal": //浮点数 //double doubV = 0; //double.TryParse(drValue, out doubV); newCell.SetCellValue((double)(System.Decimal)row[column]); //newCell.CellStyle = doubleStyle; break; case "System.Double": newCell.SetCellValue((double)row[column]); //newCell.CellStyle = doubleStyle; break; case "System.DBNull": //空值处理 newCell.SetCellValue(""); break; default: newCell.SetCellValue(""); break; } } #endregion rowIndex++; } MemoryStream ms = new MemoryStream(); workbook.Write(ms); return(ms.GetBuffer()); //return workbook.GetBytes(); }
/// <summary> /// 读取Excel文件的内容,并转换为DataTable /// </summary> /// <param name="s"></param> /// <returns></returns> public static DataTable ReadAsDatatable(Stream s) { HSSFWorkbook wb = new HSSFWorkbook(s); return(NPOIHelper.ConvertToDataTable(wb)); }