protected void btnExport_Click(object sender, EventArgs e) { btn1_Click(sender, e); HSSFWorkbook hssfworkbook = new HSSFWorkbook(); Sheet sheet1 = hssfworkbook.CreateSheet("Sheet1"); sheet1.CreateRow(0).CreateCell(0).SetCellValue("NO"); sheet1.GetRow(0).CreateCell(1).SetCellValue("Site"); sheet1.GetRow(0).CreateCell(2).SetCellValue("Item"); sheet1.GetRow(0).CreateCell(3).SetCellValue("Desc"); sheet1.GetRow(0).CreateCell(4).SetCellValue("Uom"); sheet1.GetRow(0).CreateCell(5).SetCellValue("Location"); sheet1.GetRow(0).CreateCell(6).SetCellValue("Bin"); sheet1.GetRow(0).CreateCell(7).SetCellValue("LotNo"); sheet1.GetRow(0).CreateCell(8).SetCellValue("Qty"); DataSet ds = (DataSet)Session["ds"]; for (int i = 1; i <= ds.Tables[0].Rows.Count; i++) { sheet1.CreateRow(i).CreateCell(0).SetCellValue(Convert.ToString(i)); sheet1.GetRow(i).CreateCell(1).SetCellValue(ds.Tables[0].Rows[i - 1][1].ToString()); sheet1.GetRow(i).CreateCell(2).SetCellValue(ds.Tables[0].Rows[i - 1][2].ToString()); sheet1.GetRow(i).CreateCell(3).SetCellValue(ds.Tables[0].Rows[i - 1][3].ToString()); sheet1.GetRow(i).CreateCell(4).SetCellValue(ds.Tables[0].Rows[i - 1][4].ToString()); sheet1.GetRow(i).CreateCell(5).SetCellValue(ds.Tables[0].Rows[i - 1][5].ToString()); sheet1.GetRow(i).CreateCell(6).SetCellValue(ds.Tables[0].Rows[i - 1][6].ToString()); sheet1.GetRow(i).CreateCell(7).SetCellValue(ds.Tables[0].Rows[i - 1][7].ToString()); sheet1.GetRow(i).CreateCell(8).SetCellValue(ds.Tables[0].Rows[i - 1][8].ToString()); } sheet1.AutoSizeColumn(0); sheet1.AutoSizeColumn(1); sheet1.AutoSizeColumn(2); sheet1.AutoSizeColumn(3); sheet1.AutoSizeColumn(4); sheet1.AutoSizeColumn(5); sheet1.AutoSizeColumn(6); sheet1.AutoSizeColumn(7); sheet1.AutoSizeColumn(8); // string fileplace = Request.PhysicalApplicationPath; // string filename = "temp(" + DateTime.Now.ToString("yyyy-MM-dd-hh-mm-ss") + ").xls"; // FileStream file = new FileStream(@fileplace+"\\Reports\\Templates\\TempFiles\\"+filename, FileMode.Create); // hssfworkbook.Write(file); // file.Close(); MemoryStream ms = new MemoryStream(); hssfworkbook.Write(ms); Response.AddHeader("Content-Disposition", string.Format("attachment;filename=TempWorkBook.xls")); Response.BinaryWrite(ms.ToArray()); hssfworkbook = null; ms.Close(); ms.Dispose(); }
public void AddList <T>(IEnumerable <T> dataList) { PropertyInfo[] propertys = typeof(T).GetProperties(); // шапка таблицы Row tableHederRow = NextRow; int index = 0; foreach (PropertyInfo property in propertys) { AddCell(property.Name, index++, tableHederRow, TextStyle.BoldTextAlignCenter, HSSFColor.GREY_25_PERCENT.index, CellBorderType.THIN, HSSFColor.GREY_80_PERCENT.index); } // заполнение данными foreach (T cls in dataList) { int indexData = 0; Row dataRow = NextRow; foreach (PropertyInfo property in cls.GetType().GetProperties()) { AddCell(property.GetValue(cls, null), indexData++, dataRow, TextStyle.NormalText, HSSFColor.WHITE.index, CellBorderType.THIN, HSSFColor.GREY_80_PERCENT.index); } } // авто ширина заполненых столбцов for (int i = 0; i < index; i++) { Sheet.AutoSizeColumn(i); // Гребанс ошибко! NPOI не правильно устанавливает автоширину столбцов. int oldColumnWidth = Sheet.GetColumnWidth(i); Sheet.SetColumnWidth(i, (int)(oldColumnWidth * 2.25)); } }
/// <summary> /// Sobrecarga. Recebe um argumento Sheet caso algum conteudo ja tenha sido /// inserido na planilha. /// </summary> /// <param name="tabela">Dados da tabela</param> /// <param name="caminhoSalvar">Diretório e nome do documento a ser salvo</param> /// <param name="planilha">Sheet já criada e com conteudo</param> /// <param name="linhaInicial">Opcional. Linha inicial da planilha</param> /// <param name="colunaInicial">Opcional. Coluna inicial, use para omitir colunas a esquerda na planilha</param> public void GerarPlanilhaNPOI(DataTable tabela, string caminhoSalvar, Sheet planilha, int linhaInicial = 0, int colunaInicial = 0) { try { GerarHeadersNPOI(tabela, planilha, ref linhaInicial, colunaInicial); Row linha; for (int linhaPlan = linhaInicial, linhaDt = 0; linhaDt < tabela.Rows.Count; linhaPlan++, linhaDt++) { DataRow linhaPlanilha = tabela.Rows[linhaDt]; linha = planilha.CreateRow(linhaPlan); for (int colunaDt = colunaInicial, colunaPlan = 0; colunaDt < tabela.Columns.Count; colunaDt++, colunaPlan++) { string valor = linhaPlanilha[colunaDt].ToString(); linha.CreateCell(colunaPlan, CellType.STRING).SetCellValue(valor); } } //apenas para redimensionar as colunas for (int colunaDt = colunaInicial, colunaPlan = 0; colunaDt < tabela.Columns.Count; colunaDt++, colunaPlan++) { planilha.AutoSizeColumn(colunaPlan); } SalvarPlanilhaNPOI(caminhoSalvar); } catch (Exception ex) { throw ex; } }
static void Main(string[] args) { InitializeWorkbook(); Sheet sheet = hssfworkbook.CreateSheet("Sheet1"); Row row = sheet.CreateRow(0); row.CreateCell(0).SetCellValue("This is a test"); row.CreateCell(1).SetCellValue("Hello"); row.CreateCell(2).SetCellValue("1234.0023"); row.CreateCell(3).SetCellValue("这是一个测试这是一个测试这是一个测试"); sheet.AutoSizeColumn(0); sheet.AutoSizeColumn(1); sheet.AutoSizeColumn(2); sheet.AutoSizeColumn(3); WriteToFile(); }
/// <summary> /// Ajusta las columnas automáticamente /// </summary> internal void AutoSizeColumns() { for (int i = 0; i < Workbook.NumberOfSheets; i++) { Sheet sheet = Workbook.GetSheetAt(i); for (int j = 1; j < 40; j++) { sheet.AutoSizeColumn(j); sheet.SetColumnWidth(j, sheet.GetColumnWidth(j) + 240); } } }
/// <summary> /// 自动设置Excel列宽 /// </summary> /// <param name="sheet">Excel表</param> private static void AutoSizeColumns(Sheet sheet) { if (sheet.PhysicalNumberOfRows > 0) { Row headerRow = sheet.GetRow(0); for (int i = 0, l = headerRow.LastCellNum; i < l; i++) { sheet.AutoSizeColumn(i); } } }
public void Export(DataTable dt) { HSSFWorkbook hssfworkbook = new HSSFWorkbook(); Sheet sheet = hssfworkbook.CreateSheet(); Row HeaderRow = sheet.CreateRow(0); for (int j = 1; j < dt.Columns.Count; j++) { HeaderRow.CreateCell(j - 1).SetCellValue(dt.Columns[j].ToString()); } for (int i = 0; i < dt.Rows.Count; i++) { Row r = sheet.CreateRow(i + 1); for (int j = 1; j < dt.Rows[i].ItemArray.Length; j++) { if (dt.Rows[i].ItemArray[j].GetType() == typeof(Int32)) { r.CreateCell(j - 1).SetCellValue(Convert.ToInt32(dt.Rows[i].ItemArray[j])); } else if (dt.Rows[i].ItemArray[j].GetType() == typeof(decimal)) { r.CreateCell(j - 1).SetCellValue(Convert.ToDouble(dt.Rows[i].ItemArray[j])); } else if (dt.Rows[i].ItemArray[j].GetType() == typeof(double)) { r.CreateCell(j - 1).SetCellValue(Convert.ToDouble(dt.Rows[i].ItemArray[j])); } else { r.CreateCell(j - 1).SetCellValue(dt.Rows[i].ItemArray[j].ToString()); } } } for (int i = 0; i < dt.Rows.Count + 1; i++) { sheet.AutoSizeColumn(i); } sheet.ForceFormulaRecalculation = true; using (FileStream f = new FileStream(Server.MapPath("Create.xls"), FileMode.Create, FileAccess.ReadWrite)) { hssfworkbook.Write(f); } // 在浏览器中直接下载 using (MemoryStream stream = new MemoryStream()) { hssfworkbook.Write(stream); Response.ContentType = "application/vnd.ms-excel"; Response.AddHeader("Content-Disposition:", "attachment; filename= PKDiffrence" + DateTime.Now.ToString("yyyy-MM-dd HHmmss") + ".xls"); Response.BinaryWrite(stream.ToArray()); } }
public void Export(GridView dt) { HSSFWorkbook hssfworkbook = new HSSFWorkbook(); Sheet sheet = hssfworkbook.CreateSheet(); Row HeaderRow = sheet.CreateRow(0); HeaderRow.CreateCell(0).SetCellValue("No"); for (int j = 0; j < dt.HeaderRow.Cells.Count; j++) { HeaderRow.CreateCell(j + 1).SetCellValue(dt.HeaderRow.Cells[j].Text); } for (int i = 0; i < dt.Rows.Count; i++) { Row r = sheet.CreateRow(i + 1); r.CreateCell(0).SetCellValue(i + 1); for (int j = 0; j < dt.Rows[i].Cells.Count; j++) { if (dt.Rows[i].Cells[j].GetType() == typeof(decimal) || dt.Rows[i].Cells[j].GetType() == typeof(int) || dt.Rows[i].Cells[j].GetType() == typeof(double) || dt.Rows[i].Cells[j].GetType() == typeof(Int64)) { r.CreateCell(j + 1).SetCellValue(Convert.ToDouble(dt.Rows[i].Cells[j].Text == " " ? "" : dt.Rows[i].Cells[j].Text)); } else { r.CreateCell(j + 1).SetCellValue(dt.Rows[i].Cells[j].Text == " " ? "" : dt.Rows[i].Cells[j].Text.ToString()); } } } for (int i = 0; i < dt.Rows.Count + 1; i++) { sheet.AutoSizeColumn(i); } sheet.ForceFormulaRecalculation = true; using (FileStream f = new FileStream(Server.MapPath("Create.xls"), FileMode.Create, FileAccess.ReadWrite)) { hssfworkbook.Write(f); } // 在浏览器中直接下载 using (MemoryStream stream = new MemoryStream()) { hssfworkbook.Write(stream); Response.ContentType = "application/vnd.ms-excel"; Response.AddHeader("Content-Disposition:", "attachment; filename= ForecastInput" + DateTime.Now.ToString("yyyy-MM-dd") + ".xls"); Response.BinaryWrite(stream.ToArray()); } }
public void ApplyColumnAutoSize() { if (_autoWidthColumns != null) { foreach (var column in _autoWidthColumns) { Sheet.AutoSizeColumn(column, true); } } if (_autoHeightRows != null) { foreach (var rowIndex in _autoHeightRows) { var row = Sheet.GetRow(rowIndex); if (row != null) { row.Height = 0; } } } }
public void Export(DataTable dt) { HSSFWorkbook hssfworkbook = new HSSFWorkbook(); Sheet sheet = hssfworkbook.CreateSheet(); Row HeaderRow = sheet.CreateRow(0); HeaderRow.CreateCell(0).SetCellValue("序号"); for (int j = 2; j < dt.Columns.Count; j++) { HeaderRow.CreateCell(j - 1).SetCellValue(dt.Columns[j - 1].ToString()); } for (int i = 0; i < dt.Rows.Count; i++) { Row r = sheet.CreateRow(i + 1); r.CreateCell(0).SetCellValue(i + 1); for (int j = 2; j < dt.Rows[i].ItemArray.Length; j++) { r.CreateCell(j - 1).SetCellValue(dt.Rows[i].ItemArray[j - 1].ToString()); } } for (int i = 0; i < dt.Rows.Count + 1; i++) { sheet.AutoSizeColumn(i); } sheet.ForceFormulaRecalculation = true; using (FileStream f = new FileStream(Server.MapPath("Create.xls"), FileMode.Create, FileAccess.ReadWrite)) { hssfworkbook.Write(f); } // 在浏览器中直接下载 using (MemoryStream stream = new MemoryStream()) { hssfworkbook.Write(stream); Response.ContentType = "application/vnd.ms-excel"; Response.AddHeader("Content-Disposition:", "attachment; filename= MaterialForecast_Purchasing" + DateTime.Now.ToString("yyyy-MM-dd") + ".xls"); Response.BinaryWrite(stream.ToArray()); } }
public void Export(GridView gv, int style) { HSSFWorkbook hssfworkbook = new HSSFWorkbook(); Sheet sheet = hssfworkbook.CreateSheet(); Row HeaderRow = sheet.CreateRow(0); for (int j = 0; j < gv.Columns.Count; j++) { HeaderRow.CreateCell(j).SetCellValue(gv.Columns[j].HeaderText); } for (int i = 0; i < gv.Rows.Count; i++) { Row r = sheet.CreateRow(i + 1); for (int j = 0; j < gv.Rows[i].Cells.Count; j++) { string lable = "Label" + (j + 1); r.CreateCell(j).SetCellValue(((Label)gv.Rows[i].Cells[j].FindControl(lable)).Text); } } for (int i = 0; i < gv.Rows.Count + 1; i++) { sheet.AutoSizeColumn(i); } sheet.ForceFormulaRecalculation = true; using (FileStream f = new FileStream(Server.MapPath("Create.xls"), FileMode.Create, FileAccess.ReadWrite)) { hssfworkbook.Write(f); } // 在浏览器中直接下载 using (MemoryStream stream = new MemoryStream()) { hssfworkbook.Write(stream); Response.ContentType = "application/vnd.ms-excel"; Response.AddHeader("Content-Disposition:", "attachment; filename=" + lblPid.Text + "_" + DateTime.Now.ToString("yyyy-MM-dd") + ".xls"); Response.BinaryWrite(stream.ToArray()); } }
// <summary> // Render DataTable to Excel File // </summary> // <param name="sourceTable">Source DataTable</param> // <param name="fileName">Destination File name</param> public static void ExportDataTableToExcel(DataTable SourceTable, string FileName, string Servicio, string Reporte, string FechaDesde, string FechaHasta) { HSSFWorkbook workbook = new HSSFWorkbook(); MemoryStream memoryStream = new MemoryStream(); Sheet sheet = workbook.CreateSheet("Hoja1"); HSSFPatriarch patriarch = (HSSFPatriarch)sheet.CreateDrawingPatriarch(); //create the anchor HSSFClientAnchor anchor; anchor = new HSSFClientAnchor(0, 0, 0, 255, 0, 0, 4, 7); anchor.AnchorType = 1; //load the picture and get the picture index in the workbook //HSSFPicture picture = (HSSFPicture)patriarch.CreatePicture(anchor, LoadImage(@"C:\Documents and Settings\Administrador\Escritorio\sisss\images\Logo_Dydcom.jpg", workbook)); //Reset the image to the original size. //picture.Resize(); //picture.LineStyle = HSSFPicture.LINESTYLE_NONE; //Definimos la fila inicial para crear los encabezados Row headerRow = sheet.CreateRow(0); sheet.DisplayGridlines = true; //Cell CellServicio = sheet.CreateRow(3).CreateCell(0); //CellServicio.SetCellValue(new HSSFRichTextString("Reporte desde el periodo " + FechaDesde + " hasta " + FechaHasta)); //Cell CellFecha = sheet.CreateRow(2).CreateCell(4); //CellFecha.SetCellValue(new HSSFRichTextString("Fecha: " + DateTime.Now)); //Cell CellReporte = sheet.CreateRow(3).CreateCell(4); //CellReporte.SetCellValue(new HSSFRichTextString("Reporte: " + Reporte)); // IWorkbook doc Font font = workbook.CreateFont(); font.FontHeightInPoints = 11; font.FontName = "Calibri"; //font.Boldweight = (short)FontBoldWeight.BOLD; // handling header. foreach (DataColumn column in SourceTable.Columns) { //headerRow.RowNum = 4; // Create New Cell Cell headerCell = headerRow.CreateCell(column.Ordinal); // Set Cell Value headerCell.SetCellValue(column.ColumnName); headerCell.CellStyle.SetFont(font); // Create Style CellStyle headerCellStyle = workbook.CreateCellStyle(); //headerCellStyle.FillForegroundColor = HSSFColor.WHITE.index; //headerCellStyle.FillPattern = FillPatternType.SOLID_FOREGROUND; // Add Style to Cell headerCell.CellStyle = headerCellStyle; } //Fila inicial para el la escritura de datos. int rowIndex = 1; foreach (DataRow row in SourceTable.Rows) { Row dataRow = sheet.CreateRow(rowIndex); foreach (DataColumn column in SourceTable.Columns) { if (column.Ordinal == 0) { dataRow.CreateCell(column.Ordinal).SetCellValue(row[column].ToString()); } else { dataRow.CreateCell(column.Ordinal).SetCellValue(Convert.ToString(row[column])); } } rowIndex++; } rowIndex++; //sheet.CreateRow(rowIndex).CreateCell(0).SetCellValue("Total:"); //sheet.GetRow(rowIndex).CreateCell(1).CellFormula = "SUM(B7:B" + (rowIndex) + ")"; //sheet.GetRow(rowIndex).CreateCell(2).CellFormula = "SUM(C7:C" + (rowIndex) + ")"; //sheet.GetRow(rowIndex).CreateCell(3).CellFormula = "SUM(D7:D" + (rowIndex) + ")"; //sheet.GetRow(rowIndex).GetCell(3).CellStyle.DataFormat = HSSFDataFormat.GetBuiltinFormat("#,##0"); //Autodimencionando el ancho de las columnas foreach (DataColumn column in SourceTable.Columns) { sheet.AutoSizeColumn(column.Ordinal); } workbook.Write(memoryStream); memoryStream.Flush(); HttpResponse response = HttpContext.Current.Response; response.ContentType = "application/vnd.ms-excel"; response.AddHeader("Content-Disposition", string.Format("attachment;filename={0}", FileName)); response.Clear(); response.BinaryWrite(memoryStream.GetBuffer()); response.End(); }
public JsValue AutoSizeColumn(JsValue column) { Sheet.AutoSizeColumn(column.ConvertToInt32().GetValueOrDefault()); return(JsValue.Undefined); }
/// <summary> /// 普通单表导出npoi(集合数据+样式) /// </summary> /// <param name="sender"></param> /// <param name="e"></param> protected void Button2_Click(object sender, EventArgs e) { string filename = "test.xls"; Response.ContentType = "application/vnd.ms-excel"; Response.AddHeader("Content-Disposition", string.Format("attachment;filename={0}", filename)); Response.Clear(); HSSFWorkbook hssfworkbook = new HSSFWorkbook(); Sheet sheet1 = hssfworkbook.CreateSheet("Sheet1名称"); CellStyle style = hssfworkbook.CreateCellStyle(); style.Alignment = HorizontalAlignment.CENTER; style.FillBackgroundColor = HSSFColor.PINK.index; var row0 = sheet1.CreateRow(0).CreateCell(0); row0.SetCellValue("This is a Sample");//sheet标题 row0.CellStyle = style; var j = 17; #region 居中/自动换行 CellStyle styleCenter = hssfworkbook.CreateCellStyle(); //样式 styleCenter.Alignment = NPOI.SS.UserModel.HorizontalAlignment.CENTER; //文字水平对齐方式 styleCenter.VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment.CENTER; //文字垂直对齐方式 styleCenter.WrapText = true; //自动换行 sheet1.CreateRow(j).CreateCell(j).CellStyle = styleCenter; var cell17 = sheet1.CreateRow(j).CreateCell(j); var cell172 = sheet1.CreateRow(j).CreateCell(j + 1); cell17.CellStyle = styleCenter; cell17.SetCellValue("VLOOKUP函数和“两列同时匹配”的应用,升的网易博客"); //cell172.SetCellValue("VLOOKUP函数和“两列同时匹配”的应用,升的网易博客"); j++; #endregion #region 设置宽高度 sheet1.SetColumnWidth(1, 20 * 256); //宽度-每个字符宽度是1/256。 所以20 * 256就是20个字符宽度。 var rowwh = sheet1.CreateRow(j); rowwh.HeightInPoints = 50; //高度 rowwh.CreateCell(j).SetCellValue("宽高度"); j++; #endregion #region 自适应宽度(对中文不友好)+自动换行 /*场景: * 12林学1班 * 12林学1班 */ CellStyle autoAndWrap = hssfworkbook.CreateCellStyle(); //样式 autoAndWrap.WrapText = true; //自动换行 var rowwhauto = sheet1.CreateRow(j); var cellauto = rowwhauto.CreateCell(j); cellauto.SetCellValue(j + "自适应宽高度自适应宽高度\n自适应宽高度自适应宽高度\n自适应宽高度自适应宽高度"); sheet1.AutoSizeColumn(j); cellauto.CellStyle = autoAndWrap; j++; #endregion #region 设置背景色 CellStyle style1 = hssfworkbook.CreateCellStyle(); style1.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.BLUE.index; style1.FillPattern = FillPatternType.SOLID_FOREGROUND; sheet1.CreateRow(j).CreateCell(j).CellStyle = style1; j++; #endregion #region 自定义背景色 HSSFPalette palette = hssfworkbook.GetCustomPalette(); //调色板实例 palette.SetColorAtIndex((short)8, (byte)184, (byte)204, (byte)228); HSSFColor hssFColor = palette.FindColor((byte)184, (byte)204, (byte)228); CellStyle style2 = hssfworkbook.CreateCellStyle(); style2.FillPattern = FillPatternType.SOLID_FOREGROUND; style2.FillForegroundColor = hssFColor.GetIndex(); sheet1.CreateRow(j).CreateCell(j).CellStyle = style2; j++; #endregion #region 设置字体颜色 CellStyle style3 = hssfworkbook.CreateCellStyle(); Font font1 = hssfworkbook.CreateFont(); font1.Color = hssFColor.GetIndex();//颜色 style3.SetFont(font1); var cell20 = sheet1.CreateRow(j).CreateCell(j); cell20.CellStyle = style3; cell20.SetCellValue("666666666"); j++; #endregion List <ModelStu> data = StuDaTa.GetData(); string[] arrthead = { "ID", "name", "age", "pc" }; sheet1.AddMergedRegion(new CellRangeAddress(0, 0, 0, arrthead.Length - 1)); Row row1 = sheet1.CreateRow(1); for (int i = 0; i < arrthead.Length; i++) { row1.CreateCell(i).SetCellValue(arrthead[i]); } for (int i = 0; i < data.Count; i++) { Row row = sheet1.CreateRow(i + 2); var colIndex = 0; row.CreateCell(colIndex++).SetCellValue(data[i].id); row.CreateCell(colIndex++).SetCellValue(data[i].name); row.CreateCell(colIndex++).SetCellValue(data[i].age); row.CreateCell(colIndex).SetCellValue(data[i].pc); } MemoryStream file = new MemoryStream(); hssfworkbook.Write(file); Response.BinaryWrite(file.GetBuffer()); Response.End(); hssfworkbook = null; file.Close(); file.Dispose(); }
/// <summary> /// 将单个数据表保存到Excel /// </summary> /// <param name="dataTable">数据表</param> /// <param name="fileName">文件名</param> public static void ExportExcel(DataTable dataTable, string fileName = null) { HSSFWorkbook workBook = new HSSFWorkbook(); Sheet sheet = workBook.CreateSheet("Sheet1"); Font font = workBook.CreateFont(); font.Boldweight = 700; CellStyle style = workBook.CreateCellStyle(); style.Alignment = HorizontalAlignment.CENTER; style.SetFont(font); int rownum = 0; Row row = sheet.CreateRow(rownum++); Cell cell; for (int i = 0; i < dataTable.Columns.Count; i++) { cell = row.CreateCell(i); cell.SetCellValue(dataTable.Columns[i].ColumnName); cell.CellStyle = style; } CellStyle dateStyle = workBook.CreateCellStyle(); DataFormat format = workBook.CreateDataFormat(); dateStyle.DataFormat = format.GetFormat("yyyy-MM-dd HH:mm:ss"); foreach (DataRow dataRow in dataTable.Rows) { row = sheet.CreateRow(rownum++); for (int i = 0; i < dataTable.Columns.Count; i++) { cell = row.CreateCell(i); string strValue = dataRow[i].ToString(); if (string.IsNullOrWhiteSpace(strValue)) { cell.SetCellValue(""); } else { switch (dataTable.Columns[i].DataType.ToString()) { case "System.DateTime": DateTime dateTime; DateTime.TryParse(strValue, out dateTime); cell.SetCellValue(dateTime); cell.CellStyle = dateStyle; break; case "System.Boolean": bool bValue; bool.TryParse(strValue, out bValue); cell.SetCellValue(bValue); break; case "System.Int16": case "System.Int32": case "System.Int64": case "System.Byte": int iValue = 0; int.TryParse(strValue, out iValue); cell.SetCellValue(iValue); break; case "System.Decimal": case "System.Double": double dValue = 0; double.TryParse(strValue, out dValue); cell.SetCellValue(dValue); break; default: cell.SetCellValue(strValue); break; } } } } for (int i = 0; i < dataTable.Columns.Count; i++) { sheet.AutoSizeColumn(i); } workBook.Write(HttpContext.Current.Response.OutputStream); workBook.Dispose(); HttpContext.Current.Response.ContentType = "application/vnd.ms-excel"; if (string.IsNullOrEmpty(fileName)) { fileName = DateTime.Now.ToString("yyyyMMddHHmmss"); } HttpContext.Current.Response.AppendHeader("Content-Disposition", "Attachment; FileName=" + GetToExcelName(fileName) + ".xls"); HttpContext.Current.Response.ContentEncoding = System.Text.Encoding.UTF8; HttpContext.Current.Response.Flush(); HttpContext.Current.Response.End(); }