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();
}
