Экспорт Excel с помощью Npoi
Я ищу, чтобы экспортировать лист Excel, используя библиотеку NPOI. Есть ли способ вставить данные в лист без потери формата данных?
Ранее я использовал библиотеку электронных таблиц 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, потому что мне нужно создавать файлы Excel, содержащие более 5 листов. 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 отформатированы как текст. Я хотел бы сохранить формат, который используется в таблице данных. Ищу помощь. Заранее спасибо!!!
2 ответа
Чтобы вставить данные из таблицы данных, вы могли бы использовать этот код вместо комментария "// Как мне вставить данные из таблицы данных в этот лист".
// 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
}
ИЗМЕНИТЬ В этом ответе я обнаружил, как установить значения даты в удобочитаемом формате.
Я знаю, что немного опоздал, но я думаю, что это может помочь другим, да, есть способ конвертировать данные напрямую в экспорт Excel без потери формата данных.
Я разработал утилиту Excel с использованием пакета NPOI, которая может
- Просто берет вашу таблицу данных или коллекцию
- И возвращает вам превосходство, сохраняя при этом все типы данных таблицы/списка в первенстве.
Репозиторий кода 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 и включает 2 файла 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);
}
}
}
}
}
}
Подробное объяснение см. по ссылке, указанной в начале.