一切福田，不離方寸，從心而覓，感無不通。

Daily Archives: 2023年4月25日

C#如何使用NPOI设置Excel下拉选项

这篇文章给大家分享的是有关C#如何使用NPOI设置Excel下拉选项的内容。小编觉得挺实用的，因此分享给大家做个参考，一起跟随小编过来看看吧。 第一种 · 直接设置下拉值，不超过255个字符（优点：逻辑简单 ；缺点：有字符限制） · 适用于下拉值为固定值，例如：状态、性别等 方法块：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
public static void SetCellDropdownList(ISheet sheet, int firstcol, int lastcol, string[] vals)
{
     //设置生成下拉框的行和列
     var cellRegions = new CellRangeAddressList(1, 65535, firstcol, lastcol);
     //设置 下拉框内容
     DVConstraint constraint = DVConstraint.CreateExplicitListConstraint(vals);
     //绑定下拉框和作用区域，并设置错误提示信息
     HSSFDataValidation dataValidate = new HSSFDataValidation(cellRegions, constraint);
     dataValidate.CreateErrorBox("输入不合法", "请输入或选择下拉列表中的值。");
     dataValidate.ShowPromptBox = true;
     sheet.AddValidationData(dataValidate);
}

调用：

1
2
3
HSSFWorkbook workbook = new HSSFWorkbook();
ISheet sheet = workbook.CreateSheet("sheet1");
ExcelHelper.SetCellDropdownList(sheet, 1, 1, new List<string>() { "男", "女", "保密" }.ToArray());

  第二种 &middot; 通过绑定值到sheet中设置下拉 &middot; 适用于数据较多，或灵活控制的值，例如：城市区域、数据表信息等。 方法块：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
public static void SetCellDropdownList(HSSFWorkbook workbook, ISheet sheet, string name, int firstcol, int lastcol, string[] vals, int sheetindex = 1)
{
      //先创建一个Sheet专门用于存储下拉项的值
      ISheet sheet2 = workbook.CreateSheet(name);
      //隐藏
      workbook.SetSheetHidden(sheetindex, true);
      int index = 0;
      foreach (var item in vals)
      {
          sheet2.CreateRow(index).CreateCell(0).SetCellValue(item);
          index++;
      }
      //创建的下拉项的区域：
      var rangeName = name + "Range";
      IName range = workbook.CreateName();
      range.RefersToFormula = name + "!$A$1:$A$" + index;
      range.NameName = rangeName;
      CellRangeAddressList regions = new CellRangeAddressList(0, 65535, firstcol, lastcol);
      DVConstraint constraint = DVConstraint.CreateFormulaListConstraint(rangeName);
      HSSFDataValidation dataValidate = new HSSFDataValidation(regions, constraint);
      dataValidate.CreateErrorBox("输入不合法", "请输入或选择下拉列表中的值。");
      dataValidate.ShowPromptBox = true;
      sheet.AddValidationData(dataValidate);
}

调用：

1
2
3
4
HSSFWorkbook workbook = new HSSFWorkbook();
ISheet sheet = workbook.CreateSheet("sheet1");
var roomTypeList = GetRoomTypeNameList();
ExcelHelper.SetCellDropdownList(workbook, sheet, "RoomTypeDictionary", 1, 1, roomTypeList.ToArray());

  另外，延伸联动下拉（直接贴源码了） 方法块：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
