导出excel

最新推荐文章于 2022-12-08 19:28:14 发布

tnblog技术分享～

最新推荐文章于 2022-12-08 19:28:14 发布

阅读量1.3k

点赞数

分类专栏： Asp.net .net 文章标签：导出excel excel 导出 NPOI

本文链接：https://blog.csdn.net/aojiancc2/article/details/21156009

版权

.net 同时被 2 个专栏收录

36 篇文章 2 订阅

订阅专栏

Asp.net

32 篇文章 0 订阅

订阅专栏

方法一：拼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();
        }

tnblog技术分享～

关注

0
点赞
踩
1

收藏

觉得还不错? 一键收藏
0
评论
导出excel

方法一：拼html新建一个一般处理程序public void ProcessRequest(HttpContext context) { context.Response.ContentType = "text/plain"; context.Response.Clear(); context.Res
复制链接

扫一扫

专栏目录