c#后台如何导出excel到本地_c# 后台 导出EXCEL方法

///

///生成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;

}