说明:NPOI组件的使用
1 添加引用
2 代码
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.IO;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using CaterBLL;
using NPOI.HSSF.UserModel;
using NPOI.SS.Formula;
using CaterModel; namespace ExcelForm
{
public partial class Form1 : Form
{
private ManagerInfoBLL bll = new ManagerInfoBLL();
List<ManagerInfoModel> modelList = new List<ManagerInfoModel>( ); public Form1()
{
InitializeComponent();
} #region 01 窗体加载
private void Form1_Load(object sender, EventArgs e)
{
modelList = bll.GetManaerList();
dgvList.DataSource = bll.GetManaerList();
}
#endregion #region 02 "导出" 按钮触发事件
private void btnExcelOutPut_Click(object sender, EventArgs e)
{
//01 创建工作簿
HSSFWorkbook workbook = new HSSFWorkbook();
//02 创建Sheet
HSSFSheet sheet = workbook.CreateSheet("管理员信息");
//03 创建行
HSSFRow row = sheet.CreateRow();
//04 创建单元格
HSSFCell cell0 = row.CreateCell();
//05 设置单元格的值
cell0.SetCellValue("管理员列表");
//06 合并单元格(开始行,开始列,结束行,结束列)
sheet.AddMergedRegion(new NPOI.HSSF.Util.Region(, , , ));
//07 设置字体居中
HSSFCellStyle styleTitle = workbook.CreateCellStyle();
styleTitle.Alignment = ;//1左，2中，3右
//08 将样式作用于单元格上
cell0.CellStyle = styleTitle;
//09 设置字体大小
HSSFFont fontTitle = workbook.CreateFont();
fontTitle.FontHeightInPoints = ;
styleTitle.SetFont(fontTitle);
//10 创建标题行
MakeTitleRow(sheet, styleTitle); //11 创建正文数据
MakeContentRow(sheet);
//12 保存工作表
FileStream stream = new FileStream(@"E:\1\ManagerInfo.xls",FileMode.Create);
workbook.Write(stream);
stream.Close();
stream.Dispose();
}
#endregion #region 03 创建标题行
private void MakeTitleRow(HSSFSheet sheet, HSSFCellStyle styleTitle)
{ //01 创建行
HSSFRow rowTitle = sheet.CreateRow();
//02创建列
HSSFCell cell0 = rowTitle.CreateCell();
cell0.SetCellValue("编号"); HSSFCell cell1 = rowTitle.CreateCell();
cell1.SetCellValue("姓名"); HSSFCell cell2 = rowTitle.CreateCell();
cell2.SetCellValue("密码"); HSSFCell cell3 = rowTitle.CreateCell();
cell3.SetCellValue("类型");
//03 将样式作用于单元格上
cell0.CellStyle = styleTitle;
cell1.CellStyle = styleTitle;
cell2.CellStyle = styleTitle;
cell3.CellStyle = styleTitle;
}
#endregion #region 04 创建内容行
private void MakeContentRow(HSSFSheet sheet)
{
//01 指定行数
int rowIndex = ;
foreach (var mi in modelList)
{
//02 创建行
HSSFRow rowContent = sheet.CreateRow(rowIndex++);
//03 创建列
HSSFCell cell0 = rowContent.CreateCell();
cell0.SetCellValue(mi.MId); HSSFCell cell1 = rowContent.CreateCell();
cell1.SetCellValue(mi.MName); HSSFCell cell2 = rowContent.CreateCell();
cell2.SetCellValue(mi.MPwd); HSSFCell cell3 = rowContent.CreateCell();
cell3.SetCellValue(mi.MType==?"经理":"员工");
}
} #endregion #region 05 导入按钮触发事件
private void btnExcelInput_Click(object sender, EventArgs e)
{
//01 定义list集合
List<ManagerInfoModel> listManager = new List<ManagerInfoModel>( );
//02 读取文件流
using (FileStream stream = new FileStream(@"E:\1\ManagerInfo.xls",FileMode.Open))
{
//02-01 创建workbook
HSSFWorkbook workbook = new HSSFWorkbook(stream);
//02-02 读取sheet
HSSFSheet sheet = workbook.GetSheetAt();
//02-03 读取数据 --跳过标题行
int rowId = ;
while (sheet.GetRow(rowId) != null)
{
HSSFRow row = sheet.GetRow(rowId);
//02-04 创建对象并实例化
ManagerInfoModel mi = new ManagerInfoModel();
mi.MId = (int)row.GetCell().NumericCellValue;
mi.MName = row.GetCell().StringCellValue;
mi.MPwd = row.GetCell().StringCellValue;
mi.MType = row.GetCell().StringCellValue == "经理" ? : ;
//02-05 将对象放入集合中
listManager.Add(mi);
rowId++;
} }
//指定为数据源
dgvList.DataSource = listManager;
}
#endregion
}
}
3 效果
二，导出设置样式
public static void ExportDQRoleDataNew2(DataTable dt, string fileName, string sheetName)
{
try
{
HSSFWorkbook wb = new HSSFWorkbook();//创建一个工作薄
ISheet sheet = wb.CreateSheet(sheetName);//在工作薄中创建一个工作表
IRow rw = sheet.CreateRow();
#region 设置样式
//设置字体01 --开始
//fontTitle ：标题 fontHead ：头部
IFont fontTitle = wb.CreateFont();
fontTitle.FontHeightInPoints = ;
fontTitle.Boldweight = (short)NPOI.SS.UserModel.FontBoldWeight.Bold;
fontTitle.FontName = "微软雅黑";
IFont fontHead = wb.CreateFont();
fontHead.FontHeightInPoints = ;
fontHead.Boldweight = (short)NPOI.SS.UserModel.FontBoldWeight.Bold;
fontHead.FontName = "微软雅黑";
//设置字体01 --结束 //设置样式02 --开始
//titleStyle:标题样式
var titleStyle = wb.CreateCellStyle();
//设置单元格上下左右边框线(不要边线)
titleStyle.BorderTop = NPOI.SS.UserModel.BorderStyle.None;
titleStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.None;
titleStyle.BorderLeft = NPOI.SS.UserModel.BorderStyle.None;
titleStyle.BorderRight = NPOI.SS.UserModel.BorderStyle.None;
//文字水平和垂直对齐方式
titleStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
titleStyle.VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment.Center;
//文字样式
titleStyle.SetFont(fontTitle); var headStyle = wb.CreateCellStyle();
//设置单元格上下左右边框线(不要边线)
headStyle.BorderTop = NPOI.SS.UserModel.BorderStyle.None;
headStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.None;
headStyle.BorderLeft = NPOI.SS.UserModel.BorderStyle.None;
headStyle.BorderRight = NPOI.SS.UserModel.BorderStyle.None;
//文字水平和垂直对齐方式
headStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
headStyle.VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment.Center;
//文字样式
headStyle.SetFont(fontHead);
//设置背景色
//s.FillForegroundColor = HSSFColor.Pink.Index
headStyle.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.CornflowerBlue.Index;
headStyle.FillPattern = FillPattern.SolidForeground; var leftStyle = wb.CreateCellStyle();
//设置单元格上下左右边框线(不要边线)
leftStyle.BorderTop = NPOI.SS.UserModel.BorderStyle.None;
leftStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.None;
leftStyle.BorderLeft = NPOI.SS.UserModel.BorderStyle.None;
leftStyle.BorderRight = NPOI.SS.UserModel.BorderStyle.None;
//文字水平和垂直对齐方式
leftStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
leftStyle.VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment.Justify;
//文字样式
leftStyle.SetFont(fontHead);
//设置样式02 --结束 #endregion
//循环一个表头来创建第一行的表头
ICell ic = rw.CreateCell();
ic.CellStyle = titleStyle; //设置样式
ic.SetCellValue(dt.Columns[].ColumnName);
//定义一个值，用于判断“所在部门”合并多少列
int rowColIndex = ;
int rowColEnd = ;
Dictionary<int, int> rowColDic = new Dictionary<int, int>();
string currentRoleCode = String.Empty;
for (int i = ; i < dt.Rows.Count; i++)
{ DataRow dr = dt.Rows[i];
if (dr[].ToString() == dr[].ToString() && dr[].ToString() == dr[].ToString())
{
rowColEnd = i;
rowColDic.Add(rowColIndex, rowColEnd);
rowColIndex = i + ; } rw = sheet.CreateRow(i + );
rw.CreateCell().SetCellValue(dr[].ToString());
rw.CreateCell().SetCellValue(dr[].ToString());
rw.CreateCell().SetCellValue(dr[].ToString());
rw.CreateCell().SetCellValue(dr[].ToString()); }
//CellRangeAddress四个参数为：起始行，结束行，起始列，结束列
sheet.AddMergedRegion(new CellRangeAddress(, , , ));
rowColDic.Remove(rowColDic.LastOrDefault().Key);
foreach (KeyValuePair<int, int> kv in rowColDic)
{
//CellRangeAddress四个参数为：起始行，结束行，起始列，结束列
sheet.AddMergedRegion(new CellRangeAddress(kv.Key, kv.Value, , ));
//为空的数据设置背景色
IRow rwhead = sheet.GetRow(kv.Key-);
rwhead.GetCell().CellStyle = headStyle;
rwhead.GetCell().CellStyle = headStyle;
rwhead.GetCell().CellStyle = headStyle;
rwhead.GetCell().CellStyle = headStyle;
//下一行的所在部门居中
IRow rwleft = sheet.GetRow(kv.Key);
rwleft.GetCell().CellStyle = leftStyle;
} //设置单元格的宽度实际上是设置其所在列宽，所以要在单元格所在列上设置（列的设置在工作表上）
sheet.SetColumnWidth(, (int)22.13 * );
sheet.SetColumnWidth(, (int)49.88 * );
sheet.SetColumnWidth(, (int)13.50 * );
sheet.SetColumnWidth(, (int)14.88 * );
MemoryStream file = new MemoryStream();
wb.Write(file);
string UserAgent = HttpContext.Current.Request.ServerVariables["http_user_agent"].ToLower();
if (UserAgent.IndexOf("firefox") > )
{
Encoding eGB3212 = Encoding.GetEncoding("GB2312");
fileName = eGB3212.GetString(eGB3212.GetBytes(fileName));
}
else
{
fileName = HttpUtility.UrlEncode(fileName, Encoding.UTF8);
}
HttpContext.Current.Response.ContentType = "application/vnd.ms-excel;charset=UTF-8";
HttpContext.Current.Response.AddHeader("Content-Disposition", string.Format("attachment;filename={0}.xls", fileName));
HttpContext.Current.Response.Clear(); file.WriteTo(HttpContext.Current.Response.OutputStream);
}
catch (Exception ex)
{
throw new Exception("导出出错，请您联系系统管理员。" + ex.Message);
}
}
三，在原来的模板上导出
/// <summary>
/// NPOI使用ShiftRows向excel插入行，并复制原有样式
/// </summary>
/// <param name="file">模板文件，包含物理路径</param>
/// <param name="dir">导出路径</param>
public string ShiftRows(string file, string dir)
{
string errorType = "";
string dqid = base.Request["OrgCode"];
string orgLevel = Request.QueryString["OrgLevel"];
//创建Excel文件的对象
FileStream fs = new FileStream(file, FileMode.Open);
//如果是xls，使用HSSFWorkbook；如果是xlsx，使用XSSFWorkbook
string extension = System.IO.Path.GetExtension(fldupload.FileName);
IWorkbook workbook;
ISheet sheet = null;
if (extension == ".xls")
{
workbook = new HSSFWorkbook(fs); if (orgLevel == "")
{
sheet = (HSSFSheet)workbook.GetSheet("集团审批角色变更");
}
else if (orgLevel == "")
{
sheet = (HSSFSheet)workbook.GetSheet("区域公司审批角色变更");
}
}
else
{
workbook = new XSSFWorkbook(fs);
if (orgLevel == "")
{
sheet = (XSSFSheet)workbook.GetSheet("集团审批角色变更");
}
else if (orgLevel == "")
{
sheet = (XSSFSheet)workbook.GetSheet("区域公司审批角色变更");
}
}
if (sheet==null)
{
errorType = "";
return errorType;
}
List<ICommonRoleOrgUserRelevance> list = BaseModelManager.CommonRoleOrgUserRelevanceDAO.RetrieveListByWhere(string.Format(" And OrgCode ='{0}' order by RoleCode", dqid));
int rowCount = sheet.LastRowNum;
int rowNullNum = rowCount; //记录一个空行，方便赋值空行的格式
for (int i = ; i <= rowCount; i++)
{
//获取当前行
IRow currentRow = sheet.GetRow(i); //
//通过正则表达式获取 角色编号
//获取角色编码
string role = currentRow.Cells[] == null ? "" : currentRow.Cells[].ToString().Trim();
if (currentRow.Cells[] == null || currentRow.Cells[].ToString().Trim() == "")
{
rowNullNum = i;
}
Regex regRole = new Regex(@"([^\(\)]+)(?=\))");
string roleCode = "";
MatchCollection mcRole = regRole.Matches(role);
if (mcRole.Count > )
{
roleCode = mcRole[mcRole.Count-].Groups[].Value;
} if (!String.IsNullOrWhiteSpace(roleCode))
{
//如果没有匹配到用户角色,那么遍历list，查看该角色下的所有用户
List<ICommonRoleOrgUserRelevance> listCurrentRoleContainsUsers = list.FindAll(c => c.RoleCode.Equals(roleCode));
string userInfo = String.Empty;
if (listCurrentRoleContainsUsers != null && listCurrentRoleContainsUsers.Count > )
{
//获取当前角色下的所有用户信息
int listCurrentRoleContainsUsersCount = listCurrentRoleContainsUsers.Count;
for (int j = ; j < listCurrentRoleContainsUsersCount; j++)
{
if (j != listCurrentRoleContainsUsersCount - )
{
//如果不是最后一个
userInfo = userInfo + listCurrentRoleContainsUsers[j].UserName + "(" + listCurrentRoleContainsUsers[j].UserID + ");";
}
else
{
userInfo = userInfo + listCurrentRoleContainsUsers[j].UserName + "(" + listCurrentRoleContainsUsers[j].UserID + ")";
}
}
//最后把获取到的值赋给（原审批人和新审批人）
currentRow.Cells[].SetCellValue(userInfo);
currentRow.Cells[].SetCellValue(userInfo);
//为了统计数据库中有，但是，表单中没有的角色，所以list中的角色每使用一次，就删除掉，那么剩下的就是 数据库中>excel中的角色
list.RemoveAll(c => c.RoleCode.Equals(roleCode));
}
}
}
//判断list中是否有剩余，那么剩下的就是 数据库中>excel中的角色 if (list != null && list.Count > )
{
string currentRoleCode = string.Empty;
string userInfo = string.Empty;
//获取所有的角色名称
List<ICommonRole> roleList = BaseModelManager.CommonRoleDAO.RetrieveListByWhere(String.Empty);//角色数据 foreach (ICommonRoleOrgUserRelevance entity in list)
{
if (currentRoleCode != entity.RoleCode)
{
if (!String.IsNullOrWhiteSpace(currentRoleCode))
{
rowCount++;
//如果不等于空，说明有数据，有数据先保存一下
var rowSource = sheet.GetRow(rowNullNum);
var rowStyle = rowSource.RowStyle;//获取为空行的样式
var rowInsert = sheet.CreateRow(rowCount);
rowInsert.RowStyle = rowStyle;
rowInsert.Height = rowSource.Height;
for (int col = ; col < rowSource.LastCellNum; col++)
{
var cellsource = rowSource.GetCell(col);
var cellInsert = rowInsert.CreateCell(col);
if (cellsource!=null)
{
var cellStyle = cellsource.CellStyle;
//设置单元格样式
if (cellStyle != null) {
cellInsert.CellStyle = cellsource.CellStyle;
}
}
}
ICommonRole entityRole= roleList.Find(c => c.RoleCode.Equals(currentRoleCode));
if (entityRole != null && !String.IsNullOrWhiteSpace(entityRole.RoleName))
{
rowInsert.Cells[].SetCellValue(entityRole.RoleName + "(" + currentRoleCode + ")");
}
else {
rowInsert.Cells[].SetCellValue(currentRoleCode);
}
rowInsert.Cells[].SetCellValue(userInfo);
rowInsert.Cells[].SetCellValue(userInfo);
}
currentRoleCode = entity.RoleCode;
userInfo = "";
userInfo = entity.UserName + "(" + entity.UserID + ")";
}
else
{
userInfo = userInfo + ";" + entity.UserName + "(" + entity.UserID + ")";
}
}
} using (MemoryStream ms = new MemoryStream())
{
if (workbook == null)
workbook.Write(ms);
else
workbook.Write(ms);
ms.Flush();
ms.Position = ;
HttpContext curContext = HttpContext.Current;
// 设置编码和附件格式
curContext.Response.ContentType = "application/vnd.ms-excel";
curContext.Response.ContentEncoding = Encoding.Default;
curContext.Response.Charset = ""; curContext.Response.AppendHeader("Content-Disposition", "attachment;filename=" + HttpUtility.UrlEncode(dir, Encoding.UTF8));
curContext.Response.BinaryWrite(ms.GetBuffer());
curContext.Response.End();
}
return errorType;
}
四，导出后火狐浏览器和其他浏览器关于文件名汉字乱码的问题
using (MemoryStream ms = new MemoryStream())
{
if (workbook == null)
{
workbook.Write(ms);
}
else
{
workbook.Write(ms);
}
ms.Flush();
ms.Position = ;
HttpContext curContext = HttpContext.Current;
// 设置编码和附件格式
curContext.Response.ContentType = "application/vnd.ms-excel";
curContext.Response.ContentEncoding = Encoding.Default;
curContext.Response.Charset = "";
//这里判断使用的浏览器是否为Firefox，Firefox导出文件时不需要对文件名显示编码，编码后文件名会乱码
//但是IE和Google需要编码才能保持文件名正常
if (curContext.Request.ServerVariables["http_user_agent"].ToString().IndexOf("Firefox") != -)
{
curContext.Response.AddHeader("Content-Disposition", "attachment;filename=" + dir);
}
else
{
curContext.Response.AddHeader("Content-Disposition", "attachment;filename="
+ System.Web.HttpUtility.UrlEncode(dir, System.Text.Encoding.UTF8));
}
curContext.Response.BinaryWrite(ms.GetBuffer());
curContext.Response.End();
}
五，导出后报Excel在“XXXX.xlsx”中发现不可读取的内容。是否恢复此工作簿的内容？如果信任此工作簿的来源，请单击“是”。Excel 已完成文件级验证和修复。此工作簿的某些部分可能已被修复或丢弃。
解决方法：在原来的基础上加“ curContext.Response.AddHeader("Content-Length", ms.Length.ToString());”
using (MemoryStream ms = new MemoryStream())
{
if (workbook == null)
{
workbook.Write(ms);
}
else
{
workbook.Write(ms);
}
ms.Flush();
ms.Position = ;
HttpContext curContext = HttpContext.Current;
// 设置编码和附件格式
curContext.Response.ContentType = "application/vnd.ms-excel";
curContext.Response.ContentEncoding = Encoding.Default;
curContext.Response.Charset = "";
//这里判断使用的浏览器是否为Firefox，Firefox导出文件时不需要对文件名显示编码，编码后文件名会乱码
//但是IE和Google需要编码才能保持文件名正常
curContext.Response.AddHeader("Content-Length", ms.Length.ToString());//注意这里
string fileName = sheet.SheetName + extension;
if (curContext.Request.ServerVariables["http_user_agent"].ToString().IndexOf("Firefox") != -)
{
curContext.Response.AddHeader("Content-Disposition", "attachment;filename=" + fileName);
}
else
{
curContext.Response.AddHeader("Content-Disposition", "attachment;filename="
+ System.Web.HttpUtility.UrlEncode(fileName, System.Text.Encoding.UTF8));
}
curContext.Response.BinaryWrite(ms.GetBuffer());
curContext.Response.End();
}