public System.Web.Mvc.ActionResult ProductDealerQueryExport(string ProductName, string DealerName)
{
var totalNumber = 0;
var dt = GetProductDealerQuery(new ProductDealerQueryRequest {
ProductName = ProductName, DealerName = DealerName
}, ref totalNumber);
if (dt.Rows.Count > 10000)
{
rtn_data rtn = new rtn_data();
return(Json(new rtn_data {
code = 0, message = "无法导出超过1万条数据"
}, JsonRequestBehavior.AllowGet));
}
var headers = new List <string>()
{
"序号", "产品组", "产品组ID", "产品名称", "产品ID", "公司名称", "公司ID"
};
var book = NPOIHelper.Export(dt, string.Empty, headers);
NpoiMemoryStream ms = new NpoiMemoryStream();
ms.AllowClose = false;
book.Write(ms);
ms.Flush();
ms.Seek(0, SeekOrigin.Begin);
ms.AllowClose = true;
return(File(ms, "application/vnd.ms-excel", "产品-经销商" + DateTime.Now.ToString("yyyyMMddHHmmss") + ".xlsx"));
}
public System.Web.Mvc.ActionResult ProductQueryExport(string ProductName, string StartTime, string EndTime)
{
var totalNumber = 0;
var dt = GetProductQuery(new ProductQueryRequest {
ProductName = ProductName, StartTime = StartTime, EndTime = EndTime
}, ref totalNumber);
if (dt.Rows.Count > 10000)
{
rtn_data rtn = new rtn_data();
return(Json(new rtn_data {
code = 0, message = "无法导出超过1万条数据"
}, JsonRequestBehavior.AllowGet));
}
var headers = new List <string>()
{
"序号", "产品ID", "产品名称", "产品组", "产品组ID", "产品有效期始", "产品有效期至", "更新日期", "客户类型", "最小融资额", "最大融资额", "最小租期", "最大租期", "最大融资比例", "尾款", "最小残值比例", "最大残值比例", "最小贷款期数", "最大贷款期数", "残值百分比", "应用残值", "客户利率", "实际利率", "补贴率", "能否编辑残值", "提前终止罚金比例", "提前结清", "还款方式", "补贴经销商", "补贴制造商"
};
var book = NPOIHelper.Export(dt, string.Empty, headers);
NpoiMemoryStream ms = new NpoiMemoryStream();
ms.AllowClose = false;
book.Write(ms);
ms.Flush();
ms.Seek(0, SeekOrigin.Begin);
ms.AllowClose = true;
return(File(ms, "application/vnd.ms-excel", "产品" + DateTime.Now.ToString("yyyyMMddHHmmss") + ".xlsx"));
}
public byte[] SerializeWorkbook()
{
NpoiMemoryStream ms = new NpoiMemoryStream();
using (NpoiMemoryStream tempStream = new NpoiMemoryStream())
{
tempStream.AllowClose = false;
_outputWorkbook.Write(tempStream);
tempStream.Flush();
tempStream.Seek(0, SeekOrigin.Begin);
tempStream.AllowClose = true;
var byteArray = tempStream.ToArray();
ms.Write(byteArray, 0, byteArray.Length);
var chartNames = new List <string>();
if (isSGRQChartIncluded)
{
chartNames.Add("SGRQ");
}
if (isIgChartIncluded)
{
chartNames.Add("Ig");
}
if (chartNames.Count == 0)
{
return(ms.ToArray());
}
else
{
_chartGenerator = new PatientDetailsExcelChartGenerator(ms.ToArray(), chartNames, _patientDetailsVM);
return(_chartGenerator.GenerateDocumentWithChart());
}
}
}
/// <summary>
/// 导出Word模板
/// 替换文档变量和添加表格
/// </summary>
/// <param name="path">文档路径</param>
/// <param name="dics">变量信息：key:变量名-value:变量值</param>
/// <param name="tuples">表格信息：表头, key:字段名-value:列名, 数据源</param>
/// <returns></returns>
public MemoryStream ExportWordCreateTable(string path, Dictionary <string, string> dics, List <Tuple <string, Dictionary <string, string>, DataTable> > tuples)
{
var document = new XWPFDocument(File.OpenRead(path));
//变量替换
if (dics != null && dics.Any())
{
DocReplaceVariable(document, dics);
}
//添加表格
if (tuples != null && tuples.Any())
{
foreach (var tuple in tuples)
{
DocAddTable(document, tuple.Item1, tuple.Item2, tuple.Item3);
}
}
var ms = new NpoiMemoryStream {
AllowClose = false
};
document.Write(ms);
ms.Flush();
ms.Seek(0, SeekOrigin.Begin);
ms.AllowClose = true;
return(ms);
}
/// <summary>
/// 导出Word模板
/// 替换文档变量和填充表格
/// </summary>
/// <param name="path">文档路径</param>
/// <param name="dics">变量信息：key:变量名-value:变量值</param>
/// <param name="tuples">表格信息：表格序号(从1开始), 字段名, 数据源</param>
/// <returns></returns>
public MemoryStream ExportWordExtendTable(string path, Dictionary <string, string> dics, List <Tuple <int, string[], DataTable> > tuples)
{
var document = new XWPFDocument(File.OpenRead(path));
//变量替换
if (dics != null && dics.Any())
{
DocReplaceVariable(document, dics);
}
//扩展表格
if (tuples != null && tuples.Any())
{
var tabCount = document.Tables.Count;
foreach (var tuple in tuples)
{
if (tuple.Item1 <= tabCount)
{
var table = document.Tables[tuple.Item1 - 1];
DocTableAddTr(table, tuple.Item2, tuple.Item3);
}
}
}
var ms = new NpoiMemoryStream {
AllowClose = false
};
document.Write(ms);
ms.Flush();
ms.Seek(0, SeekOrigin.Begin);
ms.AllowClose = true;
return(ms);
}
public static byte[] CreateExcelFileToByteArray <T>(IEnumerable <T> list, Hashtable head)
{
try
{
var workbook = new XSSFWorkbook();
using (var ms = new NpoiMemoryStream())
{
ms.AllowClose = false;
var sheet = workbook.CreateSheet() as XSSFSheet;
var headerRow = sheet.CreateRow(0) as XSSFRow;
var h = false;
var j = 1;
var type = typeof(T);
var properties = type.GetProperties();
foreach (var item in list)
{
var dataRow = sheet.CreateRow(j) as XSSFRow;
var i = 0;
foreach (var column in properties)
{
if (!h)
{
headerRow.CreateCell(i).SetCellValue(head[column.Name] == null ? column.Name : head[column.Name].ToString());
dataRow.CreateCell(i).SetCellValue(column.GetValue(item, null) == null ? "" : column.GetValue(item, null).ToString());
}
else
{
dataRow.CreateCell(i).SetCellValue(column.GetValue(item, null) == null ? "" : column.GetValue(item, null).ToString());
}
i++;
}
h = true;
j++;
}
workbook.Write(ms);
ms.Flush();
ms.Position = 0;
ms.AllowClose = true;
sheet = null;
headerRow = null;
workbook = null;
return(ms.ToArray());
}
}
catch (Exception ex)
{
return(null);
}
}
private byte[] SerializeWorkbook()
{
NpoiMemoryStream ms = new NpoiMemoryStream();
using (NpoiMemoryStream tempStream = new NpoiMemoryStream())
{
tempStream.AllowClose = false;
_outputWorkbook.Write(tempStream);
tempStream.Flush();
tempStream.Seek(0, SeekOrigin.Begin);
tempStream.AllowClose = true;
var byteArray = tempStream.ToArray();
ms.Write(byteArray, 0, byteArray.Length);
return(ms.ToArray());
}
}
/// <summary>
/// 导出Word模板
/// 仅替换文档变量
/// </summary>
/// <param name="path">文档路径</param>
/// <param name="dics">变量信息：key:变量名-value:变量值</param>
/// <returns></returns>
public MemoryStream ExportWordTemplate(string path, Dictionary <string, string> dics)
{
var document = new XWPFDocument(File.OpenRead(path));
//变量替换
DocReplaceVariable(document, dics);
var ms = new NpoiMemoryStream {
AllowClose = false
};
document.Write(ms);
ms.Flush();
ms.Seek(0, SeekOrigin.Begin);
ms.AllowClose = true;
return(ms);
}
// 从DataTable 生成 Excel
public static MemoryStream Export(DataTable dt)
{
XSSFWorkbook wb;
wb = new XSSFWorkbook();
ISheet sheet1;
sheet1 = wb.CreateSheet("sheet1");
int rowCount = dt.Rows.Count + 1;
int colCount = dt.Columns.Count;
// 创建表头
IRow titles = sheet1.CreateRow(0);
for (int i = 0; i < colCount; i++)
{
// 获取dt的表头
string title = dt.Columns[i].ColumnName;
ICell cell = titles.CreateCell(i);
cell.SetCellValue(title);
sheet1.AutoSizeColumn(i);
}
for (int i = 1; i < rowCount; i++)
{
DataRow row = dt.Rows[i - 1];
IRow excelRow = sheet1.CreateRow(i);
for (int j = 0; j < colCount; j++)
{
string content = row[j].ToString();
ICell cell = excelRow.CreateCell(j);
cell.SetCellValue(content);
}
}
NpoiMemoryStream result = new NpoiMemoryStream();
result.AllowClose = false;
wb.Write(result);
result.Flush();
result.Seek(0, SeekOrigin.Begin);
result.AllowClose = true;
return(result);
}
private FileStreamResult DetailsExcelResult(Sessie sessie, List <ApplicationUser> users)
{
NpoiMemoryStream stream = new NpoiMemoryStream();
stream.AllowClose = false;
IWorkbook workbook = new XSSFWorkbook();
ISheet excelSheet = workbook.CreateSheet("Sessie");
IRow row = excelSheet.CreateRow(0);
row.CreateCell(0).SetCellValue("Naam");
row.CreateCell(1).SetCellValue(sessie.Naam);
row = excelSheet.CreateRow(1);
row.CreateCell(0).SetCellValue("Track");
row.CreateCell(1).SetCellValue(sessie.Track.Naam);
row = excelSheet.CreateRow(2);
row.CreateCell(0).SetCellValue("Ruimte");
row.CreateCell(1).SetCellValue(sessie.Ruimte.Naam);
row = excelSheet.CreateRow(3);
row.CreateCell(0).SetCellValue("Deelnemers");
row.CreateCell(1).SetCellValue(users.Count);
excelSheet = workbook.CreateSheet("Deelnemers");
int iRow = 0;
row = excelSheet.CreateRow(iRow);
row.CreateCell(0).SetCellValue("Naam");
row.CreateCell(1).SetCellValue("Organisatie");
row.CreateCell(2).SetCellValue("Afdeling");
row.CreateCell(3).SetCellValue("Email");
foreach (ApplicationUser user in users)
{
iRow++;
row = excelSheet.CreateRow(iRow);
row.CreateCell(0).SetCellValue(user.Name);
row.CreateCell(1).SetCellValue(user.Organisation);
row.CreateCell(2).SetCellValue(user.Department);
row.CreateCell(3).SetCellValue(user.Email);
}
workbook.Write(stream);
stream.AllowClose = true;
stream.Seek(0, SeekOrigin.Begin);
return(File(stream, "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet", "sessie-" + sessie.Id.ToString().Trim() + ".xlsx"));
}
/// <summary>
/// List导出Excel
/// </summary>
/// <param name="tuple">Sheet名, key:列名-Value:字段名, 数据源</param>
/// <param name="isWidth">列宽自适应(谨慎,字数少的可用)</param>
/// <returns></returns>
public MemoryStream ExportExcelForList <T>(Tuple <string, Dictionary <string, string>, List <T> > tuple, bool isWidth = false)
{
var workbook = new XSSFWorkbook();
//添加Sheet
AddSheetForList(workbook, tuple, isWidth);
var ms = new NpoiMemoryStream {
AllowClose = false
};
workbook.Write(ms);
ms.Flush();
ms.Seek(0, SeekOrigin.Begin);
ms.AllowClose = true;
return(ms);
}
/// <summary>
/// DataTable批量导出Excel
/// 多个Sheet
/// </summary>
/// <param name="tupleList">Sheet名, key:列名-Value:字段名, 数据源</param>
/// <param name="isWidth">列宽自适应(谨慎,字数少的可用)</param>
/// <returns></returns>
public MemoryStream ExportExcelBatchDataTable(List <Tuple <string, Dictionary <string, string>, DataTable> > tupleList, bool isWidth = false)
{
var workbook = new XSSFWorkbook();
//添加Sheet
foreach (var tuple in tupleList)
{
AddSheetForDataTable(workbook, tuple, isWidth);
}
var ms = new NpoiMemoryStream {
AllowClose = false
};
workbook.Write(ms);
ms.Flush();
ms.Seek(0, SeekOrigin.Begin);
ms.AllowClose = true;
return(ms);
}
public Stream Export()
{
var products = _productRepository.GetAll();
var workbook = new XSSFWorkbook();
var sheet = workbook.CreateSheet("Products");
var numRows = 0;
var headerRow = sheet.CreateRow(numRows);
headerRow.CreateCell(0, CellType.String).SetCellValue("Id");
headerRow.CreateCell(1, CellType.String).SetCellValue("Code");
headerRow.CreateCell(2, CellType.String).SetCellValue("Name");
headerRow.CreateCell(3, CellType.String).SetCellValue("Price");
headerRow.CreateCell(4, CellType.String).SetCellValue("Last Updated");
numRows++;
foreach (var product in products)
{
var dataRow = sheet.CreateRow(numRows);
dataRow.CreateCell(0, CellType.String).SetCellValue(product.Id);
dataRow.CreateCell(1, CellType.String).SetCellValue(product.Code);
dataRow.CreateCell(2, CellType.String).SetCellValue(product.Name);
dataRow.CreateCell(3, CellType.Numeric).SetCellValue((double)product.Price);
dataRow.CreateCell(4, CellType.String).SetCellValue(product.LastUpdated?.ToString("yyyy-mm-dd"));
numRows++;
}
sheet.AutoSizeColumn(4);
var stream = new NpoiMemoryStream
{
AllowClose = false
};
workbook.Write(stream);
stream.Flush();
stream.Seek(0, SeekOrigin.Begin);
stream.AllowClose = true;
return(stream);
}
private FileStreamResult ExcelResult(List <Sessie> sessies, Dictionary <int, int> userCounts)
{
NpoiMemoryStream stream = new NpoiMemoryStream();
stream.AllowClose = false;
IWorkbook workbook = new XSSFWorkbook();
ISheet excelSheet = workbook.CreateSheet("Sessies");
int iRow = 0;
IRow row = excelSheet.CreateRow(iRow);
row.CreateCell(0).SetCellValue("Naam");
row.CreateCell(1).SetCellValue("Ruimte");
row.CreateCell(2).SetCellValue("Track");
row.CreateCell(3).SetCellValue("Tijd");
row.CreateCell(4).SetCellValue("Deelnemers");
row.CreateCell(5).SetCellValue("Capaciteit");
foreach (Sessie sessie in sessies)
{
iRow++;
row = excelSheet.CreateRow(iRow);
row.CreateCell(0).SetCellValue(sessie.Naam);
row.CreateCell(1).SetCellValue(sessie.Ruimte.Naam);
row.CreateCell(2).SetCellValue(sessie.Track.Naam);
row.CreateCell(3).SetCellValue(sessie.TimeRange());
int count = 0;
if (userCounts.ContainsKey(sessie.Id))
{
count = userCounts[sessie.Id];
}
row.CreateCell(4).SetCellValue(count);
row.CreateCell(5).SetCellValue(sessie.Ruimte.Capacity);
}
workbook.Write(stream);
stream.AllowClose = true;
stream.Seek(0, SeekOrigin.Begin);
return(File(stream, "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet", "sessies.xlsx"));
}
/// <summary>
/// Returns stream result of the export process.
/// </summary>
/// <param name="moduleNames">Modules to export.</param>
/// <param name="instanceInfo">Instance for which to execute modules.</param>
/// <returns>Result stream</returns>
public Stream GetExportStream(IEnumerable<string> moduleNames, IInstanceInfo instanceInfo)
{
// Create xlsx
IWorkbook document = new XSSFWorkbook();
// Create sheet to store results of text modules, and sumary of all other modules.
ISheet resultSummary = document.CreateSheet("Result summary");
resultSummary.CreateRow("Module", "Result", "Comment", "Description");
// Run every module and write its result.
foreach (string moduleName in moduleNames.Distinct())
{
var module = ModuleLoader.GetModule(moduleName);
var result = module.GetResults(instanceInfo);
var meta = module.GetModuleMetadata();
switch (result.ResultType)
{
case ModuleResultsType.String:
resultSummary.CreateRow(moduleName, result.Result as string, result.ResultComment, meta.Comment);
break;
case ModuleResultsType.List:
document.CreateSheet(moduleName).CreateRows(result.Result as IEnumerable<string>);
resultSummary.CreateRow(moduleName, "See details in tab", result.ResultComment, meta.Comment);
break;
case ModuleResultsType.Table:
document.CreateSheet(moduleName).CreateRows(result.Result as DataTable);
resultSummary.CreateRow(moduleName, "See details in tab", result.ResultComment, meta.Comment);
break;
case ModuleResultsType.ListOfTables:
DataSet data = result.Result as DataSet;
if (data == null)
{
resultSummary.CreateRow(moduleName, "Internal error: Invalid DataSet", result.ResultComment, meta.Comment);
break;
}
ISheet currentSheet = document.CreateSheet(moduleName);
foreach (DataTable tab in data.Tables)
{
// Create header
currentSheet.CreateRow(tab.TableName);
// Write data
currentSheet.CreateRows(tab);
// Create divider
currentSheet.CreateRow();
}
resultSummary.CreateRow(moduleName, "See details in tab", result.ResultComment, meta.Comment);
break;
default:
resultSummary.CreateRow(moduleName, "Internal error: Unknown module", result.ResultComment, meta.Comment);
continue;
}
}
// XWPFDocument.Write closes the stream. NpoiMemoryStream is used to prevent it.
NpoiMemoryStream stream = new NpoiMemoryStream(false);
document.Write(stream);
stream.Seek(0, SeekOrigin.Begin);
stream.AllowClose = true;
return stream;
}
/// <summary>
/// Returns stream result of the export process.
/// </summary>
/// <param name="moduleNames">Modules to export.</param>
/// <param name="instanceInfo">Instance for which to execute modules.</param>
/// <returns>Result stream</returns>
public Stream GetExportStream(IEnumerable <string> moduleNames, IInstanceInfo instanceInfo)
{
// Create docx
XWPFDocument document = null;
try
{
// Open docx template
document = new XWPFDocument(new FileInfo(@"Data\Templates\KInspectorReportTemplate.docx").OpenRead());
}
catch
{
// Create blank
document = new XWPFDocument();
}
// Create sumary paragraph containing results of text modules, and sumary of all other modules.
document.CreateParagraph("Result summary");
XWPFTable resultSummary = document.CreateTable();
resultSummary.GetRow(0).FillRow("Module", "Result", "Comment", "Description");
// Run every module and write its result.
foreach (string moduleName in moduleNames.Distinct())
{
var module = ModuleLoader.GetModule(moduleName);
var result = module.GetResults(instanceInfo);
var meta = module.GetModuleMetadata();
switch (result.ResultType)
{
case ModuleResultsType.String:
resultSummary.CreateRow().FillRow(moduleName, result.Result as string, result.ResultComment, meta.Comment);
break;
case ModuleResultsType.List:
document.CreateParagraph(moduleName);
document.CreateParagraph(result.ResultComment);
document.CreateTable().FillTable(result.Result as IEnumerable <string>);
resultSummary.CreateRow().FillRow(moduleName, "See details bellow", result.ResultComment, meta.Comment);
break;
case ModuleResultsType.Table:
document.CreateParagraph(moduleName);
document.CreateParagraph(result.ResultComment);
document.CreateTable().FillRows(result.Result as DataTable);
resultSummary.CreateRow().FillRow(moduleName, "See details bellow", result.ResultComment, meta.Comment);
break;
case ModuleResultsType.ListOfTables:
document.CreateParagraph(moduleName);
document.CreateParagraph(result.ResultComment);
DataSet data = result.Result as DataSet;
if (data == null)
{
resultSummary.CreateRow().FillRow(moduleName, "Internal error: Invalid DataSet", result.ResultComment, meta.Comment);
break;
}
foreach (DataTable tab in data.Tables)
{
// Create header
document.CreateParagraph(tab.TableName);
// Write data
document.CreateTable().FillRows(tab);
}
resultSummary.CreateRow().FillRow(moduleName, "See details bellow", result.ResultComment, meta.Comment);
break;
default:
resultSummary.CreateRow().FillRow(moduleName, "Internal error: Unknown module", result.ResultComment, meta.Comment);
continue;
}
}
// XWPFDocument.Write closes the stream. NpoiMemoryStream is used to prevent it.
NpoiMemoryStream stream = new NpoiMemoryStream(false);
document.Write(stream);
stream.Seek(0, SeekOrigin.Begin);
stream.AllowClose = true;
return(stream);
}
public static MemoryStream GetExcelFromDataTable(DataTable dt, string fileName)
{
try
{
IWorkbook workbook = new XSSFWorkbook(); // create *.xlsx file, use HSSFWorkbook() for creating *.xls file.XSSFWorkbook
ISheet sheet1 = workbook.CreateSheet();
IRow row1 = sheet1.CreateRow(0);
//for (int i = 0; dt.Columns.Count > i; i++)
//{
// row1.CreateCell(i).SetCellValue(dt.Columns[i].ColumnName);
//}
IRow rowHeader = sheet1.CreateRow(0);
//生成excel标题
rowHeader.CreateCell(0).SetCellValue("汇通单号");
rowHeader.CreateCell(1).SetCellValue("单据日期");
rowHeader.CreateCell(2).SetCellValue("订单号");
rowHeader.CreateCell(3).SetCellValue("收件人");
rowHeader.CreateCell(4).SetCellValue("收件人电话");
rowHeader.CreateCell(5).SetCellValue("收件人地址");
rowHeader.CreateCell(6).SetCellValue("物流公司");
rowHeader.CreateCell(7).SetCellValue("运单号");
rowHeader.CreateCell(8).SetCellValue("数量");
rowHeader.CreateCell(9).SetCellValue("状态");
for (int i = 0; dt.Rows.Count > i; i++)
{
IRow row = sheet1.CreateRow(i + 1);
for (int j = 0; dt.Columns.Count > j; j++)
{
row.CreateCell(j).SetCellValue(dt.Rows[i][j].ToString());
}
}
//using (var ms = new MemoryStream())
//{
// using (var sw = new StreamWriter(ms, Encoding.UTF8, 1024, true))
// {
// sw.WriteLine("data");
// sw.WriteLine("data 2");
// }
// ms.Position = 0;
//}
//MemoryStream ms1 = new MemoryStream();
//workbook.Write(ms1);
//ms1.Flush();
//ms1.Position = 0;
//return ms1;
NpoiMemoryStream ms = new NpoiMemoryStream
{
AllowClose = false
};
workbook.Write(ms);
ms.Flush();
ms.Seek(0, SeekOrigin.Begin);
ms.AllowClose = true;
return(ms);
//HttpResponseMessage result = new HttpResponseMessage(HttpStatusCode.OK);
//result.Content = new StreamContent(ms);
//result.Content.Headers.ContentType = new MediaTypeHeaderValue("application/octet-stream");
//result.Content.Headers.ContentDisposition = new ContentDispositionHeaderValue("attachment");
//result.Content.Headers.ContentDisposition.FileName = string.Format("{0}.xlsx", fileName);
}
catch (System.Exception ex)
{
return(null);
}
}
/// <summary>
/// Returns stream result of the export process.
/// </summary>
/// <param name="moduleNames">Modules to export.</param>
/// <param name="instanceInfo">Instance for which to execute modules.</param>
/// <returns>Result stream</returns>
public Stream GetExportStream(IEnumerable <string> moduleNames, IInstanceInfo instanceInfo)
{
// Create xlsx
IWorkbook document = new XSSFWorkbook();
// Create sheet to store results of text modules, and sumary of all other modules.
ISheet resultSummary = document.CreateSheet("Result summary");
resultSummary.CreateRow("Module", "Result", "Comment", "Description");
// Run every module and write its result.
foreach (string moduleName in moduleNames.Distinct())
{
var module = ModuleLoader.GetModule(moduleName);
var result = module.GetResults(instanceInfo);
var meta = module.GetModuleMetadata();
switch (result.ResultType)
{
case ModuleResultsType.String:
resultSummary.CreateRow(moduleName, result.Result as string, result.ResultComment, meta.Comment);
break;
case ModuleResultsType.List:
document.CreateSheet(moduleName).CreateRows(result.Result as IEnumerable <string>);
resultSummary.CreateRow(moduleName, "See details in tab", result.ResultComment, meta.Comment);
break;
case ModuleResultsType.Table:
document.CreateSheet(moduleName).CreateRows(result.Result as DataTable);
resultSummary.CreateRow(moduleName, "See details in tab", result.ResultComment, meta.Comment);
break;
case ModuleResultsType.ListOfTables:
DataSet data = result.Result as DataSet;
if (data == null)
{
resultSummary.CreateRow(moduleName, "Internal error: Invalid DataSet", result.ResultComment, meta.Comment);
break;
}
ISheet currentSheet = document.CreateSheet(moduleName);
foreach (DataTable tab in data.Tables)
{
// Create header
currentSheet.CreateRow(tab.TableName);
// Write data
currentSheet.CreateRows(tab);
// Create divider
currentSheet.CreateRow();
}
resultSummary.CreateRow(moduleName, "See details in tab", result.ResultComment, meta.Comment);
break;
default:
resultSummary.CreateRow(moduleName, "Internal error: Unknown module", result.ResultComment, meta.Comment);
continue;
}
}
// XWPFDocument.Write closes the stream. NpoiMemoryStream is used to prevent it.
NpoiMemoryStream stream = new NpoiMemoryStream(false);
document.Write(stream);
stream.Seek(0, SeekOrigin.Begin);
stream.AllowClose = true;
return(stream);
}
public ActionResult ExportExcel()
{
string filePath = Request.MapPath("/ExcelTemplate/BCGDDJK.xlsx");
//创建Excel工作薄
XSSFWorkbook workbook = new XSSFWorkbook(filePath);
//创建单元格样式
ICellStyle cellStyle = workbook.CreateCellStyle();
IFont font = workbook.CreateFont();
font.FontName = "宋体";
font.FontHeightInPoints = 10;
cellStyle.SetFont(font);
//设置单元格居中显示
cellStyle.Alignment = HorizontalAlignment.Center;
cellStyle.VerticalAlignment = VerticalAlignment.Center;
//设置文本换行显示
cellStyle.WrapText = true;
//设置单元格边框样式
cellStyle.BorderTop = BorderStyle.Thin;
cellStyle.BorderBottom = BorderStyle.Thin;
cellStyle.BorderLeft = BorderStyle.Thin;
cellStyle.BorderRight = BorderStyle.Thin;
//创建Excel工作表
ISheet sheet = workbook.GetSheetAt(0);
List <TBL_JSYDBP_BCGDDJK_ZB1> djkList = ZB1Service.LoadEntities(t => true).OrderBy(t => t.SCBH).ToList();
List <TBL_JSYDBP_BCGDDJK_ZB2> djkChildList = new List <TBL_JSYDBP_BCGDDJK_ZB2>();
foreach (var item in djkList)
{
var tempList = ZB2Service.LoadEntities(t => t.BCGDDJKID == item.BCGDDJKID && t.GLID == item.SCBH).ToList();
if (tempList.Count == 0)
{
djkChildList.Add(new TBL_JSYDBP_BCGDDJK_ZB2());
}
else
{
djkChildList.AddRange(tempList);
}
}
foreach (var djk in djkList)
{
int count = ZB2Service.LoadEntities(t => t.BCGDDJKID == djk.BCGDDJKID && t.GLID == djk.SCBH).Count();
count = count == 0 ? 1 : count;
djk.COUNT = count;
}
string[] pros = { "BCGDDD", "JSGM", "JZGDMJ", "QZST", "YSDW", "YSSJ", "YSWH", "ZBQRDW", "QRSJ", "XMWZ", "TF", "TBH", "ZXDWZ", "YDL", "SCBH" };
string[] prosChild = { "APJSXMJZYGDDW", "ZYD", "ZYGDMJ", "BCGDMJ", "SYGDMJ", "SYGDMJ", "BZ" };
int add = 0;
int first = 0;
for (int i = 0; i < djkList.Count; i++)
{
add += (int)djkList[i].COUNT - 1;
IRow row = sheet.CreateRow(i + 2 + first);
ICell cell0 = row.CreateCell(0);
cell0.SetCellValue(i + 1);
CellRangeAddress regionOne = new CellRangeAddress(i + 2 + first, i + 2 + add, 0, 0);
MergedStyle(cellStyle, sheet, regionOne);
Type t = djkList[i].GetType();
foreach (PropertyInfo pi in t.GetProperties())
{
object value = pi.GetValue(djkList[i]);
string name = pi.Name;
string type = pi.PropertyType.Name;
for (int j = 0; j < pros.Length; j++)
{
if (name == pros[j])
{
CellRangeAddress region = new CellRangeAddress(i + 2 + first, i + 2 + add, j + 1, j + 1);
ICell cell = row.CreateCell(j + 1);
double result;
if (value != null)
{
if ((!double.TryParse(value.ToString(), out result)) && type == "Nullable`1")
{
cell.SetCellValue(Convert.ToDateTime(value).ToShortDateString());
}
else
{
cell.SetCellValue(value.ToString());
}
}
MergedStyle(cellStyle, sheet, region);
}
}
}
first = add;
}
for (int i = 0; i < djkChildList.Count; i++)
{
Type t = djkChildList[i].GetType();
IRow row = sheet.GetRow(i + 2) == null?sheet.CreateRow(i + 2) : sheet.GetRow(i + 2);
foreach (PropertyInfo pi in t.GetProperties())
{
object value = pi.GetValue(djkChildList[i]);
string name = pi.Name;
string type = pi.PropertyType.Name;
for (int j = 0; j < prosChild.Length; j++)
{
if (name == prosChild[j])
{
ICell cell = row.CreateCell(j + 1 + pros.Length);
double result;
if (value != null)
{
if ((!double.TryParse(value.ToString(), out result)) && type == "Nullable`1")
{
cell.SetCellValue(Convert.ToDateTime(value).ToShortDateString());
}
else
{
cell.SetCellValue(value.ToString());
}
}
cell.CellStyle = cellStyle;
}
}
}
}
var ms = new NpoiMemoryStream();
ms.AllowClose = false;
workbook.Write(ms);
ms.Flush();
ms.Seek(0, SeekOrigin.Begin);
ms.AllowClose = true;
return(File(ms, "application/ vnd.ms-excel", "补充耕地项目指标使用台帐.xlsx"));
}
public ActionResult ExportExcel(decimal?year)
{
string filePath = Request.MapPath("/ExcelTemplate/TBL_ZDCQ_HFZFHBBT.xlsx");
//创建Excel工作薄
XSSFWorkbook workbook = new XSSFWorkbook(filePath);
//创建单元格样式
ICellStyle cellStyle = workbook.CreateCellStyle();
//设置单元格居中显示
cellStyle.Alignment = HorizontalAlignment.Center;
//设置文本换行显示
cellStyle.WrapText = true;
//设置单元格边框样式
cellStyle.BorderTop = BorderStyle.Thin;
cellStyle.BorderBottom = BorderStyle.Thin;
cellStyle.BorderLeft = BorderStyle.Thin;
cellStyle.BorderRight = BorderStyle.Thin;
//创建Excel工作表
ISheet sheet = workbook.GetSheetAt(0);
ICell cellTitle = sheet.GetRow(0).GetCell(0);
string title = cellTitle.StringCellValue;
title = title.Replace("{year}", year?.ToString());
cellTitle.SetCellValue(title);
title = title.Replace("\n", null);
var List = TBL_ZDCQ_HFZFHBBTService.LoadEntities(t => t.YEAR != null && t.YEAR == year).OrderBy(t => t.ID).ToList();
var totalEntitis = TBL_ZDCQ_HFZFHBBTService.GetTotalEntitis(year);
List.AddRange(totalEntitis);
string[] pros = { "XMMC", "PC", "HS", "NYRK", "ZXCZ1", "ZXCZ2", "HJJE", "QKSJ", "BZ" };
for (int i = 0; i < List.Count; i++)
{
IRow row = sheet.CreateRow(i + 4);
ICell cell0 = row.CreateCell(0);
cell0.SetCellValue(i + 1);
cell0.CellStyle = cellStyle;
Type t = List[i].GetType();
foreach (PropertyInfo pi in t.GetProperties())
{
object value = pi.GetValue(List[i]);
string name = pi.Name;
string type = pi.PropertyType.Name;
for (int j = 0; j < pros.Length; j++)
{
if (name == pros[j])
{
ICell cell = row.CreateCell(j + 1);
cell.CellStyle = cellStyle;
double result;
if (value == null)
{
continue;
}
if ((!double.TryParse(value.ToString(), out result)) && type == "Nullable`1")
{
cell.SetCellValue(Convert.ToDateTime(value).ToShortDateString());
}
else
{
cell.SetCellValue(value.ToString());
}
}
}
}
}
var ms = new NpoiMemoryStream();
ms.AllowClose = false;
workbook.Write(ms);
ms.Flush();
ms.Seek(0, SeekOrigin.Begin);
ms.AllowClose = true;
return(File(ms, "application/ vnd.ms-excel", $"{title}.xlsx"));
}
//得到excel文件流
public static MemoryStream ExcelStream(DataTable dt, IEnumerable <string> colNames, bool hasTitle)
{
try
{
//var list = dc.v_bs_dj_bbcdd1.Where(eps).ToList();
XSSFWorkbook hssfworkbook = new XSSFWorkbook();
ISheet sheet1 = hssfworkbook.CreateSheet("保税订单");
IRow rowHeader = sheet1.CreateRow(0);
//生成excel标题
rowHeader.CreateCell(0).SetCellValue("汇通单号");
rowHeader.CreateCell(1).SetCellValue("单据日期");
rowHeader.CreateCell(2).SetCellValue("订单号");
rowHeader.CreateCell(3).SetCellValue("收件人");
rowHeader.CreateCell(4).SetCellValue("收件人电话");
rowHeader.CreateCell(5).SetCellValue("收件人地址");
rowHeader.CreateCell(6).SetCellValue("物流公司");
rowHeader.CreateCell(7).SetCellValue("运单号");
rowHeader.CreateCell(8).SetCellValue("数量");
rowHeader.CreateCell(9).SetCellValue("状态");
//生成excel内容
//for (int i = 0; i < list.Count; i++)
//{
// NPOI.SS.UserModel.IRow rowtemp = sheet1.CreateRow(i + 1);
// rowtemp.CreateCell(0).SetCellValue(list[i].bh_user);
// rowtemp.CreateCell(1).SetCellValue(list[i].rq.Value.ToString("yyyy-MM-dd HH:mm:dd"));
// rowtemp.CreateCell(2).SetCellValue(list[i].bh_khdd);
// rowtemp.CreateCell(3).SetCellValue(list[i].re_name);
// rowtemp.CreateCell(4).SetCellValue(list[i].re_tel);
// rowtemp.CreateCell(5).SetCellValue(list[i].re_fulladdress);
// rowtemp.CreateCell(6).SetCellValue(list[i].bm_kdgs);
// rowtemp.CreateCell(7).SetCellValue(list[i].kddh);
// rowtemp.CreateCell(8).SetCellValue((int)list[i].sl_total);
// rowtemp.CreateCell(9).SetCellValue(list[i].mc_state_dd);
//}
for (int i = 0; i < 10; i++)
{
sheet1.AutoSizeColumn(i);
}
//MemoryStream file = new MemoryStream();
//hssfworkbook.Write(file);
//file.Seek(0, SeekOrigin.Begin);
//return file;
//导出Excel文件的方法
var ms = new NpoiMemoryStream();
ms.AllowClose = false;
hssfworkbook.Write(ms);
ms.Flush();
ms.Seek(0, SeekOrigin.Begin);
ms.AllowClose = true;
return(ms);
}
catch (System.Exception ex)
{
return(null);
}
//return File(file, "application/vnd.ms-excel", "保税订单.xls");
}
public ActionResult ExportExcel(long id)
{
IWorkbook wb = new XSSFWorkbook();
ICellStyle style1 = wb.CreateCellStyle(); //样式
IFont font1 = wb.CreateFont(); //字体
font1.FontName = "楷体";
font1.Boldweight = (short)FontBoldWeight.Normal; //字体加粗样式
style1.SetFont(font1); //样式里的字体设置具体的字体样式
style1.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center; //文字水平对齐方式
style1.VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment.Center; //文字垂直对齐方式
//设置边框
style1.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
style1.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
style1.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
style1.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;
IdNameDTO[] cities = idNameService.GetAll("市级");
foreach (var city in cities)
{
ISheet sheet = wb.CreateSheet(city.Name);
//int[] columnWidth = { 10, 10, 20, 10 };
//for (int i = 0; i < columnWidth.Length; i++)
//{
// //设置列宽度，256*字符数，因为单位是1/256个字符
// sheet.SetColumnWidth(i, 256 * columnWidth[i]);
//}
int rowCount = 1;
EntryListDTO[] entries = entryService.GetByTrainIdCityId(id, city.Id);
string[] columnName = { "编号", "姓名", "性别", "工作单位", "职务", "手机号", "住宿要求", "支付方式", "发票抬头", "税号", "地址", "联系方式", "开户行", "银行账号" };
IRow row;
ICell cell;
row = sheet.CreateRow(0);
for (int j = 0; j < columnName.Length; j++)
{
cell = row.CreateCell(j);//创建第j列
cell.CellStyle = style1;
ExcelHelper.SetCellValue(cell, columnName[j]);
}
if (entries != null)
{
foreach (var entry in entries)
{
row = sheet.CreateRow(rowCount);
cell = row.CreateCell(0);
cell.CellStyle = style1;
ExcelHelper.SetCellValue(cell, rowCount);
sheet.AutoSizeColumn(0);
cell = row.CreateCell(1);
cell.CellStyle = style1;
ExcelHelper.SetCellValue(cell, entry.Name);
sheet.AutoSizeColumn(1);
cell = row.CreateCell(2);
cell.CellStyle = style1;
ExcelHelper.SetCellValue(cell, entry.Gender?"男":"女");
sheet.AutoSizeColumn(2);
cell = row.CreateCell(3);
cell.CellStyle = style1;
ExcelHelper.SetCellValue(cell, entry.WorkUnits);
sheet.AutoSizeColumn(3);
cell = row.CreateCell(4);
cell.CellStyle = style1;
ExcelHelper.SetCellValue(cell, entry.Duty);
sheet.AutoSizeColumn(4);
cell = row.CreateCell(5);
cell.CellStyle = style1;
ExcelHelper.SetCellValue(cell, entry.Mobile);
sheet.AutoSizeColumn(5);
cell = row.CreateCell(6);
cell.CellStyle = style1;
ExcelHelper.SetCellValue(cell, entry.StayName);
sheet.AutoSizeColumn(6);
cell = row.CreateCell(7);
cell.CellStyle = style1;
ExcelHelper.SetCellValue(cell, entry.PayName);
sheet.AutoSizeColumn(7);
cell = row.CreateCell(8);
cell.CellStyle = style1;
ExcelHelper.SetCellValue(cell, entry.InvoiceUp);
sheet.AutoSizeColumn(8);
cell = row.CreateCell(9);
cell.CellStyle = style1;
ExcelHelper.SetCellValue(cell, entry.Ein);
sheet.AutoSizeColumn(9);
cell = row.CreateCell(10);
cell.CellStyle = style1;
ExcelHelper.SetCellValue(cell, entry.Address);
sheet.AutoSizeColumn(10);
cell = row.CreateCell(11);
cell.CellStyle = style1;
ExcelHelper.SetCellValue(cell, entry.Contact);
sheet.AutoSizeColumn(11);
cell = row.CreateCell(12);
cell.CellStyle = style1;
ExcelHelper.SetCellValue(cell, entry.OpenBank);
sheet.AutoSizeColumn(12);
cell = row.CreateCell(13);
cell.CellStyle = style1;
ExcelHelper.SetCellValue(cell, entry.BankAccount);
sheet.AutoSizeColumn(13);
rowCount++;
}
}
}
var ms = new NpoiMemoryStream();
ms.AllowClose = false;
wb.Write(ms);
ms.Flush();
ms.Seek(0, SeekOrigin.Begin);
ms.AllowClose = true;
return(File(ms, "application/vnd.ms-excel", "报名用户汇总.xls"));
}
public ActionResult ExportExcel(DateTime?txtTjrq, string txtBM, string lableBM)
{
string filePath = Request.MapPath("/ExcelTemplate/tbl_gzmxb_tj.xlsx");
//创建Excel工作薄
XSSFWorkbook workbook = new XSSFWorkbook(filePath);
//创建单元格样式
ICellStyle cellStyle = workbook.CreateCellStyle();
//设置单元格居中显示
cellStyle.Alignment = HorizontalAlignment.Center;
//设置文本换行显示
cellStyle.WrapText = true;
//设置单元格边框样式
cellStyle.BorderTop = BorderStyle.Thin;
cellStyle.BorderBottom = BorderStyle.Thin;
cellStyle.BorderLeft = BorderStyle.Thin;
cellStyle.BorderRight = BorderStyle.Thin;
//创建Excel工作表
ISheet sheet = workbook.GetSheetAt(0);
var tempList = TBL_GZMXB_TJService.LoadEntities(t => true);
if (txtTjrq == null)
{
tempList = tempList.Where(t => false);
}
else
{
DateTime tjrq = Convert.ToDateTime(txtTjrq);
tempList = tempList.Where(t => t.TJRQ.Year == tjrq.Year && t.TJRQ.Month == tjrq.Month);
}
if (!string.IsNullOrEmpty(txtBM))
{
tempList = tempList.Where(t => t.BM == txtBM);
IRow row = sheet.GetRow(1);
ICell cell = row.GetCell(0);
cell.SetCellValue("单位：" + lableBM);
}
List <Model.TBL_GZMXB_TJ> tjList = tempList.ToList();
string[] pros = { "XM", "XB", "WHCD", "JRBDWSJ", "SFZHM", "GZGW", "JBGZ", "GWGZ", "HSBZ", "JTBZ", "BYXZJBGZ", "BYXZGWGZ", "HJ", "ZXSJ", "BZ" };
for (int i = 0; i < tjList.Count; i++)
{
IRow row = sheet.CreateRow(i + 4);
ICell cell0 = row.CreateCell(0);
cell0.SetCellValue(i + 1);
cell0.CellStyle = cellStyle;
Type t = tjList[i].GetType();
foreach (PropertyInfo pi in t.GetProperties())
{
object value = pi.GetValue(tjList[i]);
string name = pi.Name;
string type = pi.PropertyType.Name;
for (int j = 0; j < pros.Length; j++)
{
if (name == pros[j])
{
ICell cell = row.CreateCell(j + 1);
cell.CellStyle = cellStyle;
double result;
if (value == null)
{
continue;
}
if ((!double.TryParse(value.ToString(), out result)) && type == "Nullable`1")
{
cell.SetCellValue(Convert.ToDateTime(value).ToShortDateString());
}
else
{
cell.SetCellValue(value.ToString());
}
}
}
}
}
var ms = new NpoiMemoryStream();
ms.AllowClose = false;
workbook.Write(ms);
ms.Flush();
ms.Seek(0, SeekOrigin.Begin);
ms.AllowClose = true;
return(File(ms, "application/ vnd.ms-excel", "长沙县国土资源局劳务派遣人员调整工资明细表.xlsx"));
}
public static MemoryStream GetDataTableToMemory(DataTable rowData, IEnumerable <string> colNames, bool v)
{
XSSFWorkbook workbook = new XSSFWorkbook();
string TableName = rowData.TableName;
if (string.IsNullOrEmpty(TableName))
{
TableName = "Sheet1";
}
XSSFSheet sheet = (XSSFSheet)workbook.CreateSheet(TableName);
XSSFCellStyle dateStyle = (XSSFCellStyle)workbook.CreateCellStyle();
XSSFDataFormat format = (XSSFDataFormat)workbook.CreateDataFormat();
dateStyle.DataFormat = format.GetFormat("yyyy-mm-dd");
//dateStyle.FillBackgroundColor = IndexedColors.Blue.Index;
//dateStyle.FillPattern = FillPattern.Bricks;
XSSFCellStyle cellStyle = (XSSFCellStyle)workbook.CreateCellStyle();
//设置样式---
//设置单元格上下左右边框线
cellStyle.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;
cellStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
cellStyle.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
cellStyle.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
//文字水平和垂直对齐方式
cellStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Left;
cellStyle.VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment.Center;
//cellStyle.DataFormat = 194;
//cellStyle.FillForegroundColor = IndexedColors.Pink.Index;
//cellStyle.FillPattern = FillPattern.SolidForeground;
int rowIndex = 0;
XSSFRow eheaderRow = (XSSFRow)sheet.CreateRow(0);
List <string> lcolName = new List <string>();
foreach (var item in colNames)
{
lcolName.Add(item);
}
for (int i = 0; i < lcolName.Count; i++)
{
sheet.SetColumnWidth(i, 14 * 256);
XSSFCellStyle headStyle = (XSSFCellStyle)workbook.CreateCellStyle();
//headStyle.Alignment = CellHorizontalAlignment.CENTER;
XSSFFont font = (XSSFFont)workbook.CreateFont();
font.FontHeightInPoints = 10;
font.Boldweight = 700;
headStyle.SetFont(font);
headStyle.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;
headStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
headStyle.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
headStyle.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
//文字水平和垂直对齐方式
headStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
headStyle.VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment.Center;
eheaderRow.CreateCell(i).SetCellValue(lcolName[i]);
eheaderRow.GetCell(i).CellStyle = headStyle;
}
rowIndex = 1;
foreach (DataRow row in rowData.Rows)
{
XSSFRow dataRow = (XSSFRow)sheet.CreateRow(rowIndex);
#region 填充内容
foreach (DataColumn column in rowData.Columns)
{
if (column.ColumnName == "CARTON_GROSS_WEIGTH")
{
string ssw = row[column].ToString();
}
string ss = row[column].ToString();
XSSFCell newCell = (XSSFCell)dataRow.CreateCell(column.Ordinal);
string type = row[column].GetType().FullName.ToString();
newCell.SetCellValue(GetValue(row[column].ToString(), type));
newCell.CellStyle = cellStyle;
}
#endregion
rowIndex++;
}
NpoiMemoryStream ms = new NpoiMemoryStream
{
AllowClose = false
};
workbook.Write(ms);
ms.Flush();
ms.Seek(0, SeekOrigin.Begin);
ms.AllowClose = true;
return(ms);
//throw new NotImplementedException();
}
/// <summary>
/// 块状数据批量导出Excel
/// </summary>
/// <param name="sheetName">Sheet名</param>
/// <param name="tupleList">标题名, key:列名-Value:字段名, 数据源</param>
/// <param name="isWidth">列宽自适应(谨慎,字数少的可用)</param>
/// <returns></returns>
public MemoryStream ExportExcelBlockData(string sheetName, List <Tuple <string, Dictionary <string, string>, DataTable> > tupleList, bool isWidth = false)
{
var workbook = new XSSFWorkbook();
//创建工作簿
var sheet = workbook.CreateSheet(sheetName);
//创建样式
var tStyle = workbook.CreateCellStyle();//标题样式
var font = workbook.CreateFont();
font.Boldweight = (short)FontBoldWeight.Bold; //加粗
tStyle.SetFont(font);
tStyle.Alignment = HorizontalAlignment.Center; // 左右居中
tStyle.VerticalAlignment = VerticalAlignment.Center; // 上下居中
tStyle.WrapText = false; //是否换行
tStyle.FillForegroundColor = HSSFColor.Grey25Percent.Index; //灰色
tStyle.FillPattern = FillPattern.SolidForeground;
var cStyle = workbook.CreateCellStyle(); //内容样式
cStyle.Alignment = HorizontalAlignment.Center;
cStyle.VerticalAlignment = VerticalAlignment.Center;
cStyle.WrapText = true;
//填充数据
var maxColumn = tupleList.Select(x => x.Item2.Count).Max() - 1;
var rowIndex = 0;//行号
foreach (var tuple in tupleList)
{
//标题
var rowHead = sheet.CreateRow(rowIndex);
var cellHead = rowHead.CreateCell(0, CellType.String);
cellHead.SetCellValue(tuple.Item1);
cellHead.CellStyle = tStyle;
sheet.AddMergedRegion(new CellRangeAddress(rowIndex, rowIndex, 0, maxColumn));
rowIndex++;
//列名
var rowTitle = sheet.CreateRow(rowIndex);
for (var i = 0; i < tuple.Item2.Count; i++)
{
var text = tuple.Item2.ElementAt(i).Key;
if (string.IsNullOrEmpty(text))
{
continue;
}
var cellTitle = rowTitle.CreateCell(i, CellType.String);
cellTitle.SetCellValue(text);
cellTitle.CellStyle = cStyle;
}
sheet.CreateFreezePane(0, 1, 0, 1);
rowIndex++;
//数据
foreach (DataRow dr in tuple.Item3.Rows)
{
var row = sheet.CreateRow(rowIndex);
var height = 1;
var col = 0;
foreach (var column in tuple.Item2)
{
var value = dr.Table.Columns[column.Value] == null ? "" : dr[column.Value].ToString();
height = Math.Max(value.Count(ch => ch == '\n') + 1, height);
row.CreateCell(col).SetCellValue(value);
row.GetCell(col).CellStyle = cStyle;
col++;
}
row.Height = (short)(height * 15 * 20);
rowIndex++;
}
}
//列宽自适应
if (isWidth)
{
//列宽自适应，只对英文和数字有效
for (var i = 0; i <= maxColumn; i++)
{
sheet.AutoSizeColumn(i);
}
//获取当前列的宽度，然后对比本列的长度，取最大值
for (var columnNum = 0; columnNum <= maxColumn; columnNum++)
{
var columnWidth = sheet.GetColumnWidth(columnNum) / 256;
for (var rowNum = 1; rowNum <= sheet.LastRowNum; rowNum++)
{
//当前行未被使用过
var currentRow = sheet.GetRow(rowNum) ?? sheet.CreateRow(rowNum);
if (currentRow.GetCell(columnNum) == null)
{
continue;
}
var currentCell = currentRow.GetCell(columnNum);
var length = Encoding.Default.GetBytes(currentCell.ToString()).Length;
if (columnWidth < length)
{
columnWidth = length;
}
}
sheet.SetColumnWidth(columnNum, columnWidth < 255 ? columnWidth * 256 : 6000);
}
}
var ms = new NpoiMemoryStream {
AllowClose = false
};
workbook.Write(ms);
ms.Flush();
ms.Seek(0, SeekOrigin.Begin);
ms.AllowClose = true;
return(ms);
}
public ActionResult CreateExcel(long id)
{
if (id <= 0)
{
return(Json(new AjaxResult {
Status = "error", ErrorMsg = "不存在这个答题活动"
}));
}
UserDTO[] dtos = userService.GetByActivityIdIsWon1(id);
IWorkbook wb1 = new XSSFWorkbook();
ISheet sheet1 = wb1.CreateSheet();
sheet1.AutoSizeColumn(1);
//sheet1.SetColumnWidth(0, 30 * 256);
IRow Row1 = sheet1.CreateRow(0);
ICellStyle style = wb1.CreateCellStyle();
style.Alignment = HorizontalAlignment.Center;
//新建一个字体样式对象
IFont font = wb1.CreateFont();
//设置字体加粗样式
font.Boldweight = short.MaxValue;
//使用SetFont方法将字体样式添加到单元格样式中
style.SetFont(font);
ICell cell0 = Row1.CreateCell(0);
cell0.CellStyle = style;
cell0.SetCellValue("编号");
ICell cell1 = Row1.CreateCell(1);
cell1.CellStyle = style;
cell1.SetCellValue("昵称");
ICell cell2 = Row1.CreateCell(2);
cell2.CellStyle = style;
cell2.SetCellValue("姓名");
ICell cell3 = Row1.CreateCell(3);
cell3.CellStyle = style;
cell3.SetCellValue("手机号");
ICell cell4 = Row1.CreateCell(4);
cell4.CellStyle = style;
cell4.SetCellValue("联系地址");
ICell cell5 = Row1.CreateCell(5);
cell5.CellStyle = style;
cell5.SetCellValue("参与活动次数");
ICell cell6 = Row1.CreateCell(6);
cell6.CellStyle = style;
cell6.SetCellValue("中奖次数");
int i = 1;
foreach (var dto in dtos)
{
Row1 = sheet1.CreateRow(i++);
cell0 = Row1.CreateCell(0);
cell0.CellStyle = style;
cell0.SetCellValue(dto.Id);
cell1 = Row1.CreateCell(1);
cell1.CellStyle = style;
cell1.SetCellValue(dto.NickName);
cell2 = Row1.CreateCell(2);
cell2.CellStyle = style;
cell2.SetCellValue(dto.Name);
cell3 = Row1.CreateCell(3);
cell3.CellStyle = style;
cell3.SetCellValue(dto.Mobile);
cell4 = Row1.CreateCell(4);
cell4.CellStyle = style;
cell4.SetCellValue(dto.Address);
cell5 = Row1.CreateCell(5);
cell5.CellStyle = style;
cell5.SetCellValue(dto.PassCount);
cell6 = Row1.CreateCell(6);
cell6.CellStyle = style;
cell6.SetCellValue(dto.WinCount);
}
for (int j = 0; j <= 6; j++)
{
sheet1.SetColumnWidth(j, 20 * 150);
}
sheet1.SetColumnWidth(4, 40 * 256);
var ms = new NpoiMemoryStream();
ms.AllowClose = false;
wb1.Write(ms);
ms.Flush();
ms.Seek(0, SeekOrigin.Begin);
ms.AllowClose = true;
// 写入到客户端
return(File(ms, "application/vnd.ms-excel", "获奖用户信息.xls"));
}
/// <summary>
/// Returns stream result of the export process.
/// </summary>
/// <param name="moduleNames">Modules to export.</param>
/// <param name="instanceInfo">Instance for which to execute modules.</param>
/// <returns>Result stream</returns>
public Stream GetExportStream(IEnumerable<string> moduleNames, IInstanceInfo instanceInfo)
{
// Create docx
XWPFDocument document = null;
try
{
// Open docx template
document = new XWPFDocument(new FileInfo(@"Data\Templates\KInspectorReportTemplate.docx").OpenRead());
}
catch
{
// Create blank
document = new XWPFDocument();
}
// Create sumary paragraph containing results of text modules, and sumary of all other modules.
document.CreateParagraph("Result summary");
XWPFTable resultSummary = document.CreateTable();
resultSummary.GetRow(0).FillRow("Module", "Result", "Comment", "Description");
// Run every module and write its result.
foreach (string moduleName in moduleNames.Distinct())
{
var module = ModuleLoader.GetModule(moduleName);
var result = module.GetResults(instanceInfo);
var meta = module.GetModuleMetadata();
switch (result.ResultType)
{
case ModuleResultsType.String:
resultSummary.CreateRow().FillRow(moduleName, result.Result as string, result.ResultComment, meta.Comment);
break;
case ModuleResultsType.List:
document.CreateParagraph(moduleName);
document.CreateParagraph(result.ResultComment);
document.CreateTable().FillTable(result.Result as IEnumerable<string>);
resultSummary.CreateRow().FillRow(moduleName, "See details bellow", result.ResultComment, meta.Comment);
break;
case ModuleResultsType.Table:
document.CreateParagraph(moduleName);
document.CreateParagraph(result.ResultComment);
document.CreateTable().FillRows(result.Result as DataTable);
resultSummary.CreateRow().FillRow(moduleName, "See details bellow", result.ResultComment, meta.Comment);
break;
case ModuleResultsType.ListOfTables:
document.CreateParagraph(moduleName);
document.CreateParagraph(result.ResultComment);
DataSet data = result.Result as DataSet;
if (data == null)
{
resultSummary.CreateRow().FillRow(moduleName, "Internal error: Invalid DataSet", result.ResultComment, meta.Comment);
break;
}
foreach (DataTable tab in data.Tables)
{
// Create header
document.CreateParagraph(tab.TableName);
// Write data
document.CreateTable().FillRows(tab);
}
resultSummary.CreateRow().FillRow(moduleName, "See details bellow", result.ResultComment, meta.Comment);
break;
default:
resultSummary.CreateRow().FillRow(moduleName, "Internal error: Unknown module", result.ResultComment, meta.Comment);
continue;
}
}
// XWPFDocument.Write closes the stream. NpoiMemoryStream is used to prevent it.
NpoiMemoryStream stream = new NpoiMemoryStream(false);
document.Write(stream);
stream.Seek(0, SeekOrigin.Begin);
stream.AllowClose = true;
return stream;
}
public ActionResult ExportToExcel(SerialNumberDataViewModel model)
{
string strErrorMessage = string.Empty;
MethodReturnResult <DataSet> result = new MethodReturnResult <DataSet>();
try
{
DataTable dt = new DataTable();
RPTLotQueryDetailParameter param = new RPTLotQueryDetailParameter();
param = GetQueryCondition(model, false);
using (LotQueryServiceClient client = new LotQueryServiceClient())
{
MethodReturnResult <DataSet> ds = client.GetMapDataQueryDb(ref param);
if (ds.Code == 0 && ds.Data != null && ds.Data.Tables.Count > 0)
{
dt = ds.Data.Tables[0];
}
}
string template_path = Server.MapPath("~\\Labels\\");//模板路径
string template_file = template_path + "匹配数据模板.xlsx";
FileInfo tempFileInfo = new FileInfo(template_file);
FileStream file = new FileStream(template_file, FileMode.Open, FileAccess.Read);
IWorkbook xssfworkbook = new XSSFWorkbook(file);
//创建sheet
ISheet sheet1 = xssfworkbook.GetSheet("Sheet1");
//创建单元格和单元格样式
ICellStyle styles = xssfworkbook.CreateCellStyle();
styles.FillForegroundColor = 10;
styles.BorderBottom = BorderStyle.Thin;
styles.BorderLeft = BorderStyle.Thin;
styles.BorderRight = BorderStyle.Thin;
styles.BorderTop = BorderStyle.Thin;
styles.VerticalAlignment = VerticalAlignment.Center;
styles.Alignment = HorizontalAlignment.Center;
IFont font = xssfworkbook.CreateFont();
font.Boldweight = 10;
styles.SetFont(font);
ICellStyle style = xssfworkbook.CreateCellStyle();
style.FillForegroundColor = 10;
style.BorderBottom = BorderStyle.Thin;
style.BorderLeft = BorderStyle.Thin;
style.BorderRight = BorderStyle.Thin;
style.BorderTop = BorderStyle.Thin;
style.VerticalAlignment = VerticalAlignment.Center;
IFont fonts = xssfworkbook.CreateFont();
font.Boldweight = 10;
for (int j = 0; j < dt.Rows.Count; j++)
{
ICell cellData = null;
IRow rowData = null;
rowData = sheet1.CreateRow(j + 2);
#region //数据
cellData = rowData.CreateCell(rowData.Cells.Count);
cellData.CellStyle = style;
cellData.SetCellValue(j + 1); //序号
cellData = rowData.CreateCell(rowData.Cells.Count);
cellData.CellStyle = style;
cellData.SetCellValue(dt.Rows[j]["SN码"].ToString());//SN码
cellData = rowData.CreateCell(rowData.Cells.Count);
cellData.CellStyle = style;
cellData.SetCellValue(dt.Rows[j]["标签条码"].ToString());//标签条码
cellData = rowData.CreateCell(rowData.Cells.Count);
cellData.CellStyle = style;
cellData.SetCellValue(dt.Rows[j]["标签条码位数"].ToString());//标签条码位数
cellData = rowData.CreateCell(rowData.Cells.Count);
cellData.CellStyle = style;
cellData.SetCellValue(dt.Rows[j]["标签条码格式"].ToString());//标签条码格式
cellData = rowData.CreateCell(rowData.Cells.Count);
cellData.CellStyle = style;
cellData.SetCellValue(dt.Rows[j]["工单号"].ToString());//工单号
cellData = rowData.CreateCell(rowData.Cells.Count);
cellData.CellStyle = style;
cellData.SetCellValue(dt.Rows[j]["包装号"].ToString());//包装号
cellData = rowData.CreateCell(rowData.Cells.Count);
cellData.CellStyle = style;
cellData.SetCellValue(dt.Rows[j]["产品编码"].ToString());//产品编码
cellData = rowData.CreateCell(rowData.Cells.Count);
cellData.CellStyle = style;
cellData.SetCellValue(dt.Rows[j]["工步"].ToString());//工步
cellData = rowData.CreateCell(rowData.Cells.Count);
cellData.CellStyle = style;
cellData.SetCellValue(dt.Rows[j]["订单编号"].ToString());//订单编号
#endregion
}
var ms = new NpoiMemoryStream();
ms.AllowClose = false;
xssfworkbook.Write(file);
xssfworkbook.Write(ms);
ms.Flush();
ms.Position = 0;
ms.AllowClose = false;
return(File(ms, "application/vnd.ms-excel", "匹配数据模板.xlsx"));
}
catch (Exception ex)
{
result.Code = 1000;
result.Message = ex.Message;
result.Detail = ex.ToString();
return(Content(result.Message));
}
}
/// <summary>
/// 导出批次物料出库数据到Excel
/// </summary>
/// <param name="model"></param>
/// <returns></returns>
public async Task <ActionResult> ExportOutToExcel(LotMaterialListOutViewModel model)
{
DataTable dt = new DataTable();
if (Resulted.Code == 0 && Resulted.Data != null && Resulted.Data.Tables.Count > 0)
{
dt = Resulted.Data.Tables[0];
}
else
{
using (LotMaterialListServiceClient client = new LotMaterialListServiceClient())
{
MaterialDataParameter p = new MaterialDataParameter()
{
ProductMaterialCode = model.ProductMaterialCode,
BomMaterialCode = model.BomMaterialCode,
BomMaterialName = model.BomMaterialName,
OutPackageNo = model.OutPackageNo,
OutStartTime = model.OutStartTime,
OutEndTime = model.OutEndTime,
PageSize = model.PageSize,
PageNo = 0
};
await Task.Run(() =>
{
MethodReturnResult <DataSet> ds = client.GetRPTMaterialData(ref p);
dt = ds.Data.Tables[0];
});
}
}
#region 导出到EXCEL
string filePath = System.Web.HttpContext.Current.Server.MapPath("~/LotMaterialOutData.xlsx");
FileStream fs = new FileStream(filePath, FileMode.OpenOrCreate, FileAccess.Read);
//创建工作薄。
IWorkbook wb = new XSSFWorkbook();
//IWorkbook wb = new HSSFWorkbook();
//设置EXCEL格式
ICellStyle style = wb.CreateCellStyle();
style.FillForegroundColor = 10;
//有边框
style.BorderBottom = BorderStyle.Thin;
style.BorderLeft = BorderStyle.Thin;
style.BorderRight = BorderStyle.Thin;
style.BorderTop = BorderStyle.Thin;
IFont font = wb.CreateFont();
font.Boldweight = 10;
style.SetFont(font);
ISheet ws = null;
for (int j = 0; j < dt.Rows.Count; j++)
{
if (j == 0)
{
ws = wb.CreateSheet();
IRow row = ws.CreateRow(0);
ICell cell = null;
#region //列名
foreach (DataColumn dc in dt.Columns)
{
cell = row.CreateCell(row.Cells.Count);
cell.CellStyle = style;
cell.SetCellValue(dc.Caption);
}
#endregion
font.Boldweight = 5;
}
IRow rowData = ws.CreateRow(j + 1);
#region //数据
ICell cellData = null;
foreach (DataColumn dc in dt.Columns)
{
cellData = rowData.CreateCell(rowData.Cells.Count);
cellData.CellStyle = style;
if (dc.DataType == typeof(double) || dc.DataType == typeof(float))
{
cellData.SetCellValue(Convert.ToDouble(dt.Rows[j][dc]));
}
else if (dc.DataType == typeof(int))
{
cellData.SetCellValue(Convert.ToInt32(dt.Rows[j][dc]));
}
else
{
cellData.SetCellValue(Convert.ToString(dt.Rows[j][dc]));
}
}
#endregion
}
var ms = new NpoiMemoryStream();
ms.AllowClose = false;
wb.Write(fs);
wb.Write(ms);
ms.Flush();
ms.Position = 0;
ms.AllowClose = false;
return(File(ms, "application/vnd.ms-excel", "LotMaterialOutData.xlsx"));
#endregion
}
