使用Npoi导出Excel

Question

我希望导出一个excel表使用NPOI库。是否有任何方法在不丢失数据格式的情况下将datatable插入工作表？

以前，我经常使用Gembox电子表格库。这对我来说很管用。该代码的外观如下：

    public void ExportTest(DataSet ds)
    {

        SpreadsheetInfo.SetLicense("FREE-LIMITED-KEY");
        ExcelFile ef = new ExcelFile();
        var filename = DateTime.Now.ToString("yyyyMMdd") + "BSI_MEMBERAmendment" + ".xls";
        foreach (DataTable dt in ds.Tables)
        {
            ExcelWorksheet ws = ef.Worksheets.Add(dt.TableName);
            ws.InsertDataTable(dt,
           new InsertDataTableOptions(0, 0)
           {
               ColumnHeaders = true,
               StartRow = 0,

           });
        }

        ef.Save(this.Response, filename);

我不得不停止使用Gembox库，因为我需要使用5张以上的excel文件。不幸的是，Gembox不允许在一个自由的平台上这样做。因此，我转向了NPOI。

现在我正在使用NPOI库，我已经将代码更改为：

  public void WriteExcelWithNPOI(String extension, DataSet dataSet)
    {


        HSSFWorkbook workbook = new HSSFWorkbook(); ;

       if (extension == "xls")
        {
            workbook = new HSSFWorkbook();
        }
        else
        {
            throw new Exception("This format is not supported");
        }

        foreach (DataTable dt in dataSet.Tables)
        {
            var sheet1 = workbook.CreateSheet(dt.TableName);
            // How can i insert the data's from dataTable in this sheet
        }

        using (var exportData = new MemoryStream())
        {
            Response.Clear();
            workbook.Write(exportData);
            if (extension == "xls") 
            {
                Response.ContentType = "application/vnd.ms-excel";
                Response.AddHeader("Content-Disposition", string.Format("attachment;filename={0}", "tpms_dict.xls"));
                Response.BinaryWrite(exportData.GetBuffer());
            }
            Response.End();
        }
    }

我在使用NPOI库时遇到的问题是，导出的excel文件中的所有单元格都被格式化为文本。我想保留数据表中使用的格式。寻求帮助。提前谢谢！

Answer 1

要从数据表中插入数据，也许可以使用以下代码来代替注释"//如何在此表中插入来自dataTable的数据“。

// 1. make a header row
IRow row1 = sheet1.CreateRow(0);

for (int j = 0; j < dt.Columns.Count; j++)
{
    ICell cell = row1.CreateCell(j);
    string columnName = dt.Columns[j].ToString();
    cell.SetCellValue(columnName);
}

// 2. loop through data
for (int i = 0; i < dt.Rows.Count; i++)
{
    IRow row = sheet1.CreateRow(i + 1);
    for (int j = 0; j < dt.Columns.Count; j++)
    {
        ICell cell = row.CreateCell(j);
        string columnName = dt.Columns[j].ToString();
        cell.SetCellValue(dt.Rows[i][columnName].ToString());
    }
}
// 3. Auto size columns
for (int i = 0; i < 5; i++)
{
    for (int j = 0; j < row1.LastCellNum; j++)
    {
        sheet1.AutoSizeColumn(j);
    }
}

对于数据类型，可以使用函数cell.SetCellType(CellType.[TYPE HERE]);。函数SetCellType中输入的类型之后必须与cell.SetCellValue()中的数据类型匹配。

这将修改数据循环，如下所示：

// 2. loop through data
for (int i = 0; i < dt.Rows.Count; i++)
{
    IRow row = sheet1.CreateRow(i + 1);
    for (int j = 0; j < dt.Columns.Count; j++)
    {
        ICell cell = row.CreateCell(j);
        string columnName = dt.Columns[j].ToString();
        // Set the cell type
        cell.SetCellType(GetCorrectCellType(dt.Rows[i][columnName].GetType()))
        // Set the cell value
        cell.SetCellValue(dt.Rows[i][columnName]);
    }
}

// Function to return the correct cell type
public int GetCorrectCellType(Type dataType) 
{
   if(dataType == typeof(string))
      return CellType.String;
   else if(dataType == typeof(int) || dataType == typeof(double))
      return CellType.Numeric;
   else if(dataType == typeof(bool))
      return CellType.Boolean;
   else
      return CellType.Unknown; // Not sure how to set Date Type --> Unknown
}

编辑我发现了如何在this answer中以人类可读的格式设置日期值。

Answer 2

我知道我在这里有点晚了，但我认为这可能会对其他人有所帮助，是的，有一种方法可以直接转换datatable来导出excel，而不会丢失数据格式。

我已经用NPOI包开发了一个excel实用程序，它可以

1. 只需获取数据表或集合。
2. 并返回excel，同时保持excel中所有数据表/列表数据类型的完整性。

Github代码回购：https://github.com/ansaridawood/.NET-Generic-Excel-Export-Sample/tree/master/GenericExcelExport/ExcelExport

寻找代码解释，您可以在这里找到它：https://www.codeproject.com/Articles/1241654/Export-to-Excel-using-NPOI-Csharp-and-WEB-API

它使用NPOI DLL，它包含两个cs文件，然后您就可以了。

下面是第一个供参考的文件AbstractDataExport.cs

using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;
using System;
using System.Collections.Generic;
using System.IO;
using System.Net;
using System.Net.Http;
using System.Net.Http.Headers;

namespace GenericExcelExport.ExcelExport
{
    public interface IAbstractDataExport
    {
        HttpResponseMessage Export(List exportData, string fileName, string sheetName);
    }

    public abstract class AbstractDataExport : IAbstractDataExport
    {
        protected string _sheetName;
        protected string _fileName;
        protected List _headers;
        protected List _type;
        protected IWorkbook _workbook;
        protected ISheet _sheet;
        private const string DefaultSheetName = "Sheet1";

        public HttpResponseMessage Export
              (List exportData, string fileName, string sheetName = DefaultSheetName)
        {
            _fileName = fileName;
            _sheetName = sheetName;

            _workbook = new XSSFWorkbook(); //Creating New Excel object
            _sheet = _workbook.CreateSheet(_sheetName); //Creating New Excel Sheet object

            var headerStyle = _workbook.CreateCellStyle(); //Formatting
            var headerFont = _workbook.CreateFont();
            headerFont.IsBold = true;
            headerStyle.SetFont(headerFont);

            WriteData(exportData); //your list object to NPOI excel conversion happens here

            //Header
            var header = _sheet.CreateRow(0);
            for (var i = 0; i < _headers.Count; i++)
            {
                var cell = header.CreateCell(i);
                cell.SetCellValue(_headers[i]);
                cell.CellStyle = headerStyle;
            }

            for (var i = 0; i < _headers.Count; i++)
            {
                _sheet.AutoSizeColumn(i);
            }

            using (var memoryStream = new MemoryStream()) //creating memoryStream
            {
                _workbook.Write(memoryStream);
                var response = new HttpResponseMessage(HttpStatusCode.OK)
                {
                    Content = new ByteArrayContent(memoryStream.ToArray())
                };

                response.Content.Headers.ContentType = new MediaTypeHeaderValue
                       ("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
                response.Content.Headers.ContentDisposition = 
                       new ContentDispositionHeaderValue("attachment")
                {
                    FileName = $"{_fileName}_{DateTime.Now.ToString("yyyyMMddHHmmss")}.xlsx"
                };

                return response;
            }
        }

        //Generic Definition to handle all types of List
        public abstract void WriteData(List exportData);
    }
}

这是第二个也是最后一个文件AbstractDataExportBridge.cs

using NPOI.SS.UserModel;
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Text.RegularExpressions;

namespace GenericExcelExport.ExcelExport
{
    public class AbstractDataExportBridge : AbstractDataExport
    {
        public AbstractDataExportBridge()
        {
            _headers = new List<string>();
            _type = new List<string>();
        }

        public override void WriteData<T>(List<T> exportData)
        {
            PropertyDescriptorCollection properties = TypeDescriptor.GetProperties(typeof(T));

            DataTable table = new DataTable();

            foreach (PropertyDescriptor prop in properties)
            {
                var type = Nullable.GetUnderlyingType(prop.PropertyType) ?? prop.PropertyType;
                _type.Add(type.Name);
                table.Columns.Add(prop.Name, Nullable.GetUnderlyingType(prop.PropertyType) ?? 
                                  prop.PropertyType);
                string name = Regex.Replace(prop.Name, "([A-Z])", " $1").Trim(); //space separated 
                                                                           //name by caps for header
                _headers.Add(name);
            }

            foreach (T item in exportData)
            {
                DataRow row = table.NewRow();
                foreach (PropertyDescriptor prop in properties)
                    row[prop.Name] = prop.GetValue(item) ?? DBNull.Value;
                table.Rows.Add(row);
            }

            IRow sheetRow = null;

            for (int i = 0; i < table.Rows.Count; i++)
            {
                sheetRow = _sheet.CreateRow(i + 1);
                for (int j = 0; j < table.Columns.Count; j++)
                {
                    ICell Row1 = sheetRow.CreateCell(j);

                    string type = _type[j].ToLower();
                    var currentCellValue = table.Rows[i][j];

                    if (currentCellValue != null && 
                        !string.IsNullOrEmpty(Convert.ToString(currentCellValue)))
                    {
                        if (type == "string")
                        {
                            Row1.SetCellValue(Convert.ToString(currentCellValue));
                        }
                        else if (type == "int32")
                        {
                            Row1.SetCellValue(Convert.ToInt32(currentCellValue));
                        }
                        else if (type == "double")
                        {
                            Row1.SetCellValue(Convert.ToDouble(currentCellValue));
                        }
                    }
                    else
                    {
                        Row1.SetCellValue(string.Empty);
                    }
                }
            }
        }
    }
}

要获得详细的解释，请参阅开头提供的链接。