//public void OutputBinayStream(System.IO.FileInfo file, HttpContext context)
//{
// #region Output Binary Stream
// context.Response.Clear();
// context.Response.Charset = "GB2312";
// context.Response.AddHeader("Content-Disposition", "attachment; filename=" + System.Web.HttpContext.Current.Server.UrlEncode(file.Name));
// context.Response.AddHeader("Content-Length", file.Length.ToString());
// context.Response.ContentType = "application/ms-excel";
// context.Response.WriteFile(file.FullName);
// context.Response.End();
// #endregion
//}
#endregion
#region 动态转换成excel
public static bool ToExcelDynamic(string savePath, string SheetName, string HeadName, DataTable table, Dictionary <string, string> excelDataMap)
{
try
{
List <int> dateCellIndex = new List <int>();//日期格式列
bool header = true;
//创建工作薄
HSSFWorkbook wk = new HSSFWorkbook();
//创建一个名称为mySheet的表
ISheet tb = wk.CreateSheet(SheetName);
#region 表头样式
ICellStyle headStyle = wk.CreateCellStyle();
headStyle.FillBackgroundColor = NPOI.HSSF.Util.HSSFColor.Yellow.Index;
headStyle.Alignment = HorizontalAlignment.Center;
IFont font = wk.CreateFont();
font.FontName = "宋体";
font.FontHeightInPoints = 12;
font.Boldweight = 700;
headStyle.SetFont(font);
#endregion
#region 行样式
ICellStyle stylerow = wk.CreateCellStyle();
stylerow.Alignment = HorizontalAlignment.Center;
IFont fontrow = wk.CreateFont();
fontrow.FontName = "宋体";
fontrow.FontHeightInPoints = 12;
stylerow.SetFont(fontrow);
#endregion
#region 日期行样式
ICellStyle dateStyle = wk.CreateCellStyle();
IDataFormat format = wk.CreateDataFormat();
dateStyle.DataFormat = format.GetFormat("yyyy-MM-dd HH:mm:ss");
#endregion
//合并标题行
/// <param name="sheet">要合并单元格所在的sheet</param>
/// <param name="rowstart">开始行的索引</param>
/// <param name="rowend">结束行的索引</param>
/// <param name="colstart">开始列的索引</param>
/// <param name="colend">结束列的索引</param>
tb.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(0, 0, 0, excelDataMap.Keys.Count));
IRow rowhead = tb.CreateRow(0); //创建一行
rowhead.HeightInPoints = 25; //行高
ICell cellhead = rowhead.GetCell(0, MissingCellPolicy.CREATE_NULL_AS_BLANK); //在行中创建单元格
cellhead.CellStyle = headStyle;
cellhead.SetCellValue(HeadName); //写入表头
//SetCellRangeAddress(tb, 0, 0, 1, 20);
int rowIndex = 1;
if (header)
{
IRow row = tb.CreateRow(rowIndex); //创建一行
rowIndex++;
row.HeightInPoints = 25; //行高
int cellIndex = 0; //开始列索引
foreach (var item in excelDataMap)
{
string columsName = item.Value;
tb.SetColumnWidth(cellIndex, 16 * 256); //行宽8个汉字
ICell cell = row.GetCell(cellIndex, MissingCellPolicy.CREATE_NULL_AS_BLANK); //在行中创建单元格
cell.CellStyle = headStyle;
if (columsName == "DEPT_NAME")
{
cell.SetCellValue("单位名称");
}
else
{
cell.SetCellValue(table.Columns[columsName].ToString());//循环往第二行的单元格中添加数据
}
cellIndex++;
}
}
for (int i = 0; i < table.Rows.Count; i++)
{
IRow row = tb.CreateRow(rowIndex + i); //创建一行
row.HeightInPoints = 20; //行高
int cellIndex = 0; //开始列索引
foreach (var item in excelDataMap)
{
string columsName = item.Value;
ICell cell = row.GetCell(cellIndex, MissingCellPolicy.CREATE_NULL_AS_BLANK); //在行中创建单元格
cell.SetCellValue(table.Rows[i][columsName].ToString()); //循环往第二行的单元格中添加数据
cell.CellStyle = stylerow;
cellIndex++;
}
}
using (FileStream fs = File.OpenWrite(savePath)) //打开一个xls文件，如果没有则自行创建，如果存在myxls.xls文件则在创建是不要打开该文件！
{
wk.Write(fs); //向打开的这个xls文件中写入mySheet表并保存。
}
return(true);
}
catch (Exception)
{
return(false);
}
}
public static bool ToExcelDate(string savePath, string SheetName, string HeadName, IList listJob, string className)
{
try
{
DataTable table = ToDataTable(listJob, className);
List <int> dateCellIndex = new List <int>();//日期格式列
bool header = true;
//创建工作薄
HSSFWorkbook wk = new HSSFWorkbook();
//创建一个名称为mySheet的表
ISheet tb = wk.CreateSheet(SheetName);
#region 表头样式
ICellStyle headStyle = wk.CreateCellStyle();
headStyle.FillBackgroundColor = NPOI.HSSF.Util.HSSFColor.Yellow.Index;
headStyle.Alignment = HorizontalAlignment.Center;
IFont font = wk.CreateFont();
font.FontName = "宋体";
font.FontHeightInPoints = 12;
font.Boldweight = 700;
headStyle.SetFont(font);
#endregion
#region 行样式
ICellStyle stylerow = wk.CreateCellStyle();
stylerow.Alignment = HorizontalAlignment.Center;
IFont fontrow = wk.CreateFont();
fontrow.FontName = "宋体";
fontrow.FontHeightInPoints = 12;
stylerow.SetFont(fontrow);
#endregion
#region 日期行样式
ICellStyle dateStyle = wk.CreateCellStyle();
IDataFormat format = wk.CreateDataFormat();
dateStyle.DataFormat = format.GetFormat("yyyy-MM-dd");
#endregion
Dictionary <string, string> excelDataMap = ReadXml(className, false);
//合并标题行
/// <param name="sheet">要合并单元格所在的sheet</param>
/// <param name="rowstart">开始行的索引</param>
/// <param name="rowend">结束行的索引</param>
/// <param name="colstart">开始列的索引</param>
/// <param name="colend">结束列的索引</param>
tb.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(0, 0, 0, excelDataMap.Keys.Count));
IRow rowhead = tb.CreateRow(0); //创建一行
rowhead.HeightInPoints = 25; //行高
ICell cellhead = rowhead.GetCell(0, MissingCellPolicy.CREATE_NULL_AS_BLANK); //在行中创建单元格
cellhead.CellStyle = headStyle;
cellhead.SetCellValue(HeadName); //写入表头
//SetCellRangeAddress(tb, 0, 0, 1, 20);
int rowIndex = 1;
if (header)
{
IRow row = tb.CreateRow(rowIndex); //创建一行
rowIndex++;
row.HeightInPoints = 25; //行高
int cellIndex = 0; //开始列索引
#region 弃用
//for (int i = 0; i < table.Columns.Count; i++)
//{
// if (!table.Columns[i].ToString().EndsWith("_wennull"))
// {
// if (table.Columns[i].DataType.ToString() == "System.DateTime")//日期型
// {
// tb.SetColumnWidth(cellIndex, 20 * 256);//行宽10个汉字
// dateCellIndex.Add(cellIndex);
// }
// else
// {
// tb.SetColumnWidth(cellIndex, 16 * 256);//行宽8个汉字
// }
// ICell cell = row.GetCell(cellIndex, MissingCellPolicy.CREATE_NULL_AS_BLANK); //在行中创建单元格
// cell.CellStyle = headStyle;
// cell.SetCellValue(table.Columns[i].ToString());//循环往第二行的单元格中添加数据
// cellIndex++;
// }
//}
#endregion
foreach (var item in excelDataMap)
{
string columsName = item.Value;
if (table.Columns[columsName].DataType.ToString() == "System.DateTime") //日期型
{
tb.SetColumnWidth(cellIndex, 20 * 256); //行宽10个汉字
dateCellIndex.Add(cellIndex);
}
else
{
tb.SetColumnWidth(cellIndex, 16 * 256); //行宽8个汉字
}
ICell cell = row.GetCell(cellIndex, MissingCellPolicy.CREATE_NULL_AS_BLANK); //在行中创建单元格
cell.CellStyle = headStyle;
cell.SetCellValue(table.Columns[columsName].ToString()); //循环往第二行的单元格中添加数据
cellIndex++;
}
}
for (int i = 0; i < table.Rows.Count; i++)
{
IRow row = tb.CreateRow(rowIndex + i); //创建一行
row.HeightInPoints = 20; //行高
int cellIndex = 0; //开始列索引
#region 弃用
//for (int c = 0; c < table.Columns.Count; c++)
//{
// if (!table.Columns[c].ToString().EndsWith("_wennull"))
// {
// ICell cell = row.GetCell(cellIndex, MissingCellPolicy.CREATE_NULL_AS_BLANK); //在行中创建单元格
// if (table.Columns[c].DataType.ToString() == "System.DateTime")//日期型
// {
// string value = table.Rows[i][c].ToString();
// if (value != "")
// {
// cell.SetCellValue(Convert.ToDateTime(value).ToString("yyyy-MM-dd HH:mm:ss"));//循环往第二行的单元格中添加数据
// }
// cell.CellStyle = dateStyle;
// }
// else
// {
// cell.SetCellValue(table.Rows[i][c].ToString());//循环往第二行的单元格中添加数据
// cell.CellStyle = stylerow;
// }
// cellIndex++;
// }
//}
#endregion
foreach (var item in excelDataMap)
{
string columsName = item.Value;
ICell cell = row.GetCell(cellIndex, MissingCellPolicy.CREATE_NULL_AS_BLANK); //在行中创建单元格
if (table.Columns[columsName].DataType.ToString() == "System.DateTime") //日期型
{
string value = table.Rows[i][columsName].ToString();
if (value != "")
{
cell.SetCellValue(Convert.ToDateTime(value).ToString("yyyy-MM-dd"));//循环往第二行的单元格中添加数据
}
cell.CellStyle = dateStyle;
}
else
{
cell.SetCellValue(table.Rows[i][columsName].ToString());//循环往第二行的单元格中添加数据
cell.CellStyle = stylerow;
}
cellIndex++;
}
}
//foreach (int cellIndex in dateCellIndex)
//{
// for (int y = table.Rows.Count; y < 500; y++)//至少设置500行格式
// {
// IRow row = tb.CreateRow(rowIndex + y);//创建一行
// row.HeightInPoints = 20; //行高
// ICell cell = row.GetCell(cellIndex, MissingCellPolicy.CREATE_NULL_AS_BLANK); //在行中创建单元格
// //cell.SetCellValue("");//循环往第二行的单元格中添加数据
// cell.CellStyle = dateStyle;
// }
//}
using (FileStream fs = File.OpenWrite(savePath)) //打开一个xls文件，如果没有则自行创建，如果存在myxls.xls文件则在创建是不要打开该文件！
{
wk.Write(fs); //向打开的这个xls文件中写入mySheet表并保存。
}
return(true);
}
catch (Exception)
{
return(false);
}
}