NPOI使用入门(一)【对Excel基本操作】

最新推荐文章于 2020-12-19 17:27:01 发布

.NET跨平台

最新推荐文章于 2020-12-19 17:27:01 发布

阅读量2.4k

点赞数 2

分类专栏： NPOI.NET和EPPlus框架文章标签： excel NPOI

本文链接：https://blog.youkuaiyun.com/WuLex/article/details/52904693

版权

NPOI.NET和EPPlus框架专栏收录该内容

28 篇文章

订阅专栏

生活若剥去理想、梦想、幻想，那生命便只是一堆空架子。

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();
                }
            }
        }
    }

运行结果如图:

这里写图片描述