c# NPOI 操作 Excel 帮助类

原创于 2023-09-08 17:25:30 发布 · 371 阅读
1 ·
CC 4.0 BY-SA版权
文章标签：
#c# #excel #windows
奇葩问题专栏收录该内容
18 篇文章
订阅专栏
public class ExcelHelp
{
    /// <summary>
    /// 
    /// </summary>
    /// <typeparam name="T"></typeparam>
    /// <param name="list">数据列表</param>
    /// <param name="file">文件地址</param>
    /// <param name="sheetnum">sheet数</param>
    /// <param name="titles">表头名称列表</param>
    /// <param name="startNumber">开始行数</param>
    /// <exception cref="Exception"></exception>
    public void ListToExcel<T>(List<T> list, string file, string sevefile, int sheetnum, List<string> titles, int startNumber)
    {
        DataTable dt = new DataTable();
        dt = ListToDataTable<T>(list, titles);
        // Export(dt, file);
        string endText = "制表：                  审核：                     品质会签：                      日期：";
        ExportToExcel(dt, 4, "SMT料站表   贴装台1", endText, file);
        List<string> strings = new List<string>() { "q", "w", "e", "R" };
        OtherToExcel(file, strings, 1);

    }
    /// <summary>
    /// 写入数组，写入行号
    /// </summary>
    /// <param name="data"></param>
    /// <param name="lineNumber"></param>
    public void OtherToExcel(string file, List<string> data, int lineNumber)
    {
        HSSFWorkbook workbook;
        ISheet sheet;
        using (FileStream stream = System.IO.File.OpenRead(file))
        {
            workbook = new HSSFWorkbook(stream);
            sheet = workbook.GetSheetAt(0);
        }
        //格式
        var dateStyle = workbook.CreateCellStyle();
        var format = workbook.CreateDataFormat();
        dateStyle.DataFormat = format.GetFormat("yyyy-mm-dd");//日期格式

        var headerRow = sheet.CreateRow(lineNumber);
        //CellStyle
        ICellStyle headStyle = workbook.CreateCellStyle();
        headStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;// 左右居中    
        headStyle.VerticalAlignment = VerticalAlignment.Center;// 上下居中 
                                                               //定义font
        IFont font = workbook.CreateFont();
        font.FontHeightInPoints = 10;
        font.Boldweight = 700;
        headStyle.SetFont(font);

        foreach (string column in data)
        {
            headerRow.CreateCell(data.IndexOf(column)).SetCellValue(column);
            headerRow.GetCell(data.IndexOf(column)).CellStyle = headStyle;
            // sheet.SetColumnWidth(data.IndexOf(column), (arrColWidth[column.Ordinal] + 1) * 256);//设置列宽
        }
        using (FileStream fs = new FileStream(file, FileMode.Create, FileAccess.Write))
        {
            workbook.Write(fs);
        }
    }


