c# 导出excel

最新推荐文章于 2019-01-17 16:06:00 发布

wyb926

最新推荐文章于 2019-01-17 16:06:00 发布

阅读量130

点赞数

本文链接：https://blog.csdn.net/wyb926/article/details/86624945

版权

c# 导出excel

1,不是用第三方插件(html直接输出)

StringBuilder ssb = new StringBuilder();
StringBuilder sb = new StringBuilder();
sb.AppendFormat(@"<html>
<meta http-equiv='Content-Type' content='text/html; charset=UTF-8'>
<body>
    <table border='1'>
        <tr>
            <td align='center' colspan='1'>运单信息</td>
            <td align='center' colspan='4'>收件人信息</td>
            <td align='center' colspan='3'>托寄物信息</td>
            <td align='center' colspan='2'>保价信息</td>
            <td align='center' colspan='1'>订单金额</td>
            <td align='center' colspan='1'>服务类型</td>
            <td align='center' colspan='1'>运单备注</td>
            <td align='center' colspan='1'>配送业务类型</td>
            <td align='center' colspan='1'>运单信息</td>
        </tr>
        <tr>
            <td>关联订单</td>
            <td>姓名</td>
            <td>手机</td>
            <td>座机</td>
            <td>地址</td>
            <td>物品内容</td>
            <td>包裹数量</td>
            <td>重量（kg）</td>
            <td>保价</td>
            <td>保价金额（元）</td>
            <td>订单金额（元）</td>
            <td>代收货款</td>
            <td>备注信息</td>
            <td>配送业务类型</td>
            <td>京东订单号</td>
        </tr>{0}
    </table>
</body>
</html>", ssb);

string excelHtml = sb.ToString();// context.Request["excelHtml"];
string name = DateTime.Now.ToString();
context.Response.Buffer = true;
//输出的应用类型 
context.Response.ContentType = "application/vnd.ms-excel";
//设定编码方式，若输出的excel有乱码，可优先从编码方面解决
context.Response.Charset = "utf-8";
context.Response.ContentEncoding = System.Text.Encoding.UTF8;
//filenames是自定义的文件名
context.Response.AppendHeader("Content-Disposition", "attachment;filename=" + name + ".xls");
//content是步骤1的html，注意是string类型
context.Response.Write(excelHtml);
context.Response.End();

2,使用NPOI(管理NuGet程序包→搜索NPOI安装),也可以去NPOI官网下载引用

/// <summary>
        /// 导出excel（使用NPOI的方式）
        /// </summary>
        /// <param name="DT"></param>
        public string ExportExcel(string ids)
        {
            try
            {
                DataTable DT = mallData.GetOrderListByIds(ids);
                string path = AppDomain.CurrentDomain.BaseDirectory;
                HSSFWorkbook hssfworkbookDown;
                string modelExlPath = path + "attachments/excel/import.xls";
                if (File.Exists(modelExlPath) == false)//模板不存在
                {
                    return null;
                }
                using (FileStream file = new FileStream(modelExlPath, FileMode.Open, FileAccess.Read))
                {
                    hssfworkbookDown = new HSSFWorkbook(file);
                    file.Close();
                }
                if (DT.Rows.Count > 0)
                {
                    WriterExcel(hssfworkbookDown, 0, DT);

                    string filename = DateTime.Now.ToString("yyyyMMddHHmmss")+LibSysUtils.NewRefId().ToString().Substring(0,3)+".xls";
                    string strFilePath = path + "attachments/excel";
                    if (Directory.Exists(strFilePath) == false)
                    {
                        Directory.CreateDirectory(strFilePath);
                    }
                    strFilePath = strFilePath + "/" + filename;
                    FileStream files = new FileStream(strFilePath, FileMode.Create);
                    hssfworkbookDown.Write(files);
                    files.Close();
                    if (File.Exists(strFilePath) == false)//附件生成失败
                    {
                        return null;
                    }

                    return strFilePath;
                }
            }
            catch (Exception ex)
            {
               
            }
            return null;
        }
        /// <summary>
        /// 写入Excel
        /// </summary>
        /// <param name="hssfworkbookDown"></param>
        /// <param name="sheetIndex"></param>
        /// <param name="DT"></param>
        public  void WriterExcel(HSSFWorkbook hssfworkbookDown, int sheetIndex, DataTable DT)
        {
            try
            {
                #region 设置单元格样式
                //字体
                HSSFFont fontS9 = (HSSFFont)hssfworkbookDown.CreateFont();
                fontS9.FontName = "Arial";
                fontS9.FontHeightInPoints = 10;
                fontS9.Boldweight = (short)NPOI.SS.UserModel.FontBoldWeight.NORMAL;
                //表格
                ICellStyle TableS9 = (ICellStyle)hssfworkbookDown.CreateCellStyle();
                TableS9.BorderLeft = NPOI.SS.UserModel.BorderStyle.THIN;
                TableS9.BorderTop = NPOI.SS.UserModel.BorderStyle.THIN;
                TableS9.BorderBottom = NPOI.SS.UserModel.BorderStyle.THIN;
                TableS9.BorderRight = NPOI.SS.UserModel.BorderStyle.THIN;
                TableS9.WrapText = true;
                TableS9.SetFont(fontS9);
                #endregion

                HSSFSheet sheet = (HSSFSheet)hssfworkbookDown.GetSheetAt(sheetIndex);
                hssfworkbookDown.SetSheetHidden(sheetIndex, false);
                hssfworkbookDown.SetActiveSheet(sheetIndex);

                int n = 2;//因为模板有表头，所以从第3行开始写
                for (int j = 0; j < DT.Rows.Count; j++)
                {
                    HSSFRow dataRow = (HSSFRow)sheet.CreateRow(j + n);
                    dataRow.CreateCell(0);
                    dataRow.Cells[0].SetCellValue(DT.Rows[j]["order_id"].ToString());
                    dataRow.CreateCell(1);
                    dataRow.Cells[1].SetCellValue(DT.Rows[j]["consignee_name"].ToString());
                    dataRow.CreateCell(2);
                    dataRow.Cells[2].SetCellValue(DT.Rows[j]["consignee_tel"].ToString());
                    dataRow.CreateCell(3);
                    dataRow.Cells[3].SetCellValue("");
                    dataRow.CreateCell(4);
                    dataRow.Cells[4].SetCellValue(DT.Rows[j]["consignee_address"].ToString());
                    dataRow.CreateCell(5);
                    dataRow.Cells[5].SetCellValue("");
                    dataRow.CreateCell(6);
                    dataRow.Cells[6].SetCellValue(1);
                    dataRow.CreateCell(7);
                    dataRow.Cells[7].SetCellValue(1);
                    dataRow.CreateCell(8);
                    dataRow.Cells[8].SetCellValue("否");
                    dataRow.CreateCell(9);
                    dataRow.Cells[9].SetCellValue(0);
                    dataRow.CreateCell(10);
                    dataRow.Cells[10].SetCellValue(1);
                    dataRow.CreateCell(11);
                    dataRow.Cells[11].SetCellValue("否");
                    dataRow.CreateCell(12);
                    dataRow.Cells[12].SetCellValue("");
                    dataRow.CreateCell(13);
                    dataRow.Cells[13].SetCellValue("普通");
                    dataRow.CreateCell(14);
                    dataRow.Cells[14].SetCellValue("");

                    for (int i = 0; i <= 2; i++)//循环列，添加样式
                    {
                        dataRow.Cells[i].CellStyle = TableS9;
                    }
                }
                //设定第一行，第一列的单元格选中
                sheet.SetActiveCell(0, 0);
            }
            catch (Exception ex)
            {
            }
        }