把数据导出excel的应用很广泛,如果使用依赖于excel的com方法,则难度就很大,而且还必须安装excel,否则就不能导出。使用NPOI导出excel很简单,只需要添加一个程序集NPOI.dll的引用就可以,而且不依赖于excel,也就是不需要安装excel。自然比依赖于excel的com方法好很多。
核心代码
[csharp] view plaincopy
public static void ExportByWeb(List
list, string strFileName)
{
strFileName = DateTime.Now.ToString("yyyyMMddHHmmss") + "-" + strFileName + "-.xls";
//using (MemoryStream ms = Export(list,false))//生成文件
//{
// using (FileStream fs = new FileStream(strFileName, FileMode.Create, FileAccess.Write))
// {
// byte[] data = ms.ToArray();
// fs.Write(data, 0, data.Length);
// fs.Flush();
// }
//}
HttpContext curContext = HttpContext.Current;//web系统的下载
// 设置编码和附件格式
curContext.Response.ContentType = "application/vnd.ms-excel";
curContext.Response.ContentEncoding = Encoding.UTF8;
curContext.Response.Charset = "";
curContext.Response.AppendHeader("Content-Disposition",
"attachment;filename=" + HttpUtility.UrlEncode(strFileName, Encoding.UTF8));
curContext.Response.BinaryWrite(Export(list).GetBuffer());
curContext.Response.End();
}
private static MemoryStream Export(List list)
{
HSSFWorkbook workbook = new HSSFWorkbook();
HSSFSheet sheet = (HSSFSheet)workbook.CreateSheet();//创建Sheet页
List ColumnNames = new List();
ColumnNames.Add("产品编号");
ColumnNames.Add("产品线名称");
ColumnNames.Add("可否独立运行");
ColumnNames.Add("基本功能价格(元)");
ColumnNames.Add("高级功能价格(元)");
ColumnNames.Add("合计(元)");
//取得列宽
int[] arrColumnWidth = new int[ColumnNames.Count];
int count = 0;
foreach (string item in ColumnNames)
{
arrColumnWidth[count] = System.Text.Encoding.GetEncoding(936).GetBytes(item).Length * 2;
count++;
}
int rowIndex = 0;
#region 新建表,填充表头,填充列头,样式
if (rowIndex == 65535 || rowIndex == 0)
{
if (rowIndex != 0)
{
sheet = (HSSFSheet)workbook.CreateSheet();
}
#region 表头及样式
AddTitle(workbook, sheet, ColumnNames.Count, "产品线报价汇总");
#endregion
#region 列头及样式
AddColumnTitle(workbook, sheet, ColumnNames, arrColumnWidth);
#endregion
rowIndex = 2;
}
#endregion
WriteDataRows(list, workbook, ColumnNames.Count, sheet, rowIndex);
using (MemoryStream ms = new MemoryStream())
{
workbook.Write(ms);
ms.Flush();
ms.Position = 0;
sheet.Dispose();
//workbook.Dispose();//一般只用写这一个就OK了,他会遍历并释放所有资源,但当前版本有问题所以只释放sheet
return ms;
}
}
///
/// 添加第一行的标题
///
///
///
///
///
private static void AddTitle(HSSFWorkbook workbook, HSSFSheet sheet, int maxColumnCount, string strHeaderText)
{
HSSFRow headerRow = (HSSFRow)sheet.CreateRow(0);//添加行
headerRow.HeightInPoints = 25;//设置高度字体
headerRow.CreateCell(0).SetCellValue(strHeaderText);//设置单元格内容
HSSFCellStyle headStyle = (HSSFCellStyle)workbook.CreateCellStyle();//设置单元格样式
headStyle.Alignment = HorizontalAlignment.CENTER;//文字居中
HSSFFont font = (HSSFFont)workbook.CreateFont();//设置字体
font.FontHeightInPoints = 20;
font.Boldweight = 700;
headStyle.SetFont(font);
headerRow.GetCell(0).CellStyle = headStyle;
sheet.AddMergedRegion(new NPOI.SS.Util.Region(0, 0, 0, maxColumnCount - 1));//合并单元格
}
private static void AddColumnTitle(HSSFWorkbook workbook, HSSFSheet sheet, List ColumnNames, int[] arrColumnWidth)
{
HSSFRow headerRow = (HSSFRow)sheet.CreateRow(1);
HSSFCellStyle headStyle = (HSSFCellStyle)workbook.CreateCellStyle();
headStyle.Alignment = HorizontalAlignment.CENTER;//文字居中
headStyle.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.GOLD.index;//设置前景色
headStyle.FillPattern = FillPatternType.ALT_BARS;//
HSSFFont font = (HSSFFont)workbook.CreateFont();
font.FontHeightInPoints = 10;
font.Boldweight = 700;
headStyle.SetFont(font);
int count = 0; ;
foreach (string item in ColumnNames)
{
headerRow.CreateCell(count).SetCellValue(item);
headerRow.GetCell(count).CellStyle = headStyle;
//设置列宽
sheet.SetColumnWidth(count, (arrColumnWidth[count] + 1) * 256);//设置列宽度
count++;
}
}
private static List WriteDataRows(List parentList, HSSFWorkbook workbook, int maxColumnCount, HSSFSheet sheet, int rowIndex)
{
List listnext = new List();
CellStyle style2 = workbook.CreateCellStyle();//设置单元格的样式边框
style2.BorderBottom = CellBorderType.THIN;//
style2.BottomBorderColor = NPOI.HSSF.Util.HSSFColor.BLACK.index;
style2.BorderLeft = CellBorderType.THIN;
style2.LeftBorderColor = NPOI.HSSF.Util.HSSFColor.BLACK.index;
style2.BorderRight = CellBorderType.THIN;
style2.RightBorderColor = NPOI.HSSF.Util.HSSFColor.BLACK.index;
style2.BorderTop = CellBorderType.THIN;
style2.TopBorderColor = NPOI.HSSF.Util.HSSFColor.BLACK.index;
style2.WrapText = true;//根据文本自动调整高度
// HSSFDataFormat formatDate = (HSSFDataFormat)workbook.CreateDataFormat();
// style2.DataFormat = formatDate.GetFormat("yyyy-mm-dd");
// HSSFDataFormat formatMoney = (HSSFDataFormat)workbook.CreateDataFormat();
// style2.DataFormat = formatMoney.GetFormat("#,##0_");
foreach (JqueryEasyuiTreeGridNode parent in parentList)
{
JqueryEasyuiTreeGridNode row = parent;
int count = 0;
#region 填充内容
HSSFRow dataRow = (HSSFRow)sheet.CreateRow(rowIndex);
count = 0;
HSSFCell newCell = (HSSFCell)dataRow.CreateCell(count);
newCell.CellStyle = style2;
string drValue = row.PL_Code;
newCell.SetCellValue(drValue);
count++;
newCell = (HSSFCell)dataRow.CreateCell(count);
newCell.CellStyle = style2;
drValue = row.PL_Name;
newCell.SetCellValue(drValue);
count++;
newCell = (HSSFCell)dataRow.CreateCell(count);
newCell.CellStyle = style2;
drValue = row.PL_RunFlag;
newCell.SetCellValue(drValue);
count++;
newCell = (HSSFCell)dataRow.CreateCell(count);
newCell.CellStyle = style2;
newCell.SetCellType(CellType.NUMERIC);
if (row.PL_PRICEDesc.HasValue)
{
newCell.SetCellValue(row.PL_PRICEDesc.Value);
}
else
{
newCell.SetCellValue("");
}
count++;
newCell = (HSSFCell)dataRow.CreateCell(count);
newCell.CellStyle = style2;
newCell.SetCellType(CellType.NUMERIC);
if (row.P_PriceHigh.HasValue)
{
newCell.SetCellValue(row.P_PriceHigh.Value);
}
else
{
newCell.SetCellValue("");
}
count++;
newCell = (HSSFCell)dataRow.CreateCell(count);
newCell.CellStyle = style2;
newCell.SetCellType(CellType.NUMERIC);
newCell.SetCellValue(row.Total_Price);
count++;
//}
#endregion
rowIndex++;
}
return listnext;
}
更有通用性的代码
-
using NPOI.HPSF;
-
using NPOI.HSSF.UserModel;
-
using NPOI.SS.UserModel;
-
using NPOI.SS.Util;
-
public static class ExportToFile{
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
public static void Export(List list, string strHeaderText, string strFileName, Dictionary<string, string> FieldNames)
-
{
-
using (MemoryStream ms = Export(list, strHeaderText,FieldNames))
-
{
-
using (FileStream fs = new FileStream(strFileName, FileMode.Create, FileAccess.Write))
-
{
-
byte[] data = ms.ToArray();
-
fs.Write(data, 0, data.Length);
-
fs.Flush();
-
}
-
}
-
}
-
-
-
-
-
-
-
-
public static void ExportByWeb(List list, string strHeaderText, string strFileName, Dictionary<string, string> FieldNames)
-
{
-
-
HttpContext curContext = HttpContext.Current;
-
-
curContext.Response.ContentType = "application/vnd.ms-excel";
-
curContext.Response.ContentEncoding = Encoding.UTF8;
-
curContext.Response.Charset = "";
-
curContext.Response.AppendHeader("Content-Disposition",
-
"attachment;filename=" + HttpUtility.UrlEncode(strFileName, Encoding.UTF8));
-
curContext.Response.BinaryWrite(Export(list, strHeaderText, FieldNames).GetBuffer());
-
curContext.Response.End();
-
}
-
private static MemoryStream Export(List list, string strHeaderText, Dictionary<string, string> FieldNames)
-
{
-
HSSFWorkbook workbook = new HSSFWorkbook();
-
HSSFSheet sheet = (HSSFSheet)workbook.CreateSheet();
-
HSSFCellStyle dateStyle = (HSSFCellStyle)workbook.CreateCellStyle();
-
HSSFDataFormat format = (HSSFDataFormat)workbook.CreateDataFormat();
-
dateStyle.DataFormat = format.GetFormat("yyyy-mm-dd");
-
-
-
int[] arrColWidth = new int[FieldNames.Count];
-
int count = 0;
-
foreach (KeyValuePair<string, string> item in FieldNames)
-
{
-
arrColWidth[count] = Encoding.GetEncoding(936).GetBytes(item.Value).Length;
-
count++;
-
}
-
-
int rowIndex = 0;
-
-
foreach (TModel row in list)
-
{
-
#region 新建表,填充表头,填充列头,样式
-
if (rowIndex == 65535 || rowIndex == 0)
-
{
-
if (rowIndex != 0)
-
{
-
sheet = (HSSFSheet)workbook.CreateSheet();
-
}
-
-
#region 表头及样式
-
AddFirstRow(workbook, sheet, FieldNames, strHeaderText);
-
#endregion
-
#region 列头及样式
-
AddSecondRow(workbook, sheet, FieldNames, arrColWidth);
-
#endregion
-
rowIndex = 2;
-
}
-
#endregion
-
-
-
#region 填充内容
-
HSSFRow dataRow = (HSSFRow)sheet.CreateRow(rowIndex);
-
count = 0;
-
foreach (KeyValuePair<string, string> column in FieldNames)
-
{
-
HSSFCell newCell = (HSSFCell)dataRow.CreateCell(count);
-
PropertyInfo pinfo = typeof(TModel).GetProperty(column.Key);
-
string drValue = "";
-
string dateytype= "";
-
if (pinfo != null)
-
{
-
drValue = pinfo.GetValue(row, null) == null ? "" : pinfo.GetValue(row, null).ToString();
-
dateytype = pinfo.PropertyType.ToString();
-
}
-
-
switch (dateytype){
-
-
case "System.String":
-
newCell.SetCellValue(drValue);
-
break;
-
case "System.DateTime":
-
DateTime dateV;
-
DateTime.TryParse(drValue, out dateV);
-
newCell.SetCellValue(dateV);
-
newCell.CellStyle = dateStyle;
-
break;
-
case "System.Boolean":
-
bool boolV = false;
-
bool.TryParse(drValue, out boolV);
-
newCell.SetCellValue(boolV);
-
break;
-
case "System.Int16":
-
case "System.Int32":
-
case "System.Int64":
-
case "System.Byte":
-
int intV = 0;
-
int.TryParse(drValue, out intV);
-
newCell.SetCellValue(intV);
-
break;
-
case "System.Decimal":
-
case "System.Double":
-
case "System.Single":
-
double doubV = 0;
-
double.TryParse(drValue, out doubV);
-
newCell.SetCellValue(doubV);
-
break;
-
case "System.DBNull":
-
newCell.SetCellValue("");
-
break;
-
default:
-
newCell.SetCellValue(drValue);
-
break;
-
}
-
count++;
-
}
-
#endregion
-
-
rowIndex++;
-
}
-
-
-
using (MemoryStream ms = new MemoryStream())
-
{
-
workbook.Write(ms);
-
ms.Flush();
-
ms.Position = 0;
-
sheet.Dispose();
-
-
return ms;
-
}
-
}
-
-
-
private static void AddFirstRow(HSSFWorkbook workbook, HSSFSheet sheet, Dictionary<string, string> FieldNames, string strHeaderText)
-
{
-
HSSFRow headerRow = (HSSFRow)sheet.CreateRow(0);
-
headerRow.HeightInPoints = 25;
-
headerRow.CreateCell(0).SetCellValue(strHeaderText);
-
-
HSSFCellStyle headStyle = (HSSFCellStyle)workbook.CreateCellStyle();
-
headStyle.Alignment = HorizontalAlignment.CENTER;
-
HSSFFont font = (HSSFFont)workbook.CreateFont();
-
font.FontHeightInPoints = 20;
-
font.Boldweight = 700;
-
headStyle.SetFont(font);
-
headerRow.GetCell(0).CellStyle = headStyle;
-
sheet.AddMergedRegion(new Region(0, 0, 0, FieldNames.Count - 1));
-
}
-
private static void AddSecondRow(HSSFWorkbook workbook, HSSFSheet sheet, Dictionary<string, string> FieldNames, int[] arrColWidth)
-
{
-
-
HSSFRow headerRow = (HSSFRow)sheet.CreateRow(1);
-
HSSFCellStyle headStyle = (HSSFCellStyle)workbook.CreateCellStyle();
-
headStyle.Alignment = HorizontalAlignment.CENTER;
-
HSSFFont font = (HSSFFont)workbook.CreateFont();
-
font.FontHeightInPoints = 10;
-
font.Boldweight = 700;
-
headStyle.SetFont(font);
-
int count = 0; ;
-
foreach (KeyValuePair<string, string> item in FieldNames)
-
{
-
-
headerRow.CreateCell(count).SetCellValue(item.Value);
-
headerRow.GetCell(count).CellStyle = headStyle;
-
-
sheet.SetColumnWidth(count, (arrColWidth[count] + 1) * 256);
-
count++;
-
}
-
}
-
-
-
}
阅读(638) | 评论(0) | 转发(0) |