private void SetCityCellDropdownList(HSSFWorkbook workbook, ISheet sheet, string dictionaryName, int citycol, int areacol, int sheetIndex)
{
    var citylist = GetCityList();
    int citycount = citylist.Count;
    ISheet sheet2 = workbook.CreateSheet(dictionaryName);
    //隐藏
    workbook.SetSheetHidden(sheetIndex, true);
    #region 城市区域数据构造
    //城市
    int rowIndex = 0;
    foreach (var item in citylist)
    {
        IRow row = sheet2.CreateRow(rowIndex);
        row.CreateCell(0).SetCellValue(item.Name);
        rowIndex++;
    }
    //区域
    int n_rowIndex = 0;
    foreach (var item in citylist)
    {
        int areaIndex = 0;
        foreach (var area in item.AreaList)
        {
            IRow row = sheet2.GetRow(areaIndex);
            if (row == null)
            {
                row = sheet2.CreateRow(areaIndex);
            }
            row.CreateCell(n_rowIndex + 1).SetCellValue(area.Name);
            areaIndex++;
        }
        n_rowIndex++;
    }
    #endregion
    #region 设置数据字段范围
    //定义城市
    int columnIndex = 1;
    IName range_Country = workbook.CreateName();
    range_Country.RefersToFormula = string.Format("{0}!${1}$1:${1}${2}", dictionaryName, GetExcelColumnName(columnIndex), citycount);
    range_Country.NameName = "城市";
    //定义区
    foreach (var item in citylist)
    {
        int areacount = item.AreaList.Count;
        columnIndex++;
        IName range_area = workbook.CreateName();
        range_area.RefersToFormula = string.Format("{0}!${1}$1:${1}${2}", dictionaryName, GetExcelColumnName(columnIndex), areacount);
        range_area.NameName = item.Name;
    }
    //城市列表下拉绑定
    ExcelHelper.SetCellDropdownList(sheet, 1, 65535, citycol, citycol, "城市");
    //第二列，跟随第一列联动
    string colName = GetExcelColumnName(areacol);
    for (int j = 1; j < 500; j++)
    {
        ExcelHelper.SetCellDropdownList(sheet, j, j, areacol, areacol, string.Format("INDIRECT(${0}${1})", colName, j + 1));
    }
    #endregion
}
private string GetExcelColumnName(int columnNumber)
{
    int dividend = columnNumber;
    string columnName = String.Empty;
    int modulo;
    while (dividend > 0)
    {
        modulo = (dividend - 1) % 26;
        columnName = Convert.ToChar(65 + modulo).ToString() + columnName;
        dividend = (int)((dividend - modulo) / 26);
    }
    return columnName;
}

1
2
3
4
5
6
7
8
public static void SetCellDropdownList(ISheet sheet, int firstRow, int lastRow, int firstCol, int lastCol, string name)
{
    CellRangeAddressList regions = new CellRangeAddressList(firstRow, lastRow, firstCol, lastCol);
    DVConstraint constraint = DVConstraint.CreateFormulaListConstraint(name);
    HSSFDataValidation dataValidate = new HSSFDataValidation(regions, constraint);
    dataValidate.CreateErrorBox("输入不合法", "请输入或选择下拉列表中的值。");
    sheet.AddValidationData(dataValidate);
}

调用：

1
2
3
HSSFWorkbook workbook = new HSSFWorkbook();
ISheet sheet = workbook.CreateSheet("sheet1");
SetCityCellDropdownList(workbook, sheet, "CityDictionary", 1, 2, 1);

  from:https://www.yisu.com/zixun/688759.html

龙生   25 Apr 2023
View Details

C# NPOI XSSFWorkbook 添加下拉框

1、添加下拉框，并开启列值校验

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
/// <summary>
/// 添加下拉框，并开启列值校验
/// </summary>
public static void SetCellDropdownList(XSSFSheet sheet, int firstcol, int lastcol, List<string> vals)
{
    //设置 下拉框内容
    var dvHelper = new XSSFDataValidationHelper(sheet);
    var dvConstraint = (XSSFDataValidationConstraint)dvHelper.CreateExplicitListConstraint(vals.ToArray());
    //设置下拉框对应的行范围、列范围，行和列都是从0开始
    var addressList = new CellRangeAddressList(0, 65535, firstcol, lastcol);
    var validation = (XSSFDataValidation)dvHelper.CreateValidation(dvConstraint, addressList);
    //开启下拉框
    validation.ShowPromptBox = true;
    //开启错误提醒
    validation.ShowErrorBox = true;
    sheet.AddValidationData(validation);
}

2、调用方

