方法一:拼html
新建一个一般处理程序
public void ProcessRequest(HttpContext context)
{
context.Response.ContentType = "text/plain";
context.Response.Clear();
context.Response.Buffer = true;
context.Response.Charset = "utf-8";
context.Response.AppendHeader("Content-Disposition", "attachment;filename=" + context.Server.UrlEncode("Excel名称") + ".xls");
context.Response.ContentEncoding = System.Text.Encoding.GetEncoding("GB2312");//设置输出流为简体中文
context.Response.ContentType = "application/vnd.ms-excel";//设置输出文件类型为excel文件。
context.Response.Write("表头1\t");
context.Response.Write("表头2\t");
context.Response.Write("表头3\t");
context.Response.Write("\n");
for (int i = 0; i < 100;i++ )
{
context.Response.Write("aaa" + "\t");
context.Response.Write("bbb" + "\t");
context.Response.Write("ccdd" + "\t");
context.Response.Write("\n");
}
context.Response.End();
}
就可以进行最简单的导出excel,当然也可以写在asp.net mvc中的Controller的方法里,效果也一样
public class TreeController : Controller
{
public void ToExcelTest()
{
HttpContext.Response.ContentType = "text/plain";
HttpContext.Response.Clear();
HttpContext.Response.Buffer = true;
HttpContext.Response.Charset = "utf-8";
HttpContext.Response.AppendHeader("Content-Disposition", "attachment;filename=" + HttpContext.Server.UrlEncode("Excel名称") + ".xls");
HttpContext.Response.ContentEncoding = System.Text.Encoding.GetEncoding("GB2312");//设置输出流为简体中文
HttpContext.Response.ContentType = "application/vnd.ms-excel";//设置输出文件类型为excel文件。
HttpContext.Response.Write("<table cellpadding='1' cellspacing='1'>");
HttpContext.Response.Write("<tr >");
HttpContext.Response.Write("<td> sucessful3333"); HttpContext.Response.Write("</td>");
HttpContext.Response.Write("<td> sucessful2"); HttpContext.Response.Write("</td>");
HttpContext.Response.Write("</tr>");
HttpContext.Response.Write("</table>");
Response.End();
HttpContext.Response.End();
}
}
方法二:使用npoi
public void sccussful()
{
HSSFWorkbook workbook = new HSSFWorkbook();
MemoryStream ms = new MemoryStream();
// 新建表
Sheet sheet = workbook.CreateSheet("My Sheet");
Row r = sheet.CreateRow(0);
r.Height = 26 * 20;
Cell c = r.CreateCell(0);
c.SetCellValue("公司:xx" + "QQ:" + 123456789+" \n mmmww");
//设置样式
CellStyle style = workbook.CreateCellStyle();
style.Alignment = HorizontalAlignment.CENTER;
style.VerticalAlignment = VerticalAlignment.CENTER;
style.WrapText = true;//配合\n使用进行换行
Font font = workbook.CreateFont();
font.FontHeight = 13 * 13;
font.Boldweight = short.MaxValue;//加粗
font.FontName = "微软雅黑";
font.Color = HSSFColor.RED.index;
style.SetFont(font);
c.CellStyle = style;
Row r2 = sheet.CreateRow(1);
r2.CreateCell(0).SetCellValue("gg");
r2.CreateCell(1).SetCellValue("mm");
r2.CreateCell(2).SetCellValue("22222");
//设置列宽
sheet.SetColumnWidth(0, 16 * 256);
sheet.SetColumnWidth(1, 20 * 256);
sheet.SetColumnWidth(2, 20 * 256);
//合并单元格
CellRangeAddress cellRangeAddress = new CellRangeAddress(0, 0, 0, 2);
sheet.AddMergedRegion(cellRangeAddress);
workbook.Write(ms); Response.AddHeader("Content-Disposition", string.Format("attachment; filename=EmptyWorkbook.xls"));
Response.BinaryWrite(ms.ToArray());
workbook = null;
ms.Close();
ms.Dispose();
}
这里设置值和生成列的顺序必须一一对应,如果数据多了 改一个,或者删除一下移动重新调整比较麻烦,可以用一个计数器记录下下标
public void GetpriceListExport_DZG()
{
int Count = 0;
List<DTO_Price_Info> list = _Price_Info.GetPrice_Info_List_Select();
HSSFWorkbook workbook = new HSSFWorkbook();
MemoryStream ms = new MemoryStream();
// 新增試算表。
Sheet sheet = workbook.CreateSheet("My Sheet");
int titleposition = 0;
Row r2 = sheet.CreateRow(0);
r2.CreateCell(titleposition).SetCellValue("起运港*");
titleposition++;
r2.CreateCell(titleposition).SetCellValue("目的港*");
titleposition++;
r2.CreateCell(titleposition).SetCellValue("船公司*");
titleposition++;
r2.CreateCell(titleposition).SetCellValue("船期*");
titleposition++;
r2.CreateCell(titleposition).SetCellValue("20'GP(底价)"); titleposition++;
r2.CreateCell(titleposition).SetCellValue("40'GP(底价)"); titleposition++;
r2.CreateCell(titleposition).SetCellValue("40'HQ(底价)"); titleposition++;
r2.CreateCell(titleposition).SetCellValue("45'HQ(底价)"); titleposition++;
r2.CreateCell(titleposition).SetCellValue("40'RH(底价)"); titleposition++;
r2.CreateCell(titleposition).SetCellValue("20'GP(成本价)"); titleposition++;
r2.CreateCell(titleposition).SetCellValue("40'GP(成本价)"); titleposition++;
r2.CreateCell(titleposition).SetCellValue("40'HQ(成本价)"); titleposition++;
r2.CreateCell(titleposition).SetCellValue("45'HQ(成本价)"); titleposition++;
r2.CreateCell(titleposition).SetCellValue("40'RH(成本价)"); titleposition++;
r2.CreateCell(titleposition).SetCellValue("20'GP(标准报价)"); titleposition++;
r2.CreateCell(titleposition).SetCellValue("40'GP(标准报价)"); titleposition++;
r2.CreateCell(titleposition).SetCellValue("40'HQ(标准报价)"); titleposition++;
r2.CreateCell(titleposition).SetCellValue("45'HQ(标准报价)"); titleposition++;
r2.CreateCell(titleposition).SetCellValue("40'RH(标准报价)"); titleposition++;
r2.CreateCell(titleposition).SetCellValue("航程(天)*"); titleposition++;
r2.CreateCell(titleposition).SetCellValue("中转港"); titleposition++;
r2.CreateCell(titleposition).SetCellValue("航线*"); titleposition++;
r2.CreateCell(titleposition).SetCellValue("订舱代理"); titleposition++;
r2.CreateCell(titleposition).SetCellValue("港区"); titleposition++;
r2.CreateCell(titleposition).SetCellValue("有效期(开始)*"); titleposition++;
r2.CreateCell(titleposition).SetCellValue("有效期(结束)*"); titleposition++;
r2.CreateCell(titleposition).SetCellValue("其它RMB费用"); titleposition++;
r2.CreateCell(titleposition).SetCellValue("其它附加费"); titleposition++;
r2.CreateCell(titleposition).SetCellValue("内部备注"); titleposition++;
r2.CreateCell(titleposition).SetCellValue("外部备注"); titleposition++;
r2.CreateCell(titleposition).SetCellValue("限重"); titleposition++;
int i = 1;
decimal d = 0;
int valueposition = 0;
foreach (DTO_Price_Info item in list)
{
Row ir = sheet.CreateRow(i);
valueposition = 0;
ir.CreateCell(valueposition).SetCellValue(item.QYPortEN);
valueposition++;
ir.CreateCell(valueposition).SetCellValue(item.MDPortEN);
valueposition++;
ir.CreateCell(valueposition).SetCellValue(item.ShipEN);
valueposition++;
ir.CreateCell(valueposition).SetCellValue(item.ShipDate);
valueposition++;
ir.CreateCell(valueposition).SetCellValue(GetPrice(item.Price_20)); valueposition++;
ir.CreateCell(valueposition).SetCellValue(GetPrice(item.Price_40)); valueposition++;
ir.CreateCell(valueposition).SetCellValue(GetPrice(item.Price_40HQ)); valueposition++;
ir.CreateCell(valueposition).SetCellValue(GetPrice(item.Price_45HQ)); valueposition++;
ir.CreateCell(valueposition).SetCellValue(GetPrice(item.Price_NOR)); valueposition++;
ir.CreateCell(valueposition).SetCellValue(GetPrice(item.Price_20_zd)); valueposition++;
ir.CreateCell(valueposition).SetCellValue(GetPrice(item.Price_40_zd)); valueposition++;
ir.CreateCell(valueposition).SetCellValue(GetPrice(item.Price_40HQ_zd)); valueposition++;
ir.CreateCell(valueposition).SetCellValue(GetPrice(item.Price_45_zd)); valueposition++;
ir.CreateCell(valueposition).SetCellValue(GetPrice(item.Price_NOR_zd)); valueposition++;
ir.CreateCell(valueposition).SetCellValue(GetPrice(item.Price_20_yj)); valueposition++;
ir.CreateCell(valueposition).SetCellValue(GetPrice(item.Price_40_yj)); valueposition++;
ir.CreateCell(valueposition).SetCellValue(GetPrice(item.Price_40HQ_yj)); valueposition++;
ir.CreateCell(valueposition).SetCellValue(GetPrice(item.Price_45_yj)); valueposition++;
ir.CreateCell(valueposition).SetCellValue(GetPrice(item.Price_NOR_yj));
valueposition++;
ir.CreateCell(valueposition).SetCellValue(item.Voyage); valueposition++;
ir.CreateCell(valueposition).SetCellValue(item.MiddlePortEN); valueposition++;
ir.CreateCell(valueposition).SetCellValue(item.LineName); valueposition++;
ir.CreateCell(valueposition).SetCellValue(item.BookingAgent); valueposition++;
ir.CreateCell(valueposition).SetCellValue(item.PortArea); valueposition++;
ir.CreateCell(valueposition).SetCellValue(item.BeginDate.Value.ToString("yyyy-MM-dd")); valueposition++;
ir.CreateCell(valueposition).SetCellValue(item.EndDate.Value.ToString("yyyy-MM-dd")); valueposition++;
ir.CreateCell(valueposition).SetCellValue(item.XZ40); valueposition++;
ir.CreateCell(valueposition).SetCellValue(item.Remark); valueposition++;
ir.CreateCell(valueposition).SetCellValue(item.Remark1); valueposition++;
ir.CreateCell(valueposition).SetCellValue(item.Remark2); valueposition++;
ir.CreateCell(valueposition).SetCellValue(item.XZSM); valueposition++;
i++;
}
workbook.Write(ms); Response.AddHeader("Content-Disposition", string.Format("attachment; filename=EmptyWorkbook.xls"));
Response.BinaryWrite(ms.ToArray());
workbook = null;
ms.Close();
ms.Dispose();
}
导出实例:
效果图:
代码:
public void AJToExcel(string pricelist, string otherinfo)
{
List<Simple_DTO> sdtlist = pricelist.ToObj<List<Simple_DTO>>();
if (sdtlist.Count <= 0)
return;
BXINfo_DTO binfo = otherinfo.ToObj<BXINfo_DTO>();
HSSFWorkbook workbook = new HSSFWorkbook();
MemoryStream ms = new MemoryStream();
// 新增試算表。
Sheet sheet = workbook.CreateSheet("Sheet0");
sheet.SetColumnWidth(0, 18 * 256);
sheet.SetColumnWidth(1, 60 * 256);
sheet.AddMergedRegion(new CellRangeAddress(0, 0, 0,1));//合并单元格
sheet.AddMergedRegion(new CellRangeAddress(5, 5 + sdtlist.Count - 1, 0, 0));//合并单元格
CellStyle titlestyler = workbook.CreateCellStyle();
titlestyler.Alignment = HorizontalAlignment.CENTER;//设置水平居中
titlestyler.VerticalAlignment = VerticalAlignment.CENTER;///设置数值居中
Font fontr = workbook.CreateFont();
fontr.FontHeight = 17 * 17; //字体大小
fontr.Boldweight = short.MaxValue;//字体粗度
titlestyler.SetFont(fontr);
Row r2 = sheet.CreateRow(0);
r2.Height = 25 * 20;
r2.CreateCell(0).SetCellValue("报价单");
r2.GetCell(0).CellStyle = titlestyler;
CellStyle letfstyler = workbook.CreateCellStyle();
letfstyler.Alignment = HorizontalAlignment.CENTER;
letfstyler.VerticalAlignment = VerticalAlignment.CENTER;
CellStyle rigtstyler = workbook.CreateCellStyle();
rigtstyler.Alignment = HorizontalAlignment.LEFT;
rigtstyler.VerticalAlignment = VerticalAlignment.CENTER;
int i = 0;
int rows = 1;
Row ir = sheet.CreateRow(rows); rows++;
ir.Height = 21 * 20;
ir.CreateCell(i).SetCellValue("包含服务:"); i++;
ir.CreateCell(i).SetCellValue(binfo.bhfw); i++;
ir.GetCell(0).CellStyle = letfstyler;
ir.GetCell(1).CellStyle = rigtstyler;
i = 0;
Row ir2 = sheet.CreateRow(rows); rows++;
ir2.Height = 21 * 20;
ir2.CreateCell(i).SetCellValue("起止地址:"); i++;
ir2.CreateCell(i).SetCellValue(binfo.qzdd); i++;
ir2.GetCell(0).CellStyle = letfstyler;
ir2.GetCell(1).CellStyle = rigtstyler;
i = 0;
Row ir3 = sheet.CreateRow(rows); rows++;
ir3.Height = 21 * 20;
ir3.CreateCell(i).SetCellValue("订舱信息:"); i++;
ir3.CreateCell(i).SetCellValue(binfo.dcxx); i++;
ir3.GetCell(0).CellStyle = letfstyler;
ir3.GetCell(1).CellStyle = rigtstyler;
i = 0;
Row ir4 = sheet.CreateRow(rows); rows++;
ir4.Height = 21 * 20;
ir4.CreateCell(i).SetCellValue("出运货量:"); i++;
string[] cyhlstr = binfo.cyhl.Split(':');
if (cyhlstr.Length > 1)
{
ir4.CreateCell(i).SetCellValue(cyhlstr[1]); i++;
}
ir4.GetCell(0).CellStyle = letfstyler;
ir4.GetCell(1).CellStyle = rigtstyler;
CellStyle pricestyler = workbook.CreateCellStyle();
pricestyler.Alignment = HorizontalAlignment.LEFT;
pricestyler.VerticalAlignment = VerticalAlignment.CENTER;
Font pricefontr = workbook.CreateFont();
pricefontr.Boldweight = short.MaxValue;
pricestyler.SetFont(pricefontr);
foreach (Simple_DTO item in sdtlist)
{
i = 0;
Row ir5 = sheet.CreateRow(rows); rows++;
ir5.Height = 21 * 20;
ir5.CreateCell(i).SetCellValue("费用明细:"); i++;
ir5.CreateCell(i).SetCellValue(item.name + item.value); i++;
ir5.GetCell(0).CellStyle = letfstyler;
ir5.GetCell(1).CellStyle = pricestyler;
}
CellStyle hjstyler = workbook.CreateCellStyle();
hjstyler.Alignment = HorizontalAlignment.LEFT;
hjstyler.VerticalAlignment = VerticalAlignment.CENTER;
Font hjfontr = workbook.CreateFont();
hjfontr.Color = HSSFColor.ORANGE.index;//字体颜色
hjstyler.SetFont(hjfontr);
i = 0;
Row ir7 = sheet.CreateRow(rows); rows++;
ir7.Height = 21 * 20;
ir7.CreateCell(i).SetCellValue("分类合计:"); i++;
ir7.CreateCell(i).SetCellValue(binfo.flhj); i++;
ir7.GetCell(0).CellStyle = letfstyler;
ir7.GetCell(1).CellStyle = hjstyler;
i = 0;
Row ir8 = sheet.CreateRow(rows); rows++;
ir8.Height = 21 * 20;
ir8.CreateCell(i).SetCellValue("参考汇率"); i++;
ir8.CreateCell(i).SetCellValue("1$=6.44095¥"); i++;
ir8.GetCell(0).CellStyle = letfstyler;
ir8.GetCell(1).CellStyle = hjstyler;
i = 0;
Row ir9 = sheet.CreateRow(rows); rows++;
ir9.Height = 21 * 20;
ir9.CreateCell(i).SetCellValue("费用总计"); i++;
ir9.CreateCell(i).SetCellValue(binfo.fyzj); i++;
ir9.GetCell(0).CellStyle = letfstyler;
ir9.GetCell(1).CellStyle = hjstyler;
workbook.Write(ms); Response.AddHeader("Content-Disposition", string.Format("attachment; filename=报价单.xls"));
Response.BinaryWrite(ms.ToArray());
workbook = null;
ms.Close();
ms.Dispose();
}
用模板导出Excel,有些比较复杂的表头,我们就不用代码里边构建了
private HSSFWorkbook getWorkBook(string templetPath)
{
FileStream file = new FileStream(templetPath, FileMode.Open, FileAccess.Read);
HSSFWorkbook workbook = new HSSFWorkbook(file);
return workbook;
}
public void ToExcel(List<DTO_Price_Info> _params)
{
HSSFWorkbook workbook = getWorkBook("d://PSTAR运价导入模版.xls");//读取模板
MemoryStream ms = new MemoryStream();
Sheet sheet = workbook.GetSheet("整箱导出");//读取表
//添加数据
int i = 2;
int position = 0;
foreach (DTO_Price_Info item in _params)
{
Row ir = sheet.CreateRow(i);
ir.Height = 20 * 20;
ir.CreateCell(1).SetCellValue("上海");
ir.CreateCell(2).SetCellValue("200");
}
//导出excel
workbook.Write(ms); Response.AddHeader("Content-Disposition", string.Format("attachment; filename=PSTAR.xls"));
Response.BinaryWrite(ms.ToArray());
workbook = null;
ms.Close();
ms.Dispose();
}