以前做导出都是用项目里面现有的资源,也没有深究过,结果出来了就好,现在换了个新环境,又是新项目,开始随便网上查了一下找了个比较简单的方法,用StreamWriter 写出来的,但是后来需要导出有样式的excel,只能放弃这种方式,换了模板导出,全部搞定后在测试服务器上也测试好了,可是发布到正式的服务器上却不能用,后来发现是excel有问题,打不开也卸不掉,苦恼难道要重装的时候,想还是再换一种方法吧,想到了之前公司用的是NPOI,于是下了dll文件,重新写。
为了通用性,数据列头在获取到后先处理了,改成了要显示的列头,导出方法专门写了个类ToExcel,类代码如下:
public static void RenderToBrowser(MemoryStream ms, HttpContext context, string fileName)
{
if (HttpContext.Current.Request.Browser.Type.IndexOf("IE") > -1)
{
HttpContext.Current.Response.ContentType = "application/octet-stream";
HttpContext.Current.Response.AddHeader("Content-Disposition", "attachment; filename=" +
HttpUtility.UrlEncode(fileName, Encoding.UTF8));
}
else
{
// 设置编码和附件格式
HttpContext.Current.Response.ContentType = "application/vnd.ms-excel";
HttpContext.Current.Response.AddHeader("Content-Disposition", string.Format("attachment;filename*=utf-8'zh_cn'{0}", HttpUtility.UrlEncode(fileName)));
}
HttpContext.Current.Response.Charset = "gb2312";
HttpContext.Current.Response.ContentEncoding = Encoding.GetEncoding("gb2312");
HttpContext.Current.Response.Clear();
context.Response.BinaryWrite(ms.ToArray());
HttpContext.Current.Response.End();
}
public static MemoryStream RenderToExcel(DataTable dt, string remark, string fileName)
{
var ms = new MemoryStream();
using (dt)
{
var workbook = new HSSFWorkbook();
var sheet = workbook.CreateSheet();
var headerRow = sheet.CreateRow(0);
var cellCount = dt.Columns.Count;
//获取列宽
var arrColWidth = new int[cellCount];
for (var i = 0; i < cellCount; i++)
{
arrColWidth[i] = Encoding.GetEncoding(936).GetBytes(dt.Columns[i].ColumnName).Length;
}
foreach (DataRow row in dt.Rows)
{
for (var i = 0; i < cellCount; i++)
{
var intTemp = Encoding.GetEncoding(936).GetBytes(row[i].ToString()).Length;
if (intTemp > arrColWidth[i])
{
arrColWidth[i] = intTemp;
}
}
}
Cell cell;
//写标题文本
cell = headerRow.CreateCell(0);
cell.SetCellValue(fileName);
//设置标题行样式
var style = GetStyle(workbook);
style.Alignment = HorizontalAlignment.CENTER;
var font = workbook.CreateFont();
font.FontHeight = 20 * 20;
font.Boldweight = 700;
style.SetFont(font);
cell.CellStyle = style;
headerRow.HeightInPoints = 25;
//合并标题行
sheet.AddMergedRegion(new CellRangeAddress(0,0, 0, cellCount-1));
var dataRow = sheet.CreateRow(1);
//设置备注行样式
style = GetStyle(workbook);
cell = dataRow.CreateCell(0);
cell.SetCellValue(remark);
cell.CellStyle = style;
sheet.AddMergedRegion(new CellRangeAddress(1, 1, 0, cellCount-1));
for (var i = 1; i < cellCount; i++)
{
cell = headerRow.CreateCell(i);
cell.CellStyle = style;
cell = dataRow.CreateCell(i);
cell.CellStyle = style;
}
//列头样式
style = GetStyle(workbook);
style.Alignment = HorizontalAlignment.CENTER;
font = workbook.CreateFont();
font.FontHeightInPoints = 11;
font.Boldweight = 700;
style.SetFont(font);
dataRow = sheet.CreateRow(2);
dataRow.HeightInPoints = 15;
// handling header.
for (var i = 0; i < cellCount; i++)
{
cell = dataRow.CreateCell(i);
cell.SetCellValue(dt.Columns[i].ColumnName);
cell.CellStyle = style;
//设置列宽
sheet.SetColumnWidth(i, (arrColWidth[i] + 1) * 256);
}
//列值样式
style = GetStyle(workbook);
// handling value.
var rowIndex = 3;
foreach (DataRow row in dt.Rows)
{
dataRow = sheet.CreateRow(rowIndex);
for (var i = 0; i < cellCount; i++)
{
cell = dataRow.CreateCell(i);
double val;
if (double.TryParse(row[i].ToString(),out val))
{
cell.SetCellValue(val);
}
else
{
cell.SetCellValue(row[i].ToString());
}
cell.CellStyle = style;
//设置列宽
sheet.SetColumnWidth(i, (arrColWidth[i] + 1) * 256);
}
rowIndex++;
}
workbook.Write(ms);
ms.Flush();
ms.Position = 0;
}
return ms;
}
/// <summary>
/// 设置样式
/// </summary>
/// <param name="workbook"></param>
/// <returns></returns>
public static CellStyle GetStyle(HSSFWorkbook workbook)
{
var style = workbook.CreateCellStyle();
style.BorderTop = CellBorderType.THIN;
style.TopBorderColor = HSSFColor.BLACK.index;
style.BorderRight = CellBorderType.THIN;
style.RightBorderColor = HSSFColor.BLACK.index;
style.BorderBottom = CellBorderType.THIN;
style.BottomBorderColor = HSSFColor.BLACK.index;
style.BorderLeft = CellBorderType.THIN;
style.LeftBorderColor = HSSFColor.BLACK.index;
var font = workbook.CreateFont();
font.FontHeightInPoints = 10;
style.SetFont(font);
return style;
}
这样可以满足大部分的导出需要,如果要另外的格式,那么再加方法,其实主要就是格式的比较麻烦。
我这边获取到的数据是list<T>的,我先转换成了datatable,并且改列头及其他有需要变动的列内容。
/// <summary>
/// list转成datatable
/// </summary>
/// <param name="list"></param>
/// <param name="sum"></param>
/// <returns></returns>
public DataTable ListOut(IList<T> list, decimal sum)
{
var tempDt = new DataTable();
//此处遍历IList的结构并建立同样的DataTable
var p = list[0].GetType().GetProperties();
foreach (var pi in p)
{
tempDt.Columns.Add(pi.Name, typeof(string));
}
for (var i = 0; i < list.Count; i++)
{
var tempList = new ArrayList();
//将IList中的一条记录写入ArrayList
foreach (var pi in p)
{
object oo = pi.GetValue(list[i], null);
tempList.Add(oo);
}
var itm = new object[p.Length];
//遍历ArrayList向object[]里放数据
for (int j = 0; j < tempList.Count; j++)
{
itm.SetValue(tempList[j], j);
}
//将object[]的内容放入DataTable
tempDt.LoadDataRow(itm, true);
}
tempDt.Columns.Remove("ColumnsName1");
tempDt.Columns["ColumnsName2"].SetOrdinal(0);
tempDt.Columns["ColumnsName3"].SetOrdinal(1);
tempDt.Columns["ColumnsName4"].SetOrdinal(2);
tempDt.Columns["ColumnsName5"].SetOrdinal(3);
tempDt.Columns["ColumnsName6"].SetOrdinal(4);
tempDt.Columns["ColumnsName7"].SetOrdinal(5);
tempDt.Columns["ColumnsName8"].SetOrdinal(6);
tempDt.Columns[0].ColumnName = "列名2";
tempDt.Columns[1].ColumnName = "列名3";
tempDt.Columns[2].ColumnName = "列名4";
tempDt.Columns[3].ColumnName = "列名5";
tempDt.Columns[4].ColumnName = "列名6";
tempDt.Columns[5].ColumnName = "列名7";
tempDt.Columns[6].ColumnName = "列名8";
//加汇总行
int userid, orderid;
tempDt.Rows.Add("总计", "", "" , "", "", "", "");
for (var i = 0; i < tempDt.Rows.Count - 1; i++)
{
tempDt.Rows[i]["列名2"] = tempDt.Rows[i]["列名2"].ToString() == "0" ? "值1" : "值2";
}
//返回DataTable
return tempDt;
}
然后就是导出方法了
decimal sum;
IList<T> exportList = Manager.Query(参数1,参数2, out sum);
if (exportList.Count < 1)
{
return; //没有数据,不需要导出。
}
var dt = ListOut(exportList, sum);
var remark = "XXXXXX";
ToExcel.RenderToBrowser(ToExcel.RenderToExcel(dt, remark, "表头内容"), Context, "文件名.xls");
Response.Write("true");
Response.End();
这样终于不怕需要样式,也不用去管服务器上装的excel能否用了,只是当样式比较麻烦,查询到的一列数据,导出要就是一个表格的形式,导出的数据多时很慢,还有待优化改进。
PS:随记,有什么意见或建议欢迎提出!