使用NPOI导入导出标准的Excel
关于NPOI
NPOI是POI项目的.NET版本，是由@Tony Qu(http://tonyqus.cnblogs.com/)等大侠基于POI开发的，可以从http://npoi.codeplex.com/下载到它的最新版本。它不使用Office COM组件(Microsoft.Office.Interop.XXX.dll)，不需要安装Microsoft Office，支持对Office 97-2003的文件格式，功能比较强大。更详细的说明请看官方网站。
它的以下一些特性让我相当喜欢：
- 支持对标准的Excel读写
- 支持对流(Stream)的读写 (而Jet OLEDB和Office COM都只能针对文件)
- 支持大部分Office COM组件的常用功能
- 性能优异
- 使用简单，易上手
使用NPOI导入
需要的引用：
using System.IO; using NPOI.HSSF.UserModel; using NPOI.SS.UserModel;
需要注意的是，sheet.LastRowNum = sheet.PhysicalNumberOfRows - 1，这里可能存在BUG：当没有数据或只有一行数据时sheet.LastRowNum为0，PhysicalNumberOfRows 表现正常。
这里读取流中的Excel来创建Workbook对象，并转换成DataTable：
#region 使用NPOI导入标准Excel //需要注意的是，sheet.LastRowNum = sheet.PhysicalNumberOfRows - 1，这里可能存在BUG：当没有数据或只有一行数据时sheet.LastRowNum为0，PhysicalNumberOfRows 表现正常。 //这里读取流中的Excel来创建Workbook对象，并转换成DataTable /// <param name="excelFile">Excel的物理路径</param> public static DataTable RenderFromExcel(string excelFile) { using (FileStream stream = System.IO.File.OpenRead(excelFile)) { return RenderFromExcel(stream); } } public static DataTable RenderFromExcel(Stream excelFileStream) { IWorkbook workbook = WorkbookFactory.Create(excelFileStream); ISheet sheet = workbook.GetSheetAt(0); DataTable table = new DataTable(); IRow headerRow = sheet.GetRow(0);//第一行为标题行 int cellCount = headerRow.LastCellNum;//LastCellNum = PhysicalNumberOfCells int rowCount = sheet.LastRowNum;//LastRowNum = PhysicalNumberOfRows - 1 //handling header. for (int i = headerRow.FirstCellNum; i < cellCount; i++) { DataColumn column = new DataColumn(headerRow.GetCell(i).ToString()); table.Columns.Add(column); } for (int i = (sheet.FirstRowNum + 1); i <= rowCount; i++) { IRow row = sheet.GetRow(i); DataRow dataRow = table.NewRow(); for (int j = row.FirstCellNum; j < cellCount; j++) { if (row.GetCell(j) != null) { if (row.GetCell(j).CellType == CellType.Numeric) { // 判断单元格的值是否为日期，避免日期的年份变两位 if (DateUtil.IsCellDateFormatted(row.GetCell(j))) dataRow[j] = row.GetCell(j).DateCellValue; else dataRow[j] = row.GetCell(j).NumericCellValue; } else dataRow[j] = row.GetCell(j); } else { dataRow[j] = ""; } } table.Rows.Add(dataRow); } excelFileStream.Close(); workbook = null; sheet = null; return table; } #endregion
控制器读取DataTable中的值：
string excelfilePath=this.Server.MapPath(filePath); DataTable dt = EIHelper.RenderFromExcel(excelfilePath); ///学号，性别，姓名为 DataTable中的Columns值（Columns值是什么下面对应的就是什么） foreach (DataRow row in dt.Rows) { var aa = row["学号"]; var bb = row["姓名"]; var cc = row["性别"]; }
使用NPOI导出
视图代码：
//导出Excel
function btn_derive() {
var KeyValue = GetJqGridRowValue("#gridTable", "id");
if (typeof (KeyValue) == "undefined") {
KeyValue = "";
}
SetDeriveExcel("#gridTable", "信息");
openDialog("/School/Students/ExportExcel?KeyValue=" + KeyValue, "ExportExcel", "学生导出", 620, 300, function (iframe) {
top.frames[iframe].AcceptClick();
});
}
@using System.Collections; @{ ViewBag.Title = "ExportExcel"; Layout = "~/Views/Shared/_LayoutIndex.cshtml"; } <script> $(function () { //getAjax("/Utility/GetDeriveExcelColumn", {}, function (data) { // var JsonData = eval("(" + data + ")"); // $.each(JsonData, function (i) { // var label = JsonData[i].label; // var name = JsonData[i].name; // var hidden = JsonData[i].hidden; // if (!!label && hidden == false) { // $("#AccessView").append("<li title='" + label + "'><a id='" + name + "'><img src=\"../../Content/Images/Icon16/tag_blue.png\">" + label + "</a><i></i></li>"); // } // }); //}); $("#AccessView li").click(function () { if (!$(this).find('a').hasClass('disabled')) { if (!!$(this).hasClass("selected")) { $(this).removeClass("selected"); } else { $(this).addClass("selected"); } } }); //自定义复选框 全选/反选 $("#CheckView").click(function () { if (!!$(this).hasClass("checkAllOff")) { $(this).attr('title', '反选'); $(this).text('反选'); $(this).attr('class', 'checkAllOn'); $('#AccessView li').addClass('selected'); } else { $(this).attr('title', '全选'); $(this).text('全选'); $(this).attr('class', 'checkAllOff'); $('#AccessView li').removeClass('selected'); } }) $("#CheckView").trigger("click"); }) //确认导出 function AcceptClick() { var KeyValue = $("#RowValue").val(); var SelectedField = ""; $('#AccessView .selected a').each(function () { SelectedField += $(this).attr('lable') + ":" + $(this).attr('id') + ","; }); Loading(true, "正在处理要导出数据..."); window.setTimeout(function () { location.href = "/School/Students/GetExportExcel?ExportField=" + escape(SelectedField) + "&KeyValue=" + KeyValue; Loading(false); }, 200); } </script> <div class="note-prompt" style="margin: 1px;"> 温馨提示：选中复选框即可导出相应字段，取消选中则回收相应字段。 </div> <div class="border" style="margin: 1px;"> <div class="btnbartitle"> <div style="float: left"> 系统视图 <span class="ModuleName"></span> </div> <div style="float: right"> <label id="CheckView" class="checkAllOff" title="全选">全选</label> </div> </div> <div style="height: 225px; overflow: auto;"> <ul id="AccessView" class="sys_spec_text"> @foreach (DictionaryEntry item in (ViewBag.FieldsValues as Hashtable)) { <li title="@item.Value"> <a id="@item.Key" lable="@item.Value"><img src="/Content/Images/Icon16/tag_blue.png">@item.Value</a><i></i></li> } </ul> <input id="RowValue" type="hidden" value="@ViewBag.RowValue" /> </div> </div>
控制器代码：
/// <summary> /// 导出Excel窗口，供选择要导出的列 /// </summary> /// <returns></returns> public ActionResult ExportExcel(string KeyValue) { ////根据实体类获取表的所有列 //Hashtable ht = HashtableHelper.GetModelToHashtable(new Students()); //ht.Remove("Password"); //ht.Remove("Portrait"); //ht.Remove("Token"); //ht.Remove("AccountStatus"); //ht.Remove("LoginStatus"); //ht.Remove("GraduateProvinceId"); //ht.Remove("GraduateCityId"); //ht.Remove("Family1Title"); //ht.Remove("Family1Name"); //ht.Remove("Family1Age"); //ht.Remove("Family1Politic"); //ht.Remove("Family1Company"); //ht.Remove("Family1Job"); //ht.Remove("Family1CompanyAddress"); //ht.Remove("Family1Postcode"); //ht.Remove("Family1TelephoneNO"); //ht.Remove("Family1RestDay"); //ht.Remove("Family2Title"); //ht.Remove("Family2Name"); //ht.Remove("Family2Age"); //ht.Remove("Family2Politic"); //ht.Remove("Family2Company"); //ht.Remove("Family2Job"); //ht.Remove("Family2CompanyAddress"); //ht.Remove("Family2Postcode"); //ht.Remove("Family2TelephoneNO"); //ht.Remove("Family2RestDay"); //ht.Remove("Family3Title"); //ht.Remove("Family3Name"); //ht.Remove("Family3Age"); //ht.Remove("Family3Politic"); //ht.Remove("Family3Company"); //ht.Remove("Family3Job"); //ht.Remove("Family3CompanyAddress"); //ht.Remove("Family3Postcode"); //ht.Remove("Family3TelephoneNO"); //ht.Remove("Family3RestDay"); //ht.Remove("CreateUserId"); //ht.Remove("ModifyUserId"); //ArrayList akeys = new ArrayList(ht.Keys); //ViewBag.Fields = akeys; ////获取选择的行 //ViewBag.RowValue = KeyValue; Hashtable ht = new Hashtable(); ht.Add("Name", "姓名"); ht.Add("Gender", "性别"); ht.Add("SchoolName", "学校"); ht.Add("ClassStudentCode", "学号"); ht.Add("Telephone", "手机号"); ht.Add("Email", "邮箱"); ArrayList akeys = new ArrayList(ht.Keys); ArrayList avalues = new ArrayList(ht.Values); //ViewBag.Fields = akeys; ViewBag.FieldsValues = ht; //获取选择的行 ViewBag.RowValue = KeyValue; return View(); } /// <summary> /// 获取导出的Excel文件 /// </summary> /// <param name="ExportField">要导出的列</param> /// <param name="KeyValue">要导出的数据行（自行选择的）</param> /// <returns></returns> public ActionResult GetExportExcel(string ExportField, string KeyValue) { try { string[] dataColumn = StringHelper.GetStrArray(StringHelper.DelLastComma(ExportField)); string xlsName = DateTime.Now.ToShortDateString() + "学生列表.xls"; ExportImportHelper.ExportXlsByWeb(studentsbll.GetTable(KeyValue), xlsName, dataColumn, "Sheet1"); return Json(new { Success = true, Code = "1", Message = "导出成功！" }, JsonRequestBehavior.AllowGet); } catch (Exception ex) { return Json(new { Success = false, Code = "-1", Message = "导出失败！" + ex.Message }, JsonRequestBehavior.AllowGet); } }
逻辑层Gettable方法：
/// <summary> /// 查询所包含的关键字的值的数据 /// </summary> /// <param name="KeyValue">形如："abc,ddd,222,af"的数据</param> /// <returns></returns> public DataTable GetTable(string KeyValue) { StringBuilder strSql = new StringBuilder(); strSql.Append(@"Select*From Students Where 1=1"); if (!string.IsNullOrEmpty(KeyValue)) { //string userid = StringHelper.GetSingleMark(KeyValue); //userid太长传递，或者效率太低，又加个自动增加的Int Id strSql.Append(@" And Id in (" + KeyValue + ")"); } return Repository().FindTableBySql(strSql.ToString()); }
使用NPOI导出PDF文件，导出Docx文件，导出Xls文件（方法整理）
using System; using System.Text; using System.Data; using NPOI.HSSF.UserModel; using System.IO; using NPOI.XWPF.UserModel; using NPOI.XSSF.UserModel; using iTextSharp.text; using iTextSharp.text.pdf; using NPOI.SS.UserModel; using System.Web; using NPOI.SS.Util; namespace Dw.Utilities { public class ExportImportHelper { #region 导出PDF文件 /// <summary> /// 导出PDF文件 /// </summary> /// <param name="localFilePath">文件保存路径</param> /// <param name="dtSource">数据源</param> /// <param name="HorV">页面横竖（为空表示竖，有非空值为横）</param> public static void ExportPDF(string localFilePath, DataTable dtSource, string HorV) { iTextSharp.text.io.StreamUtil.AddToResourceSearch("iTextAsian.dll"); iTextSharp.text.io.StreamUtil.AddToResourceSearch("iTextAsianCmaps.dll"); BaseFont bf; string basepath = System.Web.HttpContext.Current.Server.MapPath("\\Themes\\fonts\\STSONG.TTF"); try { bf = BaseFont.CreateFont(basepath, BaseFont.IDENTITY_H, BaseFont.NOT_EMBEDDED); } catch { bf = BaseFont.CreateFont("C:\\WINDOWS\\Fonts\\STSONG.TTF", BaseFont.IDENTITY_H, BaseFont.NOT_EMBEDDED); } Font font = new Font(bf); iTextSharp.text.Document pdf; if (string.IsNullOrEmpty(HorV)) pdf = new iTextSharp.text.Document(); else pdf = new iTextSharp.text.Document(PageSize.A4.Rotate()); PdfPTable table = new PdfPTable(dtSource.Columns.Count); table.HorizontalAlignment = Element.ALIGN_CENTER; PdfPCell cell; for (int i = 0; i < dtSource.Rows.Count + 1; i++) { for (int j = 0; j < dtSource.Columns.Count; j++) { if (i == 0) cell = new PdfPCell(new Phrase(dtSource.Columns[j].ColumnName, font)); else cell = new PdfPCell(new Phrase(dtSource.Rows[i - 1][j].ToString(), font)); table.AddCell(cell); } } using (FileStream fs = new FileStream(localFilePath, FileMode.Create, FileAccess.Write)) { PdfWriter.GetInstance(pdf, fs); pdf.Open(); pdf.Add(table); pdf.Close(); } } #endregion #region DataTable导出Docx /// <summary> /// 导出Docx /// </summary> /// <param name="localFilePath">文件保存路径</param> /// <param name="dtSource">数据源</param> public static void ExportDocx(string localFilePath, DataTable dtSource) { XWPFDocument doc = new XWPFDocument(); XWPFTable table = doc.CreateTable(dtSource.Rows.Count + 1, dtSource.Columns.Count); for (int i = 0; i < dtSource.Rows.Count + 1; i++) { for (int j = 0; j < dtSource.Columns.Count; j++) { if (i == 0) { table.GetRow(i).GetCell(j).SetText(dtSource.Columns[j].ColumnName); } else { table.GetRow(i).GetCell(j).SetText(dtSource.Rows[i - 1][j].ToString()); } } } using (FileStream fs = new FileStream(localFilePath, FileMode.Create, FileAccess.Write)) { doc.Write(fs); } } #endregion #region DataTable导出Xls /// <summary> /// 由DataTable导出Xls，用于Web系统。james.wang 2015-12-31夜 /// </summary> /// <param name="sourceTable">要导出数据的DataTable</param> /// <param name="fileName">指定Excel工作表名称</param> /// <param name="sheetName">工作表名称</param> /// <returns>Excel工作表</returns> public static void ExportXlsByWeb(DataTable sourceTable, string fileName, string[] dataColumn, string sheetName) { MemoryStream ms = StreamFunction(sourceTable, dataColumn, sheetName) as MemoryStream; if (HttpContext.Current.Request.UserAgent.ToLower().Contains("msie")) { fileName = HttpUtility.UrlEncode(fileName); } HttpContext.Current.Response.AppendHeader("Content-Disposition", "attachment;filename=" + fileName); HttpContext.Current.Response.BinaryWrite(ms.ToArray()); HttpContext.Current.Response.End(); ms.Close(); ms = null; } private static Stream StreamFunction(DataTable dtSource, string[] dataColumn, string sheetName) { IWorkbook workbook = new HSSFWorkbook(); MemoryStream ms = new MemoryStream(); ISheet sheet = workbook.CreateSheet(sheetName); IRow headerRow = sheet.CreateRow(0); //格式化日期样式 var dateStyle = workbook.CreateCellStyle(); dateStyle.Alignment = HorizontalAlignment.Left; dateStyle.VerticalAlignment = VerticalAlignment.Center; var format = workbook.CreateDataFormat(); dateStyle.DataFormat = format.GetFormat("yyyy-MM-dd"); //取得列宽 int[] arrColWidth = new int[dtSource.Columns.Count]; foreach (DataColumn item in dtSource.Columns) { arrColWidth[item.Ordinal] = Encoding.GetEncoding(936).GetBytes(item.ColumnName.ToString()).Length; } for (int i = 0; i < dtSource.Rows.Count; i++) { for (int j = 0; j < dtSource.Columns.Count; j++) { int intTemp = Encoding.GetEncoding(936).GetBytes(dtSource.Rows[i][j].ToString()).Length; if (intTemp > arrColWidth[j]) { arrColWidth[j] = intTemp; } } } int rowIndex = 0; foreach (DataRow row in dtSource.Rows) { #region 新建表，填充列头，样式 if (rowIndex == 65535 || rowIndex == 0) { if (rowIndex != 0) { sheet = (XSSFSheet)workbook.CreateSheet(); } #region 列头及样式 HSSFCellStyle headStyle = (HSSFCellStyle)workbook.CreateCellStyle(); headStyle.Alignment = HorizontalAlignment.Center; HSSFFont font = (HSSFFont)workbook.CreateFont(); font.FontHeightInPoints = 10; font.Boldweight = 700; headStyle.SetFont(font); int k = 0; foreach (DataColumn column in dtSource.Columns) { //只导出需要的列列头 foreach (string item in dataColumn) { string[] stritem = item.Split(':'); if (stritem[1].ToLower() == column.ColumnName.ToLower()) { headerRow.CreateCell(k).SetCellValue(stritem[0]); headerRow.GetCell(k).CellStyle = headStyle; //设置列宽,因为Excel单元格宽度不能超过255个字符，所以这里要判断一下，不然会出错 var colWidth = (arrColWidth[column.Ordinal] + 1) * 256; if (colWidth >= 65280) colWidth = 20000; sheet.SetColumnWidth(k, colWidth); k++; } } } #endregion rowIndex = 1; } #endregion #region 填充内容 var dataRow = sheet.CreateRow(rowIndex); var style = workbook.CreateCellStyle(); style.VerticalAlignment = VerticalAlignment.Center; //垂直居中 int g = 0; foreach (DataColumn column in dtSource.Columns) { //只导出需要的列值 foreach (string item in dataColumn) { string[] stritem = item.Split(':'); if (stritem[1].ToLower() == column.ColumnName.ToLower()) { var newCell = dataRow.CreateCell(g); string drValue = row[column].ToString(); switch (column.DataType.ToString()) { case "System.String"://字符串类型 //接入图片 string[] picArr = drValue.Split('.'); string picType = (picArr[picArr.Length - 1]).ToLower(); if (picType == "jpg" || picType == "png" || picType == "gif") { string picpath = HttpContext.Current.Server.MapPath(drValue); if (File.Exists(picpath)) { dataRow.HeightInPoints = 40; //设置行高度 sheet.SetColumnWidth(g, 7*256); //设置宽度 byte[] bytes = System.IO.File.ReadAllBytes(picpath); int pictureIdx = workbook.AddPicture(bytes, NPOI.SS.UserModel.PictureType.JPEG); // Create the drawing patriarch. This is the top level container for all shapes. var getsheet = workbook.GetSheet(sheetName); var patriarch = getsheet.CreateDrawingPatriarch(); //add a picture var anchor = new HSSFClientAnchor(0, 0, 0, 0, g, rowIndex, g, rowIndex); var pict = patriarch.CreatePicture(anchor, pictureIdx); pict.Resize(1.0); } } else { newCell.SetCellValue(drValue); newCell.CellStyle = style; } 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": double doubV = 0; double.TryParse(drValue, out doubV); newCell.SetCellValue(doubV); break; case "System.DBNull"://空值处理 newCell.SetCellValue(""); break; default: newCell.SetCellValue(""); break; } g++; } } } #endregion rowIndex++; } workbook.Write(ms); ms.Flush(); ms.Position = 0; sheet = null; headerRow = null; workbook = null; return ms; } /// <summary> /// WinForm使用DataTable导出到Excel文件 Excel 2003 /// </summary> /// <param name="sourceDs">源DataTable</param> /// <param name="fileName">路径</param> /// <param name="sheetName">指定Excel工作表名称(多个用,号隔开)</param> /// <returns></returns> public static void ExportXlsByForm(DataTable sourceDs, string fileName, string[] dataColumn, string sheetName) { using (MemoryStream ms = StreamFunction(sourceDs, dataColumn, sheetName) as MemoryStream) { using (FileStream fs = new FileStream(fileName, FileMode.Create, FileAccess.Write)) { byte[] data = ms.ToArray(); fs.Write(data, 0, data.Length); fs.Flush(); } } } #endregion #region DataTable导出Excel2007 /// <summary> /// 由DataTable导出Excel2007 /// </summary> /// <param name="sourceTable">要导出数据的DataTable</param> /// <param name="sheetName">工作表名称</param> /// <returns>Excel工作表</returns> private static Stream ExportDataTableToExcel2007(DataTable sourceTable, string sheetName) { IWorkbook workbook = new XSSFWorkbook(); MemoryStream ms = new MemoryStream(); ISheet sheet = workbook.CreateSheet(sheetName); IRow headerRow = sheet.CreateRow(0); // handling header. foreach (DataColumn column in sourceTable.Columns) headerRow.CreateCell(column.Ordinal).SetCellValue(column.ColumnName); // handling value. int rowIndex = 1; foreach (DataRow row in sourceTable.Rows) { IRow dataRow = sheet.CreateRow(rowIndex); foreach (DataColumn column in sourceTable.Columns) { dataRow.CreateCell(column.Ordinal).SetCellValue(row[column].ToString()); } rowIndex++; } workbook.Write(ms); ms.Flush(); //ms.Position = 0; sheet = null; headerRow = null; workbook = null; return ms; } /// <summary> /// DataTable导出到Excel文件 Excel 2007 /// </summary> /// <param name="sourceDs">源DataTable</param> /// <param name="fileName">路径</param> /// <param name="sheetName">指定Excel工作表名称(多个用,号隔开)</param> /// <returns></returns> public static void ExportDataTableToExcel2007(DataTable sourceDs, string fileName, string sheetName) { using (MemoryStream ms = ExportDataTableToExcel2007(sourceDs, sheetName) as MemoryStream) { using (FileStream fs = new FileStream(fileName, FileMode.Create, FileAccess.Write)) { byte[] data = ms.ToArray(); fs.Write(data, 0, data.Length); fs.Flush(); } } } /// <summary> /// web由DataTable导出Excel /// </summary> /// <param name="sourceTable">要导出数据的DataTable</param> /// <param name="fileName">指定Excel工作表名称</param> /// <param name="sheetName">工作表名称</param> /// <returns>Excel工作表</returns> public static void ExportByWebDataTableToExcel2007(DataTable sourceTable, string fileName, string sheetName) { MemoryStream ms = ExportDataTableToExcel2007(sourceTable, sheetName) as MemoryStream; if (HttpContext.Current.Request.UserAgent.ToLower().Contains("msie")) { fileName = HttpUtility.UrlEncode(fileName); } HttpContext.Current.Response.AppendHeader("Content-Disposition", "attachment;filename=" + fileName); HttpContext.Current.Response.BinaryWrite(ms.ToArray()); HttpContext.Current.Response.End(); ms.Close(); ms = null; } #endregion #region 导出带有模板的Xls (可以一个Excel里有多个sheet表) private static void ToCreateCell(string FieldName,string columnName,HSSFWorkbook workbook, HSSFSheet sheet,HSSFCellStyle cellstyle,string colItem, string cellValue,string dataType) { if (FieldName.ToLower() == columnName.ToLower()) { var strspe = colItem.Split(':'); int firstRow = int.Parse(strspe[1]); int firstCol = int.Parse(strspe[2]); HSSFCell cell = (HSSFCell)sheet.GetRow(firstRow).CreateCell(firstCol); //插入图片 string[] picArr = cellValue.Split('.'); string picType = (picArr[picArr.Length - 1]).ToLower(); if (dataType == "System.String" && (picType == "jpg" || picType == "png" || picType == "gif")) { string picpath = HttpContext.Current.Server.MapPath(cellValue); if (File.Exists(picpath)) { //sheet.CreateRow(firstRow).HeightInPoints = 40; //设置行高度 //sheet.SetColumnWidth(firstCol, 7 * 256); //设置宽度 byte[] bytes = File.ReadAllBytes(picpath); int pictureIdx = workbook.AddPicture(bytes, NPOI.SS.UserModel.PictureType.JPEG); var patriarch = sheet.CreateDrawingPatriarch(); var anchor = new HSSFClientAnchor(0, 0, 0, 0, firstCol, firstRow, int.Parse(strspe[4]), int.Parse(strspe[3])); var pict = patriarch.CreatePicture(anchor, pictureIdx); pict.Resize(1.0); } } else { cell.SetCellValue(cellValue.Replace("§", " / ")); cell.CellStyle = cellstyle; cell.CellStyle.Alignment = HorizontalAlignment.Left; cell.CellStyle.VerticalAlignment = VerticalAlignment.Center; cell.CellStyle.BorderBottom = BorderStyle.Thin; } } } /// <summary> /// 导出带有模板的Xls(可以一个Excel里有多个sheet表) james2016-1-2 8:17 /// </summary> /// <param name="templateFilePath"></param> /// <param name="exportFilePath"></param> /// <param name="keyValue"></param> public static void ExportTempleteXls(string templateFilePath,string exportFilePath,DataTable sourceTable, string[] columnData) { HSSFWorkbook workbook=new HSSFWorkbook(); using(FileStream file=new FileStream(templateFilePath, FileMode.Open, FileAccess.Read)) { workbook = new HSSFWorkbook(file); file.Close(); } //获取指定的Sheet工作表名 HSSFSheet sheet = (HSSFSheet)workbook.GetSheetAt(0); HSSFCellStyle cellstyle = (HSSFCellStyle)workbook.CreateCellStyle(); /////////////////////////////////////////////////////// foreach (DataRow row in sourceTable.Rows) { foreach (DataColumn column in sourceTable.Columns) { foreach(var item in columnData) { var strspe = item.Split(':'); ToCreateCell(strspe[0], column.ColumnName, workbook, sheet, cellstyle, item, row[column].ToString(), column.DataType.ToString()); } } } /////////////////////////////////////////////////////// //创建文件 FileStream files = new FileStream(exportFilePath, FileMode.Create); workbook.Write(files); files.Close(); FileInfo filet = new FileInfo(exportFilePath); HttpContext.Current.Response.Clear(); HttpContext.Current.Response.Charset = "GB2312"; HttpContext.Current.Response.ContentEncoding = System.Text.Encoding.UTF8; // 添加头信息，为"文件下载/另存为"对话框指定默认文件名 HttpContext.Current.Response.AddHeader("Content-Disposition", "attachment; filename=" + HttpContext.Current.Server.UrlEncode("55.xls")); //HttpContext.Current.Response.AppendHeader("Content-Disposition", "attachment;filename=" + fileName); // 添加头信息，指定文件大小，让浏览器能够显示下载进度 HttpContext.Current.Response.AddHeader("Content-Length", filet.Length.ToString()); // 指定返回的是一个不能被客户端读取的流，必须被下载 HttpContext.Current.Response.ContentType = "application/ms-excel"; // 把文件流发送到客户端 HttpContext.Current.Response.WriteFile(filet.FullName); // 停止页面的执行 HttpContext.Current.Response.End(); } #endregion #region Excel导入DataTable /// <summary> /// 由Excel导入DataTable /// </summary> /// <param name="excelFileStream">Excel文件流</param> /// <param name="sheetName">Excel工作表名称</param> /// <param name="headerRowIndex">Excel表头行索引</param> /// <returns>DataTable</returns> public static DataTable ImportDataTableFromExcel(Stream excelFileStream, string sheetName, int headerRowIndex) { IWorkbook workbook = WorkbookFactory.Create(excelFileStream); ISheet sheet = workbook.GetSheet(sheetName); DataTable table = new DataTable(); IRow headerRow = sheet.GetRow(headerRowIndex); int cellCount = headerRow.LastCellNum; for (int i = headerRow.FirstCellNum; i < cellCount; i++) { DataColumn column = new DataColumn(headerRow.GetCell(i).StringCellValue); table.Columns.Add(column); } for (int i = (sheet.FirstRowNum + 1); i <= sheet.LastRowNum; i++) { IRow row = sheet.GetRow(i); DataRow dataRow = table.NewRow(); for (int j = row.FirstCellNum; j < cellCount; j++) { if (row.GetCell(j) != null) { if (row.GetCell(j).CellType == CellType.Numeric) { // 判断单元格的值是否为日期，避免日期的年份变两位 if (DateUtil.IsCellDateFormatted(row.GetCell(j))) dataRow[j] = row.GetCell(j).DateCellValue; else dataRow[j] = row.GetCell(j).NumericCellValue; } else dataRow[j] = row.GetCell(j); } else { dataRow[j] = ""; } } table.Rows.Add(dataRow); } excelFileStream.Close(); workbook = null; sheet = null; return table; } /// <summary> /// 由Excel导入DataTable /// </summary> /// <param name="excelFilePath">Excel文件路径，为物理路径。</param> /// <param name="sheetName">Excel工作表名称</param> /// <param name="headerRowIndex">Excel表头行索引</param> /// <returns>DataTable</returns> public static DataTable ImportDataTableFromExcel(string excelFilePath, string sheetName, int headerRowIndex) { using (FileStream stream = System.IO.File.OpenRead(excelFilePath)) { return ImportDataTableFromExcel(stream, sheetName, headerRowIndex); } } /// <summary> /// 由Excel导入DataTable /// </summary> /// <param name="excelFileStream">Excel文件流</param> /// <param name="sheetName">Excel工作表索引</param> /// <param name="headerRowIndex">Excel表头行索引</param> /// <returns>DataTable</returns> public static DataTable ImportDataTableFromExcel(Stream excelFileStream, int sheetIndex, int headerRowIndex) { IWorkbook workbook = WorkbookFactory.Create(excelFileStream); ISheet sheet = workbook.GetSheetAt(sheetIndex); DataTable table = new DataTable(); IRow headerRow = sheet.GetRow(headerRowIndex); int cellCount = headerRow.LastCellNum; for (int i = headerRow.FirstCellNum; i < cellCount; i++) { if (headerRow.GetCell(i) == null || headerRow.GetCell(i).StringCellValue.Trim() == "") { // 如果遇到第一个空列，则不再继续向后读取 cellCount = i + 1; break; } DataColumn column = new DataColumn(headerRow.GetCell(i).StringCellValue); table.Columns.Add(column); } for (int i = (sheet.FirstRowNum + 1); i <= sheet.LastRowNum; i++) { IRow row = sheet.GetRow(i); if (row == null || row.GetCell(0) == null || row.GetCell(0).ToString().Trim() == "") { // 如果遇到第一个空行，则不再继续向后读取 break; } DataRow dataRow = table.NewRow(); for (int j = row.FirstCellNum; j < cellCount; j++) { if (row.GetCell(j) != null) { if (row.GetCell(j).CellType == CellType.Numeric) { // 判断单元格的值是否为日期，避免日期的年份变两位 if (DateUtil.IsCellDateFormatted(row.GetCell(j))) dataRow[j] = row.GetCell(j).DateCellValue; else dataRow[j] = row.GetCell(j).NumericCellValue; } else dataRow[j] = row.GetCell(j); } else { dataRow[j] = ""; } } table.Rows.Add(dataRow); } excelFileStream.Close(); workbook = null; sheet = null; return table; } /// <summary> /// 由Excel导入DataTable /// </summary> /// <param name="excelFilePath">Excel文件路径，为物理路径。</param> /// <param name="sheetName">Excel工作表索引</param> /// <param name="headerRowIndex">Excel表头行索引</param> /// <returns>DataTable</returns> public static DataTable ImportDataTableFromExcel(string excelFilePath, int sheetIndex, int headerRowIndex) { using (FileStream stream = System.IO.File.OpenRead(excelFilePath)) { return ImportDataTableFromExcel(stream, sheetIndex, headerRowIndex); } } #endregion #region Excel导入DataSet /// <summary> /// 由Excel导入DataSet，如果有多个工作表，则导入多个DataTable /// </summary> /// <param name="excelFileStream">Excel文件流</param> /// <param name="headerRowIndex">Excel表头行索引</param> /// <returns>DataSet</returns> public static DataSet ImportDataSetFromExcel(Stream excelFileStream, int headerRowIndex) { DataSet ds = new DataSet(); IWorkbook workbook = WorkbookFactory.Create(excelFileStream); for (int a = 0, b = workbook.NumberOfSheets; a < b; a++) { ISheet sheet = workbook.GetSheetAt(a); DataTable table = new DataTable(); IRow headerRow = sheet.GetRow(headerRowIndex); int cellCount = headerRow.LastCellNum; for (int i = headerRow.FirstCellNum; i < cellCount; i++) { if (headerRow.GetCell(i) == null || headerRow.GetCell(i).StringCellValue.Trim() == "") { // 如果遇到第一个空列，则不再继续向后读取 cellCount = i + 1; break; } DataColumn column = new DataColumn(headerRow.GetCell(i).StringCellValue); table.Columns.Add(column); } for (int i = (sheet.FirstRowNum + 1); i <= sheet.LastRowNum; i++) { IRow row = sheet.GetRow(i); if (row == null || row.GetCell(0) == null || row.GetCell(0).ToString().Trim() == "") { // 如果遇到第一个空行，则不再继续向后读取 break; } DataRow dataRow = table.NewRow(); for (int j = row.FirstCellNum; j < cellCount; j++) { if (row.GetCell(j) != null) { if (row.GetCell(j).CellType == CellType.Numeric) { // 判断单元格的值是否为日期，避免日期的年份变两位 if (DateUtil.IsCellDateFormatted(row.GetCell(j))) dataRow[j] = row.GetCell(j).DateCellValue; else dataRow[j] = row.GetCell(j).NumericCellValue; } else dataRow[j] = row.GetCell(j); } else { dataRow[j] = ""; } } table.Rows.Add(dataRow); } ds.Tables.Add(table); } excelFileStream.Close(); workbook = null; return ds; } /// <summary> /// 由Excel导入DataSet，如果有多个工作表，则导入多个DataTable /// </summary> /// <param name="excelFilePath">Excel文件路径，为物理路径。</param> /// <param name="headerRowIndex">Excel表头行索引</param> /// <returns>DataSet</returns> public static DataSet ImportDataSetFromExcel(string excelFilePath, int headerRowIndex) { using (FileStream stream = System.IO.File.OpenRead(excelFilePath)) { return ImportDataSetFromExcel(stream, headerRowIndex); } } #endregion #region 导入补充 // 另外为导入补充一点知识： // 1、将Excel的列索引转换为列名，列索引从0开始，列名从A开始。如第0列为A，第1列为B... //代码 /// <summary> /// 将Excel的列索引转换为列名，列索引从0开始，列名从A开始。如第0列为A，第1列为B... /// </summary> /// <param name="index">列索引</param> /// <returns>列名，如第0列为A，第1列为B...</returns> public static string ConvertColumnIndexToColumnName(int index) { index = index + 1; int system = 26; char[] digArray = new char[100]; int i = 0; while (index > 0) { int mod = index % system; if (mod == 0) mod = system; digArray[i++] = (char)(mod - 1 + 'A'); index = (index - 1) / 26; } StringBuilder sb = new StringBuilder(i); for (int j = i - 1; j >= 0; j--) { sb.Append(digArray[j]); } return sb.ToString(); } // 2、当从Excel获取年月日时，会存在一定的问题，应该在一下代码中，可以想到存在的问题，所以我们可以写个方法封装一下： /// <summary> /// 转化日期 /// </summary> /// <param name="date">日期</param> /// <returns></returns> public static DateTime ConvertDate(string date) { DateTime dt = new DateTime(); string[] time = date.Split('-'); int year = Convert.ToInt32(time[2]); int month = Convert.ToInt32(time[0]); int day = Convert.ToInt32(time[1]); string years = Convert.ToString(year); string months = Convert.ToString(month); string days = Convert.ToString(day); if (months.Length == 4) { dt = Convert.ToDateTime(date); } else { string rq = ""; if (years.Length == 1) { years = "0" + years; } if (months.Length == 1) { months = "0" + months; } if (days.Length == 1) { days = "0" + days; } rq = "20" + years + "-" + months + "-" + days; dt = Convert.ToDateTime(rq); } return dt; } #endregion } }
