static void CreateChart(IDrawing drawing, ISheet sheet, IClientAnchor anchor, string serie1, string serie2) { IChart chart = drawing.CreateChart(anchor); IChartLegend legend = chart.GetOrCreateLegend(); legend.Position = LegendPosition.TopRight; ILineChartData <double, double> data = chart.ChartDataFactory.CreateLineChartData <double, double>(); // Use a category axis for the bottom axis. IChartAxis bottomAxis = chart.GetChartAxisFactory().CreateCategoryAxis(AxisPosition.Bottom); IValueAxis leftAxis = chart.GetChartAxisFactory().CreateValueAxis(AxisPosition.Left); leftAxis.SetCrosses(AxisCrosses.AutoZero); IChartDataSource <double> xs = DataSources.FromNumericCellRange(sheet, new CellRangeAddress(0, 0, 0, NUM_OF_COLUMNS - 1)); IChartDataSource <double> ys1 = DataSources.FromNumericCellRange(sheet, new CellRangeAddress(1, 1, 0, NUM_OF_COLUMNS - 1)); IChartDataSource <double> ys2 = DataSources.FromNumericCellRange(sheet, new CellRangeAddress(2, 2, 0, NUM_OF_COLUMNS - 1)); var s1 = data.AddSeries(xs, ys1); s1.SetTitle(serie1); var s2 = data.AddSeries(xs, ys2); s2.SetTitle(serie2); chart.Plot(data, bottomAxis, leftAxis); }
private static void CreateChart(ISheet sheet, IDrawing drawing, IClientAnchor anchor, string serieTitle, int startDataRow, int endDataRow, int columnIndex) { XSSFChart chart = (XSSFChart)drawing.CreateChart(anchor); IBarChartData <string, double> barChartData = chart.ChartDataFactory.CreateBarChartData <string, double>(); IChartLegend legend = chart.GetOrCreateLegend(); legend.Position = LegendPosition.Bottom; IChartAxis bottomAxis = chart.ChartAxisFactory.CreateCategoryAxis(AxisPosition.Bottom); bottomAxis.MajorTickMark = AxisTickMark.None; IValueAxis leftAxis = chart.ChartAxisFactory.CreateValueAxis(AxisPosition.Left); leftAxis.Crosses = AxisCrosses.AutoZero; leftAxis.SetCrossBetween(AxisCrossBetween.Between); IChartDataSource <string> categoryAxis = DataSources.FromStringCellRange(sheet, new CellRangeAddress(startDataRow, endDataRow, 0, 0)); IChartDataSource <double> valueAxis = DataSources.FromNumericCellRange(sheet, new CellRangeAddress(startDataRow, endDataRow, columnIndex, columnIndex)); var serie = barChartData.AddSeries(categoryAxis, valueAxis); serie.SetTitle(serieTitle); chart.Plot(barChartData, bottomAxis, leftAxis); }
static void CreateChart(IDrawing drawing, ISheet sheet, IClientAnchor anchor, string serie1, string serie2, bool enableMajorGridline = false) { XSSFChart chart = (XSSFChart)drawing.CreateChart(anchor); chart.SetTitle("Test 1"); IChartLegend legend = chart.GetOrCreateLegend(); legend.Position = LegendPosition.TopRight; ILineChartData <double, double> data = chart.ChartDataFactory.CreateLineChartData <double, double>(); // Use a category axis for the bottom axis. IChartAxis bottomAxis = chart.ChartAxisFactory.CreateCategoryAxis(AxisPosition.Bottom); IValueAxis leftAxis = chart.ChartAxisFactory.CreateValueAxis(AxisPosition.Left); leftAxis.Crosses = AxisCrosses.AutoZero; IChartDataSource <double> xs = DataSources.FromNumericCellRange(sheet, new CellRangeAddress(0, 0, 0, NUM_OF_COLUMNS - 1)); IChartDataSource <double> ys1 = DataSources.FromNumericCellRange(sheet, new CellRangeAddress(1, 1, 0, NUM_OF_COLUMNS - 1)); IChartDataSource <double> ys2 = DataSources.FromNumericCellRange(sheet, new CellRangeAddress(2, 2, 0, NUM_OF_COLUMNS - 1)); var s1 = data.AddSeries(xs, ys1); s1.SetTitle(serie1); var s2 = data.AddSeries(xs, ys2); s2.SetTitle(serie2); chart.Plot(data, bottomAxis, leftAxis); //add major gridline, available since NPOI 2.5.5 var plotArea = chart.GetCTChart().plotArea; plotArea.catAx[0].AddNewMajorGridlines(); plotArea.valAx[0].AddNewMajorGridlines(); }
static void Main(string[] args) { IWorkbook wb = new XSSFWorkbook(); ISheet sheet = wb.CreateSheet("linechart"); int NUM_OF_ROWS = 3; int NUM_OF_COLUMNS = 10; // 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 anchor = drawing.CreateAnchor(0, 0, 0, 0, 0, 5, 10, 15); IChart chart = drawing.CreateChart(anchor); IChartLegend legend = chart.GetOrCreateLegend(); legend.Position = LegendPosition.TopRight; ILineChartData <double, double> data = chart.GetChartDataFactory().CreateLineChartData <double, double>(); // Use a category axis for the bottom axis. IChartAxis bottomAxis = chart.GetChartAxisFactory().CreateCategoryAxis(AxisPosition.Bottom); IValueAxis leftAxis = chart.GetChartAxisFactory().CreateValueAxis(AxisPosition.Left); leftAxis.SetCrosses(AxisCrosses.AutoZero); IChartDataSource <double> xs = DataSources.FromNumericCellRange(sheet, new CellRangeAddress(0, 0, 0, NUM_OF_COLUMNS - 1)); IChartDataSource <double> ys1 = DataSources.FromNumericCellRange(sheet, new CellRangeAddress(1, 1, 0, NUM_OF_COLUMNS - 1)); IChartDataSource <double> ys2 = DataSources.FromNumericCellRange(sheet, new CellRangeAddress(2, 2, 0, NUM_OF_COLUMNS - 1)); var s1 = data.AddSerie(xs, ys1); s1.SetTitle("title1"); var s2 = data.AddSerie(xs, ys2); s2.SetTitle("title2"); chart.Plot(data, bottomAxis, leftAxis); using (FileStream fs = File.Create("test.xlsx")) { wb.Write(fs); } }
public void GenaretionChart() { FileStream RfileStream = new FileStream("D:\\test.xlsx", FileMode.Open, FileAccess.Read); //建立讀取資料的FileStream XSSFWorkbook wb = new XSSFWorkbook(RfileStream); //讀取檔案內的Workbook物件 ISheet Wsheet = wb.GetSheetAt(1); //選擇圖表存放的sheet ISheet Rsheet = wb.GetSheetAt(0); //選擇資料來源的sheet IDrawing drawing = Wsheet.CreateDrawingPatriarch(); //sheet產生drawing物件 IClientAnchor clientAnchor = drawing.CreateAnchor(0, 0, 0, 0, 0, 0, 5, 10); //設定圖表位置 IChart chart = drawing.CreateChart(clientAnchor); //產生chart物件 IChartLegend legend = chart.GetOrCreateLegend(); //還沒研究出這行在做甚麼 legend.Position = LegendPosition.TopRight; ILineChartData <double, double> data = chart.ChartDataFactory.CreateLineChartData <double, double>(); //產生存放資料的物件(資料型態為double) IChartAxis bottomAxis = chart.ChartAxisFactory.CreateCategoryAxis(AxisPosition.Bottom); //設定X軸 IValueAxis leftAxis = chart.ChartAxisFactory.CreateValueAxis(AxisPosition.Left); //設定Y軸 bottomAxis.Crosses = AxisCrosses.AutoZero; //設定X軸數值開始為0 leftAxis.Crosses = AxisCrosses.AutoZero; //設定Y軸數值開始為0 IChartDataSource <double> xs = DataSources.FromNumericCellRange(Rsheet, new CellRangeAddress(0, 4, 0, 0)); //取得要讀取sheet的資料位置(CellRangeAddress(first_row,end_row, first_column, end_column)) //x軸資料 IChartDataSource <double> ys1 = DataSources.FromNumericCellRange(Rsheet, new CellRangeAddress(0, 4, 1, 1)); //第一條y軸資料 IChartDataSource <double> ys2 = DataSources.FromNumericCellRange(Rsheet, new CellRangeAddress(0, 4, 2, 2)); //第二條y軸資料 data.AddSeries(xs, ys1); data.AddSeries(xs, ys2); //加入到data chart.Plot(data, bottomAxis, leftAxis); //加入到chart FileStream WfileStream = new FileStream("D:\\test.xlsx", FileMode.Create, FileAccess.Write); //建立寫入資料的FileStream wb.Write(WfileStream); //將workbook寫入資料 RfileStream.Close(); //關閉FileStream WfileStream.Close(); //關閉FileStream }
public void Main() { IWorkbook wb = new XSSFWorkbook(); ISheet sheet = wb.CreateSheet("Sheet 1"); int NUM_OF_ROWS = 3; int NUM_OF_COLUMNS = 10; // 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 anchor = drawing.CreateAnchor(0, 0, 0, 0, 0, 5, 10, 15); IChart chart = drawing.CreateChart(anchor); IChartLegend legend = chart.GetOrCreateLegend(); legend.Position = (LegendPosition.TopRight); IScatterChartData <double, double> data = chart.ChartDataFactory.CreateScatterChartData <double, double>(); IValueAxis bottomAxis = chart.ChartAxisFactory.CreateValueAxis(AxisPosition.Bottom); IValueAxis leftAxis = chart.ChartAxisFactory.CreateValueAxis(AxisPosition.Left); leftAxis.Crosses = AxisCrosses.AutoZero; IChartDataSource <double> xs = DataSources.FromNumericCellRange(sheet, new CellRangeAddress(0, 0, 0, NUM_OF_COLUMNS - 1)); IChartDataSource <double> ys1 = DataSources.FromNumericCellRange(sheet, new CellRangeAddress(1, 1, 0, NUM_OF_COLUMNS - 1)); IChartDataSource <double> ys2 = DataSources.FromNumericCellRange(sheet, new CellRangeAddress(2, 2, 0, NUM_OF_COLUMNS - 1)); data.AddSeries(xs, ys1); data.AddSeries(xs, ys2); chart.Plot(data, bottomAxis, leftAxis); // Write the output to a file FileStream sw = File.Create("test.xlsx"); wb.Write(sw); sw.Close(); }
private IWorkbook CreateWorkbookWithChart() { IWorkbook wb = new XSSFWorkbook(); ISheet sheet = wb.CreateSheet("linechart"); int NUM_OF_ROWS = 3; int NUM_OF_COLUMNS = 10; // 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 anchor = Drawing.CreateAnchor(0, 0, 0, 0, 0, 5, 10, 15); IChart chart = Drawing.CreateChart(anchor); IChartLegend legend = chart.GetOrCreateLegend(); legend.Position = (/*setter*/ LegendPosition.TopRight); ILineChartData <double, double> data = chart.ChartDataFactory.CreateLineChartData <double, double>(); // Use a category axis for the bottom axis. IChartAxis bottomAxis = chart.ChartAxisFactory.CreateCategoryAxis(AxisPosition.Bottom); IValueAxis leftAxis = chart.ChartAxisFactory.CreateValueAxis(AxisPosition.Left); leftAxis.Crosses = (/*setter*/ AxisCrosses.AutoZero); IChartDataSource <double> xs = DataSources.FromNumericCellRange(sheet, new CellRangeAddress(0, 0, 0, NUM_OF_COLUMNS - 1)); IChartDataSource <double> ys1 = DataSources.FromNumericCellRange(sheet, new CellRangeAddress(1, 1, 0, NUM_OF_COLUMNS - 1)); IChartDataSource <double> ys2 = DataSources.FromNumericCellRange(sheet, new CellRangeAddress(2, 2, 0, NUM_OF_COLUMNS - 1)); data.AddSeries(xs, ys1); data.AddSeries(xs, ys2); chart.Plot(data, bottomAxis, leftAxis); return(wb); }
public void TestAccessMethods() { IWorkbook wb = new XSSFWorkbook(); ISheet sheet = wb.CreateSheet(); IDrawing Drawing = sheet.CreateDrawingPatriarch(); IClientAnchor anchor = Drawing.CreateAnchor(0, 0, 0, 0, 1, 1, 10, 30); IChart chart = Drawing.CreateChart(anchor); IValueAxis axis = chart.ChartAxisFactory.CreateValueAxis(AxisPosition.Bottom); axis.SetCrossBetween(AxisCrossBetween.MidpointCategory); Assert.AreEqual(axis.GetCrossBetween(), AxisCrossBetween.MidpointCategory); axis.Crosses = (AxisCrosses.AutoZero); Assert.AreEqual(axis.Crosses, AxisCrosses.AutoZero); Assert.AreEqual(chart.GetAxis().Count, 1); }
public void TestAccessMethods() { IWorkbook wb = new XSSFWorkbook(); ISheet sheet = wb.CreateSheet(); IDrawing Drawing = sheet.CreateDrawingPatriarch(); IClientAnchor anchor = Drawing.CreateAnchor(0, 0, 0, 0, 1, 1, 10, 30); IChart chart = Drawing.CreateChart(anchor); IValueAxis axis = chart.GetChartAxisFactory().CreateValueAxis(AxisPosition.BOTTOM); axis.SetCrossBetween(AxisCrossBetween.MIDPOINT_CATEGORY); Assert.AreEqual(axis.GetCrossBetween(), AxisCrossBetween.MIDPOINT_CATEGORY); axis.SetCrosses(AxisCrosses.AUTO_ZERO); Assert.AreEqual(axis.GetCrosses(), AxisCrosses.AUTO_ZERO); Assert.AreEqual(chart.GetAxis().Count, 1); }
static void CreateChart(ISheet sheet) { XSSFDrawing drawing = (XSSFDrawing)sheet.CreateDrawingPatriarch(); XSSFClientAnchor anchor = (XSSFClientAnchor)drawing.CreateAnchor(0, 0, 0, 0, 3, 0, 10, 15); IChart chart = drawing.CreateChart(anchor); IChartLegend legend = chart.GetOrCreateLegend(); legend.Position = LegendPosition.Bottom; IBarChartData <string, double> data = chart.ChartDataFactory.CreateBarChartData <string, double>(); IChartAxis bottomAxis = chart.ChartAxisFactory.CreateCategoryAxis(AxisPosition.Bottom); bottomAxis.MajorTickMark = AxisTickMark.None; IValueAxis leftAxis = chart.ChartAxisFactory.CreateValueAxis(AxisPosition.Left); leftAxis.Crosses = AxisCrosses.AutoZero; leftAxis.SetCrossBetween(AxisCrossBetween.Between); IChartDataSource <string> categories = DataSources.FromStringCellRange(sheet, new CellRangeAddress(0, GetPracas().Count() - 1, 0, 0)); // Abono IChartDataSource <double> xAbono = DataSources.FromNumericCellRange(sheet, new CellRangeAddress(0, GetPracas().Count() - 1, 1, 1)); // Isento IChartDataSource <double> xIsento = DataSources.FromNumericCellRange(sheet, new CellRangeAddress(0, GetPracas().Count() - 1, 2, 2)); // Violação IChartDataSource <double> xViolacao = DataSources.FromNumericCellRange(sheet, new CellRangeAddress(0, GetPracas().Count() - 1, 3, 3)); var s1 = data.AddSeries(categories, xAbono); s1.SetTitle("Abono"); var s2 = data.AddSeries(categories, xIsento); s2.SetTitle("Isento"); var s3 = data.AddSeries(categories, xViolacao); s3.SetTitle("Violação"); chart.Plot(data, bottomAxis, leftAxis); }
private XSSFWorkbook CreateBarchart() { XSSFWorkbook workbook = new XSSFWorkbook(); XSSFSheet sheet = (XSSFSheet)workbook.CreateSheet("WorkForceAnalytics"); XSSFCellStyle styleHeader = (XSSFCellStyle)workbook.CreateCellStyle(); styleHeader.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center; //styleHeader.SetFont(getNewXSSFFont(workbook, styleHeader)); XSSFCellStyle style = (XSSFCellStyle)workbook.CreateCellStyle(); style.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Left; XSSFRow row1 = (XSSFRow)sheet.CreateRow(0); row1.CreateCell(0).SetCellValue(""); List <string> lstdatase = new List <string>(4) { "A1", "B1", "C1", "D1", "E1" }; for (int i = 1; i < 5; i++) { row1.CreateCell(i).SetCellValue(lstdatase[i - 1].ToString()); row1.GetCell(i).CellStyle = styleHeader; } int rowvalue = 1; List <string> lstdata = new List <string>(8) { "A", "B", "C", "D", "E", "F", "G", "H" }; int d = 10; XSSFDrawing drawing = (XSSFDrawing)sheet.CreateDrawingPatriarch(); XSSFClientAnchor anchor = (XSSFClientAnchor)drawing.CreateAnchor(0, 0, 0, 0, 6, 1, 15, 18); IChart chart = drawing.CreateChart(anchor); IChartLegend legend = chart.GetOrCreateLegend(); legend.Position = (LegendPosition.Bottom); IBarChartData <string, double> data = chart.ChartDataFactory.CreateBarChartData <string, double>(); IChartAxis bottomAxis = chart.ChartAxisFactory.CreateCategoryAxis(AxisPosition.Bottom); IValueAxis leftAxis = chart.ChartAxisFactory.CreateValueAxis(AxisPosition.Left); leftAxis.Crosses = AxisCrosses.AutoZero; leftAxis.SetCrossBetween(AxisCrossBetween.Between); IChartDataSource <string> xs = DataSources.FromStringCellRange(sheet, new NPOI.SS.Util.CellRangeAddress(0, 0, 1, 5 - 1)); for (int ii = 0; ii < 8; ii++) { XSSFRow rownew = (XSSFRow)sheet.CreateRow(rowvalue); rownew.CreateCell(0).SetCellValue(lstdata[ii].ToString()); for (int i = 1; i < 5; i++) { rownew.CreateCell(i).SetCellValue(d * 0.1); d++; rownew.GetCell(i).CellStyle = style; } rowvalue++; IChartDataSource <double> ys = DataSources.FromNumericCellRange(sheet, new NPOI.SS.Util.CellRangeAddress(ii + 1, ii + 1, 1, 5 - 1)); data.AddSeries(xs, ys).SetTitle(lstdata[ii].ToString()); } chart.Plot(data, bottomAxis, leftAxis); sheet.ForceFormulaRecalculation = true; return(workbook); }
public void Run() { IWorkbook wb = new XSSFWorkbook(); ISheet sheet = wb.CreateSheet("linechart"); IRow row; ICell cell; // line chart 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"); // ScatterChart ISheet sheet1 = wb.CreateSheet("ScatterChart"); for (int rowIndex = 0; rowIndex < NUM_OF_ROWS; rowIndex++) { row = sheet1.CreateRow((short)rowIndex); for (int colIndex = 0; colIndex < NUM_OF_COLUMNS; colIndex++) { cell = row.CreateCell((short)colIndex); cell.SetCellValue(colIndex * (rowIndex + 1)); } } drawing = sheet1.CreateDrawingPatriarch(); IClientAnchor anchor = drawing.CreateAnchor(0, 0, 0, 0, 0, 5, 10, 15); IChart chart = drawing.CreateChart(anchor); IChartLegend legend = chart.GetOrCreateLegend(); legend.Position = (LegendPosition.TopRight); IScatterChartData <double, double> data = chart.ChartDataFactory.CreateScatterChartData <double, double>(); IValueAxis bottomAxis = chart.ChartAxisFactory.CreateValueAxis(AxisPosition.Bottom); IValueAxis leftAxis = chart.ChartAxisFactory.CreateValueAxis(AxisPosition.Left); leftAxis.Crosses = AxisCrosses.AutoZero; IChartDataSource <double> xs = DataSources.FromNumericCellRange(sheet1, new CellRangeAddress(0, 0, 0, NUM_OF_COLUMNS - 1)); IChartDataSource <double> ys1 = DataSources.FromNumericCellRange(sheet1, new CellRangeAddress(1, 1, 0, NUM_OF_COLUMNS - 1)); IChartDataSource <double> ys2 = DataSources.FromNumericCellRange(sheet1, new CellRangeAddress(2, 2, 0, NUM_OF_COLUMNS - 1)); data.AddSeries(xs, ys1); data.AddSeries(xs, ys2); chart.Plot(data, bottomAxis, leftAxis); using (FileStream fs = File.Create(@"C:\00.Dev\temp\ChartTest.xlsx")) { wb.Write(fs); } }