用NPOI把数据库内容导入到Excel中实例（NPOI版本2.0) 博客分类： asp.net开发
程序员文章站
2024-03-21 21:38:58
...
注意 [url=http://dl.iteye.com/topics/download/add2e1a2-f4a9-339c-889f-acc1f6162031]NPOI版本2.0 [/url]
namespace HLS.PCS.WebSite.Pages
{
public class NPOIHelper
{
#region 变量初始化
private HSSFWorkbook hssfworkbook;
private CellStyle titlestyle;
private CellStyle normalstylename;
private CellStyle headerstyle;
private CellStyle normalstylecenter;
private CellStyle normalstyleleft;
/// <summary>
/// /// 初始化
/// /// </summary>
protected void InitializeWorkbook()
{
hssfworkbook = new HSSFWorkbook();
//create a entry of DocumentSummaryInformation
DocumentSummaryInformation dsi = PropertySetFactory.CreateDocumentSummaryInformation();
dsi.Company = "NPOI Team";
hssfworkbook.DocumentSummaryInformation = dsi;
//create a entry of SummaryInformation
SummaryInformation si = PropertySetFactory.CreateSummaryInformation();
si.Subject = "NPOI SDK Example";
hssfworkbook.SummaryInformation = si;
}
/// <summary>
/// 设置字体
/// </summary>
protected void SetFont()
{
#region 字体格式
//标题
Font titlefont = hssfworkbook.CreateFont();
titlefont.FontHeight = 20 * 20;
titlefont.Boldweight = (short)NPOI.SS.UserModel.FontBoldWeight.BOLD;
titlestyle = hssfworkbook.CreateCellStyle();
titlestyle.Alignment = HorizontalAlignment.CENTER;
titlestyle.VerticalAlignment = VerticalAlignment.CENTER;
titlestyle.SetFont(titlefont);
//表头
Font headerfont = hssfworkbook.CreateFont();
headerfont.FontHeight = 14 * 14;
headerfont.Boldweight = (short)NPOI.SS.UserModel.FontBoldWeight.BOLD;
headerstyle = hssfworkbook.CreateCellStyle();
headerstyle.Alignment = HorizontalAlignment.CENTER;
headerstyle.VerticalAlignment = VerticalAlignment.CENTER;
headerstyle.BorderBottom = CellBorderType.THIN;
headerstyle.BorderLeft = CellBorderType.THIN;
headerstyle.BorderRight = CellBorderType.THIN;
headerstyle.BorderTop = CellBorderType.THIN;
headerstyle.SetFont(headerfont);
//一般
Font normalfont = hssfworkbook.CreateFont();
normalfont.FontHeight = 14 * 14;
normalstylename = hssfworkbook.CreateCellStyle();
normalstylename.Alignment = HorizontalAlignment.LEFT;
normalstylename.VerticalAlignment = VerticalAlignment.CENTER;
normalstylename.SetFont(normalfont);
normalstylecenter = hssfworkbook.CreateCellStyle();
normalstylecenter.Alignment = HorizontalAlignment.CENTER;
normalstylecenter.VerticalAlignment = VerticalAlignment.CENTER;
normalstylecenter.BorderBottom = CellBorderType.THIN;
normalstylecenter.BorderLeft = CellBorderType.THIN;
normalstylecenter.BorderRight = CellBorderType.THIN;
normalstylecenter.BorderTop = CellBorderType.THIN;
normalstylecenter.SetFont(normalfont);
normalstyleleft = hssfworkbook.CreateCellStyle();
normalstyleleft.Alignment = HorizontalAlignment.LEFT;
normalstyleleft.VerticalAlignment = VerticalAlignment.CENTER;
normalstyleleft.BorderBottom = CellBorderType.THIN;
normalstyleleft.BorderLeft = CellBorderType.THIN;
normalstyleleft.BorderRight = CellBorderType.THIN;
normalstyleleft.BorderTop = CellBorderType.THIN;
normalstyleleft.SetFont(normalfont);
#endregion
}
/// <summary>
/// 写入到excel文件
/// </summary>
/// <param name="hssfworkbook"></param>
/// <returns></returns>
protected MemoryStream WriteToStream(HSSFWorkbook hssfworkbook)
{
//Write the stream data of workbook to the root directory
MemoryStream file = new MemoryStream();
hssfworkbook.Write(file);
return file;
}
/// <summary>
/// 文件下载
/// </summary>
/// <param name="filename"></param>
protected void Download(string filename)
{
filename = HttpUtility.UrlEncode(filename, System.Text.Encoding.UTF8);
System.Web.HttpContext.Current.Response.ContentType = "application/vnd.ms-excel";
System.Web.HttpContext.Current.Response.AddHeader("Content-Disposition", string.Format("attachment;filename={0}", filename));
System.Web.HttpContext.Current.Response.Clear();
Open(filename);
System.Web.HttpContext.Current.Response.BinaryWrite(WriteToStream(hssfworkbook).GetBuffer());
System.Web.HttpContext.Current.Response.End();
}
/// <summary>
/// 文件打开
/// </summary>
/// <param name="FullFileName"></param>
protected void Open(string FullFileName)
{
try
{
//FileName--要下载的文件名
FileInfo DownloadFile = new FileInfo(FullFileName);
if (DownloadFile.Exists)
{
System.Web.HttpContext.Current.Response.Clear();
System.Web.HttpContext.Current.Response.ClearHeaders();
System.Web.HttpContext.Current.Response.Buffer = false;
System.Web.HttpContext.Current.Response.ContentType = "application/octet-stream";
System.Web.HttpContext.Current.Response.AppendHeader("Content-Disposition", "attachment;filename=" + HttpUtility.UrlEncode(DownloadFile.FullName, System.Text.Encoding.ASCII));
System.Web.HttpContext.Current.Response.AppendHeader("Content-Length", DownloadFile.Length.ToString());
System.Web.HttpContext.Current.Response.WriteFile(DownloadFile.FullName);
System.Web.HttpContext.Current.Response.Flush();
System.Web.HttpContext.Current.Response.End();
}
else
{
//文件不存在
}
}
catch
{
//打开时异常了
}
}
#endregion
/// <summary>
/// 创建标题与列对应关系
/// </summary>
/// <returns></returns>
public Dictionary<string, string> createDic()
{
Dictionary<string, string> dic = new Dictionary<string, string>();
if (!dic.ContainsKey("RowNumber"))
{
dic.Add("RowNumber", "序号");
}
if (!dic.ContainsKey("PCMonth"))
{
dic.Add("PCMonth", "回款月份");
}
if (!dic.ContainsKey("PCCompany"))
{
dic.Add("PCCompany", "公司");
}
if (!dic.ContainsKey("ContractNumber"))
{
dic.Add("ContractNumber", "合同号");
}
if (!dic.ContainsKey("ContractName"))
{
dic.Add("ContractName", "合同名称");
}
if (!dic.ContainsKey("FirstParty"))
{
dic.Add("FirstParty", "甲方");
}
if (!dic.ContainsKey("SignDate"))
{
dic.Add("SignDate", "签订日期");
}
if (!dic.ContainsKey("SignPerson"))
{
dic.Add("SignPerson", "签订人");
}
if (!dic.ContainsKey("PCDepartment"))
{
dic.Add("PCDepartment", "部门");
}
if (!dic.ContainsKey("ResponsiblePersonId"))
{
dic.Add("ResponsiblePersonId", "回款责任人工号");
}
if (!dic.ContainsKey("ResponsiblePersonName"))
{
dic.Add("ResponsiblePersonName", "回款责任人");
}
if (!dic.ContainsKey("ContractMoney"))
{
dic.Add("ContractMoney", "合同额");
}
if (!dic.ContainsKey("Currency"))
{
dic.Add("Currency", "币别");
}
if (!dic.ContainsKey("ReceivedMoney"))
{
dic.Add("ReceivedMoney", "已收款");
}
if (!dic.ContainsKey("TransitAccount"))
{
dic.Add("TransitAccount", "未收款");
}
if (!dic.ContainsKey("PCPossibility"))
{
dic.Add("PCPossibility", "回款可能性");
}
if (!dic.ContainsKey("MonthPromisePC"))
{
dic.Add("MonthPromisePC", "本月承诺回款");
}
if (!dic.ContainsKey("NoPromiseReason"))
{
dic.Add("NoPromiseReason", "未承诺原因");
}
if (!dic.ContainsKey("Remark"))
{
dic.Add("Remark", "备注");
}
return dic;
}
/// <summary>
/// 导出到excel
/// </summary>
public void Export(DataSet ds)
{
InitializeWorkbook();
SetFont();
Sheet sheet = hssfworkbook.CreateSheet("单据信息");
sheet.DisplayGridlines = false;
#region 设置列宽
sheet.SetColumnWidth(0, 6 * 256);
sheet.SetColumnWidth(1, 24 * 256);
sheet.SetColumnWidth(2, 16 * 256);
sheet.SetColumnWidth(3, 16 * 256);
sheet.SetColumnWidth(4, 16 * 256);
sheet.SetColumnWidth(5, 16 * 256);
sheet.SetColumnWidth(6, 30 * 256);
sheet.SetColumnWidth(7, 30 * 256);
sheet.SetColumnWidth(8, 10 * 256);
sheet.SetColumnWidth(9, 16 * 256);
sheet.SetColumnWidth(10, 50 * 256);
#endregion
Row r;
Cell cell;
CellRangeAddress region;
Dictionary<string, string> dic = createDic();
r = sheet.CreateRow(0);
r.HeightInPoints = 18;
int itt = 0;
foreach (var item in dic.Values)
{
cell = r.CreateCell(itt);
cell.SetCellValue(item);
cell.CellStyle = headerstyle;
itt++;
}
if (ds != null && ds.Tables.Count != 0)
{
DataTable dt = ds.Tables[0];
for (int i = 0; i < dt.Rows.Count; i++)
{
r = sheet.CreateRow(i + 1);
r.HeightInPoints = 18;
int j = 0;
string typename = "";
foreach (KeyValuePair<string, string> keyValue in dic)
{
cell = r.CreateCell(j); //序号
string colname = keyValue.Key;
if (colname == "Type")
{
typename = dt.Rows[i][colname].ToString();
}
if (typename == "补考勤单" || typename == "撤销单" || typename == "销假单")
{
if (colname == "HStart" || colname == "HEnd")
{
cell.SetCellValue(dt.Rows[i][colname].ToString().Split(' ')[0]);
}
else if (colname == "daydiff")
{
cell.SetCellValue("");
}
else
{
cell.SetCellValue(dt.Rows[i][colname].ToString());
}
}
else
{
cell.SetCellValue(dt.Rows[i][colname].ToString());
}
cell.CellStyle = normalstylecenter;
j++;
}
}
}
string filename = "单据查询结果.xls";
Download(filename);
}
}
}