常用Excel导出方法

• 2020 年 4 月 8 日
• 筆記

　　最近项目中用到导出Excel,项目已有的方法1和2,导出的excel，看似是exce格式，其实只是改了后缀名。

　　用wps打开看着格式没问题

　　实际另存为的时候格式显示是txt

于是找到了改为NPOI，导出的Excel格式正常。

1.文件流的方式

public static string DataToExcel(Page page, string s_FileName, DataTable m_DataTable)   {              string FileName = page.Server.MapPath("/"+ s_FileName+".xls"); //文件存放路径                if (System.IO.File.Exists(FileName))   //存在则删除              {                  System.IO.File.Delete(FileName);              }              System.IO.FileStream objFileStream;              System.IO.StreamWriter objStreamWriter;              string strLine = "";              objFileStream = new System.IO.FileStream(FileName, System.IO.FileMode.OpenOrCreate, System.IO.FileAccess.Write);              objStreamWriter = new System.IO.StreamWriter(objFileStream, Encoding.Unicode);             // objStreamWriter.              for (int i = 0; i < m_DataTable.Columns.Count; i++)              {                  strLine = strLine + m_DataTable.Columns[i].Caption.ToString() + Convert.ToChar(9);      //写列标题              }              objStreamWriter.WriteLine(strLine);              strLine = "";              for (int i = 0; i < m_DataTable.Rows.Count; i++)              {                  for (int j = 0; j < m_DataTable.Columns.Count; j++)                  {                      if (m_DataTable.Rows[i].ItemArray[j] == null)                          strLine = strLine + " " + Convert.ToChar(9);                                    //写内容                      else                      {                          string rowstr = "";                          rowstr = m_DataTable.Rows[i].ItemArray[j].ToString();                          if (rowstr.IndexOf("rn") > 0)                              rowstr = rowstr.Replace("rn", " ");                          if (rowstr.IndexOf("t") > 0)                              rowstr = rowstr.Replace("t", " ");                          strLine = strLine + rowstr + Convert.ToChar(9);                      }                  }                  objStreamWriter.WriteLine(strLine);                    strLine = "";              }              objStreamWriter.Close();              objFileStream.Close();              return FileName;        //返回生成文件的绝对路径   }

2.Response方式导出Excel

  public static void ExportExcelByDataTable(Page page, string strReportName, DataTable dtReport)    {              try              {                  string strFileName = string.Format("attachment;filename={1}.xls", page.Server.UrlEncode(strReportName),                      page.Server.UrlEncode(DateTime.Now.ToString("yyyyMMddHHmmss")));                  page.Response.Clear();                  page.Response.Buffer = true;                  page.Response.Charset = "GB2312";                  page.Response.AppendHeader("Content-Disposition", strFileName);                  page.Response.ContentEncoding = Encoding.GetEncoding("GB2312");                  page.Response.ContentType = "application/ms-excel";                  StringBuilder stringBuilder = new StringBuilder();                  string strt = "";                  for (int m_ColumnsCount = 0; m_ColumnsCount < dtReport.Columns.Count; m_ColumnsCount++)                  {                      stringBuilder.Append(strt);                      stringBuilder.Append(dtReport.Columns[m_ColumnsCount].ColumnName);                      strt = "t";                  }                  stringBuilder.Append('n');                  for (int rowCount = 0; rowCount < dtReport.Rows.Count; rowCount++)                  {                      strt = "";                      for (int rowColumnsCount = 0; rowColumnsCount < dtReport.Columns.Count; rowColumnsCount++)                      {                          stringBuilder.Append(strt);                          stringBuilder.Append(dtReport.Rows[rowCount][dtReport.Columns[rowColumnsCount].ColumnName].ToString().Replace("n", "").Replace("r", ""));                          strt = "t";                      }                      stringBuilder.Append('n');                  }                  page.Response.Write(stringBuilder.ToString());                  page.Response.End();              }              catch (Exception error)              {                  throw new Exception(error.Message);              }   }

3.使用NPOI

　需要引入第三方DLL，NPOI.dll和NPOI.OOXML.dll。

  public static void ExportExcel(Page page, string strReportName, DataTable dtReport)     {              //HttpContext curContext = HttpContext.Current;              //设置编码及附件格式              page.Response.ContentType = "application/vnd.ms-excel";              page.Response.ContentEncoding = Encoding.UTF8;              page.Response.Charset = "";              string fullName = HttpUtility.UrlEncode(strReportName+".xls", Encoding.UTF8);              page.Response.AppendHeader("Content-Disposition",                  "attachment;filename=" + HttpUtility.UrlEncode(fullName, Encoding.UTF8));  //attachment后面是分号              byte[] data = TableToExcel(dtReport, fullName).GetBuffer();              page.Response.BinaryWrite(TableToExcel(dtReport, fullName).GetBuffer());              page.Response.End();          }            public static MemoryStream TableToExcel(DataTable dt, string file)          {              //创建workbook              IWorkbook workbook;              string fileExt = Path.GetExtension(file).ToLower();              if (fileExt == ".xlsx")                  workbook = new XSSFWorkbook();              else if (fileExt == ".xls")                  workbook = new HSSFWorkbook();              else                  workbook = null;              //创建sheet              ISheet sheet = workbook.CreateSheet("Sheet1");                //表头              IRow headrow = sheet.CreateRow(0);              for (int i = 0; i < dt.Columns.Count; i++)              {                  ICell headcell = headrow.CreateCell(i);                  headcell.SetCellValue(dt.Columns[i].ColumnName);              }              //表内数据              for (int i = 0; i < dt.Rows.Count; i++)              {                  IRow row = sheet.CreateRow(i + 1);                  for (int j = 0; j < dt.Columns.Count; j++)                  {                      ICell cell = row.CreateCell(j);                      cell.SetCellValue(dt.Rows[i][j].ToString());                  }              }                //转化为字节数组              MemoryStream ms = new MemoryStream();              workbook.Write(ms);              ms.Flush();              ms.Position = 0;              return ms;    }