说明: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(0); //04 创建单元格 HSSFCell cell0 = row.CreateCell(0); //05 设置单元格的值 cell0.SetCellValue("管理员列表"); //06 合并单元格(开始行,开始列,结束行,结束列) sheet.AddMergedRegion(new NPOI.HSSF.Util.Region(0, 0, 0, 3)); //07 设置字体居中 HSSFCellStyle styleTitle = workbook.CreateCellStyle(); styleTitle.Alignment = 2;//1左，2中，3右 //08 将样式作用于单元格上 cell0.CellStyle = styleTitle; //09 设置字体大小 HSSFFont fontTitle = workbook.CreateFont(); fontTitle.FontHeightInPoints = 14; styleTitle.SetFont(fontTitle); //10 创建标题行 MakeTitleRow(sheet, styleTitle); //11 创建正文数据 MakeContentRow(sheet); //12 保存工作表 FileStream stream = new FileStream(@"E:1ManagerInfo.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(1); //02创建列 HSSFCell cell0 = rowTitle.CreateCell(0); cell0.SetCellValue("编号"); HSSFCell cell1 = rowTitle.CreateCell(1); cell1.SetCellValue("姓名"); HSSFCell cell2 = rowTitle.CreateCell(2); cell2.SetCellValue("密码"); HSSFCell cell3 = rowTitle.CreateCell(3); 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 = 2; foreach (var mi in modelList) { //02 创建行 HSSFRow rowContent = sheet.CreateRow(rowIndex++); //03 创建列 HSSFCell cell0 = rowContent.CreateCell(0); cell0.SetCellValue(mi.MId); HSSFCell cell1 = rowContent.CreateCell(1); cell1.SetCellValue(mi.MName); HSSFCell cell2 = rowContent.CreateCell(2); cell2.SetCellValue(mi.MPwd); HSSFCell cell3 = rowContent.CreateCell(3); cell3.SetCellValue(mi.MType==1?"经理":"员工"); } } #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:1ManagerInfo.xls",FileMode.Open)) { //02-01 创建workbook HSSFWorkbook workbook = new HSSFWorkbook(stream); //02-02 读取sheet HSSFSheet sheet = workbook.GetSheetAt(0); //02-03 读取数据 --跳过标题行 int rowId = 2; while (sheet.GetRow(rowId) != null) { HSSFRow row = sheet.GetRow(rowId); //02-04 创建对象并实例化 ManagerInfoModel mi = new ManagerInfoModel(); mi.MId = (int)row.GetCell(0).NumericCellValue; mi.MName = row.GetCell(1).StringCellValue; mi.MPwd = row.GetCell(2).StringCellValue; mi.MType = row.GetCell(2).StringCellValue == "经理" ? 1 : 0; //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(0); #region 设置样式 //设置字体01 --开始 //fontTitle ：标题 fontHead ：头部 IFont fontTitle = wb.CreateFont(); fontTitle.FontHeightInPoints = 14; fontTitle.Boldweight = (short)NPOI.SS.UserModel.FontBoldWeight.Bold; fontTitle.FontName = "微软雅黑"; IFont fontHead = wb.CreateFont(); fontHead.FontHeightInPoints = 11; 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(0); ic.CellStyle = titleStyle; //设置样式 ic.SetCellValue(dt.Columns[0].ColumnName); //定义一个值，用于判断“所在部门”合并多少列 int rowColIndex = 2; int rowColEnd = 2; Dictionary<int, int> rowColDic = new Dictionary<int, int>(); string currentRoleCode = String.Empty; for (int i = 0; i < dt.Rows.Count; i++) { DataRow dr = dt.Rows[i]; if (dr[0].ToString() == dr[1].ToString() && dr[1].ToString() == dr[2].ToString()) { rowColEnd = i; rowColDic.Add(rowColIndex, rowColEnd); rowColIndex = i + 2; } rw = sheet.CreateRow(i + 1); rw.CreateCell(0).SetCellValue(dr[0].ToString()); rw.CreateCell(1).SetCellValue(dr[1].ToString()); rw.CreateCell(2).SetCellValue(dr[2].ToString()); rw.CreateCell(3).SetCellValue(dr[3].ToString()); } //CellRangeAddress四个参数为：起始行，结束行，起始列，结束列 sheet.AddMergedRegion(new CellRangeAddress(0, 0, 0, 3)); rowColDic.Remove(rowColDic.LastOrDefault().Key); foreach (KeyValuePair<int, int> kv in rowColDic) { //CellRangeAddress四个参数为：起始行，结束行，起始列，结束列 sheet.AddMergedRegion(new CellRangeAddress(kv.Key, kv.Value, 0, 0)); //为空的数据设置背景色 IRow rwhead = sheet.GetRow(kv.Key-1); rwhead.GetCell(0).CellStyle = headStyle; rwhead.GetCell(1).CellStyle = headStyle; rwhead.GetCell(2).CellStyle = headStyle; rwhead.GetCell(3).CellStyle = headStyle; //下一行的所在部门居中 IRow rwleft = sheet.GetRow(kv.Key); rwleft.GetCell(0).CellStyle = leftStyle; } //设置单元格的宽度实际上是设置其所在列宽，所以要在单元格所在列上设置（列的设置在工作表上） sheet.SetColumnWidth(0, (int)22.13 * 256); sheet.SetColumnWidth(1, (int)49.88 * 256); sheet.SetColumnWidth(2, (int)13.50 * 256); sheet.SetColumnWidth(3, (int)14.88 * 256); MemoryStream file = new MemoryStream(); wb.Write(file); string UserAgent = HttpContext.Current.Request.ServerVariables["http_user_agent"].ToLower(); if (UserAgent.IndexOf("firefox") > 0) { 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 == "10") { sheet = (HSSFSheet)workbook.GetSheet("集团审批角色变更"); } else if (orgLevel == "20") { sheet = (HSSFSheet)workbook.GetSheet("区域公司审批角色变更"); } } else { workbook = new XSSFWorkbook(fs); if (orgLevel == "10") { sheet = (XSSFSheet)workbook.GetSheet("集团审批角色变更"); } else if (orgLevel == "20") { sheet = (XSSFSheet)workbook.GetSheet("区域公司审批角色变更"); } } if (sheet==null) { errorType = "1"; 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 = 1; i <= rowCount; i++) { //获取当前行 IRow currentRow = sheet.GetRow(i); // //通过正则表达式获取 角色编号 //获取角色编码 string role = currentRow.Cells[1] == null ? "" : currentRow.Cells[1].ToString().Trim(); if (currentRow.Cells[1] == null || currentRow.Cells[1].ToString().Trim() == "") { rowNullNum = i; } Regex regRole = new Regex(@"([^()]+)(?=))"); string roleCode = ""; MatchCollection mcRole = regRole.Matches(role); if (mcRole.Count > 0) { roleCode = mcRole[mcRole.Count-1].Groups[1].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 > 0) { //获取当前角色下的所有用户信息 int listCurrentRoleContainsUsersCount = listCurrentRoleContainsUsers.Count; for (int j = 0; j < listCurrentRoleContainsUsersCount; j++) { if (j != listCurrentRoleContainsUsersCount - 1) { //如果不是最后一个 userInfo = userInfo + listCurrentRoleContainsUsers[j].UserName + "(" + listCurrentRoleContainsUsers[j].UserID + ");"; } else { userInfo = userInfo + listCurrentRoleContainsUsers[j].UserName + "(" + listCurrentRoleContainsUsers[j].UserID + ")"; } } //最后把获取到的值赋给（原审批人和新审批人） currentRow.Cells[2].SetCellValue(userInfo); currentRow.Cells[3].SetCellValue(userInfo); //为了统计数据库中有，但是，表单中没有的角色，所以list中的角色每使用一次，就删除掉，那么剩下的就是 数据库中>excel中的角色 list.RemoveAll(c => c.RoleCode.Equals(roleCode)); } } } //判断list中是否有剩余，那么剩下的就是 数据库中>excel中的角色 if (list != null && list.Count > 0) { 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 = 0; 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[2].SetCellValue(entityRole.RoleName + "(" + currentRoleCode + ")"); } else { rowInsert.Cells[2].SetCellValue(currentRoleCode); } rowInsert.Cells[3].SetCellValue(userInfo); rowInsert.Cells[4].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 = 0; 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 = 0; 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") != -1) { 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 = 0; 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") != -1) { 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(); }