///
///生成excel文件流///
///
///
/// 样式格式
///
public static void DataTableToExcel(DataTable dt, string filePath, HorizontalAlignment styleType, int sheetIndex = 0)
{
IWorkbook workbook= null;try{using (FileStream file = newFileStream(filePath, FileMode.Open, FileAccess.Read))
{
workbook= newXSSFWorkbook(file);
ISheet sheet=workbook.GetSheetAt(sheetIndex);
ICellStyle headerCellStyle=workbook.CreateCellStyle();
headerCellStyle.BorderBottom=BorderStyle.Thin;
headerCellStyle.BorderLeft=BorderStyle.Thin;
headerCellStyle.BorderRight=BorderStyle.Thin;
headerCellStyle.BorderTop=BorderStyle.Thin;
headerCellStyle.Alignment= styleType;//设置title样式(居中,靠右)
IFont headerFont =workbook.CreateFont();
headerFont.FontHeightInPoints= 11;
headerFont.Boldweight= (short)FontBoldWeight.Bold;
headerCellStyle.SetFont(headerFont);int colIndex = 0;
IRow headerRow= sheet.CreateRow(0);foreach (DataColumn dc indt.Columns)
{
ICell cell=headerRow.CreateCell(colIndex);
cell.SetCellValue(dc.ColumnName);
cell.CellStyle=headerCellStyle;
colIndex++;
}
IFont cellFont=workbook.CreateFont();
cellFont.FontHeightInPoints= 11;
cellFont.Boldweight= (short)FontBoldWeight.Normal;
ICellStyle cellStyleNumber=workbook.CreateCellStyle();
cellStyleNumber.BorderBottom=BorderStyle.Thin;
cellStyleNumber.BorderLeft=BorderStyle.Thin;
cellStyleNumber.BorderRight=BorderStyle.Thin;
cellStyleNumber.BorderTop=BorderStyle.Thin;
cellStyleNumber.SetFont(cellFont);
IDataFormat dataFormatNumber=workbook.CreateDataFormat();
cellStyleNumber.DataFormat= dataFormatNumber.GetFormat("0");
ICellStyle cellStyleNumberic=workbook.CreateCellStyle();
cellStyleNumberic.BorderBottom=BorderStyle.Thin;
cellStyleNumberic.BorderLeft=BorderStyle.Thin;
cellStyleNumberic.BorderRight=BorderStyle.Thin;
cellStyleNumberic.BorderTop=BorderStyle.Thin;
cellStyleNumberic.SetFont(cellFont);
IDataFormat dataFormatNumberic=workbook.CreateDataFormat();
cellStyleNumberic.DataFormat= dataFormatNumberic.GetFormat("0.00");
ICellStyle cellStyleDateTime=workbook.CreateCellStyle();
cellStyleDateTime.BorderBottom=BorderStyle.Thin;
cellStyleDateTime.BorderLeft=BorderStyle.Thin;
cellStyleDateTime.BorderRight=BorderStyle.Thin;
cellStyleDateTime.BorderTop=BorderStyle.Thin;
cellStyleDateTime.SetFont(cellFont);
IDataFormat dataFormatDateTime=workbook.CreateDataFormat();
cellStyleDateTime.DataFormat= dataFormatDateTime.GetFormat("MM/dd/yyyy");
ICellStyle cellStyleText=workbook.CreateCellStyle();
cellStyleText.BorderBottom=BorderStyle.Thin;
cellStyleText.BorderLeft=BorderStyle.Thin;
cellStyleText.BorderRight=BorderStyle.Thin;
cellStyleText.BorderTop=BorderStyle.Thin;
cellStyleText.SetFont(cellFont);
IDataFormat dataFormatText=workbook.CreateDataFormat();//cellStyleText.DataFormat = dataFormatText.GetFormat("@");
int rowIndex = 1;int cellIndex = 0;foreach (DataRow dr indt.Rows)
{
IRow dataRow=sheet.CreateRow(rowIndex);foreach (DataColumn dc indt.Columns)
{
ICell cell=dataRow.CreateCell(cellIndex);
Int64 number= 0;double numberic = 0;
DateTime dateTime= newDateTime();string columnValue =dr[dc].ToString();if (Int64.TryParse(columnValue, outnumber))
{
cell.SetCellValue(number);
cellStyleNumber.Alignment=styleType;
cell.CellStyle=cellStyleNumber;
}else if (double.TryParse(columnValue, out numberic) && columnValue.Contains('.'))
{
cell.SetCellValue(numberic);
cellStyleNumber.Alignment=styleType;
cell.CellStyle=cellStyleNumberic;
}else if (DateTime.TryParse(columnValue, outdateTime))
{if (dc.ToString().Trim().ToLower()== "createtime")
{if (dateTime.ToString("yyyy-MM-dd") == "0001-01-01")
{
cell.SetCellValue("");
}else{
cell.SetCellValue(dateTime.ToString("MM/dd/yyyy HH:mm:ss"));
}
}else{if (dateTime.ToString("yyyy-MM-dd") == "0001-01-01")
{
cell.SetCellValue("");
}else{
cell.SetCellValue(dateTime.ToString("MM/dd/yyyy"));
}
}
cellStyleDateTime.Alignment=styleType;
cell.CellStyle=cellStyleDateTime;
}else{
cell.SetCellValue(columnValue);
cellStyleText.Alignment=styleType;
cell.CellStyle=cellStyleText;
}
cellIndex++;
}
cellIndex= 0;
rowIndex++;
}//for (int i = 0; i < colIndex; i++)//{//sheet.AutoSizeColumn(i);//}
MemoryStream ms = newMemoryStream();
workbook.Write(ms);
ms.Flush();using (FileStream saveFile = newFileStream(filePath, FileMode.Create, FileAccess.Write))
{byte[] data =ms.ToArray();
saveFile.Write(data,0, data.Length);
saveFile.Flush();
data= null;
}
}
}catch(Exception ex)
{throwex;
}finally{
workbook= null;
}
}
------------对上面方法调用---------------
///
/// 下载文件
///
///
///
///
public static string DownLoadExcel(DataTable dt, string fileName, string styleType = "")
{
//当前日文件夹
string dateString = System.DateTime.Now.ToString("yyyyMMdd");
//删除其他日期文件夹
DirectoryInfo dir = new DirectoryInfo(HostingEnvironment.MapPath("~/TemparyFile"));
FileSystemInfo[] fileinfo = dir.GetFileSystemInfos(); //返回目录中所有文件和子目录
foreach (FileSystemInfo i in fileinfo)
{
if (i is DirectoryInfo && !i.Name.Equals(dateString) && !i.Name.ToLower().Equals("userfile")) //判断是否文件夹
{
DirectoryInfo subdir = new DirectoryInfo(i.FullName);
subdir.Delete(true); //删除子目录和文件
}
}
//无当天文件夹时创建文件夹
if (!Directory.Exists(HostingEnvironment.MapPath("~/TemparyFile/" + dateString)))
{
Directory.CreateDirectory(HostingEnvironment.MapPath("~/TemparyFile/" + dateString));
}
string sourceFile = HostingEnvironment.MapPath("~/TemparyFile/ExcelTemplate.xlsx");
var date = DateTime.Now.Year.ToString() + DateTime.Now.Month.ToString() + DateTime.Now.Day.ToString() + DateTime.Now.Hour.ToString() + DateTime.Now.Minute.ToString() + DateTime.Now.Second.ToString();
//string destFileName = fileName+"-" + Guid.NewGuid() + ".xlsx";
string destFileName = fileName + "_" + date + ".xlsx";
string destFilePath = HostingEnvironment.MapPath("~/TemparyFile/" + dateString) + "/" + destFileName;
File.Copy(sourceFile, destFilePath);
switch (styleType.ToLower())
{
case "center":
ExcelHandle.DataTableToExcel(dt, destFilePath, NPOI.SS.UserModel.HorizontalAlignment.Center);
break;
case "right":
ExcelHandle.DataTableToExcel(dt, destFilePath, NPOI.SS.UserModel.HorizontalAlignment.Right);
break;
default:
ExcelHandle.DataTableToExcel(dt, destFilePath);
break;
}
return "/TemparyFile/" + dateString + "/" + destFileName;
}