1
2
3
4
5
6
7
8
public static void main()
{
    var workbook = new XSSFWorkbook();
    XSSFSheet sheet = (XSSFSheet)workbook.CreateSheet("sheet1");
    var validateList=new List<string>(){"选项1","选项2","选项3"};
    SetCellDropdownList(sheet, 2, 2, validateList)
}

  from:https://blog.csdn.net/a304954732/article/details/127220912

龙生   25 Apr 2023
View Details

C# 使用NPOI导出Excel，首行冻结，添加筛选，填充颜色，列宽自适应

使用NuGet安装NPOI，添加以下命名空间

1
2
3
4
using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using NPOI.SS.Util;
using NPOI.XSSF.UserModel;

  添加类 ExcelExporter

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
public static class ExcelExporter
{
    /// <summary>
    /// DataTable导出Excel
    /// </summary>
    /// <param name="dataTable">数据源</param>
    /// <param name="fileName">保存的文件名</param>
    /// <param name="sheetName">表名</param>
    public static void ExportToExcel(this DataTable dataTable, string fileName, string sheetName = "Sheet1")
    {
        if (dataTable == null || dataTable.Rows.Count == 0)
            throw new Exception("No data to export");
 
        ISheet sheet = null;
        IWorkbook workbook = null;
        try
        {
            using (FileStream fs = new FileStream(fileName, FileMode.OpenOrCreate, FileAccess.ReadWrite))
            {
                if (fileName.IndexOf(".xlsx") > 0) // 2007版本
                    workbook = new XSSFWorkbook();
                else if (fileName.IndexOf(".xls") > 0) // 2003版本
                    workbook = new HSSFWorkbook();
                else
                    throw new NotSupportedException("Not supported file extension");
 
                if (string.IsNullOrEmpty(sheetName))
                    sheetName = "Sheet1";
 
                sheet = workbook.CreateSheet(sheetName);
 
                ICellStyle headerStyle = workbook.CreateCellStyle(); //首行填充黄色
                headerStyle.FillForegroundColor = IndexedColors.Yellow.Index;
                headerStyle.FillPattern = FillPattern.SolidForeground;
 
                IRow row = sheet.CreateRow(0);
                for (int columnIndex = 0; columnIndex < dataTable.Columns.Count; columnIndex++)
                {
                    var cell = row.CreateCell(columnIndex);
                    cell.CellStyle = headerStyle;
                    cell.SetCellValue(dataTable.Columns[columnIndex].ColumnName); //设置列名
                }
 
                for (int rowIndex = 0; rowIndex < dataTable.Rows.Count; rowIndex++)
                {
                    row = sheet.CreateRow(rowIndex + 1);
                    for (int columnIndex = 0; columnIndex < dataTable.Columns.Count; columnIndex++)
                    {
                        row.CreateCell(columnIndex).SetCellValue(Convert.ToString(dataTable.Rows[rowIndex][columnIndex]));
                    }
                }
 
                for (int columnIndex = 0; columnIndex < dataTable.Columns.Count; columnIndex++)
                {
                    sheet.AutoSizeColumn(columnIndex); //自适应宽度
                }
 
                sheet.SetAutoFilter(new CellRangeAddress(0, 0, 0, dataTable.Columns.Count - 1)); //首行筛选
                sheet.CreateFreezePane(dataTable.Columns.Count, 1); //首行冻结
 
                workbook.Write(fs); //写入到excel
            }
        }
        finally
        {
            if (workbook != null)
                workbook.Close();
        }
    }
}

使用时传入文件路径，即可将DataTable导出到指定位置。 如要导出集合类数据IEnumerable<T>，使用反射转成DataTable即可。方法大同小异，这里就不写了   from:https://www.qyyshop.com/info/813140.html

龙生   25 Apr 2023
View Details

NPOI自适应列宽

