public void WorkbookProperties()
{
XSSFWorkbook workbook = new XSSFWorkbook();
try
{
POIXMLProperties props = workbook.GetProperties();
Assert.IsNotNull(props);
//the Application property must be set for new workbooks, see Bugzilla #47559
Assert.AreEqual("NPOI", props.ExtendedProperties.GetUnderlyingProperties().Application);
PackagePropertiesPart opcProps = props.CoreProperties.GetUnderlyingProperties();
Assert.IsNotNull(opcProps);
opcProps.SetTitleProperty("Testing Bugzilla #47460");
Assert.AreEqual("NPOI", opcProps.GetCreatorProperty());
opcProps.SetCreatorProperty("*****@*****.**");
XSSFWorkbook wbBack = (XSSFWorkbook)XSSFTestDataSamples.WriteOutAndReadBack(workbook);
Assert.AreEqual("NPOI", wbBack.GetProperties().ExtendedProperties.GetUnderlyingProperties().Application);
opcProps = wbBack.GetProperties().CoreProperties.GetUnderlyingProperties();
Assert.AreEqual("Testing Bugzilla #47460", opcProps.GetTitleProperty());
Assert.AreEqual("*****@*****.**", opcProps.GetCreatorProperty());
}
finally
{
workbook.Close();
}
}
static XSSFWorkbook InitializeXWorkbook()
{
XSSFWorkbook _xssfWorkbook = new XSSFWorkbook();
_xssfWorkbook.GetProperties().CoreProperties.Subject = "";
_xssfWorkbook.GetProperties().CoreProperties.Revision = "";
return(_xssfWorkbook);
}
public void TestCustomProperties()
{
POIXMLDocument wb = new XSSFWorkbook();
POIXMLProperties.CustomProperties customProps = wb.GetProperties().GetCustomProperties();
customProps.AddProperty("test-1", "string val");
customProps.AddProperty("test-2", 1974);
customProps.AddProperty("test-3", 36.6);
//Adding a duplicate
try
{
customProps.AddProperty("test-3", 36.6);
Assert.Fail("expected exception");
}
catch (ArgumentException e)
{
Assert.AreEqual("A property with this name already exists in the custom properties", e.Message);
}
customProps.AddProperty("test-4", true);
wb = (XSSFWorkbook)XSSFTestDataSamples.WriteOutAndReadBack((XSSFWorkbook)wb);
CT_CustomProperties ctProps =
wb.GetProperties().GetCustomProperties().GetUnderlyingProperties();
Assert.AreEqual(4, ctProps.sizeOfPropertyArray());
CT_Property p;
p = ctProps.GetPropertyArray(0);
Assert.AreEqual("{D5CDD505-2E9C-101B-9397-08002B2CF9AE}", p.fmtid);
Assert.AreEqual("test-1", p.name);
Assert.AreEqual("string val", p.Item.ToString());
Assert.AreEqual(2, p.pid);
p = ctProps.GetPropertyArray(1);
Assert.AreEqual("{D5CDD505-2E9C-101B-9397-08002B2CF9AE}", p.fmtid);
Assert.AreEqual("test-2", p.name);
Assert.AreEqual(1974, p.Item);
Assert.AreEqual(3, p.pid);
p = ctProps.GetPropertyArray(2);
Assert.AreEqual("{D5CDD505-2E9C-101B-9397-08002B2CF9AE}", p.fmtid);
Assert.AreEqual("test-3", p.name);
Assert.AreEqual(36.6, p.Item);
Assert.AreEqual(4, p.pid);
p = ctProps.GetPropertyArray(3);
Assert.AreEqual("{D5CDD505-2E9C-101B-9397-08002B2CF9AE}", p.fmtid);
Assert.AreEqual("test-4", p.name);
Assert.AreEqual(true, p.Item);
Assert.AreEqual(5, p.pid);
}
private void CreateExcel <T>(IEnumerable <T> source)
{
_wb = new XSSFWorkbook();
var sh = (XSSFSheet)_wb.CreateSheet("Sheet1");
POIXMLProperties props = _wb.GetProperties();
props.CoreProperties.Creator = "WYRMS";
props.CoreProperties.Created = DateTime.Now;
var properties = typeof(T).GetProperties().ToList();
//构建表头
var header = sh.CreateRow(0);
for (var j = 0; j < properties.Count; j++)
{
var display = properties[j].GetCustomAttributes(typeof(DisplayAttribute), true)
.Cast <DisplayAttribute>()
.FirstOrDefault();
header.CreateCell(j).SetCellValue(display != null ? display.Name : properties[j].Name);
}
var list = source.ToList();
//填充数据
for (var i = 0; i < list.Count; i++)
{
var r = sh.CreateRow(i + 1);
for (var j = 0; j < properties.Count; j++)
{
var value = properties[j].GetValue(list[i], null);
r.CreateCell(j).SetCellValue(value == null ? "" : value.ToString());
}
}
}
public ExcelConverter()
{
this._workbook = new XSSFWorkbook();
var props = _workbook.GetProperties();
props.CoreProperties.Title = "iPemSystem";
props.CoreProperties.Subject = "iPemSystem";
props.CoreProperties.Creator = "Steven H";
props.CoreProperties.Created = DateTime.Now;
props.CoreProperties.Description = "Delta GreenTech(China) Co., Ltd. All Rights Reserved";
this._worksheets = new Dictionary <int, XSSFSheet>();
this._ffffff = new XSSFColor(new byte[3] {
254, 254, 254
});
this._fafafa = new XSSFColor(new byte[3] {
249, 249, 249
});
this._f5f5f5 = new XSSFColor(new byte[3] {
244, 244, 244
});
this._c0c0c0 = new XSSFColor(new byte[3] {
191, 191, 191
});
this._666666 = new XSSFColor(new byte[3] {
101, 101, 101
});
this._157fcc = new XSSFColor(new byte[3] {
20, 126, 203
});
this._000000 = new XSSFColor(new byte[3] {
0, 0, 0
});
}
public HttpResponseMessage Get([FromUri] List <string> sheets, [FromUri] List <string> locals)
{
MetaDataSheet metadata = new MetaDataSheet();
LookupTableSheet lts = new LookupTableSheet();
//Create Workbook
var workbook = new XSSFWorkbook();
var xmlProps = workbook.GetProperties();
xmlProps.CoreProperties.Creator = "Saint-Gobain - PIM";
var tableNames = Repository.GetAll <LookupTables>().Where(lt => sheets.Contains(lt.Id.ToString())).Select(lt => lt.TableName).ToList();
metadata.CreateMetaData(workbook, tableNames);
lts.CreateLookupTableSheet(workbook, sheets);
using (var memoryStream = new 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 = $"LookupTable{DateTime.Now.ToString("yyyyMMddHHmmss")}.xlsx"
};
response.Content.Headers.Add("Access-Control-Expose-Headers", "Content-Disposition");
return(response);
}
}
public static void PrepareWorkbookTest()
{
var entities = new[]
{
new { Name = "Alice", Age = 10 },
new { Name = "Mike", Age = 10 },
new { Name = "Jane", Age = 10 },
};
// var workbook = ExcelHelper.PrepareWorkbook(ExcelFormat.Xlsx);
var setting = new ExcelSetting();
var workbook = new XSSFWorkbook();
var props = workbook.GetProperties();
props.CoreProperties.Creator = setting.Author;
props.CoreProperties.Created = DateTime.Now;
props.CoreProperties.Modified = DateTime.Now;
props.CoreProperties.Title = setting.Title;
props.CoreProperties.Subject = setting.Subject;
props.CoreProperties.Category = setting.Category;
props.CoreProperties.Description = setting.Description;
props.ExtendedProperties.GetUnderlyingProperties().Company = setting.Company;
props.ExtendedProperties.GetUnderlyingProperties().Application = "WeihanLi.Npoi";
props.ExtendedProperties.GetUnderlyingProperties().AppVersion = "1.9.5";
// props.ExtendedProperties.GetUnderlyingProperties().AppVersion = typeof(ExcelHelper).Assembly.GetName().Version?.ToString();
workbook.ImportData(entities);
workbook.WriteToFile(ApplicationHelper.MapPath("raw_output_0.xlsx"));
}
/// <summary>
/// 创建excel
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="source"></param>
private XSSFWorkbook CreateExcel <T>(IEnumerable <T> source)
{
var wb = new XSSFWorkbook();
var sh = (XSSFSheet)wb.CreateSheet("Sheet1");
var props = wb.GetProperties();
props.CoreProperties.Creator = "葛氏国际物流";
props.CoreProperties.Created = DateTime.Now;
var properties = typeof(T).GetProperties().Where(a => a.GetCustomAttribute <ExcelNoMapAttribute>() == null).ToList();
//构建表头
var header = sh.CreateRow(0);
for (var j = 0; j < properties.Count; j++)
{
var display = properties[j].GetCustomAttribute <DisplayAttribute>();
var name = display?.Name ?? properties[j].Name;
var headCell = header.CreateCell(j);
headCell.CellStyle = GetHeadStyle(wb);
headCell.SetCellValue(name);
}
var list = source.ToList();
//填充数据
for (var i = 0; i < list.Count; i++)
{
var r = sh.CreateRow(i + 1);
for (var j = 0; j < properties.Count; j++)
{
var value = properties[j].GetValue(list[i], null).ToStr();
if (properties[j].PropertyType == typeof(DateTime))
{
var dataTimeCell = r.CreateCell(j);
dataTimeCell.CellStyle = DataTimeStyle(wb);
dataTimeCell.SetCellValue(value.TryDateTime());
}
else if (properties[j].PropertyType == typeof(bool))
{
r.CreateCell(j).SetCellValue(value.TryBool());
}
else if (properties[j].PropertyType == typeof(int) || properties[j].PropertyType == typeof(decimal) ||
properties[j].PropertyType == typeof(float) ||
properties[j].PropertyType == typeof(double) || properties[j].PropertyType == typeof(long))
{
r.CreateCell(j).SetCellValue(value.TryDouble());
}
else
{
r.CreateCell(j).SetCellValue(value);
}
sh.AutoSizeColumn(j);
}
}
return(wb);
}
public void TestListOfCustomProperties()
{
FileInfo inp = POIDataSamples.GetSpreadSheetInstance().GetFileInfo("ExcelWithAttachments.xlsm");
OPCPackage pkg = OPCPackage.Open(inp, PackageAccess.READ);
XSSFWorkbook wb = new XSSFWorkbook(pkg);
Assert.IsNotNull(wb.GetProperties());
Assert.IsNotNull(wb.GetProperties().CustomProperties);
foreach (CT_Property prop in wb.GetProperties().CustomProperties.GetUnderlyingProperties().GetPropertyList())
{
Assert.IsNotNull(prop);
}
wb.Close();
pkg.Close();
}
/// <summary>
/// 初始化工作簿
/// </summary>
/// <param name="excelFile">Excel文件路径</param>
/// <param name="excelSetting">用于加载数据的Excel设置</param>
/// <returns></returns>
private static IWorkbook InitializeWorkbook(string excelFile, ExcelSetting excelSetting = null)
{
var setting = excelSetting ?? ExcelSetting.Default;
if (setting.UseXlsx)
{
if (!string.IsNullOrWhiteSpace(excelFile) && File.Exists(excelFile))
{
using (var file = new FileStream(excelFile, FileMode.Open, FileAccess.Read))
{
var workbook = new XSSFWorkbook(file);
_formulaEvaluator = new XSSFFormulaEvaluator(workbook);
return(workbook);
}
}
else
{
var workbook = new XSSFWorkbook();
_formulaEvaluator = new XSSFFormulaEvaluator(workbook);
var props = workbook.GetProperties();
props.CoreProperties.Creator = setting.Author;
props.CoreProperties.Subject = setting.Subject;
props.ExtendedProperties.GetUnderlyingProperties().Company = setting.Company;
return(workbook);
}
}
else
{
if (!string.IsNullOrWhiteSpace(excelFile) && File.Exists(excelFile))
{
using (var file = new FileStream(excelFile, FileMode.Open, FileAccess.Read))
{
var workbook = new HSSFWorkbook(file);
_formulaEvaluator = new HSSFFormulaEvaluator(workbook);
return(workbook);
}
}
else
{
var workbook = new HSSFWorkbook();
_formulaEvaluator = new HSSFFormulaEvaluator(workbook);
var dsi = PropertySetFactory.CreateDocumentSummaryInformation();
dsi.Company = setting.Company;
workbook.DocumentSummaryInformation = dsi;
var si = PropertySetFactory.CreateSummaryInformation();
si.Author = setting.Author;
si.Subject = setting.Subject;
workbook.SummaryInformation = si;
return(workbook);
}
}
}
private static void CreateExcel(Dictionary <Type, List <object> > source, XSSFWorkbook wb)
{
int sheetCount = 0;
foreach (var key in source.Keys)
{
sheetCount++;
string sheetName = "Sheet" + sheetCount;
var classdisplay = key.GetCustomAttributes(typeof(DisplayNameAttribute), true)
.Cast <DisplayNameAttribute>()
.FirstOrDefault();
if (classdisplay != null)
{
sheetName = classdisplay.DisplayName;
}
var sh = (XSSFSheet)wb.CreateSheet(sheetName);
POIXMLProperties props = wb.GetProperties();
props.CoreProperties.Creator = "MAX";
props.CoreProperties.Created = DateTime.Now;
var properties = key.GetProperties().ToList();
//构建表头
var header = sh.CreateRow(0);
for (var j = 0; j < properties.Count; j++)
{
var display = properties[j].GetCustomAttributes(typeof(DisplayAttribute), true)
.Cast <DisplayAttribute>()
.FirstOrDefault();
if (display != null)
{
header.CreateCell(j).SetCellValue(display.Name);
}
else
{
header.CreateCell(j).SetCellValue(properties[j].Name);
}
}
var list = source[key];
//填充数据
for (var i = 0; i < list.Count; i++)
{
var r = sh.CreateRow(i + 1);
for (var j = 0; j < properties.Count; j++)
{
var value = properties[j].GetValue(list[i], null);
r.CreateCell(j).SetCellValue(value == null ? "" : value.ToString());
}
}
}
}
public static XSSFWorkbook ListToXlsx <T>(List <T> list, string sheetName)
{
XSSFWorkbook workbook = new XSSFWorkbook();
POIXMLProperties props = workbook.GetProperties();
props.CoreProperties.Creator = "北京易天正诚信息技术有限公司";
props.CoreProperties.Created = DateTime.Now;
props.CustomProperties.AddProperty("壁虎科技", "壁虎科技");
ListToSheetXlsx(workbook, list, sheetName);
return(workbook);
}
public async Task <BaseResponse> Xlsx()
{
List <ExcelTestClass> list = ExcelTestClass.GetList();
//BaseDirectory后面有\所以exel前面就不加\了
var storePath = AppDomain.CurrentDomain.BaseDirectory + "Excel";
if (!Directory.Exists(storePath))
{
Directory.CreateDirectory(storePath);
}
string fileNam = "导出数据-" + DateTime.Now.ToString("yyyyMMddHHmmss") + ".xlsx";
string fullPath = storePath + "\\" + fileNam;
XSSFWorkbook workbook = new XSSFWorkbook();
POIXMLProperties props = workbook.GetProperties();
props.CoreProperties.Creator = "北京易天正诚信息技术有限公司";
props.CoreProperties.Subject = "壁虎科技";
props.CoreProperties.Title = "壁虎科技";
props.CoreProperties.Created = DateTime.Now;
props.CustomProperties.AddProperty("壁虎科技", "壁虎科技");
XSSFCellStyle headStyle = (XSSFCellStyle)workbook.CreateCellStyle();
headStyle.Alignment = HorizontalAlignment.Center;
XSSFFont font = (XSSFFont)workbook.CreateFont();
font.FontHeightInPoints = 12;
font.Boldweight = 700;
headStyle.SetFont(font);
var sheet = workbook.CreateSheet();
IRow headerRow = sheet.CreateRow(0);
var headCell = headerRow.CreateCell(0);
headCell.SetCellValue("111");
FileStream file = new FileStream(fullPath, FileMode.Create);
workbook.Write(file);
file.Close();
return(BaseResponse.Ok(fullPath));
}
static void Main(string[] args)
{
XSSFWorkbook workbook = new XSSFWorkbook();
ISheet sheet1 = workbook.CreateSheet("Sheet1");
POIXMLProperties props = workbook.GetProperties();
props.CoreProperties.Creator = "NPOI 2.0.5";
props.CoreProperties.Created = DateTime.Now;
props.CustomProperties.AddProperty("NPOI Team", "Hello World!");
FileStream sw = File.Create("test.xlsx");
workbook.Write(sw);
sw.Close();
}
public void TestWorkbookExtendedProperties()
{
XSSFWorkbook workbook = new XSSFWorkbook();
POIXMLProperties props = workbook.GetProperties();
Assert.IsNotNull(props);
ExtendedProperties properties =
props.ExtendedProperties;
CT_ExtendedProperties
ctProps = properties.GetUnderlyingProperties();
String appVersion = "3.5 beta";
String application = "POI";
ctProps.Application = (application);
ctProps.AppVersion = (appVersion);
ctProps = null;
properties = null;
props = null;
XSSFWorkbook newWorkbook =
(XSSFWorkbook)XSSFTestDataSamples.WriteOutAndReadBack(workbook);
Assert.IsTrue(workbook != newWorkbook);
POIXMLProperties newProps = newWorkbook.GetProperties();
Assert.IsNotNull(newProps);
ExtendedProperties newProperties =
newProps.ExtendedProperties;
Assert.AreEqual(application, newProperties.Application);
Assert.AreEqual(appVersion, newProperties.AppVersion);
CT_ExtendedProperties
newCtProps = newProperties.GetUnderlyingProperties();
Assert.AreEqual(application, newCtProps.Application);
Assert.AreEqual(appVersion, newCtProps.AppVersion);
}
/// <summary>
/// get a excel workbook
/// </summary>
/// <param name="isXlsx">is for *.xlsx file</param>
/// <param name="excelSetting">excelSettings</param>
/// <returns></returns>
public static IWorkbook PrepareWorkbook(bool isXlsx, ExcelSetting excelSetting)
{
var setting = (excelSetting ?? DefaultExcelSetting) ?? new ExcelSetting();
if (isXlsx)
{
var workbook = new XSSFWorkbook();
var props = workbook.GetProperties();
props.CoreProperties.Creator = setting.Author;
props.CoreProperties.Created = DateTime.Now;
props.CoreProperties.Modified = DateTime.Now;
props.CoreProperties.Title = setting.Title;
props.CoreProperties.Subject = setting.Subject;
props.CoreProperties.Category = setting.Category;
props.CoreProperties.Description = setting.Description;
props.ExtendedProperties.GetUnderlyingProperties().Company = setting.Company;
props.ExtendedProperties.GetUnderlyingProperties().Application = InternalConstants.ApplicationName;
props.ExtendedProperties.GetUnderlyingProperties().AppVersion = Version.ToString();
return(workbook);
}
else
{
var workbook = new HSSFWorkbook();
////create a entry of DocumentSummaryInformation
var dsi = PropertySetFactory.CreateDocumentSummaryInformation();
dsi.Company = setting.Company;
dsi.Category = setting.Category;
workbook.DocumentSummaryInformation = dsi;
////create a entry of SummaryInformation
var si = PropertySetFactory.CreateSummaryInformation();
si.Title = setting.Title;
si.Subject = setting.Subject;
si.Author = setting.Author;
si.CreateDateTime = DateTime.Now;
si.Comments = setting.Description;
si.ApplicationName = InternalConstants.ApplicationName;
workbook.SummaryInformation = si;
return(workbook);
}
}
/// <summary>
/// 添加文件信息
/// </summary>
/// <param name="excelFilePath">Excel文件路径</param>
/// <param name="documentInfo">文件信息。如果为null，则不设置文件信息</param>
public static void AddDocumentInfo(string excelFilePath, DocumentInfo documentInfo)
{
if (!File.Exists(excelFilePath))
{
return;
}
if (documentInfo == null)
{
return;
}
IWorkbook workbook = GetWorkbook(excelFilePath);
if (workbook != null)
{
if (workbook is HSSFWorkbook)
{
HSSFWorkbook hssfWorkbook = workbook as HSSFWorkbook;
var documentSummaryInfo = hssfWorkbook.DocumentSummaryInformation;
var summaryInfo = hssfWorkbook.SummaryInformation;
DocumentSummaryInformation dsi = documentSummaryInfo ?? PropertySetFactory.CreateDocumentSummaryInformation();
dsi.Company = documentInfo.Company;
dsi.Category = documentInfo.Category;
dsi.Manager = documentInfo.Manager;
if (documentSummaryInfo == null)
{
hssfWorkbook.DocumentSummaryInformation = dsi;
}
SummaryInformation si = summaryInfo ?? PropertySetFactory.CreateSummaryInformation();
si.Author = documentInfo.Author;
si.Subject = documentInfo.Subject;
si.Title = documentInfo.Title;
si.Comments = documentInfo.Comments;
if (summaryInfo == null)
{
hssfWorkbook.SummaryInformation = si;
}
Save(hssfWorkbook, excelFilePath);
}
else if (workbook is XSSFWorkbook)
{
XSSFWorkbook xssfWorkbook = workbook as XSSFWorkbook;
var xmlProps = xssfWorkbook.GetProperties();
var coreProps = xmlProps.CoreProperties;
var underlyingProperties = xmlProps.ExtendedProperties.GetUnderlyingProperties();
coreProps.Creator = documentInfo.Author;
coreProps.Subject = documentInfo.Subject;
coreProps.Title = documentInfo.Title;
coreProps.Description = documentInfo.Comments;
coreProps.Category = documentInfo.Category;
underlyingProperties.Company = documentInfo.Company;
underlyingProperties.Manager = documentInfo.Manager;
Save(xssfWorkbook, excelFilePath);
}
}
}
/// <summary>
/// Excelを出力する
/// </summary>
/// <param name="filePath"></param>
/// <param name="sheetName"></param>
/// <param name="fileOutputType"></param>
internal void CreateExcel(string filePath, string sheetName, FileOutputType fileOutputType)
{
try
{
IWorkbook workbook = null;
ISheet sheet = null;
string ext = System.IO.Path.GetExtension(filePath);
switch (fileOutputType)
{
case FileOutputType.追記:
if (System.IO.File.Exists(filePath))
{
using (var fs = File.OpenRead(filePath))
{
workbook = WorkbookFactory.Create(fs);
}
sheet = workbook.GetSheet(sheetName);
}
else
{
if (ext.ToUpper() == ".xls")
{
workbook = new HSSFWorkbook();
sheet = workbook.CreateSheet(sheetName);
}
else
{
workbook = new XSSFWorkbook();
sheet = workbook.CreateSheet(sheetName);
}
}
break;
case FileOutputType.書き:
if (ext.ToUpper() == ".xls")
{
workbook = new HSSFWorkbook();
sheet = workbook.CreateSheet(sheetName);
}
else
{
workbook = new XSSFWorkbook();
sheet = workbook.CreateSheet(sheetName);
}
break;
}
switch (fileOutputType)
{
case FileOutputType.追記:
{
int last = sheet.LastRowNum;
if (last > 0)
{
last++;
}
for (int r = 0; r < ValueList.Count; r++)
{
IRow row = sheet.CreateRow(r + last);
for (int c = 0; c < ValueList[r].Count; c++)
{
ICell cell = row.CreateCell(c);
cell.SetCellValue(ValueList[r][c]);
}
}
using (var fs = File.Create(filePath))
{
try
{
if (workbook is XSSFWorkbook)
{
XSSFWorkbook b = (XSSFWorkbook)workbook;
POIXMLProperties props = b.GetProperties();
props.CoreProperties.Creator = "Macrobo";
}
}
catch (Exception) {}
workbook.Write(fs);
workbook.Close();
}
}
break;
case FileOutputType.書き:
{
for (int r = 0; r < ValueList.Count; r++)
{
IRow row = sheet.CreateRow(r);
for (int c = 0; c < ValueList[r].Count; c++)
{
ICell cell = row.CreateCell(c);
cell.SetCellValue(ValueList[r][c]);
}
}
if (System.IO.File.Exists(filePath))
{
System.IO.File.Delete(filePath);
}
using (var fs = File.Create(filePath))
{
try
{
if (workbook is XSSFWorkbook)
{
XSSFWorkbook b = (XSSFWorkbook)workbook;
POIXMLProperties props = b.GetProperties();
props.CoreProperties.Creator = "Macrobo";
}
}
catch (Exception) { }
workbook.Write(fs);
workbook.Close();
}
}
break;
}
}
catch (Exception ex)
{
throw Program.ThrowException(ex);
}
}
public HttpResponseMessage Get([FromUri] List <string> sheets, [FromUri] List <string> locals)
{
MetaDataSheet metadata = new MetaDataSheet();
EnvironmentSheet enr = new EnvironmentSheet();
Enviroment_catalog enc = new Enviroment_catalog();
Catalog_Sheet cat = new Catalog_Sheet();
EntitySheet entity = new EntitySheet();
Entity_ContainerSheet econ = new Entity_ContainerSheet();
AttributeMasterSheet atm = new AttributeMasterSheet();
CategoryMasterSheet ctm = new CategoryMasterSheet();
AttributeMappingSheet attrmap = new AttributeMappingSheet();
TechnicalCategorySheet tcs = new TechnicalCategorySheet();
//Create Workbook
var workbook = new XSSFWorkbook();
var xmlProps = workbook.GetProperties();
xmlProps.CoreProperties.Creator = "Saint-Gobain - PIM";
metadata.CreateMetaData(workbook, Enum.GetNames(typeof(EnumDataModel)));
if (sheets.Contains(EnumDataModel.Environment.ToString()))
{
enr.CreateEnviroment(workbook);
}
if (sheets.Contains(EnumDataModel.Catalog.ToString()))
{
cat.CreateCatalog(workbook);
}
if (sheets.Contains(EnumDataModel.Enviroment_catalog.ToString()))
{
enc.CreateEnviroment_catalog(workbook);
}
if (sheets.Contains(EnumDataModel.Entity.ToString()))
{
entity.CreateEntity(workbook);
}
if (sheets.Contains(EnumDataModel.Entity_Containor.ToString()))
{
econ.CreateEntity_Containor(workbook);
}
if (sheets.Contains(EnumDataModel.AttributeMaster.ToString()))
{
atm.CreateAttributeMaster(workbook);
}
if (sheets.Contains(EnumDataModel.CategoryMaster.ToString()))
{
ctm.CreateCategoryMaster(workbook);
}
if (sheets.Contains(EnumDataModel.AttributeMapping.ToString()))
{
attrmap.CreateAttributeMapping(workbook);
}
if (sheets.Contains(EnumDataModel.Technical_Category.ToString()))
{
tcs.CreateTechnical_Category(workbook);
}
string fileName = $"DataModel{DateTime.Now.ToString("yyyyMMddHHmmss")}.xlsx";
using (var memoryStream = new 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
};
response.Content.Headers.Add("Access-Control-Expose-Headers", "Content-Disposition");
return(response);
}
}
/// <summary>
/// 创建excel
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="source"></param>
private void CreateExcel <T>(IEnumerable <T> source)
{
_wb = new XSSFWorkbook();
var sh = (XSSFSheet)_wb.CreateSheet("Sheet1");
var props = _wb.GetProperties();
props.CoreProperties.Creator = "陈珙公司";
props.CoreProperties.Created = DateTime.Now;
var properties = typeof(T).GetProperties().ToList();
//构建表头
var header = sh.CreateRow(0);
for (var j = 0; j < properties.Count; j++)
{
var display = properties[j].GetCustomAttributes(typeof(DisplayAttribute), true)
.Cast <DisplayAttribute>()
.FirstOrDefault();
var headCell = header.CreateCell(j);
headCell.CellStyle = HeadStyle;
headCell.SetCellValue(display != null ? display.Name : properties[j].Name);
}
var list = source.ToList();
//填充数据
for (var i = 0; i < list.Count; i++)
{
var r = sh.CreateRow(i + 1);
for (var j = 0; j < properties.Count; j++)
{
var value = properties[j].GetValue(list[i], null).ToStr();
if (properties[j].PropertyType == typeof(DateTime))
{
var dataTimeCell = r.CreateCell(j);
dataTimeCell.CellStyle = DataTimeStyle;
dataTimeCell.SetCellValue(value.TryDateTime());
}
else if (properties[j].PropertyType == typeof(bool))
{
r.CreateCell(j).SetCellValue(value.TryBool());
}
else if (properties[j].PropertyType == typeof(int) || properties[j].PropertyType == typeof(decimal) ||
properties[j].PropertyType == typeof(float) ||
properties[j].PropertyType == typeof(double) || properties[j].PropertyType == typeof(long))
{
r.CreateCell(j).SetCellValue(value.TryDouble());
}
else
{
r.CreateCell(j).SetCellValue(value);
}
}
}
for (var j = 0; j < properties.Count; j++)
{
sh.AutoSizeColumn(j);
}
}
