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);
}
public string setCellData(string sheetName, string colName, int rowNum, string data)
{
fs = new FileStream(path, FileMode.Open, FileAccess.Read);
workbook = new XSSFWorkbook(fs);
if (rowNum <= 0)
{
return("");
}
int colNum = -1;
int index = workbook.GetSheetIndex(sheetName);
if (index == -1)
{
return("");
}
sheet = workbook.GetSheetAt(index);
row = sheet.GetRow(0);
for (int i = 0; i < row.LastCellNum; i++)
{
if (row.GetCell(i).StringCellValue.Equals(colName))
{
colNum = i;
}
}
if (colNum == -1)
{
return("");
}
row = sheet.GetRow(rowNum - 1);
if (row == null)
{
row = sheet.CreateRow(rowNum - 1);
}
cell = row.GetCell(colNum - 1);
if (cell == null)
{
cell = row.CreateCell(colNum);
}
ICellStyle cs = workbook.CreateCellStyle();
cs.WrapText = true;
cell.CellStyle = cs;
cell.SetCellValue(data);
FileStream f = new FileStream(path, FileMode.Create, FileAccess.ReadWrite);
workbook.Write(f);
f.Close();
fs.Close();
return(data);
}
/// <summary>
/// 给单元格加边框，居中
/// </summary>
private ICellStyle AddStytle(XSSFWorkbook workbook)
{
ICellStyle style = workbook.CreateCellStyle();// 样式
// 设置单元格的样式：水平对齐居中
style.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
style.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin; // 下边框为细线边框
style.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin; // 左边框
style.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin; // 上边框
style.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin; // 右边框
return(style);
}
private void setCellStyle(XSSFWorkbook workbook, ICell cell)
{
var fCellStyle = (XSSFCellStyle)workbook.CreateCellStyle();
fCellStyle.FillForegroundXSSFColor = new XSSFColor(Color.Green);
fCellStyle.FillPattern = FillPattern.SolidForeground;
fCellStyle.VerticalAlignment = VerticalAlignment.Center;
fCellStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
cell.CellStyle = fCellStyle;
}
private void CreateReportSheet(XSSFWorkbook workbook, string sectionName, IList <WeeklyStatusReport.WorkItemDetail> workItems, 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);
idCell.CellStyle = hlinkstyle;
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($"{workItems.Count(i => i.WorkItemType == WorkItemTypes.Bug)} bugs / {workItems.Count(i => i.WorkItemType == WorkItemTypes.Task)} tasks");
summaryRow.CreateCell(cellIdx++, CellType.String);
if (includeTags)
{
summaryRow.CreateCell(cellIdx++, CellType.String);
}
summaryRow.CreateCell(cellIdx++, CellType.Numeric).SetCellValue(workItems.Sum(i => i.EstimatedToComplete));
summaryRow.CreateCell(cellIdx, CellType.Numeric).SetCellValue(workItems.Sum(i => i.TimeSpent));
AutosizeCells(excelSheet, summaryRow.Cells.Count);
}
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);
}
private XSSFCellStyle CreateDefStyle(XSSFWorkbook workbook)
{
var defStyle = (XSSFCellStyle)workbook.CreateCellStyle();
defStyle.SetFont(CreateDefaultFont(workbook));
defStyle.WrapText = true;
defStyle.Alignment = HorizontalAlignment.Left;
defStyle.VerticalAlignment = VerticalAlignment.Center;
return(defStyle);
}
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"));
}
private XSSFCellStyle CreateTimeFormatStyle(XSSFWorkbook workbook, IFont font)
{
var timeStyle = (XSSFCellStyle)workbook.CreateCellStyle();
timeStyle.SetFont(font ?? CreateDefaultFont(workbook));
timeStyle.WrapText = true;
timeStyle.Alignment = HorizontalAlignment.Left;
timeStyle.VerticalAlignment = VerticalAlignment.Center;
timeStyle.DataFormat = workbook.CreateDataFormat().GetFormat("hh:mm");
return(timeStyle);
}
/// <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 void CreateEmptyExcel(string excelName)
{
XSSFWorkbook workbook = new XSSFWorkbook();
ISheet sheet = workbook.CreateSheet();
ICellStyle style = workbook.CreateCellStyle();
style.FillPattern = FillPattern.SolidForeground;
style.FillForegroundColor = 42;
style.BorderLeft =
style.BorderTop =
style.BorderRight =
style.BorderBottom = BorderStyle.Thin;
style.Alignment = HorizontalAlignment.Center;
sheet.CreateRow(0).RowStyle = style;
sheet.CreateRow(1).RowStyle = style;
XSSFDataValidationHelper helper = new XSSFDataValidationHelper((XSSFSheet)sheet);
XSSFDataValidationConstraint constraint =
(XSSFDataValidationConstraint)helper.CreateExplicitListConstraint(Enum.GetNames(typeof(DataConverter.DataType)));
CellRangeAddressList range = new CellRangeAddressList(1, 1, 0, 255);
sheet.AddValidationData((XSSFDataValidation)helper.CreateValidation(constraint, range));
style = workbook.CreateCellStyle();
style.FillPattern = FillPattern.SolidForeground;
style.FillForegroundColor = 42;
style.BorderLeft =
style.BorderTop =
style.BorderRight = BorderStyle.Thin;
style.BorderBottom = BorderStyle.Medium;
style.Alignment = HorizontalAlignment.Center;
sheet.CreateRow(2).RowStyle = style;
using (FileStream fs = new FileStream(ExcelDirectory + excelName, FileMode.Create, FileAccess.Write))
{
workbook.Write(fs);
}
workbook.Close();
}
public void TestCloneStyleSameWB()
{
XSSFWorkbook wb = new XSSFWorkbook();
Assert.AreEqual(1, wb.NumberOfFonts);
XSSFFont fnt = (XSSFFont)wb.CreateFont();
fnt.FontName = ("TestingFont");
Assert.AreEqual(2, wb.NumberOfFonts);
XSSFCellStyle orig = (XSSFCellStyle)wb.CreateCellStyle();
orig.Alignment = (HorizontalAlignment.Right);
orig.SetFont(fnt);
orig.DataFormat = (short)18;
Assert.AreEqual(HorizontalAlignment.Right, orig.Alignment);
Assert.AreEqual(fnt, orig.GetFont());
Assert.AreEqual(18, orig.DataFormat);
XSSFCellStyle clone = (XSSFCellStyle)wb.CreateCellStyle();
Assert.AreNotEqual(HorizontalAlignment.Right, clone.Alignment);
Assert.AreNotEqual(fnt, clone.GetFont());
Assert.AreNotEqual(18, clone.DataFormat);
clone.CloneStyleFrom(orig);
Assert.AreEqual(HorizontalAlignment.Right, clone.Alignment);
Assert.AreEqual(fnt, clone.GetFont());
Assert.AreEqual(18, clone.DataFormat);
Assert.AreEqual(2, wb.NumberOfFonts);
clone.Alignment = HorizontalAlignment.Left;
clone.DataFormat = 17;
Assert.AreEqual(HorizontalAlignment.Right, orig.Alignment);
Assert.AreEqual(18, orig.DataFormat);
Assert.IsNotNull(XSSFTestDataSamples.WriteOutAndReadBack(wb));
}
/// <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>
/// 导出教师
/// </summary>
/// <param name="list"></param>
/// <param name="title"></param>
/// <returns></returns>
public byte[] ExportTeacher(List <ExportTeacherModel> list, string title)
{
XSSFWorkbook workbook = new XSSFWorkbook();
ISheet sheet = workbook.CreateSheet(title);
DataTable tblDatas = new DataTable("Datas");
//设置边框样式
ICellStyle style0 = workbook.CreateCellStyle();
BasicStyles(style0);
Title(6, sheet, title, workbook);
List <string> TableHead = new List <string>
{
"帐号",
"姓名",
"性别",
"学科",
"年级",
"班级"
};
DataList(tblDatas, TableHead, sheet, workbook, 1);
//数据
for (int i = 0; i < list.Count; i++)
{
IRow row1 = sheet.CreateRow(i + 2);
ICell cell = row1.CreateCell(0);
cell.SetCellValue(i + 1);
cell = row1.CreateCell(1);
cell.SetCellValue(list[i].LoginName);
cell = row1.CreateCell(2);
cell.SetCellValue(list[i].UserName);
cell = row1.CreateCell(3);
cell.SetCellValue(list[i].Gender);
cell = row1.CreateCell(4);
cell.SetCellValue(list[i].SubjectName);
cell = row1.CreateCell(5);
cell.SetCellValue(list[i].GradeName);
cell = row1.CreateCell(6);
cell.SetCellValue(list[i].ClassName);
TraverseStyle(6, row1, style0);
}
sheet.SetColumnWidth(0, 6 * 256);
sheet.SetColumnWidth(1, 34 * 256);
ColumnWidth(2, 6, 15, sheet);
MemoryStream stream = new MemoryStream();
workbook.Write(stream);
var buf = stream.ToArray();
return(buf);
}
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);
}
public string setCellData(string sheetName, int colNum, int rowNum, string data)
{
fs = new FileStream(path, FileMode.Open, FileAccess.ReadWrite);
workbook = new XSSFWorkbook(fs);
if (rowNum <= 0)
{
return("");
}
int index = workbook.GetSheetIndex(sheetName);
if (index == -1)
{
return("");
}
sheet = workbook.GetSheetAt(index);
row = sheet.GetRow(rowNum - 1);
if (row == null)
{
row = sheet.CreateRow(rowNum - 1);
}
cell = row.GetCell(colNum - 1);
if (cell == null)
{
cell = row.CreateCell(colNum - 1);
}
//for (int rownum = 1; rownum <= 1000; rownum++)
//{
// row = sheet.CreateRow(rownum);
// cell = row.CreateCell(colNum-1);
// ICellStyle cs = workbook.CreateCellStyle();
// cs.WrapText = true;
// cell.CellStyle = cs;
// cell.SetCellValue(data);
//}
ICellStyle cs = workbook.CreateCellStyle();
cs.WrapText = true;
cell.CellStyle = cs;
cell.SetCellValue(data);
FileStream f = new FileStream(path, FileMode.Create, FileAccess.ReadWrite);
workbook.Write(f);
f.Close();
fs.Close();
return(data);
}
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);
}
}
//3 - > 10
public static void Helper()
{
IWorkbook workbook = new XSSFWorkbook();
ISheet sheet1 = workbook.CreateSheet("Sheet1");
IFont font1 = workbook.CreateFont();
font1.Color = IndexedColors.Red.Index;
int x = 1;
for (int i = 0; i < 15; i++)
{
IRow row = sheet1.CreateRow(i);
for (int j = 0; j < 15; j++)
{
ICell cell = row.CreateCell(j);
if (x % 2 == 0)
{
//fill background with blue
ICellStyle style1 = workbook.CreateCellStyle();
style1.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.Blue.Index2;
style1.FillPattern = FillPattern.SolidForeground;
cell.CellStyle = style1;
}
else
{
//fill background with yellow
ICellStyle style1 = workbook.CreateCellStyle();
style1.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.Yellow.Index2;
style1.FillPattern = FillPattern.SolidForeground;
cell.CellStyle = style1;
}
x++;
}
}
FileStream sw = File.Create("asd");
workbook.Write(sw);
sw.Close();
}
public static void testc()
{
//1.创建EXCEL中的Workbook
IWorkbook myworkbook = new XSSFWorkbook();
//2.创建Workbook中的Sheet
ISheet mysheet = myworkbook.CreateSheet("sheet1");
mysheet.SetColumnWidth(0, 40 * 256);
//3.创建Row中的Cell并赋值
IRow row0 = mysheet.CreateRow(0); row0.CreateCell(0).SetCellValue("130925199662080044");
IRow row1 = mysheet.CreateRow(1); row1.CreateCell(0).SetCellValue("" + DateTime.Now + "");
//4.创建CellStyle与DataFormat并加载格式样式
IDataFormat dataformat = myworkbook.CreateDataFormat();
//【Tips】
// 1.使用@ 或 text 都可以
// 2.再也不用为身份证号发愁了
ICellStyle style0 = myworkbook.CreateCellStyle();
style0.DataFormat = dataformat.GetFormat("@");
ICellStyle style1 = myworkbook.CreateCellStyle();
style1.DataFormat = dataformat.GetFormat("text");
//5.将CellStyle应用于具体单元格
row0.GetCell(0).CellStyle = style0;
row1.GetCell(0).CellStyle = style1;
//6.保存
FileStream file = new FileStream(@"c:\myworkbook9.xlsx", FileMode.Create);
myworkbook.Write(file);
file.Close();
}
public static ICellStyle ColorMainStoreJP(XSSFWorkbook templateWorkbook)
{
ICellStyle styleColor = templateWorkbook.CreateCellStyle();
styleColor.FillForegroundColor = IndexedColors.LightCornflowerBlue.Index;
styleColor.FillBackgroundColor = IndexedColors.Grey80Percent.Index;
styleColor.FillPattern = FillPattern.SolidForeground;
styleColor.BorderBottom = BorderStyle.Thin;
styleColor.BorderLeft = BorderStyle.Thin;
styleColor.BorderRight = BorderStyle.Thin;
styleColor.BorderTop = BorderStyle.Thin;
return(styleColor);
}
public void TestBug52348()
{
XSSFWorkbook workbook = XSSFTestDataSamples.OpenSampleWorkbook("52348.xlsx");
StylesTable st = workbook.GetStylesSource();
Assert.AreEqual(0, st.StyleXfsSize);
XSSFCellStyle style = workbook.CreateCellStyle() as XSSFCellStyle; // no exception at this point
Assert.IsNull(style.GetStyleXf());
Assert.IsNotNull(XSSFTestDataSamples.WriteOutAndReadBack(workbook));
}
static void Main(string[] args)
{
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));
var rowIndex = 0;
IRow row = sheet1.CreateRow(rowIndex);
row.Height = 30 * 80;
row.CreateCell(0).SetCellValue("this is content, very long content, very long content, very long content, very long content");
sheet1.AutoSizeColumn(0);
rowIndex++;
var sheet2 = workbook.CreateSheet("Sheet2");
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);
workbook.Write(fs);
}
}
public XlsxResultsWriter(string resultsXlsxFileName)
{
this.resultsXlsxFileName = resultsXlsxFileName;
this.wb = new XSSFWorkbook();
this.workSheet = wb.CreateSheet();
this.cellStyleComment = wb.CreateCellStyle();
this.cellStyleComment.WrapText = true;
this.cellStyleFailure = wb.CreateCellStyle();
this.cellStyleFailure.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.LightOrange.Index;
this.cellStyleFailure.FillPattern = FillPattern.SolidForeground;
this.cellStyleFailure.WrapText = true;
this.cellStyleInconclusive = wb.CreateCellStyle();
this.cellStyleInconclusive.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.LightYellow.Index;
this.cellStyleInconclusive.FillPattern = FillPattern.SolidForeground;
this.cellStyleInconclusive.WrapText = true;
this.headerRow = workSheet.CreateRow(0);
headerRow.CreateCell(0, CellType.String).SetCellValueWithSanitize("Student ID");
}
private ICellStyle getStyleFromColor(Color color, XSSFWorkbook wb)
{
short col = (short)ColorTranslator.ToOle(color);
if (!colorMap.ContainsKey(col))
{
ICellStyle s = wb.CreateCellStyle();
s.FillForegroundColor = col;
s.FillPattern = FillPattern.SolidForeground;
colorMap.Add(col, s);
}
return(colorMap[col]);
}
public void TestGetFillForegroundColor()
{
XSSFWorkbook wb = new XSSFWorkbook();
StylesTable styles = wb.GetStylesSource();
Assert.AreEqual(1, wb.NumCellStyles);
Assert.AreEqual(2, styles.GetFills().Count);
XSSFCellStyle defaultStyle = (XSSFCellStyle)wb.GetCellStyleAt((short)0);
Assert.AreEqual(IndexedColors.Automatic.Index, defaultStyle.FillForegroundColor);
Assert.AreEqual(null, defaultStyle.FillForegroundColorColor);
Assert.AreEqual(FillPattern.NoFill, defaultStyle.FillPattern);
XSSFCellStyle customStyle = (XSSFCellStyle)wb.CreateCellStyle();
customStyle.FillPattern = (FillPattern.SolidForeground);
Assert.AreEqual(FillPattern.SolidForeground, customStyle.FillPattern);
Assert.AreEqual(3, styles.GetFills().Count);
customStyle.FillForegroundColor = (IndexedColors.BrightGreen.Index);
Assert.AreEqual(IndexedColors.BrightGreen.Index, customStyle.FillForegroundColor);
Assert.AreEqual(4, styles.GetFills().Count);
for (int i = 0; i < 3; i++)
{
XSSFCellStyle style = (XSSFCellStyle)wb.CreateCellStyle();
style.FillPattern = (FillPattern.SolidForeground);
Assert.AreEqual(FillPattern.SolidForeground, style.FillPattern);
Assert.AreEqual(4, styles.GetFills().Count);
style.FillForegroundColor = (IndexedColors.BrightGreen.Index);
Assert.AreEqual(IndexedColors.BrightGreen.Index, style.FillForegroundColor);
Assert.AreEqual(4, styles.GetFills().Count);
}
Assert.IsNotNull(XSSFTestDataSamples.WriteOutAndReadBack(wb));
}
public static bool AppendExcel(DataTable dt, string fileName)
{
bool result = true;
FileStream fs = new FileStream(fileName, FileMode.Open, FileAccess.Read, FileShare.ReadWrite);//读
//POIFSFileSystem ps = new POIFSFileSystem(fs);
IWorkbook workbook = new XSSFWorkbook(fs);
ISheet sheet = workbook.GetSheetAt(0); //获取工作表
FileStream fsAppend = new FileStream(fileName, FileMode.Open, FileAccess.Write, FileShare.ReadWrite); //读
var lastRow = sheet.LastRowNum;
var _doubleCellStyle = workbook.CreateCellStyle();
_doubleCellStyle.DataFormat = workbook.CreateDataFormat().GetFormat("#,##0.00");
for (int i = 0; i < dt.Rows.Count; i++)
{
IRow row = sheet.CreateRow((lastRow + i + 1));
var dataRow = dt.Rows[i];
for (int j = 0; j < dt.Columns.Count; j++)
{
var cellValue = dataRow[j];
string value = string.Empty;
if (cellValue is decimal)
{
var temp = Convert.ToDouble(dataRow[j]);
// value = temp.ToString("#0.##");
var cell = row.CreateCell(j);
cell.SetCellType(CellType.Numeric);
cell.SetCellValue(temp);
//transfer
row.Cells[j].CellStyle = _doubleCellStyle;
}
else if (cellValue is DateTime)
{
value = dataRow[j] == null ? "" : Convert.ToDateTime(dataRow[j]).ToString("yyyy-MM-dd");
row.CreateCell(j).SetCellValue(value);
}
else
{
value = dataRow[j] == null ? "" : dataRow[j].ToString();
row.CreateCell(j).SetCellValue(value);
}
}
}
//fs.Write();
fsAppend.Flush();
workbook.Write(fsAppend);//写入文件
workbook = null;
fsAppend.Close();
return(result);
}
/// <summary>
/// 导出中奖的名单
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void Button1_Click(object sender, EventArgs e)
{
SaveFileDialog saveFileDialog = new SaveFileDialog();
//设置文件标题
saveFileDialog.Title = "导出Excel文件";
//设置文件类型
saveFileDialog.Filter = "Excel 工作簿(*.xlsx)|*.xlsx|Excel 97-2003 工作簿(*.xls)|*.xls";
//设置默认文件类型显示顺序
saveFileDialog.FilterIndex = 1;
//是否自动在文件名中添加扩展名
saveFileDialog.AddExtension = true;
//是否记忆上次打开的目录
saveFileDialog.RestoreDirectory = true;
//设置默认文件名
saveFileDialog.FileName = "中奖名单表.xlsx";
//按下确定选择的按钮
if (saveFileDialog.ShowDialog() == DialogResult.OK)
{
string localFilePath = saveFileDialog.FileName.ToString();
XSSFWorkbook excelBook = new XSSFWorkbook();
ICellStyle style = excelBook.CreateCellStyle();
//创建Excel工作表 Sheet=故障码信息
ISheet sheet1 = excelBook.CreateSheet("某某");
IRow row1 = sheet1.CreateRow(0);
//给标题的每一个单元格赋值
row1.CreateCell(0).SetCellValue("编号"); //0
row1.CreateCell(1).SetCellValue("奖项"); //0
row1.CreateCell(2).SetCellValue("中奖人名"); //0
for (int i = 0; i < list_win.Count; i++)
{
//sheet1.CreateRow(i).
//创建行
IRow rowTemp = sheet1.CreateRow(i + 1);
//故障码DTC
rowTemp.CreateCell(0).SetCellValue(list_win[i].Id);
rowTemp.CreateCell(1).SetCellValue(list_win[i].Awards);
rowTemp.CreateCell(2).SetCellValue(list_win[i].UserName);
}
using (FileStream fs = new FileStream(localFilePath, FileMode.Create, FileAccess.Write))
{
using (MemoryStream stream = new MemoryStream())
{
excelBook.Write(stream);
var buf = stream.ToArray();
fs.Write(buf, 0, buf.Length);
}
}
}
}
/// <summary>
/// Render DataTable to NPOI Excel 2003 MemoryStream
/// NOTE: Limitation of 65,536 rows suppored by XLS
/// </summary>
/// <param name="sourceTable">Source DataTable</param>
/// <returns>MemoryStream containing NPOI Excel workbook</returns>
public static Stream RenderDataTableToExcelXSSF(DataTable sourceTable)
{
XSSFWorkbook xssfworkbook = new XSSFWorkbook();
MemoryStream memoryStream = new MemoryStream();
// By default NPOI creates "Sheet0" which is inconsistent with Excel using "Sheet1"
XSSFSheet sheet = (XSSFSheet)xssfworkbook.CreateSheet("Sheet1");
XSSFRow headerRow = (XSSFRow)sheet.CreateRow(0);
// Header Row
foreach (DataColumn column in sourceTable.Columns)
{
headerRow.CreateCell(column.Ordinal).SetCellValue(column.ColumnName);
}
// Detail Rows
int rowIndex = 1;
// 建立儲存格樣式。
XSSFCellStyle style1 = (XSSFCellStyle)xssfworkbook.CreateCellStyle();//workbook.CreateCellStyle();
style1.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.Blue.Index2;
style1.FillBackgroundColor = NPOI.HSSF.Util.HSSFColor.Red.Index;
style1.FillPattern = NPOI.SS.UserModel.FillPattern.SolidForeground;//HSSFCellStyle.SOLID_FOREGROUND;
XSSFFont f = (XSSFFont)xssfworkbook.CreateFont();
f.Color = NPOI.HSSF.Util.HSSFColor.Red.Index;
f.FontName = "宋体";
foreach (DataRow row in sourceTable.Rows)
{
XSSFRow dataRow = (XSSFRow)sheet.CreateRow(rowIndex);
foreach (DataColumn column in sourceTable.Columns)
{
//HSSFCell cell1 = dataRow.CreateCell(column.Ordinal).SetCellValue(row[column].ToString());
XSSFCell cell = (XSSFCell)dataRow.CreateCell(column.Ordinal);
cell.CellStyle = style1;
cell.CellStyle.SetFont(f);
cell.SetCellValue(row[column].ToString());
}
rowIndex++;
}
xssfworkbook.Write(memoryStream);
memoryStream.Flush();
memoryStream.Position = 0;
return(memoryStream);
}
/// <summary>
///
/// </summary>
/// <param name="savePath"></param>
/// <param name="mbPath"></param>
/// <param name="dataList"></param>
/// <returns></returns>
public static bool ToSingleMbExcel(string savePath, string mbPath, List <SingleMb> dataList)
{
try
{
IWorkbook workbook;
try
{
using (FileStream file = new FileStream(mbPath, FileMode.Open, FileAccess.Read))
{
workbook = new HSSFWorkbook(file);//创建对应文件EXCEL2003
}
}
catch (Exception ex)
{
using (FileStream file = new FileStream(mbPath, FileMode.Open, FileAccess.Read))
{
workbook = new XSSFWorkbook(file);//创建对应文件EXCEL2007
}
}
ISheet sheet = workbook.GetSheetAt(0);
ICellStyle cellStyle = workbook.CreateCellStyle();
cellStyle.WrapText = true;//换行
foreach (SingleMb item in dataList)
{
IRow row = sheet.GetRow(item.rowIndex - 1);
if (row == null)
{
row = sheet.CreateRow(item.rowIndex - 1);
}
ICell cell = row.GetCell(item.cellIndex - 1, MissingCellPolicy.CREATE_NULL_AS_BLANK); //在行中创建单元格
//cell.CellStyle = cellStyle;
cell.SetCellValue(item.value); //循环往第二行的单元格中添加数据
}
using (MemoryStream ms = new MemoryStream())
{
using (FileStream fs = new FileStream(savePath, FileMode.Create, FileAccess.Write))
{
workbook.Write(fs);
}
}
}
catch (Exception)
{
return(false);
}
return(true);
}
