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); }