C#导出Excel,Csv

相信大家经常接触导出了,这边本人就记录下导出的方法以便后面回顾.

xls支持6万条数据

xlsx支持100万条数据 

CSV可以用记事本打开可以用excel打开

  1 using NPOI;
  2 using NPOI.OpenXml4Net;
  3 using NPOI.OpenXmlFormats;
  4 using NPOI.SS.UserModel;
  5 using NPOI.HSSF.UserModel;
  6 using NPOI.XSSF.UserModel;
  7 using NPOI.HSSF.Util;
  8 
  9         /// <summary>
 10         /// DataTable生成到excel(目前仅支持2维列表导出)
 11         /// </summary>
 12         /// <param name="source">数据集</param>
 13         /// <param name="fullPath"></param>
 14         /// <returns></returns>
 15         public static void DataTableExportExcel(DataTable source, string fullPath)
 16         {
 17             try
 18             {
 19                 int index = fullPath.LastIndexOf('.');
 20                 string extension = fullPath.Substring(index);
 21                 IWorkbook workBook = null;
 22                 if (extension == ".xls")
 23                 {
 24                     workBook = new HSSFWorkbook();
 25                 }
 26                 if (extension == ".xlsx")
 27                 {
 28                     workBook = new XSSFWorkbook();
 29                 }
 30                 ISheet sheet = workBook.CreateSheet("Sheet1");
 31                 IRow headRow = sheet.CreateRow(0);
 32 
 33                 headRow.HeightInPoints = 20;//设置表头高度
 34                 ICellStyle cellStyle = workBook.CreateCellStyle();
 35                 cellStyle.Alignment = HorizontalAlignment.Center;
 36                 cellStyle.VerticalAlignment = VerticalAlignment.Center;
 37                 cellStyle.FillPattern = FillPattern.SolidForeground;
 38                 cellStyle.BorderBottom = BorderStyle.Thin;
 39                 cellStyle.BorderLeft = BorderStyle.Thin;
 40                 cellStyle.BorderRight = BorderStyle.Thin;
 41                 cellStyle.BorderTop = BorderStyle.Thin;
 42                 cellStyle.FillForegroundColor = HSSFColor.Grey25Percent.Index;
 43                 for (int i = 0; i < source.Columns.Count; i++)//生成表头
 44                 {
 45                     headRow.CreateCell(i).SetCellValue(source.Columns[i].ColumnName);
 46                     sheet.SetColumnWidth(i, (source.Columns[i].ColumnName.Length * 3) * 256);
 47                     headRow.GetCell(i).CellStyle = cellStyle;
 48                 }
 49                 IRow row = null;
 50                 ICellStyle cellStyleRow = workBook.CreateCellStyle();
 51                 cellStyleRow.Alignment = HorizontalAlignment.Center;
 52                 cellStyleRow.VerticalAlignment = VerticalAlignment.Center;
 53                 cellStyleRow.FillPattern = FillPattern.SolidForeground;
 54                 cellStyleRow.BorderBottom = BorderStyle.Thin;
 55                 cellStyleRow.BorderLeft = BorderStyle.Thin;
 56                 cellStyleRow.BorderRight = BorderStyle.Thin;
 57                 cellStyleRow.BorderTop = BorderStyle.Thin;
 58                 cellStyleRow.FillForegroundColor = HSSFColor.White.Index;
 59                 for (int i = 0; i < source.Rows.Count; i++)//生成数据体
 60                 {
 61                     row = sheet.CreateRow(i + 1);
 62                     for (int j = 0; j < source.Columns.Count; j++)
 63                     {
 64                         row.CreateCell(j).SetCellValue(source.Rows[i][j] == null ? "" : source.Rows[i][j].ToString());
 65                         row.GetCell(j).CellStyle = cellStyleRow;
 66                     }
 67                 }
 68                 using (FileStream stream = File.OpenWrite(fullPath))
 69                 {
 70                     workBook.Write(stream);
 71                 }
 72             }
 73             catch (Exception ex)
 74             {
 75                 throw new Exception("生成excel异常数据出错，" + ex.Message);
 76             }
 77         }
 78 
 79 
 80 
 81 
 82         /// <summary>
 83         /// 写入CSV
 84         /// </summary>
 85         /// <param name="fileName">文件名</param>
 86         /// <param name="dt">要写入的datatable</param>
 87         public static void WriteCSV(string fileName, DataTable dt)
 88         {
 89             FileStream fs;
 90             StreamWriter sw;
 91 
 92 
 93             string data = null;
 94 
 95             //判断文件是否存在,存在就不再次写入列名
 96             if (!File.Exists(fileName))
 97             {
 98                 fs = new FileStream(fileName, FileMode.Create, FileAccess.Write);
 99                 sw = new StreamWriter(fs, Encoding.UTF8);
100 
101                 //写出列名称
102                 for (int i = 0; i < dt.Columns.Count; i++)
103                 {
104                     data += dt.Columns[i].ColumnName.ToString();
105                     if (i < dt.Columns.Count - 1)
106                     {
107                         data += ",";//中间用，隔开
108                     }
109                 }
110                 sw.WriteLine(data);
111             }
112             else
113             {
114                 fs = new FileStream(fileName, FileMode.Append, FileAccess.Write);
115                 sw = new StreamWriter(fs, Encoding.UTF8);
116             }
117 
118             //写出各行数据
119             for (int i = 0; i < dt.Rows.Count; i++)
120             {
121                 data = null;
122                 for (int j = 0; j < dt.Columns.Count; j++)
123                 {
124                     data += dt.Rows[i][j].ToString();
125                     if (j < dt.Columns.Count - 1)
126                     {
127                         data += "\t" + ",";//中间用，隔开 这里加入\t是因为 导出excle时会把数字长度超过会科学计数法显示
128                     }
129                 }
130                 sw.WriteLine(data);
131             }
132             sw.Close();
133             fs.Close();
134         }

