private void btnExcelExport_Click(object sender, EventArgs e)
{
ManagerInfoBll miBll = new ManagerInfoBll();
var list = miBll.GetList();
dataGridView1.DataSource = list;
//创建Excel工作薄
XSSFWorkbook workbook = new XSSFWorkbook();
//创建单元格样式
ICellStyle cellTitleStyle = workbook.CreateCellStyle();
//设置单元格居中显示
cellTitleStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
//创建字体
IFont font = workbook.CreateFont();
//设置字体加粗显示
font.IsBold = true;
cellTitleStyle.SetFont(font);
//创建Excel工作表
ISheet sheet = workbook.CreateSheet("管理员");
//创建Excel行
IRow row = sheet.CreateRow(0);
//创建Excel单元格
NPOI.SS.UserModel.ICell cell = row.CreateCell(0);
//设置单元格值
cell.SetCellValue("管理员管理");
//设置单元格合并
sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(0, 0, 0, 3));
cell.CellStyle = cellTitleStyle;
for (int i = 0; i < list.Count; i++)
{
IRow rowDate = sheet.CreateRow(i + 1);
Type t = list[i].GetType();
int count = 0;
foreach (PropertyInfo pi in t.GetProperties())
{
object value = pi.GetValue(list[i]);
string name = pi.Name;
NPOI.SS.UserModel.ICell cellDate = rowDate.CreateCell(count);
if (i == 0)
{
cellDate.SetCellValue(name);
}
else
{
cellDate.SetCellValue(value.ToString());
}
sheet.AutoSizeColumn(count);
count++;
}
}
using (FileStream fs = new FileStream(@"C:\Users\Saber\Desktop\Demo.xlsx", FileMode.OpenOrCreate))
{
workbook.Write(fs);
}
}
public static NpoiMemoryStream ExportExcel <T>(T[] lists, string sheetName)
{
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;
ISheet sheet = wb.CreateSheet(sheetName);
IRow row;
ICell cell;
Type t1 = typeof(T);
PropertyInfo[] props = t1.GetProperties();
row = sheet.CreateRow(0);
for (int j = 0; j < props.Count(); j++)
{
cell = row.CreateCell(j);//创建第j列
cell.CellStyle = style1;
ExcelHelper.SetCellValue(cell, props[j].CustomAttributes.First().ToString().Split('"')[1]);
sheet.AutoSizeColumn(0);
}
int rowIndex = 1;
foreach (var list in lists)
{
row = sheet.CreateRow(rowIndex);//创建第j列
for (int i = 0; i < props.Count(); i++)
{
cell = row.CreateCell(i);
cell.CellStyle = style1;
ExcelHelper.SetCellValue(cell, props[i].GetValue(list));
sheet.AutoSizeColumn(i);
}
rowIndex++;
}
var ms = new NpoiMemoryStream();
ms.AllowClose = false;
wb.Write(ms);
ms.Flush();
ms.Seek(0, SeekOrigin.Begin);
ms.AllowClose = true;
return(ms);
}
private static void ExportExcel()
{
var newFile = @"newbook.core.xlsx";
using (var fs = new FileStream(newFile, FileMode.Create, FileAccess.Write)) {
IWorkbook workbook = new XSSFWorkbook();
ISheet sheet1 = workbook.CreateSheet("Sheet1");
sheet1.AddMergedRegion(new CellRangeAddress(0, 0, 0, 10));
//ICreationHelper cH = wb.GetCreationHelper();
var rowIndex = 0;
IRow row = sheet1.CreateRow(rowIndex);
row.Height = 30 * 80;
var cell = row.CreateCell(0);
var font = workbook.CreateFont();
font.IsBold = true;
font.Color = HSSFColor.DarkBlue.Index2;
cell.CellStyle.SetFont(font);
cell.SetCellValue("A very long piece of text that I want to auto-fit innit, yeah. Although if it gets really, really long it'll probably start messing up more.");
sheet1.AutoSizeColumn(0);
rowIndex++;
// 新增試算表。
var sheet2 = workbook.CreateSheet("My Sheet");
// 建立儲存格樣式。
var style1 = workbook.CreateCellStyle();
style1.FillForegroundColor = HSSFColor.Blue.Index2;
style1.FillPattern = FillPattern.SolidForeground;
var style2 = workbook.CreateCellStyle();
style2.FillForegroundColor = HSSFColor.Yellow.Index2;
style2.FillPattern = FillPattern.SolidForeground;
// 設定儲存格樣式與資料。
var cell2 = sheet2.CreateRow(0).CreateCell(0);
cell2.CellStyle = style1;
cell2.SetCellValue(0);
cell2 = sheet2.CreateRow(1).CreateCell(0);
cell2.CellStyle = style2;
cell2.SetCellValue(1);
cell2 = sheet2.CreateRow(2).CreateCell(0);
cell2.CellStyle = style1;
cell2.SetCellValue(2);
cell2 = sheet2.CreateRow(3).CreateCell(0);
cell2.CellStyle = style2;
cell2.SetCellValue(3);
cell2 = sheet2.CreateRow(4).CreateCell(0);
cell2.CellStyle = style1;
cell2.SetCellValue(4);
workbook.Write(fs);
}
Console.WriteLine("Excel Done");
}
static public void ExportToXLSX(SpreadsheetComponentData spreadsheet, string path)
{
CultureInfo cc = Thread.CurrentThread.CurrentCulture, cuc = Thread.CurrentThread.CurrentUICulture;
CultureInfo ci = new CultureInfo("en-US");
Thread.CurrentThread.CurrentCulture = ci;
Thread.CurrentThread.CurrentUICulture = ci;
XSSFWorkbook wb = new XSSFWorkbook();
XSSFSheet sh = (XSSFSheet)wb.CreateSheet("Sheet1");
// column header
var headerFont = wb.CreateFont();
headerFont.Boldweight = (short)FontBoldWeight.Bold;
var headerStyle = wb.CreateCellStyle();
headerStyle.SetFont(headerFont);
bool hasHeader = false;
if (spreadsheet.HasCustomLabeledColumnHeader())
{
var r = sh.CreateRow(0);
for (int col = 0; col < spreadsheet.columnDatas.Count; ++col)
{
var cell = r.CreateCell(col);
cell.CellStyle = headerStyle;
cell.SetCellValue(spreadsheet.columnDatas[col].HasCustomLabel() ? spreadsheet.columnDatas[col].label : SpreadsheetComponentData.GetDefaultColumnName(col));
}
hasHeader = true;
}
for (int row = 0; row < spreadsheet.rowDatas.Count; ++row)
{
var r = sh.CreateRow(row + (hasHeader ? 1 : 0));
for (int col = 0; col < spreadsheet.columnDatas.Count; ++col)
{
var cell = r.CreateCell(col);
SetCellValue(cell, spreadsheet.cells[row][col]);
}
}
try
{
using (var fs = new FileStream(path, FileMode.Create, FileAccess.Write))
{
wb.Write(fs);
}
}
catch (Exception e)
{
throw new Exception("Cannot write to Excel file. Another program might be using it.");
}
finally
{
Thread.CurrentThread.CurrentCulture = cc;
Thread.CurrentThread.CurrentUICulture = cuc;
}
}
/// <summary>
/// 创建一个Excel
/// </summary>
/// <param name="data"></param>
/// <param name="fileNamePath">文件名绝对路径</param>
/// <param name="folder">文件所在的文件夹绝对路径</param>
public void CreateExcel(DataTable data, string fileNamePath, string folder = null)
{
var wk = new XSSFWorkbook();
var sheet = wk.CreateSheet("Sheet1");
//字体设置
var font = wk.CreateFont();
font.Color = HSSFColor.Red.Index;
var style = wk.CreateCellStyle();
style.SetFont(font);
var count = 0;
var headerRow = sheet.CreateRow(count);
foreach (DataColumn column in data.Columns)
headerRow.CreateCell(column.Ordinal).SetCellValue(column.Caption);
count++;
foreach (DataRow row in data.Rows)
{
var datarow = sheet.CreateRow(count);
foreach (DataColumn column in data.Columns)
{
var cell = datarow.CreateCell(column.Ordinal);
var cellValue = row[column].ToString();
if (!string.IsNullOrEmpty(cellValue))
{
var array = cellValue.Split('#');
if (array.Length == 2)
cell.CellStyle = style;
cell.SetCellValue(array[0]);
}
else
{
cell.SetCellValue(cellValue);
}
}
++count;
}
if (!string.IsNullOrWhiteSpace(folder))
{
if (!Directory.Exists(folder))
{
Directory.CreateDirectory(folder);
}
}
using (var openWrite = File.OpenWrite(fileNamePath)) //打开一个xlsx文件，如果没有则自行创建，如果存在myxls.xlsx文件则在创建是不要打开该文件！
{
wk.Write(openWrite); //向打开的这个xlsx文件中写入mySheet表并保存。
}
}
private void ApplyBoldCellStyle(ICell cell)
{
ICellStyle boldFontCellStyle = _outputWorkbook.CreateCellStyle();
IFont boldFont = _outputWorkbook.CreateFont();
boldFont.IsBold = true;
boldFontCellStyle.SetFont(boldFont);
cell.CellStyle = boldFontCellStyle;
}
private IFont GetDefaultFont()
{
var font = _workbook.CreateFont();
font.FontHeightInPoints = 11;
font.FontName = "Tahoma";
return(font);
}
private XSSFFont CreateDefaultFont(XSSFWorkbook workbook)
{
var font = (XSSFFont)workbook.CreateFont();
font.FontHeightInPoints = 10;
font.FontName = "Roboto Regular";
return(font);
}
private void CopyFontStyle(IWorkbook wb, XSSFCell oldCell, XSSFCellStyle newCellStyle)
{
NPOI.SS.UserModel.IFont font = destinationWb.CreateFont();
NPOI.SS.UserModel.IFont sourceFont = oldCell.CellStyle.GetFont(wb);
font.FontName = sourceFont.FontName;
font.FontHeightInPoints = sourceFont.FontHeightInPoints;
font.Boldweight = sourceFont.Boldweight;
newCellStyle.SetFont(font);
}
private IFont getFont(XSSFWorkbook book, short boldweight, short size)
{
IFont font = book.CreateFont();
font.FontName = "Arial";
font.Boldweight = boldweight;
font.FontHeightInPoints = size;
return(font);
}
/// <summary>
/// 将dataTable的数据到处到excel
/// </summary>
/// <param name="dt"></param>
/// <returns></returns>
private bool ToExcel(DataTable dt)
{
try
{
//新建一个文件流
FileStream fs = new FileStream(this.filePath, FileMode.OpenOrCreate, FileAccess.ReadWrite);
IWorkbook workBook = new XSSFWorkbook(); //创建一个excel文件
this.sheetName = string.IsNullOrWhiteSpace(this.sheetName) ? "sheet1" : this.sheetName;
ISheet sheet = workBook.CreateSheet(this.sheetName); //创建一个sheet
//处理表格标题
IRow row = sheet.CreateRow(0);
row.CreateCell(0).SetCellValue(this.title);
//合并单元格
sheet.AddMergedRegion(new CellRangeAddress(0, 0, 0, dt.Columns.Count - 1));
row.Height = 500;
//单元样式
ICellStyle cellStyle = workBook.CreateCellStyle();
//字体
IFont font = workBook.CreateFont();
font.FontName = "微软雅黑";
font.FontHeightInPoints = 17;
cellStyle.SetFont(font);
//水平垂直居中
cellStyle.VerticalAlignment = VerticalAlignment.Center;
cellStyle.Alignment = HorizontalAlignment.Center;
row.Cells[0].CellStyle = cellStyle;
//处理表哥列头
row = sheet.CreateRow(1);
for (int i = 0; i < dt.Columns.Count; i++)
{
row.CreateCell(i).SetCellValue(dt.Columns[i].ColumnName);
row.Height = 350;
sheet.AutoSizeColumn(i);
}
//处理数据内容
for (int i = 0; i < dt.Rows.Count; i++)
{
row = sheet.CreateRow(2 + i);
row.Height = 250;
for (int j = 0; j < dt.Columns.Count; j++)
{
row.CreateCell(j).SetCellValue(dt.Rows[i][j].ToString().Trim());
sheet.SetColumnWidth(j, 256 * 15);
}
}
//写入数据流
workBook.Write(fs);
fs.Flush();
fs.Close();
return(true);
}
catch (Exception)
{
return(false);
}
}
public void OutExcelData()
{
string ExportFileName = "SystemData.xlsx"; string ExportFileTitle = "Data";
Response.Clear();
Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
XSSFWorkbook NpoiWB = new XSSFWorkbook();
XSSFCellStyle xCellStyle = (XSSFCellStyle)NpoiWB.CreateCellStyle();
XSSFDataFormat NpoiFormat = (XSSFDataFormat)NpoiWB.CreateDataFormat();
xCellStyle.SetDataFormat(NpoiFormat.GetFormat("[DbNum2][$-804]0"));
XSSFCellStyle cellStyleFontColor = (XSSFCellStyle)NpoiWB.CreateCellStyle();
XSSFFont font1 = (XSSFFont)NpoiWB.CreateFont(); font1.Color = (short)10; font1.IsBold = true;
cellStyleFontColor.SetFont(font1);
ISheet xSheet = NpoiWB.CreateSheet(ExportFileTitle);
List <string> listColumn = new List <string>()
{
"SystemClass", "systemValue", "SystemTitle", "SystemNotation", "SystemRemark", "SystemStatus"
};
IRow xRowT = xSheet.CreateRow(0); xRowT.HeightInPoints = 40;
for (int i = 0; i < listColumn.Count; i++)
{
ICell xCellT = xRowT.CreateCell(i);
xCellT.SetCellValue(listColumn[i]);
}
List <listSystemDetail> systemDetailList = new List <listSystemDetail>();
systemDetailList = sdModels.reListSystemDetail();
for (int i = 0; i < systemDetailList.Count; i++)
{
listSystemDetail dr = systemDetailList[i];
List <string> listData = new List <string>();
listData.Add(dr.lSystemClass.ToString());
listData.Add(dr.lSystemValue.ToString());
listData.Add(dr.lSystemTitle.ToString());
listData.Add(dr.lSystemNotation.ToString());
listData.Add(dr.lSystemRemark.ToString());
listData.Add(dr.lSystemStatus.ToString());
IRow xRowD = xSheet.CreateRow(i + 1);
xRowD.HeightInPoints = 40;
for (int b = 0; b < listData.Count; b++)
{
ICell xCellData = xRowD.CreateCell(b);
xCellData.SetCellValue(listData[b]);
}
}
MemoryStream MS = new MemoryStream(); NpoiWB.Write(MS);
Response.AddHeader("Content-Disposition", "attachment; filename=" + ExportFileName + "");
Response.BinaryWrite(MS.ToArray());
NpoiWB = null; MS.Close(); MS.Dispose();
Response.Flush();
Response.End();
}
public async Task CreateExcelTemplateAsync(string entityname, string filename)
{
var mapping = await this.Queryable().Where(x => x.EntitySetName == entityname && x.IsEnabled == true).ToListAsync();
var finame = new FileInfo(filename);
if (File.Exists(filename))
{
File.Delete(filename);
}
var workbook = new XSSFWorkbook();
var sheet = workbook.CreateSheet(entityname);
var headerRow = sheet.CreateRow(0);
//Below loop is create header
var headstyle = workbook.CreateCellStyle();
var font = workbook.CreateFont();
font.FontHeightInPoints = 11;
font.IsBold = true;
headstyle.SetFont(font);
headstyle.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
headstyle.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;
headstyle.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
headstyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
headstyle.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.Grey25Percent.Index;
headstyle.FillPattern = FillPattern.SolidForeground;
var col = 0;
foreach (var row in mapping)
{
var cell = headerRow.CreateCell(col++);
cell.SetCellValue(row.SourceFieldName);
cell.CellStyle = headstyle;
if (row.TypeName == "DateTime")
{
var format = workbook.CreateDataFormat();
headstyle.DataFormat = format.GetFormat("yyyy-MM-dd HH:mm");
}
else if (row.TypeName.ToLower() == "decimal")
{
var format = workbook.CreateDataFormat();
headstyle.DataFormat = format.GetFormat("#,##0.00");
}
else if (row.TypeName.ToLower() == "int")
{
var format = workbook.CreateDataFormat();
headstyle.DataFormat = format.GetFormat("#,##0");
}
}
using (var file = new FileStream(filename, FileMode.Create))
{
workbook.Write(file);
}
}
public XSSFCellStyle normalFont(XSSFWorkbook wb)
{
var font = wb.CreateFont();
font.Boldweight = (short)FontBoldWeight.Normal;
XSSFCellStyle xStyle = (XSSFCellStyle)wb.CreateCellStyle();
xStyle.SetFont(font);
return(xStyle);
}
public IFont GetFont(short fontSize, string fontName, IColor fontColor)
{
var font = Workbook.CreateFont();
font.Boldweight = 100;
((XSSFFont)font).SetColor((XSSFColor)fontColor);
font.FontName = fontName;
font.FontHeightInPoints = fontSize;
return(font);
}
/// <summary>
/// 创建Excel
/// </summary>
/// <param name="fileName">表名</param>
/// <param name="data">表数据</param>
/// <returns></returns>
private string SaveProductsExcel(string fileName, List <ProductListDto> data)
{
var fullPath = ExcelHelper.GetSavePath(_hostingEnvironment.WebRootPath) + fileName;
using (var fs = new FileStream(fullPath, FileMode.Create, FileAccess.Write))
{
IWorkbook workbook = new XSSFWorkbook();
ISheet sheet = workbook.CreateSheet("Employees");
var rowIndex = 0;
IRow titleRow = sheet.CreateRow(rowIndex);
string[] titles = { "商品规格", "商品类型", "指导零售价", "是否是特色商品", "包码", "条码", "搜索次数", "商品id", "商品code", "所属公司", "是否启用" };
var fontTitle = workbook.CreateFont();
fontTitle.IsBold = true;
for (int i = 0; i < titles.Length; i++)
{
var cell = titleRow.CreateCell(i);
cell.CellStyle.SetFont(fontTitle);
cell.SetCellValue(titles[i]);
//ExcelHelper.SetCell(titleRow.CreateCell(i), fontTitle, titles[i]);
}
var font = workbook.CreateFont();
foreach (var item in data)
{
rowIndex++;
IRow row = sheet.CreateRow(rowIndex);
ExcelHelper.SetCell(row.CreateCell(0), font, item.Specification);
ExcelHelper.SetCell(row.CreateCell(1), font, item.TypeName);
ExcelHelper.SetCell(row.CreateCell(2), font, item.Price.ToString());
var isRareStr = item.IsRare.ToString();
ExcelHelper.SetCell(row.CreateCell(3), font, (isRareStr == "true" || isRareStr == "True") ? "是" : "否");
ExcelHelper.SetCell(row.CreateCell(4), font, item.PackageCode);
ExcelHelper.SetCell(row.CreateCell(5), font, item.BarCode);
ExcelHelper.SetCell(row.CreateCell(6), font, item.SearchCount.ToString());
ExcelHelper.SetCell(row.CreateCell(7), font, item.ItemId);
ExcelHelper.SetCell(row.CreateCell(8), font, item.ItemCode);
ExcelHelper.SetCell(row.CreateCell(9), font, item.Company);
var isActionStr = item.IsAction.ToString();
ExcelHelper.SetCell(row.CreateCell(10), font, (isActionStr == "true" || isActionStr == "True") ? "是" : "否");
}
workbook.Write(fs);
}
return("/files/downloadtemp/" + fileName);
}
public ICellStyle text_bold(XSSFWorkbook workbook)
{
IFont font = workbook.CreateFont();
font.Boldweight = (short)FontBoldWeight.Bold;
ICellStyle cellStyle = workbook.CreateCellStyle();
cellStyle.SetFont(font);
return(cellStyle);
}
/// <summary>
/// Writes out to an excel file contained in the runtimes folder
/// </summary>
/// <param name="pullRequests"></param>
public string ExportPullRequestData(List <PullRequest> pullRequests)
{
// instantiating NPOI objects
IWorkbook workbook = new XSSFWorkbook();
ISheet sheet = workbook.CreateSheet("Telemetry");
DataTable table = new DataTable();
// Can make this dynamic
table.Columns.Add("Date");
table.Columns.Add("GitHub Handle");
table.Columns.Add("MSFT/External");
table.Columns.Add("Total Contribution");
foreach (PullRequest pr in pullRequests)
{
table.Rows.Add(pr.Date, pr.User.Login, pr.Group, pr.Files.Count);
}
// Create the sheet
IRow headerRow = sheet.CreateRow(0);
for (int i = 0; i < table.Columns.Count; i++)
{
ICell cell = headerRow.CreateCell(i);
cell.SetCellValue(table.Columns[i].ColumnName);
IFont fontStyle = workbook.CreateFont();
fontStyle.IsBold = true;
cell.CellStyle.SetFont(fontStyle);
}
for (int i = 0; i < table.Rows.Count; i++)
{
IRow sheetRow = sheet.CreateRow(i + 1);
for (int j = 0; j < table.Columns.Count; j++)
{
ICell cell = sheetRow.CreateCell(j);
string cellValue = table.Rows[i][j].ToString();
cell.SetCellValue(cellValue);
}
}
// Writing out to the same directory as the app
string fileName = $"Telemetry_{DateTime.Now.ToString("MMddyyyyHHmmss")}.xlsx";
using (System.IO.FileStream file = new System.IO.FileStream(fileName, System.IO.FileMode.Create))
workbook.Write(file);
Console.WriteLine($"Successfully saved file: {fileName}");
return(fileName);
}
private void CreateReportSheet(XSSFWorkbook workbook, string sectionName, IEnumerable <WorkItemDetail> workItems, WorkItemsReportViewModel prReport, bool includeTags = false)
{
var excelSheet = workbook.CreateSheet(sectionName);
var creationHelper = workbook.GetCreationHelper();
var hlinkstyle = workbook.CreateCellStyle();
var hlinkfont = workbook.CreateFont();
hlinkfont.Underline = FontUnderlineType.Single;
hlinkfont.Color = HSSFColor.Blue.Index;
hlinkstyle.SetFont(hlinkfont);
SetHeader(excelSheet, includeTags);
int rowIdx = 1, cellIdx;
foreach (var reportEntry in workItems)
{
cellIdx = 0;
var row = excelSheet.CreateRow(rowIdx);
var idCell = row.CreateCell(cellIdx++, CellType.String);
idCell.SetCellValue(reportEntry.WorkItemId);
var link = creationHelper.CreateHyperlink(HyperlinkType.Url);
link.Address = $"https://dynamicscrm.visualstudio.com/{reportEntry.WorkItemProject}/_workitems/edit/{reportEntry.WorkItemId}";
idCell.Hyperlink = link;
row.CreateCell(cellIdx++, CellType.String).SetCellValue(reportEntry.WorkItemTitle);
row.CreateCell(cellIdx++, CellType.String).SetCellValue(reportEntry.WorkItemType);
if (includeTags)
{
row.CreateCell(cellIdx++, CellType.String).SetCellValue(reportEntry.Tags);
}
row.CreateCell(cellIdx++, CellType.Numeric).SetCellValue(reportEntry.EstimatedToComplete);
row.CreateCell(cellIdx, CellType.Numeric).SetCellValue(reportEntry.TimeSpent);
rowIdx++;
}
var summaryRow = excelSheet.CreateRow(rowIdx);
cellIdx = 0;
summaryRow.CreateCell(cellIdx++, CellType.String).SetCellValue("Total: ");
summaryRow.CreateCell(cellIdx++, CellType.String).SetCellValue($"{prReport.GetTotalBugs(workItems)} bugs / {prReport.GetTotalTasks(workItems)} tasks");
summaryRow.CreateCell(cellIdx++, CellType.String);
if (includeTags)
{
_ = summaryRow.CreateCell(cellIdx++, CellType.String);
}
summaryRow.CreateCell(cellIdx++, CellType.Numeric).SetCellValue(prReport.GetTotalEstimated(workItems));
summaryRow.CreateCell(cellIdx, CellType.Numeric).SetCellValue(prReport.GetTotalTimeSpent(workItems));
// AutosizeCells(excelSheet, summaryRow.Cells.Count);
}
/// <summary>
/// 创建任务明细Excel
/// </summary>
/// <param name="fileName">文件名</param>
/// <param name="data">文件数据</param>
/// <returns></returns>
public string CreateSheduleDetailExcel(string fileName, List <SheduleDetailTaskListDto> data)
{
var fullPath = ExcelHelper.GetSavePath(_hostingEnvironment.WebRootPath) + fileName;
using (var fs = new FileStream(fullPath, FileMode.Create, FileAccess.Write))
{
IWorkbook workbook = new XSSFWorkbook();
ISheet sheet = workbook.CreateSheet("SheduleSum");
var rowIndex = 0;
IRow titleRow = sheet.CreateRow(rowIndex);
string[] titles = { "区域", "计划名", "计划时间", "任务名", "任务类型", "计划数", "完成数", "逾期数", "状态", "烟技员", "烟农" };
var fontTitle = workbook.CreateFont();
fontTitle.IsBold = true;
for (int i = 0; i < titles.Length; i++)
{
var cell = titleRow.CreateCell(i);
cell.CellStyle.SetFont(fontTitle);
cell.SetCellValue(titles[i]);
//ExcelHelper.SetCell(titleRow.CreateCell(i), fontTitle, titles[i]);
}
var font = workbook.CreateFont();
foreach (var item in data)
{
rowIndex++;
IRow row = sheet.CreateRow(rowIndex);
ExcelHelper.SetCell(row.CreateCell(0), font, item.AreaName);
ExcelHelper.SetCell(row.CreateCell(1), font, item.SheduleName);
ExcelHelper.SetCell(row.CreateCell(2), font, item.Time);
ExcelHelper.SetCell(row.CreateCell(3), font, item.TaskName);
ExcelHelper.SetCell(row.CreateCell(4), font, item.TypeName);
ExcelHelper.SetCell(row.CreateCell(5), font, item.VisitNum.ToString());
ExcelHelper.SetCell(row.CreateCell(6), font, item.CompleteNum.ToString());
ExcelHelper.SetCell(row.CreateCell(7), font, item.Status == ScheduleStatusEnum.已逾期 ? (item.VisitNum.Value - item.CompleteNum.Value).ToString() : "0");
ExcelHelper.SetCell(row.CreateCell(8), font, item.StatusName);
ExcelHelper.SetCell(row.CreateCell(9), font, item.EmployeeName);
ExcelHelper.SetCell(row.CreateCell(10), font, item.GrowerName);
}
workbook.Write(fs);
}
return("/files/downloadtemp/" + fileName);
}
/// <summary>
/// 获取表头样式
/// </summary>
/// <param name="workbook"></param>
/// <returns></returns>
public static XSSFCellStyle GetHeadStyle(this XSSFWorkbook workbook)
{
XSSFCellStyle headStyle = (XSSFCellStyle)workbook.CreateCellStyle();
headStyle.Alignment = HorizontalAlignment.Center;
XSSFFont font = (XSSFFont)workbook.CreateFont();
font.FontHeightInPoints = 12;
font.Boldweight = 700;
headStyle.SetFont(font);
return(headStyle);
}
private ICellStyle CreatColumnRowStyle(XSSFWorkbook workbook)
{
ICellStyle headStyle = workbook.CreateCellStyle();
headStyle.Alignment = HorizontalAlignment.CENTER;
IFont font = workbook.CreateFont();
font.FontHeightInPoints = 10;
font.Boldweight = 700;
headStyle.SetFont(font);
return(headStyle);
}
public async Task <FileResult> GetPdf()
{
IWorkbook workbook = new XSSFWorkbook();
ISheet sheet1 = workbook.CreateSheet("Sheet1");
var titleFont = workbook.CreateFont();
titleFont.Boldweight = (short)NPOI.SS.UserModel.FontBoldWeight.Bold;
var titleStyle = workbook.CreateCellStyle();
titleStyle.SetFont(titleFont);
IRow row = sheet1.CreateRow(0);
string[] headers = new string[] { "Word", "Meaning", "UK", "US", "Example", "Subcategory", "Category", "Date add" };
for (var i = 0; i < headers.Length; i++)
{
ICell cell = row.CreateCell(i);
cell.SetCellValue(headers[i]);
cell.CellStyle = titleStyle;
}
var filter = new WordQuery();
filter.WithoutPagination = true;
var queryResult = await repository.GetWords(filter);
for (var i = 0; i < queryResult.Items.Count(); i++)
{
row = sheet1.CreateRow(i + 1);
var word = queryResult.Items.ElementAt(i);
row.CreateCell(0).SetCellValue(word.Name);
row.CreateCell(1).SetCellValue(word.Meaning);
row.CreateCell(2).SetCellValue(word.PronunciationUK);
row.CreateCell(3).SetCellValue(word.PronunciationUS);
row.CreateCell(4).SetCellValue(Regex.Replace(word.Example, "<.*?>", String.Empty));
row.CreateCell(5).SetCellValue(word.Subcategory.Name);
row.CreateCell(6).SetCellValue(word.Subcategory.Category.Name);
row.CreateCell(7).SetCellValue(String.Format("{0:MM-dd-yyyy}", word.CreatedAt));
}
for (var i = 0; i < headers.Length; i++)
{
sheet1.AutoSizeColumn(i);
}
var stream = new MemoryStream();
workbook.Write(stream);
return(File(new MemoryStream(stream.ToArray()), "application/vnd.ms-excel", "plik.xls"));
}
public bool CrearArchivoEmail()
{
bool resultado = true;
try
{
string tempFile = Path.GetTempPath() + Guid.NewGuid().ToString() + ".xlsx";
XSSFWorkbook wb = new XSSFWorkbook();
using (var ms = new MemoryStream()) {
ISheet sheet = wb.CreateSheet("Destinatarios");
int indexCabecera = 0;
var rowCabecera = sheet.CreateRow(indexCabecera);
XSSFCellStyle cellStyle = (XSSFCellStyle)wb.CreateCellStyle();
cellStyle.FillBackgroundColor = HSSFColor.DarkBlue.Index;
XSSFFont font = (XSSFFont)wb.CreateFont();
var color = new XSSFColor(new byte[] { 255, 255, 255 });
font.SetColor(color);
cellStyle.SetFont(font);
cellStyle.FillPattern = FillPattern.SolidForeground;
var cell1 = rowCabecera.CreateCell(0);
var cell2 = rowCabecera.CreateCell(1);
cell1.CellStyle = cellStyle;
cell1.SetCellValue(Enumeradores.EXCEL_CABECERA_CORREO);
cell2.CellStyle = cellStyle;
cell2.SetCellValue(Enumeradores.EXCEL_CABECERA_NOMBRE);
wb.Write(ms);
File.WriteAllBytes(tempFile, ms.ToArray());
}
System.Diagnostics.Process.Start(tempFile);
}
catch (Exception)
{
throw;
}
return(resultado);
}
private XSSFCellStyle GetNormalRowStyleHeader(XSSFWorkbook hssfworkbook)
{
if (normalRowStyleHeader == null)
{
IFont font = hssfworkbook.CreateFont();
font.FontHeightInPoints = 8;
font.Color = NPOI.HSSF.Util.HSSFColor.White.Index;
font.IsBold = true;
font.FontName = "Verdana";
//byte[] rgb = new byte[3] { 192, 0, 0 };
byte[] rgb = new byte[3] {
0, 68, 132
};
//XSSFCellStyle normalRowStyleHeaders = (XSSFCellStyle)hssfworkbook.CreateCellStyle();
//normalRowStyleHeaders.SetFillForegroundColor(new XSSFColor(System.Drawing.Color.Azure));
//normalRowStyleHeaders.SetFillBackgroundColor(new XSSFColor(System.Drawing.Color.Black));
//CellStyle style = workbook.createCellStyle();
//style.setFillForegroundColor(IndexedColors.GREEN.getIndex());
//style.setFillPattern(CellStyle.SOLID_FOREGROUND);
XSSFCellStyle normalRowStyleHeaders = (XSSFCellStyle)hssfworkbook.CreateCellStyle();
normalRowStyleHeaders.SetFillForegroundColor(new XSSFColor(rgb));
normalRowStyleHeaders.FillPattern = FillPattern.SolidForeground;
//normalRowStyleHeaders.SetFillBackgroundColor(new XSSFColor(rgb));
//normalRowStyleHeaders.setFillPattern(XSSFCellStyle.);
//normalRowStyleHeaders.setFill;
normalRowStyleHeaders.Alignment = HorizontalAlignment.Center;
normalRowStyleHeaders.VerticalAlignment = VerticalAlignment.Center;
normalRowStyleHeaders.SetFont(font);
//XSSFCellStyle HeaderCellStyle1 = (XSSFCellStyle)hssfworkbook.CreateCellStyle();
//normalRowStyleHeader.SetFillForegroundColor(new XSSFColor(rgb));
//normalRowStyleHeader.bac = NPOI.HSSF.Util.HSSFColor.DarkBlue.Index;
//normalRowStyle.LeftBorderColor =
normalRowStyleHeaders.BorderTop = BorderStyle.None;
normalRowStyleHeaders.BorderRight = BorderStyle.None;
normalRowStyleHeaders.BorderBottom = BorderStyle.None;
normalRowStyleHeaders.BorderLeft = BorderStyle.None;
normalRowStyleHeader = normalRowStyleHeaders;
}
return(normalRowStyleHeader);
}
/// <summary>
/// 导出excel
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="sheetName">页名</param>
/// <param name="cellHead">属性的名称(key)和显示名称(value)</param>
/// <param name="data">导出到excel数据源</param>
/// <returns></returns>
public XSSFWorkbook Export <T>(string sheetName, Dictionary <string, string> cellHead, IQueryable <T> data)
{
//HSSF使用于2007之前的xls版本，XSSF适用于2007及其之后的xlsx版本
XSSFWorkbook xk = new XSSFWorkbook();
ISheet sheet = xk.CreateSheet(sheetName);
ICellStyle style = xk.CreateCellStyle();
style.WrapText = true;
IFont font = xk.CreateFont();
font.Boldweight = (short)FontBoldWeight.Bold;
style.SetFont(font);
IRow row = sheet.CreateRow(0);
for (int i = 0; i < cellHead.Count; i++)
{
ICell cell = row.CreateCell(i);
cell.SetCellValue(cellHead.ElementAt(i).Value);
cell.SetCellType(CellType.String);
cell.CellStyle = style;
}
int rowIndex = 0;
foreach (var entity in data)
{
rowIndex++;
row = sheet.CreateRow(rowIndex);
for (int j = 0; j < cellHead.Count; j++)
{
var cellValue = string.Empty;
var property = entity.GetType().GetProperties().FirstOrDefault(e => e.Name == cellHead.ElementAt(j).Key);
if (property != null)
{
cellValue = property.GetValue(entity).ToString();
//对时间初始值赋值为空
if (cellValue.Trim() == "0001/1/1 0:00:00" || cellValue.Trim() == "0001/1/1 23:59:59")
{
cellValue = "";
}
}
row.CreateCell(j).SetCellValue(cellValue);
}
}
return(xk);
}
private static void Facturas(string saveAs)
{
using (var fs = new FileStream(saveAs, FileMode.Create, FileAccess.Write))
{
IWorkbook workbook = new XSSFWorkbook();
ISheet current_sheet = workbook.CreateSheet("Results");
var headerStyle = workbook.CreateCellStyle();
headerStyle.FillForegroundColor = HSSFColor.Grey80Percent.Index;
headerStyle.FillPattern = FillPattern.SolidForeground;
var headerFont = workbook.CreateFont();
headerFont.Color = HSSFColor.White.Index;
headerFont.IsBold = true;
IRow headers = current_sheet.CreateRow(0);
headers.CreateCell(0).SetCellValue("Producto");
headers.CreateCell(1).SetCellValue("Fraccion");
headers.CreateCell(2).SetCellValue("Costo");
headers.CreateCell(3).SetCellValue("Valor Agregado");
headers.CreateCell(4).SetCellValue("Peso");
headers.CreateCell(5).SetCellValue("Medida");
headers.CreateCell(6).SetCellValue("Po");
headers.CreateCell(7).SetCellValue("Factura");
int row_count = 1;
foreach (SO job in jobsList)
{
IRow current_row = current_sheet.CreateRow(row_count);
current_row.CreateCell(0).SetCellValue(job.Product); //producto (0)
current_row.CreateCell(2).SetCellValue(job.Cost); // costo (2)
current_row.GetCell(2).SetCellType(CellType.Numeric);
current_row.CreateCell(3).SetCellValue(job.AddedValue); //valor agregado (3)
current_row.GetCell(3).SetCellType(CellType.Numeric);
current_row.CreateCell(4).SetCellValue(job.Weight); // peso (4)
current_row.GetCell(4).SetCellType(CellType.Numeric);
current_row.CreateCell(5).SetCellValue(job.UM); //medida!!
current_row.CreateCell(6).SetCellValue(job.PO); //po_only!!
current_row.CreateCell(7).SetCellValue(job.Factura); //factura!!
row_count += 1;
}
IRow headersRow = current_sheet.GetRow(0);
for (int i = 0; i < 8; i++)
{
current_sheet.AutoSizeColumn(i);
var cellToFormat = headersRow.GetCell(i);
cellToFormat.CellStyle = headerStyle;
cellToFormat.CellStyle.SetFont(headerFont);
}
workbook.Write(fs);
}
}
/// <summary>
/// 将DataTable数据导出到Excel文件中(xlsx)
/// </summary>
/// <param name="dt"></param>
/// <param name="file"></param>
public static MemoryStream TableToExcelForXLSX(DataTable dt)
{
XSSFWorkbook xssfworkbook = new XSSFWorkbook();
ISheet sheet = xssfworkbook.CreateSheet("sheet1");
//加工 只导出Caption不为空的数据
List <DataColumn> cols = new List <DataColumn>();
for (int i = 0; i < dt.Columns.Count; i++)
{
if (!string.IsNullOrEmpty(dt.Columns[i].Caption))
{
cols.Add(dt.Columns[i]);
}
}
//表头
IRow row = sheet.CreateRow(0);
for (int i = 0; i < cols.Count; i++)
{
ICell cell = row.CreateCell(i);
cell.SetCellValue(cols[i].Caption);
ICellStyle style = xssfworkbook.CreateCellStyle(); //创建样式对象
IFont font = xssfworkbook.CreateFont(); //创建一个字体样式对象
font.Color = new HSSFColor.White().Indexed;
font.FontHeightInPoints = 13; //字体大小
style.FillBackgroundColor = HSSFColor.RoyalBlue.Index;
style.FillForegroundColor = HSSFColor.RoyalBlue.Index;
style.FillPattern = FillPattern.SolidForeground;
style.SetFont(font); //将字体样式赋给样式对象
cell.CellStyle = style; //把样式赋给单元格
}
//数据
for (int i = 0; i < dt.Rows.Count; i++)
{
IRow row1 = sheet.CreateRow(i + 1);
for (int j = 0; j < cols.Count; j++)
{
ICell cell = row1.CreateCell(j);
cell.SetCellValue(dt.Rows[i][cols[j]].ToString());
}
}
using (MemoryStream ms = new MemoryStream())
{
xssfworkbook.Write(ms);
ms.Flush();
return(ms);
}
}
/// <summary>
/// Se encarga de poner el titulo de las tablas y de definil el estilo que tendra cada columna por defecto
/// </summary>
private void Addcabeceras()
{
var row = _currentsheet.GetRow(_rowInicial - 1) ?? _currentsheet.CreateRow(_rowInicial - 1);
var cell = 0;
var item = _informacion.FirstOrDefault();
if (item == null)
{
return;
}
foreach (var prop in item.GetType().GetProperties().Where(p => !p.GetGetMethod().GetParameters().Any()))
{
var celda = row.GetCell(cell) ?? row.CreateCell(cell);
if (_mascaras)
{
var hfont = _excel.CreateFont();
hfont.FontHeightInPoints = 12;
hfont.Color = IndexedColors.Black.Index;
hfont.FontName = "Century Gothic";
if (prop.PropertyType == typeof(DateTime))
{
var style = _excel.CreateCellStyle();
style.DataFormat = _excel.CreateDataFormat().GetFormat("m/d/yyyy");
style.SetFont(hfont);
_currentsheet.SetDefaultColumnStyle(cell, style);
}
else if (prop.PropertyType == typeof(decimal))
{
var style = _excel.CreateCellStyle();
style.DataFormat = _excel.CreateDataFormat().GetFormat("[$$-409]#,##0.00");
style.SetFont(hfont);
_currentsheet.SetDefaultColumnStyle(cell, style);
celda.SetCellType(CellType.Numeric);
}
else
{
var style = _excel.CreateCellStyle();
style.SetFont(hfont);
_currentsheet.SetDefaultColumnStyle(cell, style);
celda.SetCellType(CellType.Numeric);
}
}
cell++;
celda.SetCellValue(prop.Name);
}
}
private string SaveExhibitionShopsAsyncsExcel(string fileName, List <ExhibitionShopListDto> data)
{
var fullPath = ExcelHelper.GetSavePath(_hostingEnvironment.WebRootPath) + fileName;
using (var fs = new FileStream(fullPath, FileMode.Create, FileAccess.Write))
{
IWorkbook workbook = new XSSFWorkbook();
ISheet sheet = workbook.CreateSheet("WeChatUser");
var rowIndex = 0;
IRow titleRow = sheet.CreateRow(rowIndex);
string[] titles = { "参赛编号", "零售客户编码", "客户名称", "店铺名称", "所属区县", "店铺地址", "店铺电话", "实时票数", "店铺会员数" };
var fontTitle = workbook.CreateFont();
fontTitle.IsBold = true;
for (int i = 0; i < titles.Length; i++)
{
var cell = titleRow.CreateCell(i);
cell.CellStyle.SetFont(fontTitle);
cell.SetCellValue(titles[i]);
}
var font = workbook.CreateFont();
foreach (var item in data)
{
rowIndex++;
IRow row = sheet.CreateRow(rowIndex);
ExcelHelper.SetCell(row.CreateCell(0), font, item.NoId);
ExcelHelper.SetCell(row.CreateCell(1), font, item.CustCode);
ExcelHelper.SetCell(row.CreateCell(2), font, item.CustName);
ExcelHelper.SetCell(row.CreateCell(3), font, item.ShopName);
ExcelHelper.SetCell(row.CreateCell(4), font, item.Area);
ExcelHelper.SetCell(row.CreateCell(5), font, item.ShopAddress);
ExcelHelper.SetCell(row.CreateCell(6), font, item.Phone);
ExcelHelper.SetCell(row.CreateCell(7), font, item.Votes.ToString());
ExcelHelper.SetCell(row.CreateCell(8), font, item.FansNum.ToString());
}
workbook.Write(fs);
}
return("/files/downloadtemp/" + fileName);
}
