NPOI使用入门(一)【对Excel基本操作】
来源：互联网 发布：阿里云 员工收入构成 编辑：程序博客网 时间：2024/05/24 01:50
生活若剥去理想、梦想、幻想，那生命便只是一堆空架子。
Default.aspx内容:
<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Default.aspx.cs" Inherits="_Default" %><!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"><html xmlns="http://www.w3.org/1999/xhtml"><head runat="server"> <title></title></head><body> <form id="form1" runat="server"> <div> <asp:Button ID="btnExport" runat="server" Text="导出" OnClick="btnExport_Click" /> <asp:FileUpload ID="fileUpload" runat="server" /> <asp:Button ID="btnImport" runat="server" Text="导入" OnClick="btnImport_Click" /> <asp:GridView ID="gvPS" runat="server" AutoGenerateColumns="False"> <Columns> <asp:BoundField DataField="序号" HeaderText="序号" /> <asp:BoundField DataField="姓名" HeaderText="姓名" /> <asp:BoundField DataField="性别" HeaderText="性别" /> <asp:BoundField DataField="身份证" HeaderText="身份证" /> <asp:BoundField DataField="随机唯一标识码" HeaderText="随机唯一标识码" /> </Columns> </asp:GridView> </div> </form></body></html>Default.aspx.cs代码:
public partial class _Default : System.Web.UI.Page{ protected void Page_Load(object sender, EventArgs e) { //gvPS.DataSource = GetDataTable(); //gvPS.DataBind(); } protected void btnExport_Click(object sender, EventArgs e) { Dictionary<string, string> dic = new Dictionary<string, string>(); dic.Add("姓名", "姓名"); dic.Add("序号", "序号"); DataTable dt = GetDataTable(); ExcelOperate.ToExcel(dt,dic,"cs.xls"); } protected void btnImport_Click(object sender, EventArgs e) { string path = Server.MapPath("~/Temp/"); if (fileUpload.HasFile) { fileUpload.SaveAs(path + fileUpload.FileName); } DataTable dt = ExcelOperate.ToDataTable(path + fileUpload.FileName); gvPS.DataSource = dt; gvPS.DataBind(); } private DataTable GetDataTable() { DataTable dt = new DataTable(); dt.Columns.Add("序号", typeof(int)); dt.Columns.Add("姓名", typeof(string)); dt.Columns.Add("性别", typeof(string)); dt.Columns.Add("身份证", typeof(string)); dt.Columns.Add("随机唯一标识码", typeof(string)); dt.Rows.Add(1, "傅芷若", "女", "511702197407135024", Guid.NewGuid().ToString("N")); dt.Rows.Add(2, "顾岚彩", "女", "511702198304257904", Guid.NewGuid().ToString("N")); dt.Rows.Add(3, "韦问萍", "女", "511702198107283986", Guid.NewGuid().ToString("N")); dt.Rows.Add(4, "唐芷文", "女", "511702199001103486", Guid.NewGuid().ToString("N")); dt.Rows.Add(5, "姜娟巧", "女", "511702197301289703", Guid.NewGuid().ToString("N")); dt.Rows.Add(6, "郎芳芳", "女", "451025197709242781", Guid.NewGuid().ToString("N")); dt.Rows.Add(7, "罗忆梅", "女", "451025198607141183", Guid.NewGuid().ToString("N")); dt.Rows.Add(8, "廉清逸", "女", "451025197606178342", Guid.NewGuid().ToString("N")); dt.Rows.Add(9, "冯凌雪", "女", "45102519840920354X", Guid.NewGuid().ToString("N")); dt.Rows.Add(10, "柏娜兰", "女", "411525197204252845", Guid.NewGuid().ToString("N")); dt.Rows.Add(11, "卞涵韵", "女", "120000198806269580", Guid.NewGuid().ToString("N")); dt.Rows.Add(12, "岑安卉", "女", "120000198301207800", Guid.NewGuid().ToString("N")); return dt; }}Common.cs代码:
/// <summary> /// 操作Excel通用类 /// </summary> internal static class Common { /// <summary> /// 判断是否xls /// </summary> /// <param name="filePath"></param> /// <returns></returns> public static bool GetIsCompatible(string filePath) { return filePath.EndsWith(".xls", StringComparison.OrdinalIgnoreCase); } /// <summary> /// 创建工作薄 /// </summary> /// <param name="isCompatible"></param> /// <returns></returns> public static IWorkbook CreateWorkbook(bool isCompatible) { if (isCompatible) { return new HSSFWorkbook(); } else { return new XSSFWorkbook(); } } /// <summary> /// 创建工作薄(依据文件流) /// </summary> /// <param name="isCompatible"></param> /// <param name="stream"></param> /// <returns></returns> public static IWorkbook CreateWorkbook(bool isCompatible, dynamic stream) { if (isCompatible) { return new HSSFWorkbook(stream); } else { return new XSSFWorkbook(stream); } } /// <summary> /// 创建表格头单元格 /// </summary> /// <param name="sheet"></param> /// <returns></returns> public static ICellStyle GetCellStyle(IWorkbook workbook, bool isHeaderRow = false) { ICellStyle style = workbook.CreateCellStyle(); if (isHeaderRow) { style.FillPattern = FillPattern.SolidForeground; style.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.Grey25Percent.Index; IFont f = workbook.CreateFont(); f.Boldweight = (short)FontBoldWeight.Bold; style.SetFont(f); } style.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin; style.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin; style.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin; style.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin; return style; } /// <summary> /// 根据单元格内容重新设置列宽 /// </summary> /// <param name="sheet"></param> /// <param name="cell"></param> public static void ReSizeColumnWidth(ISheet sheet, ICell cell) { int cellLength = (Encoding.Default.GetBytes(cell.ToString()).Length + 5) * 256; const int maxLength = 255 * 256; if (cellLength > maxLength) { cellLength = maxLength; } int colWidth = sheet.GetColumnWidth(cell.ColumnIndex); if (colWidth < cellLength) { sheet.SetColumnWidth(cell.ColumnIndex, cellLength); } } /// <summary> /// 从工作表中生成DataTable /// </summary> /// <param name="sheet"></param> /// <param name="headerRowIndex"></param> /// <returns></returns> public static DataTable GetDataTableFromSheet(ISheet sheet, int headerRowIndex) { 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; break; } DataColumn column = new DataColumn(headerRow.GetCell(i).StringCellValue); table.Columns.Add(column); } for (int i = (headerRowIndex + 1); i <= sheet.LastRowNum; i++) { IRow row = sheet.GetRow(i); if (row != null) { DataRow dataRow = table.NewRow(); for (int j = row.FirstCellNum; j < cellCount; j++) { if (row.GetCell(j) != null) { dataRow[j] = row.GetCell(j).ToString(); } } table.Rows.Add(dataRow); } } return table; } }ExcelOperate.cs代码:
/// <summary> /// Excel操作类 /// </summary> public sealed class ExcelOperate { /// <summary> /// DataTable导出到excel /// </summary> /// <param name="dtSource">数据源</param> /// <param name="dicColAliasNames">导出的列重命名，可选</param> /// <param name="sFileName">文件名(包含后缀名)，可选</param> /// <param name="sSheetName">工作薄名，可选</param> public static void ToExcel(DataTable dtSource, IDictionary<string, string> dicColAliasNames = null, string sFileName = "新导出工作表.xls", string sSheetName = "Sheet") { 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(sFileName, Encoding.UTF8)); if (string.IsNullOrWhiteSpace(sFileName)) { sFileName = "新导出工作表.xls"; } if (string.IsNullOrWhiteSpace(sSheetName)) { sSheetName = "Sheet"; } bool isCompatible = Common.GetIsCompatible(sFileName); IWorkbook workbook = Common.CreateWorkbook(isCompatible); ICellStyle headerCellStyle = Common.GetCellStyle(workbook, true); ICellStyle cellStyle = Common.GetCellStyle(workbook); ISheet sheet = workbook.CreateSheet(sSheetName); int rowIndex = 1; int colIndex = 1; int rowIndexMax = 1048575; int colIndexMan = 16383; if (isCompatible) { rowIndexMax = 65535; colIndexMan = 255; } #region 创建列头 IRow headerRow = sheet.CreateRow(0); if (dicColAliasNames == null || dicColAliasNames.Count == 0) { foreach (DataColumn column in dtSource.Columns) { if (colIndex < colIndexMan) { ICell headerCell = headerRow.CreateCell(column.Ordinal); headerCell.SetCellValue(column.ColumnName); headerCell.CellStyle = headerCellStyle; sheet.AutoSizeColumn(headerCell.ColumnIndex); colIndex++; } } } else { int i = 0; foreach (var dic in dicColAliasNames) { if (i < colIndexMan) { ICell headerCell = headerRow.CreateCell(i); headerCell.SetCellValue(dic.Value); headerCell.CellStyle = headerCellStyle; sheet.AutoSizeColumn(headerCell.ColumnIndex); i++; } } } #endregion #region 填充内容 foreach (DataRow row in dtSource.Rows) { if (rowIndex % rowIndexMax == 0) { sheet = workbook.CreateSheet(sSheetName + ((int)rowIndex / rowIndexMax).ToString()); } IRow dataRow = sheet.CreateRow(rowIndex); if (dicColAliasNames == null || dicColAliasNames.Count == 0) { foreach (DataColumn column in dtSource.Columns) { ICell cell = dataRow.CreateCell(column.Ordinal); string rowValue = (row[column] ?? "").ToString(); switch (column.DataType.ToString()) { case "System.DateTime"://日期类型 cell.SetCellValue(rowValue == "" ? rowValue : DateTime.Parse(rowValue).ToShortDateString()); break; default: cell.SetCellValue(rowValue); break; } cell.CellStyle = cellStyle; Common.ReSizeColumnWidth(sheet, cell); } } else { int i = 0; foreach (var dic in dicColAliasNames) { ICell cell = dataRow.CreateCell(i); string rowValue = (row[dtSource.Columns[dic.Key].Ordinal] ?? "").ToString(); switch (dtSource.Columns[dic.Key].DataType.ToString()) { case "System.DateTime"://日期类型 cell.SetCellValue(rowValue == "" ? rowValue : DateTime.Parse(rowValue).ToShortDateString()); break; default: cell.SetCellValue(rowValue); break; } cell.CellStyle = cellStyle; Common.ReSizeColumnWidth(sheet, cell); i++; } } rowIndex++; } #endregion using (MemoryStream ms = new MemoryStream()) { workbook.Write(ms); ms.Dispose(); sheet = null; workbook = null; curContext.Response.BinaryWrite(ms.GetBuffer()); curContext.Response.End(); } } /// <summary> /// Excel导入到DataTable /// </summary> /// <param name="excelFilePath">Excel文件路径，为物理路径</param> /// <param name="sSheetName">Excel工作表名称，可选</param> /// <param name="headerRowIndex">Excel表头行索引，可选</param> /// <returns>DataTable</returns> public static DataTable ToDataTable(string excelFilePath, string sSheetName = "Sheet1", int headerRowIndex = 0) { if (string.IsNullOrEmpty(excelFilePath)) { return null; } if (string.IsNullOrWhiteSpace(sSheetName)) { sSheetName = "Sheet1"; } using (FileStream stream = File.OpenRead(excelFilePath)) { bool isCompatible = Common.GetIsCompatible(excelFilePath); IWorkbook workbook = Common.CreateWorkbook(isCompatible, stream); ISheet sheet = workbook.GetSheet(sSheetName); DataTable table = Common.GetDataTableFromSheet(sheet, headerRowIndex); stream.Close(); workbook = null; sheet = null; ClearNullRow(table); return table; } } /// <summary> /// Excel导入到DataSet，如果有多个工作表，则导入多个DataTable /// </summary> /// <param name="excelFilePath">Excel文件路径，为物理路径</param> /// <param name="headerRowIndex">Excel表头行索引，可选</param> /// <returns>DataSet</returns> public static DataSet ToDataSet(string excelFilePath, int headerRowIndex = 0) { if (string.IsNullOrEmpty(excelFilePath)) { return null; } using (FileStream stream = File.OpenRead(excelFilePath)) { DataSet ds = new DataSet(); bool isCompatible = Common.GetIsCompatible(excelFilePath); IWorkbook workbook = Common.CreateWorkbook(isCompatible, stream); for (int i = 0; i < workbook.NumberOfSheets; i++) { ISheet sheet = workbook.GetSheetAt(i); DataTable table = Common.GetDataTableFromSheet(sheet, headerRowIndex); ClearNullRow(table); ds.Tables.Add(table); } stream.Close(); workbook = null; return ds; } } /// <summary> /// 清空DataTable中的空行 /// </summary> /// <param name="dtSource"></param> private static void ClearNullRow(DataTable dtSource) { for (int i = dtSource.Rows.Count - 1; i > 0; i--) { bool isNull = true; for (int j = 0; j < dtSource.Columns.Count; j++) { if (dtSource.Rows[i][j] != null) { if (dtSource.Rows[i][j].ToString() != "") { isNull = false; break; } } } if (isNull) { dtSource.Rows[i].Delete(); } } } }运行结果如图:
0 1
- NPOI使用入门(一)【对Excel基本操作】
- 使用NPOI操作excel
- 使用C#对Excel进行读写操作（NPOI）以及使用EF对Mysql进行CURD
- 使用NPOI操作Excel导入导出数据
- C#中使用NPOI操作excel
- 使用第三方控件NPOI操作excel
- C# 使用NPOI操作Excel文件
- C#使用NPOI操作Excel错误解决
- .Net操作excel（使用NPOI）
- 使用 npoi 操作excel ”npoi“可从“http://www.codeplex.com/npoi”下载
- Excel操作——使用Com操作和NPOI操作
- NPOI 进行EXCEL操作
- 用NPOI操作Excel
- C# NPOI 操作excel
- NPOI操作EXCEL
- NPOI操作Excel
- C# NPOI 操作excel
- NPOI 操作Excel
- 二叉搜索树的后序遍历序列
- web性能测试：apache benchmark(ab)
- 文件操作—文件流
- sql 1
- 那些找工作的日子
- NPOI使用入门(一)【对Excel基本操作】
- 拓扑排序
- 强大的grep 命令
- Python 运算符用法总结
- XML在JAVA项目中的作用
- C 语言字符串 将一行字符串I am a student。逆序输出student。a am I;
- Javascript继承机制的设计思想
- java 读取Excel处理并在网页下载处理后的新文件
- 盒子模型详解
