C# 实现导出数据到Excel

备忘点日常代码，方便下次查找。

将数据源导出到Excel表中。

//调用方法
public void ListToExcel()
{
     //其中header的格式为  name(excel的表头名,逗号分隔)|header（值对应数据库的字段code，逗号分隔） 
     GridToExcelByNPOIForList(list, "非车险投保单" + DateTime.Now.ToString("yyyyMMdd"), header);
}


//实现方法
public void GridToExcelByNPOIForList<T>(List<T> blist, string fileName, string header)
        {
            //获取导出的name跟code
            string[] extendarry = header.Split('|');
            string headerName = extendarry[0].Substring(0, extendarry[0].Length - 1);
            string headerCode = extendarry[1].Substring(0, extendarry[1].Length - 1);
            string[] exportColumnTitles = headerName.Split(',');
            string[] exportColumns = headerCode.Split(',');
            //指定临时文件存放位置
            string basePath = System.AppDomain.CurrentDomain.BaseDirectory;
            string dllPath = Path.GetDirectoryName(this.GetType().Assembly.CodeBase);
            string tempFilePath = "";
            tempFilePath = basePath + "TMP_Attachments";
            if (!Directory.Exists(tempFilePath))
            {
                Directory.CreateDirectory(tempFilePath);
            }
            //文件在服务器存放的临时路径
            string path = tempFilePath + "/" + fileName + ".xls";
            if (System.IO.File.Exists(path))
            {
                path = tempFilePath + "/" + fileName + DateTime.Now.Millisecond + ".xls";
            }

            try
            {
                HttpResponse context = System.Web.HttpContext.Current.Response;
                HSSFWorkbook workbook = new HSSFWorkbook();
                ISheet sheet = workbook.CreateSheet("Sheet1");

                //文本格式
                ICellStyle cellStyle = workbook.CreateCellStyle();
                IFont font = workbook.CreateFont();
                font.FontHeightInPoints = 12;
                font.FontName = "宋体";
                cellStyle.SetFont(font);
                //为避免日期格式被Excel自动替换，所以设定 format 为 『@』 表示一率当成text來看
                cellStyle.DataFormat = HSSFDataFormat.GetBuiltinFormat("@");

                //数字格式
                ICellStyle cellStyle2 = workbook.CreateCellStyle();
                IFont font2 = workbook.CreateFont();
                font2.FontHeightInPoints = 12;
                font2.FontName = "宋体";
                cellStyle2.SetFont(font2);
                cellStyle2.DataFormat = 194;

                //用column name 作为列名
                int icolIndex = 0;
                IRow headerRow = sheet.CreateRow(0);
                //headerRow.Height = (short)20 * (short)15;
                for (int i = 0; i < exportColumnTitles.Length; i++)
                {
                    ICell cell = headerRow.CreateCell(icolIndex);
                    cell.SetCellValue(exportColumnTitles[i]);
                    cell.CellStyle = cellStyle;
                    icolIndex++;
                }

                //建立内容行
                int iRowIndex = 1;
                int iCellIndex = 0;

                foreach (var item in blist)
                {
                    IRow DataRow = sheet.CreateRow(iRowIndex);

                    for (int i = 0; i < exportColumns.Length; i++)
                    {
                        ICell cell = DataRow.CreateCell(iCellIndex);
                        Type t = item.GetType();

                        foreach (PropertyInfo pi in t.GetProperties())
                        {
                            object pvalue = pi.GetValue(item, null);//用pi.GetValue获得值
                            string name = pi.Name;//获取name
                            if (exportColumns[i].ToString().Equals(name))//如果name跟选择导出的一致
                            {
                                if (pvalue != null)
                                {
                                    if (pvalue.GetType() == typeof(decimal))
                                    {
                                        cell.SetCellValue(Convert.ToDouble(pvalue));
                                        cell.CellStyle = cellStyle2;
                                    }
                                    else
                                    {
                                        cell.SetCellValue(Convert.ToString(pvalue));
                                        cell.CellStyle = cellStyle;
                                    }
                                }
                                else
                                {
                                    cell.SetCellValue("");
                                    cell.CellStyle = cellStyle;
                                }
                            }
                            else
                            {   //如果列名为RowIndex,则生成一个自增的列
                                if (exportColumns[i].ToString() == "RowIndex")
                                {
                                    cell.SetCellValue(iRowIndex);
                                    cell.CellStyle = cellStyle;
                                }
                            }
                        }
                        iCellIndex++;
                    }
                    iCellIndex = 0;
                    iRowIndex++;
                }

                //自适应列宽度
                for (int i = 0; i < icolIndex; i++)
                {
                    sheet.AutoSizeColumn(i);
                    int columnWidth = sheet.GetColumnWidth(i) / 256;
                    if (columnWidth > 100)
                    {
                        columnWidth = 100;
                    }
                    sheet.SetColumnWidth(i, (columnWidth + 2) * 256);
                }

                //写Excel
                FileStream file = new FileStream(path, FileMode.OpenOrCreate);
                workbook.Write(file);
                file.Flush();
                file.Close();

                //下载文件
                System.IO.FileInfo fileExport = new System.IO.FileInfo(path);
                if (fileExport.Exists)
                {
                    context.Clear();
                    bool isFireFox = false;
                    if (Request.ServerVariables["http_user_agent"].ToLower().IndexOf("firefox") != -1)
                    {
                        isFireFox = true;
                    }
                    if (isFireFox == true)
                    {
                        context.AppendHeader("Content-Disposition", "attachment;filename=" + fileName + ".xls");
                    }
                    else
                    {
                        context.AppendHeader("Content-Disposition", "attachment;filename=" + HttpUtility.UrlEncode(fileName + ".xls", System.Text.Encoding.UTF8).ToString());
                    }
                    context.AddHeader("Content-Length", fileExport.Length.ToString());
                    context.ContentType = "application/octet-stream";
                    context.Filter.Close();
                    context.WriteFile(fileExport.FullName);
                    context.Flush();
                    context.End();
                    //删除临时文件
                    fileExport.Delete();
                }

                删除临时文件夹
                //if (Directory.Exists(tempFilePath))
                //{
                //    Directory.Delete(tempFilePath);
                //}
            }
            catch (Exception ex)
            {
                //删除临时文件
                System.IO.FileInfo file = new System.IO.FileInfo(path);
                if (file.Exists)
                {
                    file.Delete();
                }
                删除临时文件夹
                //if (Directory.Exists(tempFilePath))
                //{
                //    Directory.Delete(tempFilePath);
                //}
                throw ex;
            }
        }