当本地没有安装Excel，但是又想导出Excel，采用Office插件是行不通的，NPOI是导出Excel的一个开源的插件。在导出Excel后，为了方便阅读，可以才采用自适应列宽的方式使得单元格的宽度和文本的宽度接近。NPOI中工作簿ISheet有自适应列宽的方法，但是其效果列宽还是比单元中文字的宽度稍微大一点。此时我们可以自己计算其宽度，自定义列宽。一下这种方式是支持中英文以及数字的。

龙生   25 Apr 2023
View Details

C#使用NPOI设置Excel单元格背景颜色(xls,xlsx)

每次用到这个都要去百度，这里记录一下，xls和xlsx两种格式设置背景颜色的区别，这里省略了前面读取模板或者创建excel的过程： xls:

1
2
3
4
5
6
7
8
9
10
11
ICellStyle style = workbook.CreateCellStyle();
//使用NPOI已经有的颜色创建
style.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.Yellow.Index;
style.FillPattern = FillPattern.SolidForeground;
//没有的颜色，使用RGB值进行创建
//这里要自定义一个颜色板覆盖掉原来的index，这里覆盖48号位置上的
HSSFPalette palette = ((HSSFWorkbook)workbook).GetCustomPalette();
palette.SetColorAtIndex(48, 0, 112, 192);
style.FillForegroundColor = palette.FindColor(0, 112, 192).Indexed;

  xlsx:

1
2
3
4
5
6
7
8
9
10
ICellStyle style = workbook.CreateCellStyle();
//使用NPOI已经有的颜色创建（这里跟xls一样，不知道为什么可以通用）
style.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.Yellow.Index;
style.FillPattern = FillPattern.SolidForeground;
//使用没有的颜色，这里和xls不一样，不需要覆盖掉原来的色板
style.FillForegroundColor = 0;
style.FillPattern = FillPattern.SolidForeground;
((XSSFColor)style.FillForegroundColorColor).SetRgb(new byte[] { 0, 176, 240 });

  from:https://blog.csdn.net/qq_34452824/article/details/119379559

龙生   25 Apr 2023
View Details

NPOI生产.xlsx文件件时，在使用book.Write(ms);后，会关闭流，这样导致再次使用Respons输出流的时候就出错了。 我看到一些网友提供的解决办法是：

NPOI生产.xlsx文件件时，在使用book.Write(ms);后，会关闭流，这样导致再次使用Respons输出流的时候就出错了。 我看到一些网友提供的解决办法是：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
public class NPOIMemoryStream : MemoryStream
{
    /// <summary>
    /// 获取流是否关闭
    /// </summary>
    public bool IsColse
    {
        get;
        private set;
    }
 
    public NPOIMemoryStream(bool colse = false)
    {
        IsColse = colse;
    }
 
    public override void Close()
    {
        if (IsColse)
        {
            base.Close();
        }
 
    }
}

使用自定义流代替内存流，这样就可以保证流不会被自定关闭了   from:https://www.cnblogs.com/caoyc/p/6215210.html

龙生   25 Apr 2023
View Details

C#使用IWorkbook 提示 Cannot access a closed Stream

原本使用 IWorkbook 中的HSSFWorkbook，代表 xls文件，最大导出行为65536，当数据量过大时，使用XSSFWorkbook,代表Xlsx文件，提供更多的行与列，在使用XSSFWorkbook写入MemoryStream时，提示Cannot access a closed Stream，是因为在使用book.Write(ms);后，会关闭流； 百度过后，使用自定义流代替内存流，这样就可以保证流不会被自定关闭了 解决方案可参考https://www.cnblogs.com/caoyc/p/6215210.html文章 ———————————————— 版权声明：本文为CSDN博主「SupperDuan」的原创文章，遵循CC 4.0 BY-SA版权协议，转载请附上原文出处链接及本声明。 原文链接：https://blog.csdn.net/dpq980522/article/details/129643588

龙生   25 Apr 2023
View Details

