static void Main(string[] args)
{
IWorkbook workbook = new XSSFWorkbook();
ISheet s1 = workbook.CreateSheet("Sheet1");
ICellStyle rowstyle = workbook.CreateCellStyle();
rowstyle.FillForegroundColor = IndexedColors.Red.Index;
rowstyle.FillPattern = FillPattern.SolidForeground;
ICellStyle c1Style = workbook.CreateCellStyle();
c1Style.FillForegroundColor = IndexedColors.Yellow.Index;
c1Style.FillPattern = FillPattern.SolidForeground;
IRow r1 = s1.CreateRow(1);
IRow r2= s1.CreateRow(2);
r1.RowStyle = rowstyle;
r2.RowStyle = rowstyle;
ICell c1 = r2.CreateCell(2);
c1.CellStyle = c1Style;
c1.SetCellValue("Test");
ICell c4 = r2.CreateCell(4);
c4.CellStyle = c1Style;
using(var fs=File.Create("test.xlsx"))
{
workbook.Write(fs);
}
}
static void Main(string[] args)
{
IWorkbook wb = new XSSFWorkbook();
// Create a Worksheet
ISheet ws = wb.CreateSheet("Sheet1");
// Aqua background
ICellStyle style = wb.CreateCellStyle();
style.FillBackgroundColor = IndexedColors.Aqua.Index;
style.FillPattern = FillPattern.BigSpots;
IRow row = ws.CreateRow(0);
ICell cell = row.CreateCell(1);
cell.SetCellValue("X");
cell.CellStyle = style;
// Orange "foreground", foreground being the fill foreground not the font color.
style = wb.CreateCellStyle();
style.FillBackgroundColor = IndexedColors.Orange.Index;
style.FillPattern = FillPattern.SolidForeground;
cell = row.CreateCell(2);
cell.SetCellValue("X");
cell.CellStyle = style;
FileStream sw = File.Create("test.xlsx");
wb.Write(sw);
sw.Close();
}
static void Main(string[] args)
{
IWorkbook wb = new XSSFWorkbook();
ISheet sheet1 = wb.CreateSheet("First Sheet");
ISheet sheet2 = wb.CreateSheet("Second Sheet");
// Note that sheet name is Excel must not exceed 31 characters
// and must not contain any of the any of the following characters:
// 0x0000
// 0x0003
// colon (:)
// backslash (\)
// asterisk (*)
// question mark (?)
// forward slash (/)
// opening square bracket ([)
// closing square bracket (])
// You can use org.apache.poi.ss.util.WorkbookUtil#createSafeSheetName(String nameProposal)}
// for a safe way to create valid names, this utility replaces invalid characters with a space (' ')
String safeName = WorkbookUtil.CreateSafeSheetName("[O'Brien's sales*?]");
ISheet sheet3 = wb.CreateSheet(safeName);
FileStream sw = File.Create("newWorksheet.xls");
wb.Write(sw);
sw.Close();
}
static void Main(string[] args)
{
IWorkbook workbook = new XSSFWorkbook();
////cell style for hyperlinks
////by default hyperlinks are blue and underlined
ICellStyle hlink_style = workbook.CreateCellStyle();
IFont hlink_font = workbook.CreateFont();
hlink_font.Underline = (byte)FontUnderlineType.SINGLE;
hlink_font.Color = HSSFColor.BLUE.index;
hlink_style.SetFont(hlink_font);
ICell cell;
ISheet sheet = workbook.CreateSheet("Hyperlinks");
//URL
cell = sheet.CreateRow(0).CreateCell(0);
cell.SetCellValue("URL Link");
XSSFHyperlink link = new XSSFHyperlink(HyperlinkType.URL);
link.Address = ("http://poi.apache.org/");
cell.Hyperlink = (link);
cell.CellStyle = (hlink_style);
//link to a file in the current directory
cell = sheet.CreateRow(1).CreateCell(0);
cell.SetCellValue("File Link");
link = new XSSFHyperlink(HyperlinkType.FILE);
link.Address = ("link1.xls");
cell.Hyperlink = (link);
cell.CellStyle = (hlink_style);
//e-mail link
cell = sheet.CreateRow(2).CreateCell(0);
cell.SetCellValue("Email Link");
link = new XSSFHyperlink(HyperlinkType.EMAIL);
//note, if subject contains white spaces, make sure they are url-encoded
link.Address = ("mailto:[email protected]?subject=Hyperlinks");
cell.Hyperlink = (link);
cell.CellStyle = (hlink_style);
//link to a place in this workbook
//Create a target sheet and cell
ISheet sheet2 = workbook.CreateSheet("Target ISheet");
sheet2.CreateRow(0).CreateCell(0).SetCellValue("Target ICell");
cell = sheet.CreateRow(3).CreateCell(0);
cell.SetCellValue("Worksheet Link");
link = new XSSFHyperlink(HyperlinkType.DOCUMENT);
link.Address = ("'Target ISheet'!A1");
cell.Hyperlink = (link);
cell.CellStyle = (hlink_style);
FileStream sw = File.Create("test.xlsx");
workbook.Write(sw);
sw.Close();
}
static void Main(string[] args)
{
IWorkbook workbook = new XSSFWorkbook();
workbook.CreateSheet("Sheet A1");
workbook.CreateSheet("Sheet A2");
workbook.CreateSheet("Sheet A3");
FileStream sw = File.Create("test.xlsx");
workbook.Write(sw);
sw.Close();
}
static void Main(string[] args)
{
IWorkbook wb = new XSSFWorkbook();
wb.CreateSheet("new sheet");
wb.CreateSheet("second sheet");
ISheet cloneSheet = wb.CloneSheet(0);
FileStream sw = File.Create("newWorksheet.xls");
wb.Write(sw);
sw.Close();
}
static void Main(string[] args)
{
IWorkbook wb = new XSSFWorkbook();
ISheet sheet1 = wb.CreateSheet("Sheet1");
sheet1.SetMargin(MarginType.RightMargin, 0.5d);
sheet1.SetMargin(MarginType.TopMargin, 0.6d);
sheet1.SetMargin(MarginType.LeftMargin, 0.4d);
sheet1.SetMargin(MarginType.BottomMargin, 0.3d);
sheet1.PrintSetup.Copies = 3;
sheet1.PrintSetup.NoColor = true;
sheet1.PrintSetup.Landscape = true;
sheet1.PrintSetup.PaperSize = (short)PaperSize.A4+1;
sheet1.FitToPage = true;
sheet1.PrintSetup.FitHeight = 2;
sheet1.PrintSetup.FitWidth = 3;
sheet1.IsPrintGridlines = true;
sheet1.CreateRow(0).CreateCell(0).SetCellValue("This is a Sample");
int x = 1;
for (int i = 1; i <= 15; i++)
{
IRow row = sheet1.CreateRow(i);
for (int j = 0; j < 15; j++)
{
row.CreateCell(j).SetCellValue(x++);
}
}
ISheet sheet2 = wb.CreateSheet("Sheet2");
sheet2.PrintSetup.Copies = 1;
sheet2.PrintSetup.Landscape = false;
sheet2.PrintSetup.Notes = true;
//sheet2.PrintSetup.EndNote = true;
//sheet2.PrintSetup.CellError = DisplayCellErrorType.ErrorAsNA;
sheet2.PrintSetup.PaperSize = (short)PaperSize.A5+1;
x = 100;
for (int i = 1; i <= 15; i++)
{
IRow row = sheet2.CreateRow(i);
for (int j = 0; j < 15; j++)
{
row.CreateCell(j).SetCellValue(x++);
}
}
FileStream sw = File.Create("test.xlsx");
wb.Write(sw);
sw.Close();
}
protected void btn_click(object sender, EventArgs e)
{
//FileStream fs = new FileStream(Server.MapPath(@"\Content\Sample.xlsx"), FileMode.Open, FileAccess.Read);
//XSSFWorkbook temWorkBook = new XSSFWorkbook(fs);
//ISheet nsheet = temWorkBook.GetSheet("Sheet1");
//IRow datarow = nsheet.GetRow(4);
//datarow.GetCell(0).SetCellValue(77);
//nsheet.ForceFormulaRecalculation = true;
//using (var ms = new MemoryStream())
//{
// temWorkBook.Write(ms);
// Response.Clear();
// Response.ContentType = "Application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
// Response.AppendHeader("Content-Disposition","inline;filename=Sample"+DateTime.Now.ToString("yyyyMMMdd")+".xlsx");
// Response.BinaryWrite(ms.ToArray());
// Response.End();
//}
IWorkbook workbook = new XSSFWorkbook();
ISheet sheet1 = workbook.CreateSheet("New Sheet");
ISheet sheet2 = workbook.CreateSheet("Second Sheet");
ICell cell1 = sheet1.CreateRow(0).CreateCell(0);
IFont fontBold = workbook.CreateFont();
fontBold.Boldweight = (short)FontBoldWeight.Bold;
ICellStyle style1 = workbook.CreateCellStyle();
style1.SetFont(fontBold);
cell1.CellStyle = style1;
cell1.SetCellValue("sample value");
int x = 1;
for (int i = 1; i <= 15; i++)
{
IRow row = sheet1.CreateRow(i);
for(int j = 0;j < 15;j++)
{
row.CreateCell(j).SetCellValue(x++);
}
}
using (var ms = new MemoryStream())
{
workbook.Write(ms);
Response.Clear();
Response.ContentType = "Application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
Response.AppendHeader("Content-Disposition", "inline;filename=Sample" + DateTime.Now.ToString("yyyyMMMdd") + ".xlsx");
Response.BinaryWrite(ms.ToArray());
Response.End();
}
}
static void Main(string[] args)
{
IWorkbook wb = new XSSFWorkbook();
wb.CreateSheet("new sheet");
wb.CreateSheet("second sheet");
wb.CreateSheet("third sheet");
wb.SetSheetOrder("second sheet", 0);
wb.SetSheetOrder("new sheet", 1);
wb.SetSheetOrder("third sheet", 2);
FileStream sw = File.Create("../../data/Reordered.xls");
wb.Write(sw);
sw.Close();
}
static void Main(string[] args)
{
IWorkbook wb = new XSSFWorkbook();
// Create a Worksheet
ISheet ws = wb.CreateSheet("Sheet1");
// Create a new font and alter it
IFont font = wb.CreateFont();
font.FontHeightInPoints = 24;
font.FontName = "Courier New";
font.IsItalic = true;
font.IsStrikeout = true;
// Fonts are set into a style so create a new one to use.
ICellStyle style = wb.CreateCellStyle();
style.SetFont(font);
IRow row = ws.CreateRow(0);
// Create a cell and put a value in it.
ICell cell = row.CreateCell(1);
cell.SetCellValue("Thisi s a test of fonts");
cell.CellStyle = style;
FileStream sw = File.Create("test.xlsx");
wb.Write(sw);
sw.Close();
}
static void Main(string[] args)
{
IWorkbook wb = new XSSFWorkbook();
ISheet sheet1 = wb.CreateSheet("First Sheet");
//add picture data to this workbook.
byte[] bytes = File.ReadAllBytes("../../data/aspose.png");
int pictureIdx = wb.AddPicture(bytes, PictureType.PNG);
ICreationHelper helper = wb.GetCreationHelper();
// Create the drawing patriarch. This is the top level container for all shapes.
IDrawing drawing = sheet1.CreateDrawingPatriarch();
// add a picture shape
IClientAnchor anchor = helper.CreateClientAnchor();
//set top-left corner of the picture,
//subsequent call of Picture#resize() will operate relative to it
anchor.Col1 = 3;
anchor.Row1 = 2;
IPicture pict = drawing.CreatePicture(anchor, pictureIdx);
//auto-size picture relative to its top-left corner
pict.Resize();
FileStream sw = File.Create("../../data/image.xlsx");
wb.Write(sw);
sw.Close();
}
public override void CreateWorkBook(string destinationPath)
{
try
{
var workbook = new XSSFWorkbook();
var sheet1 = (XSSFSheet)workbook.CreateSheet("Sheet1");
sheet1.CreateRow(0).CreateCell(0).SetCellValue("This is a Sample xlsx Sheet");
//var x = 1;
//for (var i = 1; i <= 10000; i++)
//{
// var row = sheet1.CreateRow(i);
// for (var j = 0; j < 50; j++)
// {
// row.CreateCell(j).SetCellValue("cell" + x++);
// }
//}
var s = sheet1 as ISheet;
CreateRows(20000, 40, ref s);
using (var f = File.Create(destinationPath))
{
workbook.Write(f);
}
}
catch (Exception e)
{
throw new Exception("Cannot create Excell SpreadSheet (.xlsx)", e);
}
}
static void Main(string[] args)
{
IWorkbook workbook = new XSSFWorkbook();
ISheet sheet1 = workbook.CreateSheet("Sheet1");
// Setting support for Right To Left
sheet1.IsRightToLeft = true;
sheet1.CreateRow(0).CreateCell(0).SetCellValue("This is a Sample");
int x = 1;
for(int i = 1; i <= 15; i++)
{
IRow row = sheet1.CreateRow(i);
for(int j = 0; j < 15; j++)
{
row.CreateCell(j).SetCellValue(x++);
}
}
FileStream sw = File.Create("test.xlsx");
workbook.Write(sw);
sw.Close();
}
public void TestBug48936()
{
IWorkbook w = new XSSFWorkbook();
ISheet s = w.CreateSheet();
int i = 0;
List<String> lst = ReadStrings("48936-strings.txt");
foreach (String str in lst)
{
s.CreateRow(i++).CreateCell(0).SetCellValue(str);
}
try
{
w = XSSFTestDataSamples.WriteOutAndReadBack(w);
}
catch (POIXMLException)
{
Assert.Fail("Detected Bug #48936");
}
s = w.GetSheetAt(0);
i = 0;
foreach (String str in lst)
{
String val = s.GetRow(i++).GetCell(0).StringCellValue;
Assert.AreEqual(str, val);
}
}
static void Main(string[] args)
{
IWorkbook workbook = new XSSFWorkbook();
ISheet sheet1 = workbook.CreateSheet("PictureSheet");
IDrawing patriarch = sheet1.CreateDrawingPatriarch();
//create the anchor
XSSFClientAnchor anchor = new XSSFClientAnchor(500, 200, 0, 0, 2, 2, 4, 7);
anchor.AnchorType = 2;
//load the picture and get the picture index in the workbook
//first picture
int imageId= LoadImage("../../image/HumpbackWhale.jpg", workbook);
XSSFPicture picture = (XSSFPicture)patriarch.CreatePicture(anchor, imageId);
//Reset the image to the original size.
//picture.Resize(); //Note: Resize will reset client anchor you set.
picture.LineStyle = LineStyle.DashDotGel;
//second picture
int imageId2 = LoadImage("../../image/HumpbackWhale.jpg", workbook);
XSSFClientAnchor anchor2 = new XSSFClientAnchor(500, 200, 0, 0, 5, 10, 7, 15);
XSSFPicture picture2 = (XSSFPicture)patriarch.CreatePicture(anchor2, imageId2);
picture.LineStyle = LineStyle.DashDotGel;
FileStream sw = File.Create("test.xlsx");
workbook.Write(sw);
sw.Close();
}
public void Create(Bilan bilan, string path)
{
var splitPath = path.Split('\\');
var pathFile = splitPath.Take(splitPath.Count() - 1).Aggregate((e, p) => e + "\\" + p) + "\\";
var fileNameOld = splitPath.Last();
var fileName = "synoptique_" + fileNameOld;
using (var stream = new FileStream(pathFile + fileName, FileMode.Create, FileAccess.Write))
{
var workbook = new XSSFWorkbook();
var sheet = workbook.CreateSheet("synoptique");
var cadreArmoire = new CadreArmoire(sheet, workbook);
cadreArmoire.Create(bilan.PageDeGarde.ChambrePMZ);
var cadrePA = new CadrePA(sheet, workbook);
cadrePA.Create(bilan.PageDeGarde.ChambrePointAboutement);
var positionnementEtudeCreator = new PositionnementEtudeCreator(sheet, workbook);
var hauteur = 9;
var numeroCassette = 0;
foreach (var positionnementEtude in bilan.PositionnementEtudes)
{
positionnementEtudeCreator.Create(positionnementEtude, ref hauteur, ref numeroCassette);
}
SetWidth(sheet);
workbook.Write(stream);
}
}
static void Main(string[] args)
{
IWorkbook wb = new XSSFWorkbook();
// Create a Worksheet
ISheet ws = wb.CreateSheet("Sheet1");
ICellStyle style = wb.CreateCellStyle();
//Setting the line of the top border
style.BorderTop = BorderStyle.Thick;
style.TopBorderColor = 256;
style.BorderLeft = BorderStyle.Thick;
style.LeftBorderColor = 256;
style.BorderRight = BorderStyle.Thick;
style.RightBorderColor = 256;
style.BorderBottom = BorderStyle.Thick;
style.BottomBorderColor = 256;
IRow row = ws.CreateRow(0);
ICell cell = row.CreateCell(1);
cell.CellStyle = style;
FileStream sw = File.Create("test.xlsx");
wb.Write(sw);
sw.Close();
}
protected void Button1_Click(object sender, EventArgs e)
{
string filename = "test.xlsx";
Response.Clear();
Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
Response.AddHeader("Content-Disposition", string.Format("attachment;filename={0}", filename));
XSSFWorkbook workbook = new XSSFWorkbook();
ISheet sheet1 = workbook.CreateSheet("Sheet1");
sheet1.CreateRow(0).CreateCell(0).SetCellValue("This is a Sample");
int x = 1;
for (int i = 1; i <= 15; i++)
{
IRow row = sheet1.CreateRow(i);
for (int j = 0; j < 15; j++)
{
row.CreateCell(j).SetCellValue(x++);
}
}
using (var f = File.Create(@"c:\test.xlsx"))
{
workbook.Write(f);
}
Response.WriteFile(@"c:\test.xlsx");
//http://social.msdn.microsoft.com/Forums/en-US/3a7bdd79-f926-4a5e-bcb0-ef81b6c09dcf/responseoutputstreamwrite-writes-all-but-insetrs-a-char-every-64k?forum=ncl
//workbook.Write(Response.OutputStream); cannot be used
//root cause: Response.OutputStream will insert unnecessary byte into the response bytes.
Response.Flush();
Response.End();
}
/// <summary>
/// return File(bs, "application/vnd.ms-excel");
/// </summary>
/// <param name="tb"></param>
/// <returns></returns>
public static byte[] DataTableToExcel(DataTable tb)
{
tb = tb ?? throw new Exception($"无法把空{nameof(DataTable)}转成Excel");
using (var ms = new MemoryStream())
{
var workbook = new NPOI.XSSF.UserModel.XSSFWorkbook();
var sheet = workbook.CreateSheet(ValidateHelper.IsPlumpString(tb.TableName) ? tb.TableName : "sheet");
var style = GetStyle(workbook,
NPOI.HSSF.Util.HSSFColor.White.Index, NPOI.HSSF.Util.HSSFColor.Black.Index);
for (int i = 0; i < tb.Rows.Count; ++i)
{
var row = sheet.CreateRow(i);
for (int j = 0; j < tb.Columns.Count; ++j)
{
var cell = row.CreateCell(j);
var data = tb.Rows[i][j];
cell.SetCellValue(ConvertHelper.GetString(data));
cell.CellStyle = style;
}
}
workbook.Write(ms);
workbook.Clear();
tb.Clear();
var bs = ms.ToArray();
return(bs);
}
}
public void TestNoColsWithoutWidthWhenGroupingAndCollapsing()
{
XSSFWorkbook wb = new XSSFWorkbook();
XSSFSheet sheet = (XSSFSheet)wb.CreateSheet("test");
sheet.SetColumnWidth(4, 5000);
sheet.SetColumnWidth(5, 5000);
sheet.GroupColumn((short)4, (short)5);
sheet.SetColumnGroupCollapsed(4, true);
CT_Cols cols = sheet.GetCTWorksheet().GetColsArray(0);
//logger.log(POILogger.DEBUG, "test52186_2/cols:" + cols);
wb = XSSFTestDataSamples.WriteOutAndReadBack(wb, "testNoColsWithoutWidthWhenGroupingAndCollapsing");
sheet = (XSSFSheet)wb.GetSheet("test");
for (int i = 4; i <= 5; i++)
{
Assert.AreEqual(5000, sheet.GetColumnWidth(i), "Unexpected width of column " + i);
}
cols = sheet.GetCTWorksheet().GetColsArray(0);
foreach (CT_Col col in cols.GetColArray())
{
Assert.IsTrue(col.IsSetWidth(), "Col width attribute is unset: " + col.ToString());
}
}
public static void Main(string[] args)
{
IWorkbook wb = new XSSFWorkbook();
ISheet s1=wb.CreateSheet("Monthly Salary Report");
IRow headerRow = s1.CreateRow(0);
headerRow.CreateCell(0).SetCellValue("First Name");
s1.SetColumnWidth(0, 20 * 256);
headerRow.CreateCell(1).SetCellValue("Last Name");
s1.SetColumnWidth(1, 20 * 256);
headerRow.CreateCell(2).SetCellValue("Salary");
headerRow.CreateCell(3).SetCellValue("Tax Rate");
headerRow.CreateCell(4).SetCellValue("Tax");
headerRow.CreateCell(5).SetCellValue("Delivery");
int row = 1;
GenerateRow(s1, row++, "Bill", "Zhang", 5000, 9.0/100);
GenerateRow(s1, row++, "Amy", "Huang", 8000, 11.0/100);
GenerateRow(s1, row++, "Tomos", "Johnson", 6000, 9.0/100);
GenerateRow(s1, row++, "Macro", "Jeep", 12000, 15.0/100);
s1.ForceFormulaRecalculation = false;
FileStream fs = File.Create("test.xlsx");
wb.Write(fs);
fs.Close();
}
public void TestCreate()
{
XSSFWorkbook wb = new XSSFWorkbook();
XSSFSheet sheet = (XSSFSheet)wb.CreateSheet();
XSSFDrawing drawing = (XSSFDrawing)sheet.CreateDrawingPatriarch();
byte[] jpegData = Encoding.UTF8.GetBytes("test jpeg data");
IList pictures = wb.GetAllPictures();
Assert.AreEqual(0, pictures.Count);
int jpegIdx = wb.AddPicture(jpegData, PictureType.JPEG);
Assert.AreEqual(1, pictures.Count);
Assert.AreEqual("jpeg", ((XSSFPictureData)pictures[jpegIdx]).SuggestFileExtension());
Assert.IsTrue(Arrays.Equals(jpegData, ((XSSFPictureData)pictures[jpegIdx]).Data));
XSSFClientAnchor anchor = new XSSFClientAnchor(0, 0, 0, 0, 1, 1, 10, 30);
Assert.AreEqual(AnchorType.MoveAndResize, (AnchorType)anchor.AnchorType);
anchor.AnchorType = (int)AnchorType.DontMoveAndResize;
Assert.AreEqual(AnchorType.DontMoveAndResize, (AnchorType)anchor.AnchorType);
XSSFPicture shape = (XSSFPicture)drawing.CreatePicture(anchor, jpegIdx);
Assert.IsTrue(anchor.Equals(shape.GetAnchor()));
Assert.IsNotNull(shape.PictureData);
Assert.IsTrue(Arrays.Equals(jpegData, shape.PictureData.Data));
CT_TwoCellAnchor ctShapeHolder = (CT_TwoCellAnchor)drawing.GetCTDrawing().CellAnchors[0];
// STEditAs.ABSOLUTE corresponds to ClientAnchor.DONT_MOVE_AND_RESIZE
Assert.AreEqual(ST_EditAs.absolute, ctShapeHolder.editAs);
}
public void ExportExcel(IList<dynamic> items, string fileName)
{
IWorkbook workbook = new XSSFWorkbook();
ISheet worksheet = workbook.CreateSheet("Sheet1");
for (int rownum = 0; rownum < items.Count; rownum++)
{
IRow row = worksheet.CreateRow(rownum);
var item = items[rownum];
var keys = item.Keys.ToArray();
for (int index = 0; index < keys.Length; index++)
{
var key = keys[index];
ICell cell = row.CreateCell(index);
var value = item[key];
if (value != null)
{
cell.SetCellValue(value.ToString());
}
}
}
using (FileStream sw = File.Create(fileName))
{
workbook.Write(sw);
sw.Close();
}
}
static void Main(string[] args)
{
IWorkbook wb = new XSSFWorkbook();
ISheet sheet = wb.CreateSheet("linechart");
// Create a row and put some cells in it. Rows are 0 based.
IRow row;
ICell cell;
for (int rowIndex = 0; rowIndex < NUM_OF_ROWS; rowIndex++)
{
row = sheet.CreateRow((short)rowIndex);
for (int colIndex = 0; colIndex < NUM_OF_COLUMNS; colIndex++)
{
cell = row.CreateCell((short)colIndex);
cell.SetCellValue(colIndex * (rowIndex + 1));
}
}
IDrawing drawing = sheet.CreateDrawingPatriarch();
IClientAnchor anchor1 = drawing.CreateAnchor(0, 0, 0, 0, 0, 5, 10, 15);
CreateChart(drawing, sheet, anchor1, "title1","title2");
IClientAnchor anchor2 = drawing.CreateAnchor(0, 0, 0, 0, 0, 20, 10, 35);
CreateChart(drawing, sheet, anchor2, "s1", "s2");
using (FileStream fs =File.Create("test.xlsx"))
{
wb.Write(fs);
}
}
static void Main(string[] args)
{
IWorkbook workbook = new XSSFWorkbook();
ISheet sheet1 = workbook.CreateSheet("Sheet1");
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 = FillPatternType.SOLID_FOREGROUND;
cell.CellStyle = style1;
}
else
{
//fill background with yellow
ICellStyle style1 = workbook.CreateCellStyle();
style1.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.YELLOW.index2;
style1.FillPattern = FillPatternType.SOLID_FOREGROUND;
cell.CellStyle = style1;
}
x++;
}
}
FileStream sw = File.Create("test.xlsx");
workbook.Write(sw);
sw.Close();
}
public void TestXSSFSetArrayFormula_multiCell()
{
ICellRange<ICell> cells;
String formula2 = "456";
XSSFWorkbook workbook = new XSSFWorkbook();
XSSFSheet sheet = (XSSFSheet)workbook.CreateSheet();
CellRangeAddress range = CellRangeAddress.ValueOf("C4:C6");
cells = sheet.SetArrayFormula(formula2, range);
Assert.AreEqual(3, cells.Size);
// sheet.SetArrayFormula Creates rows and cells for the designated range
/*
* From the spec:
* For a multi-cell formula, the c elements for all cells except the top-left
* cell in that range shall not have an f element;
*/
// Check that each cell exists and that the formula text is Set correctly on the first cell
XSSFCell firstCell = (XSSFCell)cells.TopLeftCell;
ConfirmArrayFormulaCell(firstCell, "C4", formula2, "C4:C6");
ConfirmArrayFormulaCell(cells.GetCell(1, 0), "C5");
ConfirmArrayFormulaCell(cells.GetCell(2, 0), "C6");
Assert.AreSame(firstCell, sheet.GetFirstCellInArrayFormula(firstCell));
}
static void Main(string[] args)
{
IWorkbook workbook = new XSSFWorkbook();
ISheet sheet = workbook.CreateSheet("Sheet1");
//increase the width of Column A
sheet.SetColumnWidth(0, 5000);
//create the format instance
IDataFormat format = workbook.CreateDataFormat();
// Create a row and put some cells in it. Rows are 0 based.
ICell cell = sheet.CreateRow(0).CreateCell(0);
//number format with 2 digits after the decimal point - "1.20"
SetValueAndFormat(workbook, cell, 1.2, HSSFDataFormat.GetBuiltinFormat("0.00"));
//RMB currency format with comma - "¥20,000"
ICell cell2 = sheet.CreateRow(1).CreateCell(0);
SetValueAndFormat(workbook, cell2, 20000, format.GetFormat("¥#,##0"));
//scentific number format - "3.15E+00"
ICell cell3 = sheet.CreateRow(2).CreateCell(0);
SetValueAndFormat(workbook, cell3, 3.151234, format.GetFormat("0.00E+00"));
//percent format, 2 digits after the decimal point - "99.33%"
ICell cell4 = sheet.CreateRow(3).CreateCell(0);
SetValueAndFormat(workbook, cell4, 0.99333, format.GetFormat("0.00%"));
//phone number format - "021-65881234"
ICell cell5 = sheet.CreateRow(4).CreateCell(0);
SetValueAndFormat(workbook, cell5, 02165881234,format.GetFormat("000-00000000"));
//Chinese capitalized character number - 壹贰叁 元
ICell cell6 = sheet.CreateRow(5).CreateCell(0);
SetValueAndFormat(workbook, cell6, 123, format.GetFormat("[DbNum2][$-804]0 元"));
//Chinese date string
ICell cell7 = sheet.CreateRow(6).CreateCell(0);
SetValueAndFormat(workbook, cell7, new DateTime(2004, 5, 6), format.GetFormat("yyyy年m月d日"));
cell7.SetCellValue(new DateTime(2004, 5, 6));
//Chinese date string
ICell cell8 = sheet.CreateRow(7).CreateCell(0);
SetValueAndFormat(workbook,cell8,new DateTime(2005, 11, 6),format.GetFormat("yyyy年m月d日"));
//formula value with datetime style
ICell cell9 = sheet.CreateRow(8).CreateCell(0);
cell9.CellFormula = "DateValue(\"2005-11-11\")+TIMEVALUE(\"11:11:11\")";
ICellStyle cellStyle9 = workbook.CreateCellStyle();
cellStyle9.DataFormat = HSSFDataFormat.GetBuiltinFormat("m/d/yy h:mm");
cell9.CellStyle = cellStyle9;
//display current time
ICell cell10 = sheet.CreateRow(9).CreateCell(0);
SetValueAndFormat(workbook, cell10, DateTime.Now, format.GetFormat("[$-409]h:mm:ss AM/PM;@"));
FileStream sw = File.Create("test.xlsx");
workbook.Write(sw);
sw.Close();
}
public void TestRepeatingRowsAndColums()
{
// First Test that Setting RR&C for same sheet more than once only Creates a
// single Print_Titles built-in record
XSSFWorkbook wb = new XSSFWorkbook();
wb.CreateSheet("First Sheet");
wb.SetRepeatingRowsAndColumns(0, -1, -1, -1, -1);
// Set repeating rows and columns twice for the first sheet
for (int i = 0; i < 2; i++)
{
wb.SetRepeatingRowsAndColumns(0, 0, 0, 0, 3);
//sheet.CreateFreezePane(0, 3);
}
Assert.AreEqual(1, wb.NumberOfNames);
IName nr1 = wb.GetNameAt(0);
Assert.AreEqual(XSSFName.BUILTIN_PRINT_TITLE, nr1.NameName);
Assert.AreEqual("'First Sheet'!$A:$A,'First Sheet'!$1:$4", nr1.RefersToFormula);
//remove the columns part
wb.SetRepeatingRowsAndColumns(0, -1, -1, 0, 3);
Assert.AreEqual("'First Sheet'!$1:$4", nr1.RefersToFormula);
//revert
wb.SetRepeatingRowsAndColumns(0, 0, 0, 0, 3);
//remove the rows part
wb.SetRepeatingRowsAndColumns(0, 0, 0, -1, -1);
Assert.AreEqual("'First Sheet'!$A:$A", nr1.RefersToFormula);
//revert
wb.SetRepeatingRowsAndColumns(0, 0, 0, 0, 3);
// Save and re-open
IWorkbook nwb = XSSFTestDataSamples.WriteOutAndReadBack(wb);
Assert.AreEqual(1, nwb.NumberOfNames);
nr1 = nwb.GetNameAt(0);
Assert.AreEqual(XSSFName.BUILTIN_PRINT_TITLE, nr1.NameName);
Assert.AreEqual("'First Sheet'!$A:$A,'First Sheet'!$1:$4", nr1.RefersToFormula);
// check that Setting RR&C on a second sheet causes a new Print_Titles built-in
// name to be Created
nwb.CreateSheet("SecondSheet");
nwb.SetRepeatingRowsAndColumns(1, 1, 2, 0, 0);
Assert.AreEqual(2, nwb.NumberOfNames);
IName nr2 = nwb.GetNameAt(1);
Assert.AreEqual(XSSFName.BUILTIN_PRINT_TITLE, nr2.NameName);
Assert.AreEqual("SecondSheet!$B:$C,SecondSheet!$1:$1", nr2.RefersToFormula);
nwb.SetRepeatingRowsAndColumns(1, -1, -1, -1, -1);
}
static void Main(string[] args)
{
IWorkbook workbook = new XSSFWorkbook();
ISheet sheet1 = workbook.CreateSheet("Sheet1");
//font style1: underlined, italic, red color, fontsize=20
IFont font1 = workbook.CreateFont();
font1.Color = IndexedColors.Red.Index;
font1.IsItalic = true;
font1.Underline = FontUnderlineType.Double;
font1.FontHeightInPoints = 20;
//bind font with style 1
ICellStyle style1 = workbook.CreateCellStyle();
style1.SetFont(font1);
//font style2: strikeout line, green color, fontsize=15, fontname='宋体'
IFont font2 = workbook.CreateFont();
font2.Color = IndexedColors.OliveGreen.Index;
font2.IsStrikeout = true;
font2.FontHeightInPoints = 15;
font2.FontName = "宋体";
//bind font with style 2
ICellStyle style2 = workbook.CreateCellStyle();
style2.SetFont(font2);
//apply font styles
ICell cell1 = sheet1.CreateRow(1).CreateCell(1);
cell1.SetCellValue("Hello World!");
cell1.CellStyle = style1;
ICell cell2 = sheet1.CreateRow(3).CreateCell(1);
cell2.SetCellValue("早上好！");
cell2.CellStyle = style2;
////cell with rich text
ICell cell3 = sheet1.CreateRow(5).CreateCell(1);
XSSFRichTextString richtext = new XSSFRichTextString("Microsoft OfficeTM");
//apply font to "Microsoft Office"
IFont font4 = workbook.CreateFont();
font4.FontHeightInPoints = 12;
richtext.ApplyFont(0, 16, font4);
//apply font to "TM"
IFont font3 = workbook.CreateFont();
font3.TypeOffset = FontSuperScript.Super;
font3.IsItalic = true;
font3.Color = IndexedColors.Blue.Index;
font3.FontHeightInPoints = 8;
richtext.ApplyFont(16, 18, font3);
cell3.SetCellValue(richtext);
FileStream sw = File.Create("test.xlsx");
workbook.Write(sw);
sw.Close();
}
/// <summary>
/// return File(bs, "application/vnd.ms-excel");
/// </summary>
public byte[] DataTableToExcel(DataTable tb, bool show_header = true)
{
tb = tb ?? throw new ArgumentNullException($"无法把空{nameof(DataTable)}转成Excel");
using (var ms = new MemoryStream())
{
var workbook = new NPOI.XSSF.UserModel.XSSFWorkbook();
var sheet = workbook.CreateSheet(ValidateHelper.IsNotEmpty(tb.TableName) ? tb.TableName : "sheet");
var columns = tb.Columns.AsEnumerable_ <DataColumn>();
var row_index = 0;
IRow NewRow() => sheet.CreateRow(row_index++);
if (show_header)
{
//头部
var header_style = this.GetStyle(workbook,
NPOI.HSSF.Util.HSSFColor.Black.Index,
NPOI.HSSF.Util.HSSFColor.White.Index);
var header = NewRow();
var cell_index = 0;
foreach (var col in columns)
{
var cell = header.CreateCell(cell_index++, CellType.String);
var data = col.ColumnName;
cell.SetCellValue(data);
cell.CellStyle = header_style;
}
}
var style = this.GetStyle(workbook,
NPOI.HSSF.Util.HSSFColor.White.Index,
NPOI.HSSF.Util.HSSFColor.Black.Index);
foreach (var tb_row in tb.Rows.AsEnumerable_ <DataRow>())
{
var row = NewRow();
var cell_index = 0;
foreach (var tb_col in columns)
{
var cell = row.CreateCell(cell_index++, CellType.String);
var data = tb_row[tb_col.ColumnName];
cell.SetCellValue(ConvertHelper.GetString(data));
cell.CellStyle = style;
}
}
workbook.Write(ms);
workbook.Clear();
tb.Clear();
var bs = ms.ToArray();
return(bs);
}
}
/// <summary>
/// 读取DataTable的数据,并把数据写入excel
/// </summary>
/// <param name="filePath">文件路径</param>
/// <param name="fileName">文件名</param>
/// <param name="sheetName">excel里第一个sheet的名称</param>
/// <param name="DataTable1">要写入的DataTable</param>
/// <param name="freezePaneRow">冻结拆分窗口的行数</param>
/// <returns></returns>
public string createExcel(string filePath, string fileName, string sheetName, DataTable DataTable1, int freezePaneRow)
{
string result = null;
try
{
XSSFWorkbook xssfworkbook = new XSSFWorkbook();
ISheet sheet = xssfworkbook.CreateSheet(sheetName);
//设置居中
ICellStyle styleCell = xssfworkbook.CreateCellStyle();
styleCell.VerticalAlignment = VerticalAlignment.Center;
styleCell.Alignment = HorizontalAlignment.Center;
for (int i = 0; i < DataTable1.Rows.Count; i++)
{
IRow row1 = sheet.CreateRow(i);
for (int j = 0; j < DataTable1.Columns.Count; j++)
{
ICell cell = row1.CreateCell(j);
cell.SetCellValue(DataTable1.Rows[i][j].ToString());
sheet.GetRow(i).GetCell(j).CellStyle = styleCell;
}
}
//自适应列宽
for (int i = 0; i < DataTable1.Columns.Count; i++) {
sheet.AutoSizeColumn(i);
}
//冻结拆分窗口
sheet.CreateFreezePane(1, freezePaneRow);
MemoryStream stream = new MemoryStream();
xssfworkbook.Write(stream);
var buf = stream.ToArray();
stream.Close();
stream.Dispose();
if (!Directory.Exists(filePath)) {
Directory.CreateDirectory(filePath);
}
using (FileStream fs = new FileStream(filePath + fileName, FileMode.Create, FileAccess.Write))
{
fs.Write(buf, 0, buf.Length);
fs.Flush();
fs.Close();
fs.Dispose();
}
}
catch (Exception e) {
result = e.Message;
}
return result;
}
public void TestMultipleDrawings()
{
XSSFWorkbook wb = new XSSFWorkbook();
for (int i = 0; i < 3; i++)
{
XSSFSheet sheet = (XSSFSheet)wb.CreateSheet();
XSSFDrawing drawing = (XSSFDrawing)sheet.CreateDrawingPatriarch();
}
OPCPackage pkg = wb.Package;
Assert.AreEqual(3, pkg.GetPartsByContentType(XSSFRelation.DRAWINGS.ContentType).Count);
}
public static byte[] Output(DataTable dataTable, string[] tableTitle)
{
NPOI.SS.UserModel.IWorkbook workbook = new NPOI.XSSF.UserModel.XSSFWorkbook();
NPOI.SS.UserModel.ISheet sheet = workbook.CreateSheet("sheet");
IRow Title = null;
IRow rows = null;
for (int i = 1; i <= dataTable.Rows.Count; i++)
{
//创建表头
if (i - 1 == 0)
{
Title = sheet.CreateRow(0);
for (int k = 1; k < tableTitle.Length + 1; k++)
{
Title.CreateCell(0).SetCellValue("序号");
Title.CreateCell(k).SetCellValue(tableTitle[k - 1]);
}
continue;
}
else
{
rows = sheet.CreateRow(i - 1);
for (int j = 1; j <= dataTable.Columns.Count; j++)
{
rows.CreateCell(0).SetCellValue(i - 1);
rows.CreateCell(j).SetCellValue(dataTable.Rows[i - 1][j - 1].ToString());
}
}
}
byte[] buffer = new byte[1024 * 5];
using (MemoryStream ms = new MemoryStream())
{
workbook.Write(ms);
buffer = ms.GetBuffer();
ms.Close();
}
return(buffer);
}
/// <summary>
/// return File(bs, "application/vnd.ms-excel");
/// </summary>
/// <param name="tb"></param>
/// <returns></returns>
public static byte[] DataTableToExcel(DataTable tb)
{
if (tb == null)
{
return(null);
}
using (var ms = new MemoryStream())
{
var workbook = new NPOI.XSSF.UserModel.XSSFWorkbook();
var sheet = workbook.CreateSheet(ValidateHelper.IsPlumpString(tb.TableName) ? tb.TableName : "sheet");
var style = GetStyle(workbook,
NPOI.HSSF.Util.HSSFColor.Red.Index, NPOI.HSSF.Util.HSSFColor.White.Index);
NPOI.SS.UserModel.IRow row = null;
NPOI.SS.UserModel.ICell cell = null;
for (int i = 0; i < tb.Rows.Count; ++i)
{
row = sheet.CreateRow(i);
for (int j = 0; j < tb.Columns.Count; ++j)
{
cell = row.CreateCell(j);
cell.SetCellValue(ConvertHelper.GetString(tb.Rows[i][j]));
cell.CellStyle = style;
}
}
workbook.Write(ms);
workbook.Clear();
tb.Clear();
var bs = ms.ToArray();
return(bs);
}
}
public void CreateExcelFile()
{
string FileName = @"d:\5.5.5.101_5555_Log_18.txt";
string[] filelist = File.ReadAllLines(FileName, Encoding.Default);
List <string> StringLists = new List <string>();
int gg = 0;
for (int linenum = filelist.Length - 1; linenum >= 0; linenum--)
{
if (filelist[linenum].IndexOf("ANR") > -1)
{
int first = filelist[linenum].IndexOf("ANR in ") + "ANR in ".Length;
int last = filelist[linenum].LastIndexOf(" (");
string str2 = filelist[linenum].Substring(first, last - first);
StringLists.Add(str2);
}
}
////建立Excel 2007檔案
IWorkbook workbook = new NPOI.XSSF.UserModel.XSSFWorkbook();
ISheet sheet = workbook.CreateSheet();
//合併區
sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(0, 0, 1, 2));
sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(1, 1, 1, 2));
sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(2, 2, 1, 2));
sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(3, 3, 1, 2));
sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(4, 4, 1, 2));
sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(5, 5, 1, 2));
sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(6, 6, 1, 2));
sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(7, 7, 1, 2));
sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(9, 9, 0, 6)); //合併Summary行
sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(10, 10, 0, 5)); //合併Error List行
//背景色（藍色）
ICellStyle cellStyle0 = workbook.CreateCellStyle();
cellStyle0.FillPattern = FillPattern.SolidForeground;
cellStyle0.FillForegroundColor = IndexedColors.PaleBlue.Index;
cellStyle0.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;
cellStyle0.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
cellStyle0.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
cellStyle0.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
//背景色（綠色）
ICellStyle cellStyle1 = workbook.CreateCellStyle();
cellStyle1.FillPattern = FillPattern.SolidForeground;
cellStyle1.FillForegroundColor = IndexedColors.Lime.Index;
cellStyle1.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;
cellStyle1.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
cellStyle1.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
cellStyle1.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
//背景色（粉色）
ICellStyle cellStyle2 = workbook.CreateCellStyle();
cellStyle2.FillPattern = FillPattern.SolidForeground;
cellStyle2.FillForegroundColor = IndexedColors.Tan.Index;
cellStyle2.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;
cellStyle2.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
cellStyle2.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
cellStyle2.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
//背景色（灰色）
ICellStyle cellStyle3 = workbook.CreateCellStyle();
cellStyle3.FillPattern = FillPattern.SolidForeground;
cellStyle3.FillForegroundColor = IndexedColors.Grey25Percent.Index;
//背景色（白色）
ICellStyle cellStyle4 = workbook.CreateCellStyle();
cellStyle4.FillPattern = FillPattern.SolidForeground;
cellStyle4.FillForegroundColor = IndexedColors.White.Index;
//Summary儲存格格式
ICellStyle summaryStyle = workbook.CreateCellStyle();
IFont summaryFont = workbook.CreateFont();
summaryFont.FontHeightInPoints = 18;
summaryStyle.SetFont(summaryFont);
summaryStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
summaryStyle.FillPattern = FillPattern.SolidForeground;
summaryStyle.FillForegroundColor = IndexedColors.PaleBlue.Index;
//A列
sheet.CreateRow(0).CreateCell(0).SetCellValue("Project Name");
sheet.CreateRow(1).CreateCell(0).SetCellValue("Model Name");
sheet.CreateRow(2).CreateCell(0).SetCellValue("Start Time");
sheet.CreateRow(3).CreateCell(0).SetCellValue("Renew Time");
sheet.CreateRow(4).CreateCell(0).SetCellValue("SW Build Time");
sheet.CreateRow(5).CreateCell(0).SetCellValue("Project No.");
sheet.CreateRow(6).CreateCell(0).SetCellValue("Test Device");
sheet.CreateRow(7).CreateCell(0).SetCellValue("Tester");
for (int A = 0; A < 8; A++)
{
sheet.GetRow(A).GetCell(0).CellStyle = cellStyle0;
}
//E列
sheet.GetRow(0).CreateCell(4).SetCellValue("Date");
sheet.GetRow(1).CreateCell(4).SetCellValue("Period (H)");
sheet.GetRow(2).CreateCell(4).SetCellValue("SW ISSUES");
sheet.GetRow(3).CreateCell(4).SetCellValue("System Crash");
sheet.GetRow(4).CreateCell(4).SetCellValue("Result");
sheet.GetRow(5).CreateCell(4).SetCellValue("MTBF_SW");
sheet.GetRow(6).CreateCell(4).SetCellValue("MTBF_Crash");
for (int E = 0; E < 7; E++)
{
sheet.GetRow(E).GetCell(4).CellStyle = cellStyle0;
}
sheet.GetRow(4).GetCell(4).CellStyle = cellStyle4;
//F列
sheet.GetRow(0).CreateCell(5).SetCellValue("-----");
sheet.GetRow(1).CreateCell(5).SetCellValue("-----");
sheet.GetRow(2).CreateCell(5).SetCellValue("-----");
sheet.GetRow(3).CreateCell(5).SetCellValue("-----");
sheet.GetRow(4).CreateCell(5).SetCellValue("");
sheet.GetRow(5).CreateCell(5).SetCellValue("-----");
sheet.GetRow(6).CreateCell(5).SetCellValue("-----");
for (int F = 0; F < 7; F++)
{
sheet.GetRow(F).GetCell(5).CellStyle = cellStyle2;
}
sheet.GetRow(4).GetCell(5).CellStyle = cellStyle4;
//Summary
sheet.CreateRow(9).CreateCell(0).SetCellValue("Summary");
sheet.GetRow(9).GetCell(0).CellStyle = summaryStyle;
//Error List
sheet.CreateRow(10).CreateCell(0).SetCellValue("Error List");
sheet.GetRow(10).GetCell(0).CellStyle = cellStyle3;
//Total
sheet.GetRow(10).CreateCell(6).SetCellValue("Total");
sheet.GetRow(10).GetCell(6).CellStyle = cellStyle3;
//搜尋相同字串並記次
Dictionary <string, int> dict = new Dictionary <string, int>();
foreach (string myStringList in StringLists)
{
if (dict.ContainsKey(myStringList))
{
//如果Dictionary中存在这个关键词元素，则把这个Dictionary的key+1
dict[myStringList]++;
}
else
{
//如果Dictionary中不存在这个关键词元素，则把它添加进Dictionary
dict.Add(myStringList, 1);
}
}
int rowcnt = dict.Count;
while (rowcnt != 0)
{
foreach (KeyValuePair <string, int> item in dict)
{
Console.WriteLine(item.Key);
Console.WriteLine(item.Value);
sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(10 + rowcnt, 10 + rowcnt, 0, 5)); //合併Error List行
sheet.CreateRow(10 + rowcnt).CreateCell(0).SetCellValue(item.Key);
sheet.GetRow(10 + rowcnt).CreateCell(6).SetCellValue(item.Value);
rowcnt--;
}
}
for (int c = 0; c <= 25; c++)
{
sheet.AutoSizeColumn(c);
}
FileStream file = new FileStream(@"d:\npoi.xlsx", FileMode.Create);//產生檔案
workbook.Write(file);
file.Close();
}
public void TestXSSFTextParagraph()
{
XSSFWorkbook wb = new XSSFWorkbook();
try
{
XSSFSheet sheet = wb.CreateSheet() as XSSFSheet;
XSSFDrawing Drawing = sheet.CreateDrawingPatriarch() as XSSFDrawing;
XSSFTextBox shape = Drawing.CreateTextbox(new XSSFClientAnchor(0, 0, 0, 0, 2, 2, 3, 4)) as XSSFTextBox;
XSSFTextParagraph para = shape.AddNewTextParagraph();
para.AddNewTextRun().Text = ("Line 1");
List <XSSFTextRun> Runs = para.TextRuns;
Assert.AreEqual(1, Runs.Count);
XSSFTextRun run = Runs[0];
Assert.AreEqual("Line 1", run.Text);
//Assert.IsNotNull(run.ParentParagraph);
//Assert.IsNotNull(run.XmlObject);
Assert.IsNotNull(run.GetRPr());
Assert.AreEqual(Color.FromArgb(0, 0, 0), run.FontColor);
Color color = Color.FromArgb(0, 255, 255);
run.FontColor = (/*setter*/ color);
Assert.AreEqual(color, run.FontColor);
Assert.AreEqual(11.0, run.FontSize, 0.01);
run.FontSize = (/*setter*/ 12.32);
Assert.AreEqual(12.32, run.FontSize, 0.01);
run.FontSize = (/*setter*/ -1.0);
Assert.AreEqual(11.0, run.FontSize, 0.01);
run.FontSize = (/*setter*/ -1.0);
Assert.AreEqual(11.0, run.FontSize, 0.01);
try
{
run.FontSize = (/*setter*/ 0.9);
Assert.Fail("Should fail");
}
catch (ArgumentException e)
{
Assert.IsTrue(e.Message.Contains("0.9"));
}
Assert.AreEqual(11.0, run.FontSize, 0.01);
Assert.AreEqual(0.0, run.CharacterSpacing, 0.01);
run.CharacterSpacing = (/*setter*/ 12.31);
Assert.AreEqual(12.31, run.CharacterSpacing, 0.01);
run.CharacterSpacing = (/*setter*/ 0.0);
Assert.AreEqual(0.0, run.CharacterSpacing, 0.01);
run.CharacterSpacing = (/*setter*/ 0.0);
Assert.AreEqual(0.0, run.CharacterSpacing, 0.01);
Assert.AreEqual("Calibri", run.FontFamily);
run.SetFontFamily("Arial", (byte)1, (byte)1, false);
Assert.AreEqual("Arial", run.FontFamily);
run.SetFontFamily("Arial", unchecked ((byte)-1), (byte)1, false);
Assert.AreEqual("Arial", run.FontFamily);
run.SetFontFamily("Arial", (byte)1, unchecked ((byte)-1), false);
Assert.AreEqual("Arial", run.FontFamily);
run.SetFontFamily("Arial", (byte)1, (byte)1, true);
Assert.AreEqual("Arial", run.FontFamily);
run.SetFontFamily(null, (byte)1, (byte)1, false);
Assert.AreEqual("Calibri", run.FontFamily);
run.SetFontFamily(null, (byte)1, (byte)1, false);
Assert.AreEqual("Calibri", run.FontFamily);
run.SetFont("Arial");
Assert.AreEqual("Arial", run.FontFamily);
Assert.AreEqual((byte)0, run.PitchAndFamily);
run.SetFont(null);
Assert.AreEqual((byte)0, run.PitchAndFamily);
Assert.IsFalse(run.IsStrikethrough);
run.IsStrikethrough = (/*setter*/ true);
Assert.IsTrue(run.IsStrikethrough);
run.IsStrikethrough = (/*setter*/ false);
Assert.IsFalse(run.IsStrikethrough);
Assert.IsFalse(run.IsSuperscript);
run.IsSuperscript = (/*setter*/ true);
Assert.IsTrue(run.IsSuperscript);
run.IsSuperscript = (/*setter*/ false);
Assert.IsFalse(run.IsSuperscript);
Assert.IsFalse(run.IsSubscript);
run.IsSubscript = (/*setter*/ true);
Assert.IsTrue(run.IsSubscript);
run.IsSubscript = (/*setter*/ false);
Assert.IsFalse(run.IsSubscript);
Assert.AreEqual(TextCap.NONE, run.TextCap);
Assert.IsFalse(run.IsBold);
run.IsBold = (/*setter*/ true);
Assert.IsTrue(run.IsBold);
run.IsBold = (/*setter*/ false);
Assert.IsFalse(run.IsBold);
Assert.IsFalse(run.IsItalic);
run.IsItalic = (/*setter*/ true);
Assert.IsTrue(run.IsItalic);
run.IsItalic = (/*setter*/ false);
Assert.IsFalse(run.IsItalic);
Assert.IsFalse(run.IsUnderline);
run.IsUnderline = (/*setter*/ true);
Assert.IsTrue(run.IsUnderline);
run.IsUnderline = (/*setter*/ false);
Assert.IsFalse(run.IsUnderline);
Assert.IsNotNull(run.ToString());
}
finally
{
wb.Close();
}
}
public void TestXSSFTextParagraph()
{
XSSFWorkbook wb = new XSSFWorkbook();
try
{
XSSFSheet sheet = wb.CreateSheet() as XSSFSheet;
XSSFDrawing Drawing = sheet.CreateDrawingPatriarch() as XSSFDrawing;
XSSFTextBox shape = Drawing.CreateTextbox(new XSSFClientAnchor(0, 0, 0, 0, 2, 2, 3, 4)) as XSSFTextBox;
XSSFRichTextString rt = new XSSFRichTextString("Test String");
XSSFFont font = wb.CreateFont() as XSSFFont;
Color color = Color.FromArgb(0, 255, 255);
font.SetColor(new XSSFColor(color));
font.FontName = (/*setter*/ "Arial");
rt.ApplyFont(font);
shape.SetText(/*setter*/ rt);
Assert.IsNotNull(shape.GetCTShape());
Assert.IsNotNull(shape.GetEnumerator());
Assert.IsNotNull(XSSFSimpleShape.GetPrototype());
foreach (ListAutoNumber nr in Enum.GetValues(typeof(ListAutoNumber)))
{
shape.TextParagraphs[(0)].SetBullet(nr);
Assert.IsNotNull(shape.Text);
}
shape.TextParagraphs[(0)].SetBullet(false);
Assert.IsNotNull(shape.Text);
shape.SetText("testtext");
Assert.AreEqual("testtext", shape.Text);
shape.SetText(new XSSFRichTextString());
Assert.AreEqual("null", shape.Text);
shape.AddNewTextParagraph();
shape.AddNewTextParagraph("test-other-text");
shape.AddNewTextParagraph(new XSSFRichTextString("rtstring"));
shape.AddNewTextParagraph(new XSSFRichTextString());
Assert.AreEqual("null\n\ntest-other-text\nrtstring\nnull", shape.Text);
Assert.AreEqual(TextHorizontalOverflow.OVERFLOW, shape.TextHorizontalOverflow);
shape.TextHorizontalOverflow = (/*setter*/ TextHorizontalOverflow.CLIP);
Assert.AreEqual(TextHorizontalOverflow.CLIP, shape.TextHorizontalOverflow);
shape.TextHorizontalOverflow = (/*setter*/ TextHorizontalOverflow.OVERFLOW);
Assert.AreEqual(TextHorizontalOverflow.OVERFLOW, shape.TextHorizontalOverflow);
shape.TextHorizontalOverflow = TextHorizontalOverflow.None;
Assert.AreEqual(TextHorizontalOverflow.OVERFLOW, shape.TextHorizontalOverflow);
shape.TextHorizontalOverflow = TextHorizontalOverflow.None;
Assert.AreEqual(TextHorizontalOverflow.OVERFLOW, shape.TextHorizontalOverflow);
Assert.AreEqual(TextVerticalOverflow.OVERFLOW, shape.TextVerticalOverflow);
shape.TextVerticalOverflow = (/*setter*/ TextVerticalOverflow.CLIP);
Assert.AreEqual(TextVerticalOverflow.CLIP, shape.TextVerticalOverflow);
shape.TextVerticalOverflow = (/*setter*/ TextVerticalOverflow.OVERFLOW);
Assert.AreEqual(TextVerticalOverflow.OVERFLOW, shape.TextVerticalOverflow);
shape.TextVerticalOverflow = TextVerticalOverflow.None;
Assert.AreEqual(TextVerticalOverflow.OVERFLOW, shape.TextVerticalOverflow);
shape.TextVerticalOverflow = TextVerticalOverflow.None;
Assert.AreEqual(TextVerticalOverflow.OVERFLOW, shape.TextVerticalOverflow);
Assert.AreEqual((short)VerticalAlignment.Top, shape.VerticalAlignment);
shape.VerticalAlignment = (short)VerticalAlignment.Bottom;
Assert.AreEqual(VerticalAlignment.Bottom, shape.VerticalAlignment);
shape.VerticalAlignment = (short)VerticalAlignment.Top;
Assert.AreEqual(VerticalAlignment.Top, shape.VerticalAlignment);
shape.VerticalAlignment = (short)VerticalAlignment.None;
Assert.AreEqual(VerticalAlignment.Top, shape.VerticalAlignment);
shape.VerticalAlignment = (short)VerticalAlignment.None;
Assert.AreEqual(VerticalAlignment.Top, shape.VerticalAlignment);
Assert.AreEqual(TextDirection.HORIZONTAL, shape.TextDirection);
shape.TextDirection = (/*setter*/ TextDirection.STACKED);
Assert.AreEqual(TextDirection.STACKED, shape.TextDirection);
shape.TextDirection = (/*setter*/ TextDirection.HORIZONTAL);
Assert.AreEqual(TextDirection.HORIZONTAL, shape.TextDirection);
shape.TextDirection = (/*setter*/ TextDirection.None);
Assert.AreEqual(TextDirection.HORIZONTAL, shape.TextDirection);
shape.TextDirection = (/*setter*/ TextDirection.None);
Assert.AreEqual(TextDirection.HORIZONTAL, shape.TextDirection);
Assert.AreEqual(3.6, shape.BottomInset, 0.01);
shape.BottomInset = (/*setter*/ 12.32);
Assert.AreEqual(12.32, shape.BottomInset, 0.01);
shape.BottomInset = (/*setter*/ -1);
Assert.AreEqual(3.6, shape.BottomInset, 0.01);
shape.BottomInset = (/*setter*/ -1);
Assert.AreEqual(3.6, shape.BottomInset, 0.01);
Assert.AreEqual(3.6, shape.LeftInset, 0.01);
shape.LeftInset = (/*setter*/ 12.31);
Assert.AreEqual(12.31, shape.LeftInset, 0.01);
shape.LeftInset = (/*setter*/ -1);
Assert.AreEqual(3.6, shape.LeftInset, 0.01);
shape.LeftInset = (/*setter*/ -1);
Assert.AreEqual(3.6, shape.LeftInset, 0.01);
Assert.AreEqual(3.6, shape.RightInset, 0.01);
shape.RightInset = (/*setter*/ 13.31);
Assert.AreEqual(13.31, shape.RightInset, 0.01);
shape.RightInset = (/*setter*/ -1);
Assert.AreEqual(3.6, shape.RightInset, 0.01);
shape.RightInset = (/*setter*/ -1);
Assert.AreEqual(3.6, shape.RightInset, 0.01);
Assert.AreEqual(3.6, shape.TopInset, 0.01);
shape.TopInset = (/*setter*/ 23.31);
Assert.AreEqual(23.31, shape.TopInset, 0.01);
shape.TopInset = (/*setter*/ -1);
Assert.AreEqual(3.6, shape.TopInset, 0.01);
shape.TopInset = (/*setter*/ -1);
Assert.AreEqual(3.6, shape.TopInset, 0.01);
Assert.IsTrue(shape.WordWrap);
shape.WordWrap = (/*setter*/ false);
Assert.IsFalse(shape.WordWrap);
shape.WordWrap = (/*setter*/ true);
Assert.IsTrue(shape.WordWrap);
Assert.AreEqual(TextAutofit.NORMAL, shape.TextAutofit);
shape.TextAutofit = (/*setter*/ TextAutofit.NORMAL);
Assert.AreEqual(TextAutofit.NORMAL, shape.TextAutofit);
shape.TextAutofit = (/*setter*/ TextAutofit.SHAPE);
Assert.AreEqual(TextAutofit.SHAPE, shape.TextAutofit);
shape.TextAutofit = (/*setter*/ TextAutofit.NONE);
Assert.AreEqual(TextAutofit.NONE, shape.TextAutofit);
Assert.AreEqual(5, shape.ShapeType);
shape.ShapeType = (/*setter*/ 23);
Assert.AreEqual(23, shape.ShapeType);
// TODO: should this be supported?
// shape.ShapeType=(/*setter*/-1);
// Assert.AreEqual(-1, shape.ShapeType);
// shape.ShapeType=(/*setter*/-1);
// Assert.AreEqual(-1, shape.ShapeType);
Assert.IsNotNull(shape.GetShapeProperties());
}
finally
{
wb.Close();
}
}
/// <summary>
/// 输出数据表格
/// </summary>
/// <param name="workbook">工作文档</param>
/// <param name="normalStyle">普通样式(用于表格内容)</param>
/// <param name="boldStyle">粗体样式(用于表格头部)</param>
/// <param name="table">表格数据</param>
public static void writeSheet(NPOI.XSSF.UserModel.XSSFWorkbook workbook, NPOI.SS.UserModel.ICellStyle normalStyle, NPOI.SS.UserModel.ICellStyle boldStyle, DataTable table)
{
//创建Sheet页
NPOI.SS.UserModel.ISheet sheet = workbook.CreateSheet(table.TableName);
//行号
int rowIndex = 0;
//是否需要输出表头
bool isNeedCreateHeader = true;
//输出数据到Excel
foreach (DataRow rowData in table.Rows)
{
//忽略空数据行
if (rowData.ItemArray == null || rowData.ItemArray.Length != table.Columns.Count)
{
continue;
}
//列号
int colIndex = 0;
//Excel行
NPOI.SS.UserModel.IRow row = null;
//是否需要输入表头
if (isNeedCreateHeader)
{
isNeedCreateHeader = false;
//创建行
row = sheet.CreateRow(rowIndex);
//输出列名到Excel
colIndex = 0;
foreach (DataColumn kvp in table.Columns)
{
//列名
//创建列
NPOI.SS.UserModel.ICell cell = row.CreateCell(colIndex);
//设置样式
cell.CellStyle = boldStyle;
//设置数据
cell.SetCellValue(kvp.ColumnName);
colIndex++;
}
rowIndex++;
}
//创建行
row = sheet.CreateRow(rowIndex);
//输出列值到Excel
colIndex = 0;
foreach (object val in rowData.ItemArray)
{
//列值
//创建列
NPOI.SS.UserModel.ICell cell = row.CreateCell(colIndex);
//设置样式
cell.CellStyle = normalStyle;
//设置数据
//判断是否为空
if (val != null)
{
//不为空
//判断是否为RTF内容
if (val.GetType().Name.Equals(typeof(NPOI.XSSF.UserModel.XSSFRichTextString).Name))
{
//RTF内容
cell.SetCellValue((NPOI.XSSF.UserModel.XSSFRichTextString)val);
}
else
{
//文本内容
cell.SetCellValue(val.ToString());
}
}
else
{
//为空
cell.SetCellValue(string.Empty);
}
colIndex++;
}
rowIndex++;
}
//Excel列宽自动适应
if (table.Rows.Count >= 1 && sheet.GetRow(0) != null)
{
for (int k = 0; k < sheet.GetRow(0).Cells.Count; k++)
{
sheet.AutoSizeColumn(k);
}
}
}
public IHttpActionResult ExportGroupedSalesToExcel(FilterSales filter)
{
var response = new DataResponse <string>();
try
{
#region Sales Default Export
NPOI.SS.UserModel.IWorkbook workbook = new NPOI.XSSF.UserModel.XSSFWorkbook();
NPOI.SS.UserModel.ISheet sheet = workbook.CreateSheet("SalesReport");
NPOI.SS.UserModel.ICreationHelper cH = workbook.GetCreationHelper();
string[] headers = new string[] { filter.GroupBy == 1 ? "Practice Name" : filter.GroupBy == 2 ? "Rep Name" : "Sales Team", "Sales", "Last Activity On" };
var headerRow = sheet.CreateRow(0);
//create header
for (int i = 0; i < headers.Length; i++)
{
sheet.DefaultColumnWidth = 20;
XSSFCellStyle style = (XSSFCellStyle)workbook.CreateCellStyle();
XSSFColor colorToFill = new XSSFColor(Color.Aqua);
style.FillBackgroundColor = (short)ColorTranslator.ToWin32(Color.Aqua);
headerRow.RowStyle = style;
var cell = headerRow.CreateCell(i);
cell.SetCellValue(headers[i]);
}
string[] allowedRoles = { "RDSLS" };
string[] superRoles = { "RDSLSALL" };
bool hasSuperRight = HasRight(superRoles);
bool displayPatientName = HasRight(new string[] { "VWSLSPTNT" });
if (HasRight(allowedRoles) || hasSuperRight)
{
var repository = new RepositorySales();
var dataResponse = repository.GetAllGroupedSales(filter, CurrentUser.BusinessId, CurrentUserId, hasSuperRight, CurrentUserRoles, CurrentUserDepartments, CurrentUserPrivileges, IsRep || IsSalesManager, IsSalesDirector, displayPatientName, 10, 0, false, true, mapperFilePath: this.MapperFilePath);
List <GroupedSales> salesList = dataResponse.Model.List.ToList();
int recordCount = dataResponse.Model.Pager.TotalCount;
//fill content
var rowIndex = 0;
for (int i = 0; i < recordCount; i++)
{
rowIndex++;
var row = sheet.CreateRow(rowIndex);
var objSalesItem = salesList[i];
if (objSalesItem == null)
{
continue;
}
var font = workbook.CreateFont();
font.FontHeightInPoints = 11;
font.FontName = "Calibri";
font.Boldweight = (short)NPOI.SS.UserModel.FontBoldWeight.Bold;
var cell1 = row.CreateCell(0);
cell1.CellStyle = workbook.CreateCellStyle();
cell1.CellStyle.SetFont(font);
cell1.SetCellValue(string.IsNullOrEmpty(objSalesItem.KeyName) ? "Missing Information" : objSalesItem.KeyName);
var cell2 = row.CreateCell(1);
cell2.CellStyle = workbook.CreateCellStyle();
cell2.CellStyle.SetFont(font);
cell2.SetCellValue(objSalesItem.Count);
var cell3 = row.CreateCell(2);
cell3.CellStyle = workbook.CreateCellStyle();
cell3.CellStyle.SetFont(font);
cell3.SetCellValue(Convert.ToString(objSalesItem.LastActivityOn));
}
string directory = Path.Combine("Assets", CurrentBusinessId.Value.ToString(), "Sales", "Sales-Archives", "Exports");
string fileUri = HttpContext.Current.Server.MapPath(Path.Combine("~/", directory));
if (!Directory.Exists(fileUri))
{
Directory.CreateDirectory(fileUri);
}
string fileName = string.Format("{0:yyyyMMddhhmmssfff}", DateTime.Now),
extension = "xlsx";
string filePath = Path.Combine(fileUri, string.Format("{0}.{1}", fileName, extension));
int count = 1;
isExist:
if (File.Exists(filePath))
{
fileName = string.Format("{0}{1}{2}", fileName, count, extension);
filePath = Path.Combine(fileUri, fileName);
count++;
goto isExist;
}
using (FileStream stream = new FileStream(filePath, FileMode.Create, FileAccess.Write))
{
workbook.Write(stream);
}
response.Model = Path.Combine(directory, string.Format("{0}.{1}", fileName, extension));
}
return(Ok <DataResponse>(response));
#endregion
}
catch (Exception ex)
{
ex.Log();
}
response.Message = "Internal Server Error";
return(Ok <DataResponse>(response));
}
public static System.IO.MemoryStream createExcelReport(DataTable dt)
{
using (System.IO.MemoryStream mem = new System.IO.MemoryStream())
{
var workbook = new NPOI.XSSF.UserModel.XSSFWorkbook();
#region Cell Styles
#region HeaderLabel Cell Style
var headerLabelCellStyle = workbook.CreateCellStyle();
headerLabelCellStyle.Alignment = HorizontalAlignment.Center;
headerLabelCellStyle.BorderBottom = BorderStyle.Thin;
headerLabelCellStyle.BorderRight = BorderStyle.Thin;
headerLabelCellStyle.BorderTop = BorderStyle.Thin;
headerLabelCellStyle.BorderLeft = BorderStyle.Thin;
var headerLabelFont = workbook.CreateFont();
headerLabelFont.Boldweight = (short)FontBoldWeight.Bold;
headerLabelCellStyle.SetFont(headerLabelFont);
#endregion
#region RightAligned Cell Style
var rightAlignedCellStyle = workbook.CreateCellStyle();
rightAlignedCellStyle.Alignment = HorizontalAlignment.Right;
rightAlignedCellStyle.BorderBottom = BorderStyle.Thin;
rightAlignedCellStyle.BorderRight = BorderStyle.Thin;
rightAlignedCellStyle.BorderTop = BorderStyle.Thin;
rightAlignedCellStyle.BorderLeft = BorderStyle.Thin;
#endregion
#region Currency Cell Style
var CellStyleRight = workbook.CreateCellStyle();
CellStyleRight.Alignment = HorizontalAlignment.Right;
CellStyleRight.BorderBottom = BorderStyle.Thin;
CellStyleRight.BorderRight = BorderStyle.Thin;
CellStyleRight.BorderTop = BorderStyle.Thin;
CellStyleRight.BorderLeft = BorderStyle.Thin;
#endregion
#region Detail Currency Subtotal Style
var CellStyleNormal = workbook.CreateCellStyle();
CellStyleNormal.BorderBottom = BorderStyle.Thin;
CellStyleNormal.BorderRight = BorderStyle.Thin;
CellStyleNormal.BorderTop = BorderStyle.Thin;
CellStyleNormal.BorderLeft = BorderStyle.Thin;
var detailCurrencySubtotalFont = workbook.CreateFont();
detailCurrencySubtotalFont.Boldweight = (short)FontBoldWeight.Normal;
CellStyleNormal.SetFont(detailCurrencySubtotalFont);
#endregion
#endregion
var sheet = workbook.CreateSheet(dt.TableName);
var rowIndex = 0;
var row = sheet.CreateRow(rowIndex);
List <String> columns = new List <string>();
for (int i = 0; i < dt.Columns.Count; i++)
{
columns.Add(dt.Columns[i].ColumnName);
var cell = row.CreateCell(i);
cell.SetCellValue(dt.Columns[i].ColumnName);
cell.CellStyle = headerLabelCellStyle;
}
rowIndex++;
//// Add data rows
foreach (System.Data.DataRow item in dt.Rows)
{
row = sheet.CreateRow(rowIndex);
for (int ii = 0; ii < dt.Columns.Count; ii++)
{
var cell = row.CreateCell(ii);
cell.SetCellValue(dt.Columns[ii].ColumnName);
if (dt.Columns[ii].DataType == typeof(System.Decimal))
{
cell.CellStyle = CellStyleRight;
}
else
{
cell.CellStyle = CellStyleNormal;
}
cell.SetCellValue(item[dt.Columns[ii].ColumnName].ToString());
}
rowIndex++;
}
for (var i = 0; i < sheet.GetRow(0).LastCellNum; i++)
{
sheet.AutoSizeColumn(i);
}
workbook.Write(mem);
return(mem);
}
}
public IHttpActionResult ExportAllToExcel(FilterSales filter)
{
var response = new DataResponse <string>();
try
{
if (filter.GroupBy <= 0)
{
#region Sales Default Export
NPOI.SS.UserModel.IWorkbook workbook = new NPOI.XSSF.UserModel.XSSFWorkbook();
NPOI.SS.UserModel.ISheet sheet = workbook.CreateSheet("SalesReport");
NPOI.SS.UserModel.ICreationHelper cH = workbook.GetCreationHelper();
string[] columnname = filter.DynamicFilters.Where(a => a.IsVisible == true).Select(a => a.ColumnName).ToArray();
string[] headers, columns = null;
headers = columns = columnname;
columns = columns.Select(x => x.Replace("SalesTeam", "RepGroup")).ToArray();
columns = columns.Select(x => x.Replace("CollectedDate", "CollectionDate")).ToArray();
//byte[] rgb = new byte[3] { 22, 183, 223 };
//XSSFCellStyle HeaderCellStyle = (XSSFCellStyle)workbook.CreateCellStyle();
//HeaderCellStyle.SetFillBackgroundColor(new XSSFColor(rgb));
var headerRow = sheet.CreateRow(0);
//headerRow.RowStyle.FillBackgroundColor= (short)ColorTranslator.ToWin32(Color.Aqua);
//create header
for (int i = 0; i < headers.Length; i++)
{
sheet.DefaultColumnWidth = 20;
XSSFCellStyle style = (XSSFCellStyle)workbook.CreateCellStyle();
XSSFColor colorToFill = new XSSFColor(Color.Aqua);
style.FillBackgroundColor = (short)ColorTranslator.ToWin32(Color.Aqua);
headerRow.RowStyle = style;
var cell = headerRow.CreateCell(i);
cell.SetCellValue(headers[i]);
}
string[] allowedRoles = { "RDSLS" };
string[] superRoles = { "RDSLSALL" };
bool hasSuperRight = HasRight(superRoles);
bool displayPatientName = HasRight(new string[] { "VWSLSPTNT" });
if (HasRight(allowedRoles) || hasSuperRight)
{
var repository = new RepositorySales();
var dataResponse = repository.GetAllList(filter, CurrentUser.BusinessId, CurrentUserId, hasSuperRight, CurrentUserRoles, CurrentUserDepartments, CurrentUserPrivileges, IsRep || IsSalesManager, IsSalesDirector, displayPatientName, 10, 0, false, true, mapperFilePath: this.MapperFilePath);
List <EntitySales> salesList = dataResponse.Model.List.ToList();
int recordCount = dataResponse.Model.Pager.TotalCount;
//fill content
var rowIndex = 0;
for (int i = 0; i < recordCount; i++)
{
rowIndex++;
var row = sheet.CreateRow(rowIndex);
for (int j = 0; j < columns.Length; j++)
{
var font = workbook.CreateFont();
font.FontHeightInPoints = 11;
font.FontName = "Calibri";
font.Boldweight = (short)NPOI.SS.UserModel.FontBoldWeight.Bold;
var cell = row.CreateCell(j);
cell.CellStyle = workbook.CreateCellStyle();
cell.CellStyle.SetFont(font);
string value = null;
var objSalesItem = salesList[i];
var objStaticItem = objSalesItem.GetType().GetTypeInfo().GetProperty(columns[j]);
if (objStaticItem != null)
{
var property = salesList[i].GetType().GetRuntimeProperties().FirstOrDefault(p => string.Equals(p.Name, objStaticItem.Name, StringComparison.OrdinalIgnoreCase));
if (property != null)
{
value = Convert.ToString(property.GetValue(salesList[i], null));
}
}
else
{
var objDynamicItem = objSalesItem.ReportColumnValues.FirstOrDefault(a => a.ColumnName == columns[j]);
if (objDynamicItem != null)
{
value = objDynamicItem.Value;
}
}
cell.SetCellValue(value);
}
bool isFinanceDataCount = salesList[i].FinanceDataRecordCount > 1;
if (isFinanceDataCount)
{
for (int k = 0; k < salesList[i].FinanceDataRecordCount; k++)
{
rowIndex++;
var financeRow = sheet.CreateRow(rowIndex);
for (int j = 0; j < columns.Length; j++)
{
var cell = financeRow.CreateCell(j);
string value = null;
var objSalesItem = salesList[i].FinanceDataList.ToList()[k];
var objStaticItem = objSalesItem.GetType().GetProperty(columns[j]);
if (objStaticItem != null)
{
value = Convert.ToString(objStaticItem.GetValue(objSalesItem, null));
}
else
{
var objDynamicItem = objSalesItem.FinanceColumnValues.FirstOrDefault(a => a.ColumnName == columns[j]);
if (objDynamicItem != null)
{
value = objDynamicItem.Value;
}
}
cell.SetCellValue(value);
}
}
}
}
string directory = Path.Combine("Assets", CurrentBusinessId.Value.ToString(), "Sales", "Sales-Archives", "Exports");
string fileUri = HttpContext.Current.Server.MapPath(Path.Combine("~/", directory));
if (!Directory.Exists(fileUri))
{
Directory.CreateDirectory(fileUri);
}
string fileName = string.Format("{0:yyyyMMddhhmmssfff}", DateTime.Now),
extension = "xlsx";
string filePath = Path.Combine(fileUri, string.Format("{0}.{1}", fileName, extension));
int count = 1;
isExist:
if (File.Exists(filePath))
{
fileName = string.Format("{0}{1}{2}", fileName, count, extension);
filePath = Path.Combine(fileUri, fileName);
count++;
goto isExist;
}
using (FileStream stream = new FileStream(filePath, FileMode.Create, FileAccess.Write))
{
workbook.Write(stream);
}
response.Model = Path.Combine(directory, string.Format("{0}.{1}", fileName, extension));
}
return(Ok <DataResponse>(response));
#endregion
}
#region Sales Grouped Report
return(ExportGroupedSalesToExcel(filter));
#endregion
}
catch (Exception ex)
{
ex.Log();
}
response.Message = "Internal Server Error";
return(Ok <DataResponse>(response));
}
public void TestNew()
{
XSSFWorkbook wb = new XSSFWorkbook();
XSSFSheet sheet = (XSSFSheet)wb.CreateSheet();
//multiple calls of CreateDrawingPatriarch should return the same instance of XSSFDrawing
XSSFDrawing dr1 = (XSSFDrawing)sheet.CreateDrawingPatriarch();
XSSFDrawing dr2 = (XSSFDrawing)sheet.CreateDrawingPatriarch();
Assert.AreSame(dr1, dr2);
List <POIXMLDocumentPart> rels = sheet.GetRelations();
Assert.AreEqual(1, rels.Count);
Assert.IsTrue(rels[0] is XSSFDrawing);
XSSFDrawing drawing = (XSSFDrawing)rels[0];
String drawingId = drawing.GetPackageRelationship().Id;
//there should be a relation to this Drawing in the worksheet
Assert.IsTrue(sheet.GetCTWorksheet().IsSetDrawing());
Assert.AreEqual(drawingId, sheet.GetCTWorksheet().drawing.id);
//XSSFClientAnchor anchor = new XSSFClientAnchor();
XSSFConnector c1 = drawing.CreateConnector(new XSSFClientAnchor(0, 0, 0, 0, 0, 0, 2, 2));
c1.LineWidth = 2.5;
c1.LineStyle = SS.UserModel.LineStyle.DashDotSys;
XSSFShapeGroup c2 = drawing.CreateGroup(new XSSFClientAnchor(0, 0, 0, 0, 0, 0, 5, 5));
Assert.IsNotNull(c2);
XSSFSimpleShape c3 = drawing.CreateSimpleShape(new XSSFClientAnchor(0, 0, 0, 0, 2, 2, 3, 4));
c3.SetText(new XSSFRichTextString("Test String"));
c3.SetFillColor(128, 128, 128);
XSSFTextBox c4 = (XSSFTextBox)drawing.CreateTextbox(new XSSFClientAnchor(0, 0, 0, 0, 4, 4, 5, 6));
XSSFRichTextString rt = new XSSFRichTextString("Test String");
rt.ApplyFont(0, 5, wb.CreateFont());
rt.ApplyFont(5, 6, wb.CreateFont());
c4.SetText(rt);
c4.IsNoFill = (true);
Assert.AreEqual(4, drawing.GetCTDrawing().SizeOfTwoCellAnchorArray());
List <XSSFShape> shapes = drawing.GetShapes();
Assert.AreEqual(4, shapes.Count);
Assert.IsTrue(shapes[(0)] is XSSFConnector);
Assert.IsTrue(shapes[(1)] is XSSFShapeGroup);
Assert.IsTrue(shapes[(2)] is XSSFSimpleShape);
Assert.IsTrue(shapes[(3)] is XSSFSimpleShape);
// Save and re-load it
wb = XSSFTestDataSamples.WriteOutAndReadBack(wb) as XSSFWorkbook;
sheet = wb.GetSheetAt(0) as XSSFSheet;
// Check
dr1 = sheet.CreateDrawingPatriarch() as XSSFDrawing;
CT_Drawing ctDrawing = dr1.GetCTDrawing();
// Connector, shapes and text boxes are all two cell anchors
Assert.AreEqual(0, ctDrawing.SizeOfAbsoluteAnchorArray());
Assert.AreEqual(0, ctDrawing.SizeOfOneCellAnchorArray());
Assert.AreEqual(4, ctDrawing.SizeOfTwoCellAnchorArray());
shapes = dr1.GetShapes();
Assert.AreEqual(4, shapes.Count);
Assert.IsTrue(shapes[0] is XSSFConnector);
Assert.IsTrue(shapes[1] is XSSFShapeGroup);
Assert.IsTrue(shapes[2] is XSSFSimpleShape);
Assert.IsTrue(shapes[3] is XSSFSimpleShape); //
// Ensure it got the right namespaces
//String xml = ctDrawing.ToString();
//Assert.IsTrue(xml.Contains("xmlns:xdr=\"http://schemas.openxmlformats.org/drawingml/2006/spreadsheetDrawing\""));
//Assert.IsTrue(xml.Contains("xmlns:a=\"http://schemas.openxmlformats.org/drawingml/2006/main\""));
Assert.IsNotNull(XSSFTestDataSamples.WriteOutAndReadBack(wb));
}
public void TestBug55752()
{
IWorkbook wb = new XSSFWorkbook();
try
{
ISheet sheet = wb.CreateSheet("test");
for (int i = 0; i < 4; i++)
{
IRow row = sheet.CreateRow(i);
for (int j = 0; j < 2; j++)
{
ICell cell = row.CreateCell(j);
cell.CellStyle = (wb.CreateCellStyle());
}
}
// set content
IRow row1 = sheet.GetRow(0);
row1.GetCell(0).SetCellValue("AAA");
IRow row2 = sheet.GetRow(1);
row2.GetCell(0).SetCellValue("BBB");
IRow row3 = sheet.GetRow(2);
row3.GetCell(0).SetCellValue("CCC");
IRow row4 = sheet.GetRow(3);
row4.GetCell(0).SetCellValue("DDD");
// merge cells
CellRangeAddress range1 = new CellRangeAddress(0, 0, 0, 1);
sheet.AddMergedRegion(range1);
CellRangeAddress range2 = new CellRangeAddress(1, 1, 0, 1);
sheet.AddMergedRegion(range2);
CellRangeAddress range3 = new CellRangeAddress(2, 2, 0, 1);
sheet.AddMergedRegion(range3);
Assert.AreEqual(0, range3.FirstColumn);
Assert.AreEqual(1, range3.LastColumn);
Assert.AreEqual(2, range3.LastRow);
CellRangeAddress range4 = new CellRangeAddress(3, 3, 0, 1);
sheet.AddMergedRegion(range4);
// set border
RegionUtil.SetBorderBottom((int)BorderStyle.Thin, range1, sheet, wb);
row2.GetCell(0).CellStyle.BorderBottom = BorderStyle.Thin;
row2.GetCell(1).CellStyle.BorderBottom = BorderStyle.Thin;
ICell cell0 = CellUtil.GetCell(row3, 0);
CellUtil.SetCellStyleProperty(cell0, CellUtil.BORDER_BOTTOM, BorderStyle.Thin);
ICell cell1 = CellUtil.GetCell(row3, 1);
CellUtil.SetCellStyleProperty(cell1, CellUtil.BORDER_BOTTOM, BorderStyle.Thin);
RegionUtil.SetBorderBottom((int)BorderStyle.Thin, range4, sheet, wb);
// write to file
Stream stream = new FileStream("55752.xlsx", FileMode.Create, FileAccess.ReadWrite);
try
{
wb.Write(stream);
}
finally
{
stream.Close();
}
}
finally
{
wb.Close();
}
}
public void TestCreate()
{
XSSFWorkbook workbook = new XSSFWorkbook();
XSSFSheet sheet = workbook.CreateSheet() as XSSFSheet;
XSSFRow row = sheet.CreateRow(0) as XSSFRow;
XSSFCreationHelper CreateHelper = workbook.GetCreationHelper() as XSSFCreationHelper;
String[] urls =
{
"http://apache.org/",
"www.apache.org",
"/temp",
"file:///c:/temp",
"http://apache.org/default.php?s=isTramsformed&submit=Search&la=*&li=*"
};
for (int i = 0; i < urls.Length; i++)
{
String s = urls[i];
XSSFHyperlink link = CreateHelper.CreateHyperlink(HyperlinkType.Url) as XSSFHyperlink;
link.Address = (s);
XSSFCell cell = row.CreateCell(i) as XSSFCell;
cell.Hyperlink = (link);
}
workbook = XSSFTestDataSamples.WriteOutAndReadBack(workbook) as XSSFWorkbook;
sheet = workbook.GetSheetAt(0) as XSSFSheet;
PackageRelationshipCollection rels = sheet.GetPackagePart().Relationships;
Assert.AreEqual(urls.Length, rels.Size);
for (int i = 0; i < rels.Size; i++)
{
PackageRelationship rel = rels.GetRelationship(i);
if (rel.TargetUri.IsAbsoluteUri && rel.TargetUri.IsFile)
{
Assert.AreEqual(urls[i], rel.TargetUri.LocalPath);
}
else
{
// there should be a relationship for each URL
Assert.AreEqual(urls[i], rel.TargetUri.ToString());
}
}
// Bugzilla 53041: Hyperlink relations are duplicated when saving XSSF file
workbook = XSSFTestDataSamples.WriteOutAndReadBack(workbook) as XSSFWorkbook;
sheet = workbook.GetSheetAt(0) as XSSFSheet;
rels = sheet.GetPackagePart().Relationships;
Assert.AreEqual(urls.Length, rels.Size);
for (int i = 0; i < rels.Size; i++)
{
PackageRelationship rel = rels.GetRelationship(i);
if (rel.TargetUri.IsAbsoluteUri && rel.TargetUri.IsFile)
{
Assert.AreEqual(urls[i], rel.TargetUri.LocalPath);
}
else
{
// there should be a relationship for each URL
Assert.AreEqual(urls[i], rel.TargetUri.ToString());
}
}
}
public override void SetUp()
{
wb = new XSSFWorkbook();
XSSFSheet sheet = wb.CreateSheet() as XSSFSheet;
IRow row1 = sheet.CreateRow(0);
// Create a cell and Put a value in it.
ICell cell = row1.CreateCell(0);
cell.SetCellValue("Names");
ICell cell2 = row1.CreateCell(1);
cell2.SetCellValue("#");
ICell cell7 = row1.CreateCell(2);
cell7.SetCellValue("Data");
ICell cell10 = row1.CreateCell(3);
cell10.SetCellValue("Value");
IRow row2 = sheet.CreateRow(1);
ICell cell3 = row2.CreateCell(0);
cell3.SetCellValue("Jan");
ICell cell4 = row2.CreateCell(1);
cell4.SetCellValue(10);
ICell cell8 = row2.CreateCell(2);
cell8.SetCellValue("Apa");
ICell cell11 = row1.CreateCell(3);
cell11.SetCellValue(11.11);
IRow row3 = sheet.CreateRow(2);
ICell cell5 = row3.CreateCell(0);
cell5.SetCellValue("Ben");
ICell cell6 = row3.CreateCell(1);
cell6.SetCellValue(9);
ICell cell9 = row3.CreateCell(2);
cell9.SetCellValue("Bepa");
ICell cell12 = row1.CreateCell(3);
cell12.SetCellValue(12.12);
XSSFName namedRange = sheet.Workbook.CreateName() as XSSFName;
namedRange.RefersToFormula = (/*setter*/ sheet.SheetName + "!" + "A1:C2");
pivotTable = sheet.CreatePivotTable(namedRange, new CellReference("H5"));
XSSFSheet offsetSheet = wb.CreateSheet() as XSSFSheet;
IRow tableRow_1 = offsetSheet.CreateRow(1);
offsetOuterCell = tableRow_1.CreateCell(1);
offsetOuterCell.SetCellValue(-1);
ICell tableCell_1_1 = tableRow_1.CreateCell(2);
tableCell_1_1.SetCellValue("Row #");
ICell tableCell_1_2 = tableRow_1.CreateCell(3);
tableCell_1_2.SetCellValue("Exponent");
ICell tableCell_1_3 = tableRow_1.CreateCell(4);
tableCell_1_3.SetCellValue("10^Exponent");
IRow tableRow_2 = offsetSheet.CreateRow(2);
ICell tableCell_2_1 = tableRow_2.CreateCell(2);
tableCell_2_1.SetCellValue(0);
ICell tableCell_2_2 = tableRow_2.CreateCell(3);
tableCell_2_2.SetCellValue(0);
ICell tableCell_2_3 = tableRow_2.CreateCell(4);
tableCell_2_3.SetCellValue(1);
IRow tableRow_3 = offsetSheet.CreateRow(3);
ICell tableCell_3_1 = tableRow_3.CreateCell(2);
tableCell_3_1.SetCellValue(1);
ICell tableCell_3_2 = tableRow_3.CreateCell(3);
tableCell_3_2.SetCellValue(1);
ICell tableCell_3_3 = tableRow_3.CreateCell(4);
tableCell_3_3.SetCellValue(10);
IRow tableRow_4 = offsetSheet.CreateRow(4);
ICell tableCell_4_1 = tableRow_4.CreateCell(2);
tableCell_4_1.SetCellValue(2);
ICell tableCell_4_2 = tableRow_4.CreateCell(3);
tableCell_4_2.SetCellValue(2);
ICell tableCell_4_3 = tableRow_4.CreateCell(4);
tableCell_4_3.SetCellValue(100);
namedRange = sheet.Workbook.CreateName() as XSSFName;
namedRange.RefersToFormula = (/*setter*/ "C2:E4");
namedRange.SheetIndex = (/*setter*/ sheet.Workbook.GetSheetIndex(sheet));
offsetPivotTable = offsetSheet.CreatePivotTable(namedRange, new CellReference("C6"));
}
public void SaveLoadNew()
{
XSSFWorkbook wb1 = new XSSFWorkbook();
//check that the default date system is Set to 1900
CT_WorkbookPr pr = wb1.GetCTWorkbook().workbookPr;
Assert.IsNotNull(pr);
Assert.IsTrue(pr.IsSetDate1904());
Assert.IsFalse(pr.date1904, "XSSF must use the 1900 date system");
ISheet sheet1 = wb1.CreateSheet("sheet1");
ISheet sheet2 = wb1.CreateSheet("sheet2");
wb1.CreateSheet("sheet3");
IRichTextString rts = wb1.GetCreationHelper().CreateRichTextString("hello world");
sheet1.CreateRow(0).CreateCell((short)0).SetCellValue(1.2);
sheet1.CreateRow(1).CreateCell((short)0).SetCellValue(rts);
sheet2.CreateRow(0);
Assert.AreEqual(0, wb1.GetSheetAt(0).FirstRowNum);
Assert.AreEqual(1, wb1.GetSheetAt(0).LastRowNum);
Assert.AreEqual(0, wb1.GetSheetAt(1).FirstRowNum);
Assert.AreEqual(0, wb1.GetSheetAt(1).LastRowNum);
Assert.AreEqual(0, wb1.GetSheetAt(2).FirstRowNum);
Assert.AreEqual(0, wb1.GetSheetAt(2).LastRowNum);
FileInfo file = TempFile.CreateTempFile("poi-", ".xlsx");
Stream out1 = File.OpenWrite(file.FullName);
wb1.Write(out1);
out1.Close();
// Check the namespace Contains what we'd expect it to
OPCPackage pkg = OPCPackage.Open(file.ToString());
PackagePart wbRelPart =
pkg.GetPart(PackagingUriHelper.CreatePartName("/xl/_rels/workbook.xml.rels"));
Assert.IsNotNull(wbRelPart);
Assert.IsTrue(wbRelPart.IsRelationshipPart);
Assert.AreEqual(ContentTypes.RELATIONSHIPS_PART, wbRelPart.ContentType);
PackagePart wbPart =
pkg.GetPart(PackagingUriHelper.CreatePartName("/xl/workbook.xml"));
// Links to the three sheets, shared strings and styles
Assert.IsTrue(wbPart.HasRelationships);
Assert.AreEqual(5, wbPart.Relationships.Size);
wb1.Close();
// Load back the XSSFWorkbook
XSSFWorkbook wb2 = new XSSFWorkbook(pkg);
Assert.AreEqual(3, wb2.NumberOfSheets);
Assert.IsNotNull(wb2.GetSheetAt(0));
Assert.IsNotNull(wb2.GetSheetAt(1));
Assert.IsNotNull(wb2.GetSheetAt(2));
Assert.IsNotNull(wb2.GetSharedStringSource());
Assert.IsNotNull(wb2.GetStylesSource());
Assert.AreEqual(0, wb2.GetSheetAt(0).FirstRowNum);
Assert.AreEqual(1, wb2.GetSheetAt(0).LastRowNum);
Assert.AreEqual(0, wb2.GetSheetAt(1).FirstRowNum);
Assert.AreEqual(0, wb2.GetSheetAt(1).LastRowNum);
Assert.AreEqual(0, wb2.GetSheetAt(2).FirstRowNum);
Assert.AreEqual(0, wb2.GetSheetAt(2).LastRowNum);
sheet1 = wb2.GetSheetAt(0);
Assert.AreEqual(1.2, sheet1.GetRow(0).GetCell(0).NumericCellValue, 0.0001);
Assert.AreEqual("hello world", sheet1.GetRow(1).GetCell(0).RichStringCellValue.String);
pkg.Close();
Assert.AreEqual(0, Directory.GetFiles(AppDomain.CurrentDomain.BaseDirectory, "*.tmp").Length, "At Last: There are no temporary files.");
}
public void XSSFTextParagraph_()
{
XSSFWorkbook wb = new XSSFWorkbook();
try
{
XSSFSheet sheet = wb.CreateSheet() as XSSFSheet;
XSSFDrawing Drawing = sheet.CreateDrawingPatriarch() as XSSFDrawing;
XSSFTextBox shape = Drawing.CreateTextbox(new XSSFClientAnchor(0, 0, 0, 0, 2, 2, 3, 4)) as XSSFTextBox;
XSSFRichTextString rt = new XSSFRichTextString("Test String");
XSSFFont font = wb.CreateFont() as XSSFFont;
Color color = Color.FromArgb(0, 255, 255);
font.SetColor(new XSSFColor(color));
font.FontName = (/*setter*/ "Arial");
rt.ApplyFont(font);
shape.SetText(rt);
List <XSSFTextParagraph> paras = shape.TextParagraphs;
Assert.AreEqual(1, paras.Count);
XSSFTextParagraph text = paras[(0)];
Assert.AreEqual("Test String", text.Text);
Assert.IsFalse(text.IsBullet);
Assert.IsNotNull(text.GetXmlObject());
Assert.AreEqual(shape.GetCTShape(), text.ParentShape);
Assert.IsNotNull(text.GetEnumerator());
Assert.IsNotNull(text.AddLineBreak());
Assert.IsNotNull(text.TextRuns);
Assert.AreEqual(2, text.TextRuns.Count);
text.AddNewTextRun();
Assert.AreEqual(3, text.TextRuns.Count);
Assert.AreEqual(TextAlign.LEFT, text.TextAlign);
text.TextAlign = TextAlign.None;
Assert.AreEqual(TextAlign.LEFT, text.TextAlign);
text.TextAlign = (/*setter*/ TextAlign.CENTER);
Assert.AreEqual(TextAlign.CENTER, text.TextAlign);
text.TextAlign = (/*setter*/ TextAlign.RIGHT);
Assert.AreEqual(TextAlign.RIGHT, text.TextAlign);
text.TextAlign = TextAlign.None;
Assert.AreEqual(TextAlign.LEFT, text.TextAlign);
text.TextFontAlign = (/*setter*/ TextFontAlign.BASELINE);
Assert.AreEqual(TextFontAlign.BASELINE, text.TextFontAlign);
text.TextFontAlign = (/*setter*/ TextFontAlign.BOTTOM);
Assert.AreEqual(TextFontAlign.BOTTOM, text.TextFontAlign);
text.TextFontAlign = TextFontAlign.None;
Assert.AreEqual(TextFontAlign.BASELINE, text.TextFontAlign);
text.TextFontAlign = TextFontAlign.None;
Assert.AreEqual(TextFontAlign.BASELINE, text.TextFontAlign);
Assert.IsNull(text.BulletFont);
text.BulletFont = (/*setter*/ "Arial");
Assert.AreEqual("Arial", text.BulletFont);
Assert.IsNull(text.BulletCharacter);
text.BulletCharacter = (/*setter*/ ".");
Assert.AreEqual(".", text.BulletCharacter);
//Assert.IsNull(text.BulletFontColor);
Assert.AreEqual(Color.Empty, text.BulletFontColor);
text.BulletFontColor = (/*setter*/ color);
Assert.AreEqual(color, text.BulletFontColor);
Assert.AreEqual(100.0, text.BulletFontSize, 0.01);
text.BulletFontSize = (/*setter*/ 1.0);
Assert.AreEqual(1.0, text.BulletFontSize, 0.01);
text.BulletFontSize = (/*setter*/ 1.0);
Assert.AreEqual(1.0, text.BulletFontSize, 0.01);
text.BulletFontSize = (/*setter*/ -9.0);
Assert.AreEqual(-9.0, text.BulletFontSize, 0.01);
text.BulletFontSize = (/*setter*/ -9.0);
Assert.AreEqual(-9.0, text.BulletFontSize, 0.01);
text.BulletFontSize = (/*setter*/ 1.0);
Assert.AreEqual(1.0, text.BulletFontSize, 0.01);
text.BulletFontSize = (/*setter*/ -9.0);
Assert.AreEqual(-9.0, text.BulletFontSize, 0.01);
Assert.AreEqual(0.0, text.Indent, 0.01);
text.Indent = (/*setter*/ 2.0);
Assert.AreEqual(2.0, text.Indent, 0.01);
text.Indent = (/*setter*/ -1.0);
Assert.AreEqual(0.0, text.Indent, 0.01);
text.Indent = (/*setter*/ -1.0);
Assert.AreEqual(0.0, text.Indent, 0.01);
Assert.AreEqual(0.0, text.LeftMargin, 0.01);
text.LeftMargin = (/*setter*/ 3.0);
Assert.AreEqual(3.0, text.LeftMargin, 0.01);
text.LeftMargin = (/*setter*/ -1.0);
Assert.AreEqual(0.0, text.LeftMargin, 0.01);
text.LeftMargin = (/*setter*/ -1.0);
Assert.AreEqual(0.0, text.LeftMargin, 0.01);
Assert.AreEqual(0.0, text.RightMargin, 0.01);
text.RightMargin = (/*setter*/ 4.5);
Assert.AreEqual(4.5, text.RightMargin, 0.01);
text.RightMargin = (/*setter*/ -1.0);
Assert.AreEqual(0.0, text.RightMargin, 0.01);
text.RightMargin = (/*setter*/ -1.0);
Assert.AreEqual(0.0, text.RightMargin, 0.01);
Assert.AreEqual(0.0, text.DefaultTabSize, 0.01);
Assert.AreEqual(0.0, text.GetTabStop(0), 0.01);
text.AddTabStop(3.14);
Assert.AreEqual(3.14, text.GetTabStop(0), 0.01);
Assert.AreEqual(100.0, text.LineSpacing, 0.01);
text.LineSpacing = (/*setter*/ 3.15);
Assert.AreEqual(3.15, text.LineSpacing, 0.01);
text.LineSpacing = (/*setter*/ -2.13);
Assert.AreEqual(-2.13, text.LineSpacing, 0.01);
Assert.AreEqual(0.0, text.SpaceBefore, 0.01);
text.SpaceBefore = (/*setter*/ 3.17);
Assert.AreEqual(3.17, text.SpaceBefore, 0.01);
text.SpaceBefore = (/*setter*/ -4.7);
Assert.AreEqual(-4.7, text.SpaceBefore, 0.01);
Assert.AreEqual(0.0, text.SpaceAfter, 0.01);
text.SpaceAfter = (/*setter*/ 6.17);
Assert.AreEqual(6.17, text.SpaceAfter, 0.01);
text.SpaceAfter = (/*setter*/ -8.17);
Assert.AreEqual(-8.17, text.SpaceAfter, 0.01);
Assert.AreEqual(0, text.Level);
text.Level = (/*setter*/ 1);
Assert.AreEqual(1, text.Level);
text.Level = (/*setter*/ 4);
Assert.AreEqual(4, text.Level);
Assert.IsTrue(text.IsBullet);
Assert.IsFalse(text.IsBulletAutoNumber);
text.IsBullet = (false);
text.IsBullet = (false);
Assert.IsFalse(text.IsBullet);
Assert.IsFalse(text.IsBulletAutoNumber);
text.IsBullet = (true);
Assert.IsTrue(text.IsBullet);
Assert.IsFalse(text.IsBulletAutoNumber);
Assert.AreEqual(0, text.BulletAutoNumberStart);
Assert.AreEqual(ListAutoNumber.ARABIC_PLAIN, text.BulletAutoNumberScheme);
text.IsBullet = (false);
Assert.IsFalse(text.IsBullet);
text.SetBullet(ListAutoNumber.CIRCLE_NUM_DB_PLAIN);
Assert.IsTrue(text.IsBullet);
Assert.IsTrue(text.IsBulletAutoNumber);
//Assert.AreEqual(0, text.BulletAutoNumberStart);
//This value should be 1, see CT_TextAutonumberBullet.startAt, default value is 1;
Assert.AreEqual(1, text.BulletAutoNumberStart);
Assert.AreEqual(ListAutoNumber.CIRCLE_NUM_DB_PLAIN, text.BulletAutoNumberScheme);
text.IsBullet = (false);
Assert.IsFalse(text.IsBullet);
Assert.IsFalse(text.IsBulletAutoNumber);
text.SetBullet(ListAutoNumber.CIRCLE_NUM_WD_BLACK_PLAIN, 10);
Assert.IsTrue(text.IsBullet);
Assert.IsTrue(text.IsBulletAutoNumber);
Assert.AreEqual(10, text.BulletAutoNumberStart);
Assert.AreEqual(ListAutoNumber.CIRCLE_NUM_WD_BLACK_PLAIN, text.BulletAutoNumberScheme);
Assert.IsNotNull(text.ToString());
new XSSFTextParagraph(text.GetXmlObject(), shape.GetCTShape());
}
finally
{
wb.Close();
}
}
public void Bug58245_XSSFSheetIterator()
{
XSSFWorkbook wb = new XSSFWorkbook();
wb.CreateSheet();
// =====================================================================
// Case 1: Existing code uses XSSFSheet for-each loop
// =====================================================================
// Original code (no longer valid)
/*
* for (XSSFSheet sh : wb) {
* sh.createRow(0);
* }
*/
// Option A:
foreach (XSSFSheet sh in wb)
{
sh.CreateRow(0);
}
// Option B (preferred for new code):
foreach (ISheet sh in wb)
{
sh.CreateRow(0);
}
// =====================================================================
// Case 2: Existing code creates an iterator variable
// =====================================================================
// Original code (no longer valid)
/*
* Iterator<XSSFSheet> it = wb.iterator();
* XSSFSheet sh = it.next();
* sh.createRow(0);
*/
// Option A:
{
IEnumerator <XSSFSheet> it = wb.GetEnumerator() as IEnumerator <XSSFSheet>;
XSSFSheet sh = it.Current;
sh.CreateRow(0);
}
// Option B:
{
//IEnumerator<XSSFSheet> it = wb.XssfSheetIterator();
//XSSFSheet sh = it.Current;
//sh.CreateRow(0);
}
// Option C (preferred for new code):
{
IEnumerator <ISheet> it = wb.GetEnumerator() as IEnumerator <ISheet>;
ISheet sh = it.Current;
sh.CreateRow(0);
}
wb.Close();
}
