把excel另存为xml电子表格(*.xml)，格式复制生成的xml代码，放到view中，然后根据model去生成excel的单元格和列。
下面是生成excel和下载的代码。
Response.Clear(); Response.ClearContent(); Response.Buffer = true; Response.ContentEncoding = System.Text.Encoding.UTF8; Response.ContentType = "application/ms-excel"; string downLoadFileName = title; if (Request.UserAgent.ToLower().IndexOf("msie") > -1) { downLoadFileName = HttpUtility.UrlPathEncode(downLoadFileName); } else if (Request.UserAgent.ToLower().IndexOf("firefox") > -1) { Response.AddHeader("Content-Disposition", "attachment:filename="" + downLoadFileName + """); } else Response.AddHeader("Content-Disposition", "attachment:filename=" + downLoadFileName + "");
这是另外一种
/// <summary>
/// Excel导出
/// </summary>
/// <param name="dt"></param>
/// <returns></returns>
public static string ExportGoodsSale(DataSet set)
{
try
{
IWorkbook workbook = new XSSFWorkbook();
foreach (DataTable dt in set.Tables)
{
ISheet sheet1 = workbook.CreateSheet(dt.TableName);
int CellCount = dt.Columns.Count;//列数
IRow RowHead = sheet1.CreateRow(0); //创建表头
//绑定字体样式到表头
IFont Headfont = workbook.CreateFont();
Headfont.FontName = "微软雅黑";
Headfont.Color = HSSFColor.BLACK.index;
Headfont.FontHeightInPoints = 11;
//绑定字体到样式上
ICellStyle Headstyle = workbook.CreateCellStyle();
Headstyle.VerticalAlignment = VerticalAlignment.CENTER; //垂直居中
Headstyle.Alignment = HorizontalAlignment.CENTER; //横向居中
//背景颜色
//Headstyle.FillPattern = FillPatternType.BIG_SPOTS;
//Headstyle.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.GREY_25_PERCENT.index;
//Headstyle.FillBackgroundColor = NPOI.HSSF.Util.HSSFColor.GREY_25_PERCENT.index;
Headstyle.SetFont(Headfont);
//边框颜色
Headstyle.BorderBottom = BorderStyle.DOTTED;
Headstyle.BottomBorderColor = HSSFColor.GREY_40_PERCENT.index;
Headstyle.BorderLeft = BorderStyle.DOTTED;
Headstyle.LeftBorderColor = HSSFColor.GREY_40_PERCENT.index;
Headstyle.BorderRight = BorderStyle.DOTTED;
Headstyle.RightBorderColor = HSSFColor.GREY_40_PERCENT.index;
Headstyle.BorderTop = BorderStyle.DOTTED;
Headstyle.TopBorderColor = HSSFColor.GREY_40_PERCENT.index;
//创建表头列
List<string> SellerList = new List<string>(); //门店
List<string> IntervalList = new List<string>(); //档期
foreach (DataColumn col in dt.Columns)
{
if (col.ColumnName == "PName" || col.ColumnName == "SellerInterval")
continue;
string[] colname = col.ColumnName.Split(new char[] { '_' }, StringSplitOptions.RemoveEmptyEntries);
if (!SellerList.Contains(colname[0]))
SellerList.Add(colname[0]);
if (!IntervalList.Contains(colname[1]))
IntervalList.Add(colname[1]);
}
ICell cell = RowHead.CreateCell(0);
cell.SetCellValue("商品名");
cell.CellStyle = Headstyle;
sheet1.SetColumnWidth(0, 30 * 256);
int j=1;
foreach(var seller in SellerList)
{
int s = j;
foreach(string Interval in IntervalList)
{
cell = RowHead.CreateCell(j);
cell.SetCellValue(seller);
cell.CellStyle = Headstyle;
sheet1.SetColumnWidth(j, 21 * 100);
j++;
}
int e = j;
sheet1.AddMergedRegion(new CellRangeAddress(0, 0, s, e-1));
}
cell = RowHead.CreateCell(dt.Columns.Count-1);
cell.SetCellValue("销售量");
cell.CellStyle = Headstyle;
sheet1.SetColumnWidth(dt.Columns.Count - 1, 21 * 156);
RowHead.Height = 25 * 20;
IRow RowHeadt = sheet1.CreateRow(1); //创建表头
cell = RowHeadt.CreateCell(0);
cell.SetCellValue("");
cell.CellStyle = Headstyle;
sheet1.SetColumnWidth(0, 30 * 256);
j = 1;
foreach (var seller in SellerList)
{
foreach (string Interval in IntervalList)
{
cell = RowHeadt.CreateCell(j);
cell.SetCellValue(Interval);
cell.CellStyle = Headstyle;
sheet1.SetColumnWidth(j, 21 * 100);
j++;
}
}
cell = RowHeadt.CreateCell(dt.Columns.Count - 1);
cell.SetCellValue("销售量");
cell.CellStyle = Headstyle;
sheet1.SetColumnWidth(dt.Columns.Count - 1, 21 * 156);
RowHeadt.Height = 25 * 20;
//合并头部单元格
sheet1.AddMergedRegion(new CellRangeAddress(0, 1, 0, 0));
sheet1.AddMergedRegion(new CellRangeAddress(0, 1, dt.Columns.Count - 1, dt.Columns.Count - 1));
//填充内容
//绑定字体样式到表格内容
IFont font = workbook.CreateFont();
//字体样式
font.FontName = "微软雅黑";
font.Color = HSSFColor.BLACK.index;
font.FontHeightInPoints = 11;
ICellStyle style = workbook.CreateCellStyle();
style.VerticalAlignment = VerticalAlignment.CENTER;
style.Alignment = HorizontalAlignment.RIGHT;
style.SetFont(font);
//边框样式
style.BorderBottom = BorderStyle.DOTTED;
style.BottomBorderColor = HSSFColor.GREY_40_PERCENT.index;
style.BorderLeft = BorderStyle.DOTTED;
style.LeftBorderColor = HSSFColor.GREY_40_PERCENT.index;
style.BorderRight = BorderStyle.DOTTED;
style.RightBorderColor = HSSFColor.GREY_40_PERCENT.index;
style.BorderTop = BorderStyle.DOTTED;
style.TopBorderColor = HSSFColor.GREY_40_PERCENT.index;
for (int i = 0; i < dt.Rows.Count; i++)
{
IRow row = sheet1.CreateRow((i + 2));
for (int m = 0; m < CellCount; m++)
{
ICell Tcell = row.CreateCell(m);
Tcell.SetCellValue(dt.Rows[i][m].ToString());
Tcell.CellStyle = style;
}
row.Height = 20 * 20;
}
}
string path = Path.Combine("~/Uploads/" + DateTime.Now.Year + "/" + DateTime.Now.Month + "/" + DateTime.Now.Day + "/");
if (!Directory.Exists(HttpContext.Current.Server.MapPath(path)))
{
Directory.CreateDirectory(HttpContext.Current.Server.MapPath(path));
}
string FileName = Guid.NewGuid()+".xlsx";
var fullPath = path + FileName;
FileStream sw = File.Create(HttpContext.Current.Server.MapPath(fullPath));
workbook.Write(sw);
sw.Close();
return fullPath;
}
catch (Exception ex)
{
throw ex;
}
}
/// <summary>
/// 文件下载
/// </summary>
/// <param name="filename">文件路径</param>
public static void DownLoad(string FilePath,string FileName)
{
FileInfo fileInfo = new FileInfo(HttpContext.Current.Server.MapPath(FilePath));
//以字符流的形式下载文件
FileStream fs = new FileStream(HttpContext.Current.Server.MapPath(FilePath), FileMode.Open);
byte[] bytes = new byte[(int)fs.Length];
fs.Read(bytes, 0, bytes.Length);
fs.Close();
HttpContext.Current.Response.ContentType = "application/octet-stream";
//通知浏览器下载文件而不是打开
HttpContext.Current.Response.AddHeader("Content-Disposition", "attachment; filename=" + HttpUtility.UrlEncode(FileName, System.Text.Encoding.UTF8));
HttpContext.Current.Response.BinaryWrite(bytes);
HttpContext.Current.Response.Flush();
HttpContext.Current.Response.End();
}