通过NPOI从内存流中创建EXCEL

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
        XSSFWorkbook workbook = new XSSFWorkbook(); //创建工作簿
        XSSFSheet sheet = (XSSFSheet)workbook.CreateSheet("test"); //创建表单
        XSSFRow headerRow = (XSSFRow)sheet.CreateRow(); //创建row
        headerRow.CreateCell().SetCellValue();
        headerRow.CreateCell().SetCellValue();
 
        using (MemoryStream ms = new MemoryStream())
        {
            workbook.Write(ms);
            ms.Flush();
            //Response.Write(ms);
            Response.BinaryWrite(ms.ToArray());
            Response.ContentEncoding = System.Text.Encoding.GetEncoding("GB2312");
            Response.AppendHeader("content-disposition", "attachment;filename=" + HttpUtility.UrlEncode("test", System.Text.Encoding.UTF8) + ".xlsx");
            Response.End();
        }

  from:https://www.bbsmax.com/A/xl56OW01dr/

龙生   25 Apr 2023
View Details

.NET 使用NPOI生成Excel

1、在控制器获取要导出的数据 2、通过注入 IWebHostEnvironment 服务对象来获取根目录的物理路径

1
2
3
4
5
6
7
8
9
10
11
12
13
14
    public FileResult ToExcel()
    {
        //导出数据
        DataTable dt = new DataTable("Table");
        //获取路径
        var strPath = Path.Combine(_webhostenvironment.ContentRootPath, AppConfigurtaion.Configuration["AppSettings:upload"]);
        string name = DateTime.Now.ToString("yyyyMMdd") + ".xls";
        byte[] data = null;
        using (MemoryStream ms = ComFun.Export_ToExcel(dt, strPath, colname))
        {
            data = ms.GetBuffer();
        }
        return File(data, "application/vnd.ms-excel", name);
    }

  3、引用using NPOI.SS.UserModel；using NPOI.HSSF.UserModel; 4、获取导出excel所需模板 5、创建HSSWorkbook的对象用来调用NPOI文件，如果你导入的数据时没有调用ICellStyle类的对象设置的样式话，那么出来的数据在Excel是不加任何的样式。也就是说数据过多的话，数据会挤在一排，而且超出会隐藏。所以要设置ICellStyle的属性才行。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
    public static MemoryStream Export_ToExcel(DataTable dt, string strpath, string[] colname)
    {
        //获取导出模板
        string fileName = System.IO.Path.GetDirectoryName(strpath) + @"\resource\Excel.xls";
        FileStream fs = new FileStream(fileName, FileMode.OpenOrCreate);
        HSSFWorkbook hssfworkbook = new HSSFWorkbook(fs);
        ISheet sheet = hssfworkbook.GetSheetAt(0);
        ICellStyle cellStyle = hssfworkbook.CreateCellStyle();
        IRow row = sheet.CreateRow(0);
        for (int i = 0; i < colname.Length; i++)
        {
            string name = colname[i];
            ICell cell = row.CreateCell(i);  //在第一行中创建单元格
            cell.CellStyle = cellStyle;//单元格添加样式
            cell.SetCellValue(name);//循环往第二行的单元格中添加数据  
        }
        if (dt != null && dt.Rows.Count > 0)
        {
            for (int i = 0; i < dt.Rows.Count; i++)
            {
                IRow rows = sheet.CreateRow(i + 1);
                for (int j = 0; j < colname.Length; j++)
                {
                    string name = colname[j];
                    string columnval = dt.Rows[i][name].ToString();
                    ICell cell = rows.CreateCell(j);  //在第二行中创建单元格
                    cell.CellStyle = cellStyle;//单元格添加样式
                    cell.SetCellValue(columnval);//循环往第二行的单元格中添加数据
                }
            }
 
        }
        MemoryStream file = new MemoryStream();
        hssfworkbook.Write(file);
        return file;
    }

———————————————— 版权声明：本文为CSDN博主「归-途」的原创文章，遵循CC 4.0 BY-SA版权协议，转载请附上原文出处链接及本声明。 原文链接：https://blog.csdn.net/w1824575989/article/details/125059544

龙生   25 Apr 2023
View Details

Copyright © 2024 Longsheng All rights reserved.