比如：
导入一个130k的表格，在c#中将其转为datatable后，更改了一下表头和某些少量数据。但并未增加数据！然后再将datatable转为excel表格并导出时。新表格的大小为890k.
注意：1.检查过没有隐藏的对象。
2.表格的单元格大小前后一致。
恳请大佬帮忙解决一下：
/// 导出Excel
/// </summary>
/// <param name="SourceTable">导出的数据源</param>
/// <param name="page">WEB页面对象，默认传Page，只可WEB后台调用</param>
/// <param name="type">WEB页面值，如果是素金则另作处理</param>
/// <returns></returns>
public void ExportExcel(DataTable ds, DataTable dtt, Page page, string type)
{
DataTable dt = ds;
string basePath = page.Server.MapPath(".");
//如果目录不存在，则创建目录
if (!Directory.Exists(basePath))
{
Directory.CreateDirectory(basePath);
}
string filePath = string.Empty;
string fileName = string.Empty;
//filePath = page.Server.MapPath(".") + "\\" + fileName + ".xls";
// filePath = page.Server.MapPath(".") + "\\" + System.DateTime.Now.ToString("yyyyMMddHHmmss") + ".xls";
MemoryStream ms;
if (type == "素金")
{
fileName = System.DateTime.Now.ToString("yyyyMMddHHmmss");
ms = RenderDataTableToExcelSujin(dt, page) as MemoryStream;
}
else
{
if (ds.Rows.Count > 0)
{
if (ds.Rows[0]["采购订单号"].ToString().Trim().Length != 0)
{
fileName = ds.Rows[0]["采购订单号"].ToString().Trim();
}
else
{
fileName = System.DateTime.Now.ToString("yyyyMMddHHmmss");
}
}
else
{
fileName = System.DateTime.Now.ToString("yyyyMMddHHmmss");
}
ms = RenderDataTableToExcel(dt, dtt, page) as MemoryStream;
}
FileStream fs;
//FileStream fs = new FileStream(filePath, FileMode.Create, FileAccess.Write);
try
{
filePath = page.Server.MapPath(".") + "\\" + fileName + ".xls";
fs = new FileStream(filePath, FileMode.Create, FileAccess.Write);
}
catch (Exception)
{
fileName = System.DateTime.Now.ToString("yyyyMMddHHmmss");
filePath = page.Server.MapPath(".") + "\\" + fileName + ".xls";
fs = new FileStream(page.Server.MapPath(".") + "\\" + fileName + ".xls", FileMode.Create, FileAccess.Write);
}
byte[] data = ms.ToArray();
fs.Write(data, 0, data.Length);
fs.Flush();
fs.Close();
data = null;
ms = null;
fs = null;
page.Response.ContentEncoding = System.Text.Encoding.GetEncoding("GB2312");
page.Response.HeaderEncoding = System.Text.Encoding.GetEncoding("GB2312");
page.Response.AppendHeader("content-disposition", "attachment;filename=" + System.Web.HttpUtility.UrlEncode(fileName, System.Text.Encoding.UTF8) + ".xls");
page.Response.ContentType = "Application/excel";
if (page.Response.IsClientConnected)
{
page.Response.WriteFile(filePath);
page.Response.Flush();
}
//Response.WriteFile(filePath);
//Response.Flush();
try
{
//删除导出的服务器文件
File.Delete(filePath);
}
catch (Exception)
{
}
page.Response.Clear();
}
private Stream RenderDataTableToExcelSujin(DataTable SourceTable, Page page)
{
MemoryStream ms = new MemoryStream();
HSSFWorkbook workbook = new NPOI.HSSF.UserModel.HSSFWorkbook();
ISheet sheet = workbook.CreateSheet();
//加标题
IRow headerRow2 = sheet.CreateRow(0);
headerRow2.Height = 500;
//加了两行空行
IRow dataRow1 = sheet.CreateRow(1);
//列高2000
dataRow1.Height = 500;
dataRow1.Sheet.SetColumnWidth(0, 50 * 60);
dataRow1.Sheet.SetColumnWidth(1, 50 * 60);
dataRow1.Sheet.SetColumnWidth(2, 50 * 60);
dataRow1.Sheet.SetColumnWidth(3, 50 * 95);
IRow dataRow2 = sheet.CreateRow(2);
//列高2000
dataRow2.Height = 500;
dataRow2.Sheet.SetColumnWidth(0, 50 * 60);
dataRow2.Sheet.SetColumnWidth(1, 50 * 60);
dataRow2.Sheet.SetColumnWidth(2, 50 * 60);
dataRow2.Sheet.SetColumnWidth(3, 50 * 95);
//添加Excel的表头
for (int i = 0; i < SourceTable.Columns.Count; i++)
{
headerRow2.CreateCell(i).SetCellValue(SourceTable.Columns[i].ColumnName);
}
int rowIndex = 3;
Regex regExp = new Regex(@"^(-?\d+)(\.\d+)?$");
List<string> lstColumnname = new List<string>();
foreach (DataColumn item in SourceTable.Columns)
{
for (int i = 0; i < SourceTable.Rows.Count; i++)
{
if (SourceTable.Rows[i][item.ColumnName].ToString().Trim().Length > 0)
{
if (!regExp.IsMatch(SourceTable.Rows[i][item.ColumnName].ToString().Trim()))
{
lstColumnname.Add(item.ColumnName);
break;
}
}
}
}
foreach (DataRow row in SourceTable.Rows)
{
IRow dataRow = sheet.CreateRow(rowIndex);
//列高2000
dataRow.Height = 500;
dataRow.Sheet.SetColumnWidth(0, 50 * 60);
dataRow.Sheet.SetColumnWidth(1, 50 * 60);
dataRow.Sheet.SetColumnWidth(2, 50 * 60);
dataRow.Sheet.SetColumnWidth(3, 50 * 95);
////素金特殊要求：除表头从第三行开始绑定
//if (rowIndex <= 2)
//{
// rowIndex++;
// continue;
//}
for (int i = 0; i < SourceTable.Columns.Count; i++)
{
try
{
if (row[i].ToString().Trim().Length != 0)
{
if (lstColumnname.Contains(SourceTable.Columns[i].ColumnName))
dataRow.CreateCell(i).SetCellValue(@row[i].ToString());
else
{
string aaa = SourceTable.Columns[i].ColumnName;
if (row[i].ToString().Contains("."))
{
//有小数 转double
dataRow.CreateCell(i).SetCellValue(@Convert.ToDouble((row[i].ToString() == "" ? "0" : row[i].ToString())));
}
else
{
//无小数 转int
dataRow.CreateCell(i).SetCellValue(@Convert.ToInt32(row[i].ToString()));
}
}
}
else dataRow.CreateCell(i).SetCellValue(@row[i].ToString());
}
catch (Exception)
{
dataRow.CreateCell(i).SetCellValue(@row[i].ToString());
}
}
rowIndex++;
}
workbook.Write(ms);
ms.Flush();
ms.Position = 0;
sheet = null;
headerRow2 = null;
workbook = null;
return ms;
}
