导出模型到excel/csv web api通用方法
本文关键字:api web 方法 csv 模型 excel | 更新日期: 2023-09-27 17:50:44
我在ApiController中有这个API方法:
[HttpGet]
[Route("export")]
public HttpResponseMessage Export([FromUri] int projectId)
{
StringWriter sw = new StringWriter();
sw.WriteLine("'"KeyId'",'"PageName'",'"KeyType'",'"KeyName'",'"LanguageCode'"," +
"'"KeyStatus'",'"KeyValue'",'"DateCreated'",'"DateChanged'",'"UserId'",'"KeysCount'"");
var export = _projectService.GetExportModel(projectId);
foreach (var row in export)
{
sw.WriteLine(string.Format("'"{0}'",'"{1}'",'"{2}'",'"{3}'",'"{4}'"," +
"'"{5}'",'"{6}'",'"{7}'",'"{8}'",'"{9}'"",
row.KeyId, row.PageName, row.KeyType, row.LanguageCode,
row.KeyStatus,row.KeyValue, row.DateCreated, row.DateChanged,
row.UserId, row.KeysCount));
}
var res = Request.CreateResponse(HttpStatusCode.OK);
res.Content = new StringContent(sw.ToString(), Encoding.UTF8, "text/csv");
res.Content.Headers.ContentDisposition = new ContentDispositionHeaderValue("attachment") { FileName = "Export.csv" };
return res;
}
在_projectService.GetExportModel
中,我得到List<ExportExcelModel>
:
public class ExportExcelModel
{
public long KeyId { get; set; }
public string PageName { get; set; }
public KeyType KeyType { get; set; }
public string KeyName { get; set; }
public string LanguageCode { get; set; }
public KeyValueStatus KeyStatus { get; set; }
public string KeyValue { get; set; }
public DateTime DateCreated { get; set; }
public DateTime? DateChanged { get; set; }
public int UserId { get; set; }
public long KeysCount { get; set; }
}
这个方法工作得很好,但都是硬编码的。在特征中,如果我向模型添加新属性,我将需要更新StringWriter
并将此参数添加到foreach
循环。我正在寻找更通用的方法来做到这一点。我想更新我的模型,在CSV中看到这个变化,而不改变webApi控制器中的任何一行。这可能吗?
我知道我来晚了一点,但我想这可能对别人有帮助。
我使用NPOI包开发了一个excel实用程序,它可以
- 简单地获取您的数据表或集合
- 并返回excel,同时保持excel中所有数据表/列表数据类型完整。
Github Code repo。: 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);
}
}
}
}
}
}
详细说明请参考开头提供的链接