    /// <summary>
    /// 获取 Sheet 表数据
    /// </summary>
    /// <param name="Sheet"></param>
    public void GetSheetData(ISheet Sheet)
    {
        IRow row;
        // 1. 获取行数
        var rowCount = Sheet.LastRowNum;

        // 从第四行(下标为3)开始获取数据，前三行是表头
        // 如果从第一行开始，则i=0就可以了
        for (int i = 5; i <= rowCount; i++)
        {
            //var dataTable = new DataTable_Model();
            // 获取具体行
            row = Sheet.GetRow(i);
            if (row != null)
            {
                // 2. 获取行对应的列数
                var column = row.LastCellNum;
                for (int j = 0; j < column; j++)
                {
                    // 3. 获取某行某列对应的单元格数据
                    var cellValue = row.GetCell(j).ToString();
                    // 4. 输出单元格数据        
                    Console.Write(cellValue + " ");
                }
                // 换行
                Console.WriteLine();
            }
        }
    }
    /// <summary>
    /// 泛型列表List转换为DataTable
    /// </summary>
    /// <typeparam name="T">泛型实体</typeparam>
    /// <param name="list">要转换的列表</param>
    /// <param name="titles">标题</param>
    /// <returns></returns>
    public DataTable ListToDataTable<T>(List<T> list, List<string> titles)
    {
        DataTable dt = new DataTable();
        Type listType = typeof(T);
        PropertyInfo[] properties = listType.GetProperties();
        //标题行
        if (titles != null && properties.Length == titles.Count())
        {
            for (int i = 0; i < properties.Length; i++)
            {
                PropertyInfo property = properties[i];
                dt.Columns.Add(new DataColumn(titles[i], property.PropertyType));
            }
        }
        else
        {
            for (int i = 0; i < properties.Length; i++)
            {
                PropertyInfo property = properties[i];
                dt.Columns.Add(new DataColumn(property.Name, property.PropertyType));
            }
        }
        //内容行
        foreach (T item in list)
        {
            DataRow dr = dt.NewRow();
            for (int i = 0; i < dt.Columns.Count; i++)
            {
                dr[i] = properties[i].GetValue(item, null);
            }
            dt.Rows.Add(dr);
        }
        return dt;
    }
    /// <summary>
    /// 生成表格
    /// </summary>
    /// <param name="dt">DataTable</param>
    /// <param name="startLineNumber">从第几行开始  数字需要-1  </param>
    /// <param name="HeaderText">表头文本</param>
    /// <param name="EndText">底部文本</param>
    /// <param name="fileName">文件名称</param>
    public void ExportToExcel(DataTable dt, int startLineNumber, string HeaderText, string EndText, string fileName)
    {
        var workbook = new HSSFWorkbook();
        ISheet sheet = workbook.CreateSheet(string.IsNullOrWhiteSpace(dt.TableName) ? "Sheet1" : dt.TableName);

        //右击文件“属性”信息
        #region 文件属性信息
        {
            var dsi = PropertySetFactory.CreateDocumentSummaryInformation();
            dsi.Company = "NPOI";
            workbook.DocumentSummaryInformation = dsi;

            SummaryInformation si = PropertySetFactory.CreateSummaryInformation();
            si.Author = "文件作者信息";
            si.ApplicationName = "创建程序信息";
            si.LastAuthor = "最后保存者信息";
            si.Comments = "作者信息";
            si.Title = "标题信息";
            si.Subject = "主题信息";
            si.CreateDateTime = DateTime.Now;
            workbook.SummaryInformation = si;
        }
        #endregion

        //格式
        var dateStyle = workbook.CreateCellStyle();
        var format = workbook.CreateDataFormat();
        dateStyle.DataFormat = format.GetFormat("yyyy-mm-dd");//日期格式

        //取得列宽
        var arrColWidth = new int[dt.Columns.Count];
        foreach (DataColumn item in dt.Columns)
        {
            arrColWidth[item.Ordinal] = Encoding.GetEncoding(936).GetBytes(item.ColumnName.ToString()).Length;
        }
        for (var i = 0; i < dt.Rows.Count; i++)
        {
            for (var j = 0; j < dt.Columns.Count; j++)
            {
                int intTemp = Encoding.GetEncoding(936).GetBytes(dt.Rows[i][j].ToString()).Length;
                if (intTemp > arrColWidth[j])
                {
                    arrColWidth[j] = intTemp + 10;
                }
            }
        }
        int rowIndex = 0 + startLineNumber;
        foreach (DataRow row in dt.Rows)
        {
            #region 表头 列头
            if (rowIndex == 65535 || rowIndex == startLineNumber)
            {
                if (rowIndex != startLineNumber)
                {
                    sheet = workbook.CreateSheet();//超过65535行，则新建一个Sheet
                }

                #region 表头及样式
                {
                    var headerRow = sheet.CreateRow(0);
                    headerRow.HeightInPoints = 25;
                    headerRow.CreateCell(0).SetCellValue(HeaderText);
                    //CellStyle
                    ICellStyle headStyle = workbook.CreateCellStyle();
                    headStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;// 左右居中    
                    headStyle.VerticalAlignment = VerticalAlignment.Center;// 上下居中 
                                                                           // 设置单元格的背景颜色（单元格的样式会覆盖列或行的样式）    
                    headStyle.FillForegroundColor = (short)11;
                    //定义font
                    IFont font = workbook.CreateFont();
                    font.FontHeightInPoints = 20;
                    font.Boldweight = 700;
                    headStyle.SetFont(font);
                    headerRow.GetCell(0).CellStyle = headStyle;
                    sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(0, 0, 0, dt.Columns.Count - 1));//合并区域
                }
                #endregion


                #region 列头及样式
                {
                    var headerRow = sheet.CreateRow(rowIndex);
                    //CellStyle
                    ICellStyle headStyle = workbook.CreateCellStyle();
                    headStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;// 左右居中    
                    headStyle.VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment.Center;// 上下居中 
                                                                           //定义font
                    IFont font = workbook.CreateFont();
                    font.FontHeightInPoints = 10;
                    font.Boldweight = 700;
                    headStyle.SetFont(font);

                    foreach (DataColumn column in dt.Columns)
                    {
                        headerRow.CreateCell(column.Ordinal).SetCellValue(column.ColumnName);
                        headerRow.GetCell(column.Ordinal).CellStyle = headStyle;
                        sheet.SetColumnWidth(column.Ordinal, (arrColWidth[column.Ordinal] + 1) * 256);//设置列宽
                    }
                }
                #endregion

                rowIndex++;//数据行RowIndex为2（表头和列头个占一行）
            }
            #endregion


            #region 内容
            var dataRow = sheet.CreateRow(rowIndex);
            foreach (DataColumn column in dt.Columns)
            {
                var newCell = dataRow.CreateCell(column.Ordinal);

                string drValue = row[column].ToString();

                switch (column.DataType.ToString())
                {
                    case "System.String"://字符串类型
                        newCell.SetCellValue(drValue);
                        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("");//设置单元格公式：newCell.SetCellFormula("SUM($B0:$D0)")
                        break;
                }

            }
            #endregion
            rowIndex++;
        }
        #region 表尾及样式
        if (!string.IsNullOrEmpty(EndText))
        {

            {
                var headerRow = sheet.CreateRow(rowIndex);
                headerRow.HeightInPoints = 25;
                headerRow.CreateCell(0).SetCellValue(EndText);
                //CellStyle
                ICellStyle headStyle = workbook.CreateCellStyle();
                headStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;// 左右居中    
                headStyle.VerticalAlignment = VerticalAlignment.Center;// 上下居中 
                                                                       // 设置单元格的背景颜色（单元格的样式会覆盖列或行的样式）    
                headStyle.FillForegroundColor = (short)11;
                //定义font
                IFont font = workbook.CreateFont();
                font.FontHeightInPoints = 10;
                font.Boldweight = 700;
                headStyle.SetFont(font);
                headerRow.GetCell(0).CellStyle = headStyle;
                sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(rowIndex, rowIndex, 0, dt.Columns.Count - 1));//合并区域
            }
        }
        #endregion
        using (FileStream fs = new FileStream(fileName, FileMode.Create, FileAccess.Write))
        {
            workbook.Write(fs);
        }
    }
}