最近项目中用到导出Excel,项目已有的方法1和2,导出的excel，看似是exce格式，其实只是改了后缀名。
用wps打开看着格式没问题
实际另存为的时候格式显示是txt
于是找到了改为NPOI，导出的Excel格式正常。
1.文件流的方式
代码语言：javascript复制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
代码语言：javascript复制 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。
代码语言：javascript复制 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;
}