主要是引用的NPOI 导入导出都有 这边只记录了导出

方法比较简单,传入一个查询出的DT数据集,和路径 路径一般都是配置在Web.config里面读取

1 <configuration>
2   <appSettings>
3     <add key="ExportPath" value="DownloadExcels" />
4   </appSettings>
5 </configuration>

读取config配置

string path=this.Server.MapPath("/" + System.Configuration.ConfigurationManager.AppSettings["ExportPath"]);

this.Server.MapPath //应用程序根目录所在的位置

导出方法里面代码如下:

try
                {
                    //string suffix = ".xls";
                    string suffix = ".csv";
                    string FILE_NAME = DateTime.Now.ToString("yyyyMMddHHmm");
                    //避免传入中文时出现乱码,将传过来的值转成UTF-8
                    FILE_NAME = System.Web.HttpUtility.UrlDecode(FILE_NAME, System.Text.Encoding.UTF8);

                    string exportPath = QM.path; //导出文件路径

                    if (!Directory.Exists(exportPath))
                    {
                        Directory.CreateDirectory(exportPath);
                    }
                    string fullFileName = exportPath + "//" + FILE_NAME + suffix;

                    //DataTableExportExcel(dt, fullFileName);

                    WriteCSV(fullFileName, dt);

                    json.Data = "/" + ConfigurationManager.AppSettings["ExportPath"] + "/" + FILE_NAME + suffix;
                }

View Code

 

 

这里JSON.Data最后就会形成一个路径

 1     //导出按钮
 2     function Getexport() {
 3         $.ajax({
 4             type: 'POST',
 5             url: "/Home/GetExport",
 6             data: {11             },
12             dataType: 'json',
13             success: function (res) {
14                 var json = jQuery.parseJSON(res);
15                 if (json.Success) {
16                     var downError = document.createElement("A");
17                     downError.href = json.Data;
18                     document.body.appendChild(downError);
19                     downError.click();
20                 }
21                 else {
22                     alert("信息提示导出数据出错，" + json.Message);
23                 }
24             }
25         });
26     }

 

 第一次写 也不知道怎么排版 大家见谅!

转载于:https://www.cnblogs.com/OneLdd/p/9930471.html