Beispiel #1
0
        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);
            }
        }
Beispiel #2
0
        protected override void SetValue(ICell cell, object source)
        {
            NPOI.SS.Util.CellRangeAddress region = NPOIExcelUtil.GetRange(cell);
            ISheet        sheet  = cell.Sheet;
            IDrawing      draw   = sheet.DrawingPatriarch ?? sheet.CreateDrawingPatriarch();
            IClientAnchor anchor = region != null?
                                   draw.CreateAnchor(20, 20, 0, 0, region.FirstColumn, region.FirstRow, region.LastColumn + 1, region.LastRow + 1) :
                                       draw.CreateAnchor(20, 20, 0, 0, cell.ColumnIndex, cell.RowIndex, cell.ColumnIndex + 1, cell.RowIndex + 1);

            draw.CreatePicture(anchor, sheet.Workbook.AddPicture(_func(source), PictureType.JPEG));//PNG、JPEG都没问题
        }
Beispiel #3
0
        public void Render(ICell cell)
        {
            NPOI.SS.Util.CellRangeAddress region = NPOIExcelUtil.GetRange(cell);
            ISheet        sheet  = cell.Sheet;
            IDrawing      draw   = sheet.DrawingPatriarch ?? sheet.CreateDrawingPatriarch();
            IClientAnchor anchor = region != null?
                                   draw.CreateAnchor(20, 20, 0, 0, region.FirstColumn, region.FirstRow, region.LastColumn + 1, region.LastRow + 1) :
                                       PicArea != null?
                                       draw.CreateAnchor(20, 20, 0, 0, PicArea.ColIndex, PicArea.RowIndex, PicArea.ColIndex + PicArea.ColCount, PicArea.RowIndex + PicArea.RowCount) :
                                           draw.CreateAnchor(20, 20, 0, 0, cell.ColumnIndex, cell.RowIndex, cell.ColumnIndex + 1, cell.RowIndex + 1);

            draw.CreatePicture(anchor, sheet.Workbook.AddPicture(PicSource, PictureType.JPEG));
        }
Beispiel #4
0
        public void TestFormulaCache()
        {
            IWorkbook     wb      = new XSSFWorkbook();
            ISheet        sheet   = new SheetBuilder(wb, plotData).Build();
            IDrawing      Drawing = sheet.CreateDrawingPatriarch();
            IClientAnchor anchor  = Drawing.CreateAnchor(0, 0, 0, 0, 1, 1, 10, 30);
            IChart        chart   = Drawing.CreateChart(anchor);

            IChartAxis bottomAxis = chart.ChartAxisFactory.CreateValueAxis(AxisPosition.Bottom);
            IChartAxis leftAxis   = chart.ChartAxisFactory.CreateValueAxis(AxisPosition.Left);

            IScatterChartData scatterChartData =
                chart.ChartDataFactory.CreateScatterChartData();

            DataMarker         xMarker = new DataMarker(sheet, CellRangeAddress.ValueOf("A1:E1"));
            DataMarker         yMarker = new DataMarker(sheet, CellRangeAddress.ValueOf("A2:E2"));
            IScatterChartSerie serie   = scatterChartData.AddSerie(xMarker, yMarker);

            chart.Plot(scatterChartData, bottomAxis, leftAxis);

            XSSFScatterChartData.Serie xssfScatterSerie =
                (XSSFScatterChartData.Serie)serie;
            XSSFNumberCache yCache = xssfScatterSerie.LastCalculatedYCache;

            Assert.AreEqual(5, yCache.PointCount);
            Assert.AreEqual(4.0, yCache.GetValueAt(3), 0.00001);
            Assert.AreEqual(16.0, yCache.GetValueAt(5), 0.00001);
        }
Beispiel #5
0
        public void TestOneSeriePlot()
        {
            IWorkbook     wb      = new XSSFWorkbook();
            ISheet        sheet   = new SheetBuilder(wb, plotData).Build();
            IDrawing      Drawing = sheet.CreateDrawingPatriarch();
            IClientAnchor anchor  = Drawing.CreateAnchor(0, 0, 0, 0, 1, 1, 10, 30);
            IChart        chart   = Drawing.CreateChart(anchor);

            IChartAxis bottomAxis = chart.ChartAxisFactory.CreateValueAxis(AxisPosition.Bottom);
            IChartAxis leftAxis   = chart.ChartAxisFactory.CreateValueAxis(AxisPosition.Left);

            IScatterChartData <string, double> scatterChartData =
                chart.ChartDataFactory.CreateScatterChartData <string, double>();

            IChartDataSource <String>            xs     = DataSources.FromStringCellRange(sheet, CellRangeAddress.ValueOf("A1:J1"));
            IChartDataSource <double>            ys     = DataSources.FromNumericCellRange(sheet, CellRangeAddress.ValueOf("A2:J2"));
            IScatterChartSeries <string, double> series = scatterChartData.AddSeries(xs, ys);

            Assert.IsNotNull(series);

            Assert.AreEqual(1, scatterChartData.GetSeries().Count);
            Assert.IsTrue(scatterChartData.GetSeries().Contains(series));

            chart.Plot(scatterChartData, bottomAxis, leftAxis);
        }
Beispiel #6
0
        ////重载
        //public ISheet pictureDataToSheet(ISheet sheet, HSSFPicture pictureNPOI, int startRow, int startCol, int endRow, int endCol)
        //{

        //    workbook.AddPicture(pictureNPOI.)
        //}

        /// <summary>
        /// 画矩形,2.0似乎还未实现,xslx格式未实现
        /// </summary>
        /// <param name="sheet"></param>
        /// <param name="content_string"></param>
        /// <param name="dx1"></param>
        /// <param name="dy1"></param>
        /// <param name="dx2"></param>
        /// <param name="dy2"></param>
        /// <param name="startRow"></param>
        /// <param name="startCol"></param>
        /// <param name="endRow"></param>
        /// <param name="endCol"></param>
        /// <returns></returns>
        public ISheet shapJuxingToSheet(ISheet sheet, string content_string, int dx1, int dy1, int dx2, int dy2, int startRow, int startCol, int endRow, int endCol)
        {
            if (sheet == null)
            {
                sheet = this.workbook.CreateSheet();
            }


            //创建DrawingPatriarch,存放的容器
            IDrawing patriarch = sheet.CreateDrawingPatriarch();
            //画图形的位置点
            IClientAnchor anchor = patriarch.CreateAnchor(dx1, dy1, dx2, dy2, startCol, startRow, endCol, endRow);

            //将图片文件读入workbook,用索引指向该文件
            //int pictureIdx = workbook.AddPicture(pictureNPOI, PictureType.TIFF);
            //IShape recl;



            //根据读入图片和anchor把图片插到相应的位置
            //IPicture pict = patriarch.CreatePicture(anchor, pictureIdx);
            //原始大小显示,重载可指定缩放
            //pict.Resize(0.9);
            return(sheet);
        }
Beispiel #7
0
        /*泛类型行不通
         * public ISheet pictureDataToSheet<T>(ISheet sheet, T pictureNPOI,int startRow,int startCol, int endRow,int endCol)
         * //   where T: XSSFPicture, HSSFPicture,类型应该只有一种的原因吧,无法执行类型约束为两个类,因为类的约束必须放在第一个
         * {
         *
         *  XSSFPicture pictureNPOI_XSSFPicture = pictureNPOI as XSSFPicture;
         *  HSSFPalette pictureNPOI_HSSFPalette = pictureNPOI as HSSFPalette;
         *  //XSSFPicture,HSSFPalette是类,只能有一种类型,正好是泛类型要解决的
         *  //方法和使用一样,但是T的类型取决类申城的Isheet的类型
         *  //应该使用重载
         *  if (true)
         *  {
         *
         *  }
         *  else
         *  {
         *      return null;
         *  }
         *  workbook.AddPicture(pictureNPOI.)
         * }
         */

        /// <summary>
        /// Excel sheet中插入图片
        /// </summary>
        /// <param name="sheet"></param>
        /// <param name="pictureNPOI"></param>
        /// <param name="dx1"></param>
        /// <param name="dy1"></param>
        /// <param name="dx2"></param>
        /// <param name="dy2"></param>
        /// <param name="startRow"></param>
        /// <param name="startCol"></param>
        /// <param name="endRow"></param>
        /// <param name="endCol"></param>
        /// <returns></returns>
        //重载
        public void pictureDataToSheet(ISheet sheet, byte[] pictureNPOI, int dx1, int dy1, int dx2, int dy2, int startRow, int startCol, int endRow, int endCol)
        {
            /*将实际图片转换为pictureData时使用,但是pictureNPOI本身就是picture
             * byte[] pictureByte=
             * workbook.AddPicture(, PictureType.PNG);
             */
            //判断是否有sheet
            //无,则创建
            if (sheet == null)
            {
                sheet = this.workbook.CreateSheet();
            }

            //执行向sheet写图片
            //创建DrawingPatriarch,存放的容器
            IDrawing patriarch = sheet.CreateDrawingPatriarch();
            ///System.InvalidCastException:“无法将类型为“NPOI.XSSF.UserModel.XSSFDrawing”的对象强制转换为类型“NPOI.HSSF.UserModel.HSSFPatriarch”。”
            ///            HSSFPatriarch patriarch = (HSSFPatriarch)sheetA.CreateDrawingPatriarch();
            ///    根据报错改为如下
            // IDrawing patriarch = sheet.CreateDrawingPatriarch();

            //XSSFClientAnchor anchor = new XSSFClientAnchor(dx1, dy1, dx2, dy2, startCol, startRow, endCol, endRow);
            IClientAnchor anchor = patriarch.CreateAnchor(dx1, dy1, dx2, dy2, startCol, startRow, endCol, endRow);

            //将图片文件读入workbook,用索引指向该文件
            int pictureIdx = workbook.AddPicture(pictureNPOI, PictureType.PNG);

            //根据读入图片和anchor把图片插到相应的位置
            IPicture pict = patriarch.CreatePicture(anchor, pictureIdx);
            //原始大小显示,重载可指定缩放
            //pict.Resize(0.9);
            //return sheet;
        }
Beispiel #8
0
        /// <summary>
        /// 在单元格中插入图片
        /// </summary>
        private static void InsertImage(ICell cell, byte[] picBytes, PictureType pt)
        {
            var row1     = cell.RowIndex;
            var col1     = cell.ColumnIndex;
            var workbook = cell.Sheet.Workbook;
            var sheet    = cell.Sheet;

            /* Calcualte the image size */
            MemoryStream ms  = new MemoryStream(picBytes);
            Image        Img = Bitmap.FromStream(ms, true);
            double       ImageOriginalWidth  = Img.Width;
            double       ImageOriginalHeight = Img.Height;
            double       scale = cell.Sheet.GetColumnWidth(cell.ColumnIndex) / 36.5 / ImageOriginalWidth;

            cell.Row.Height = (short)(ImageOriginalHeight * scale * 15);
            //cell.Sheet.SetColumnWidth(cell.ColumnIndex, (int)(ImageOriginalWidth * scale * 36.5));

            /* Add Picture to Workbook, Specify picture type as PNG and Get an Index */
            int pictureIdx = workbook.AddPicture(picBytes, pt);  //添加图片

            /* Create the drawing container */
            IDrawing drawing = sheet.CreateDrawingPatriarch();

            /* Create an anchor point */
            IClientAnchor anchor = drawing.CreateAnchor(0, 0, 0, 0, col1, row1, col1 + 1, row1 + 1);

            /* Invoke createPicture and pass the anchor point and ID */
            IPicture picture = drawing.CreatePicture(anchor, pictureIdx);

            /* Call resize method, which resizes the image */
            picture.Resize(1);

            picBytes = null;
        }
        /// <summary>
        /// 创建一个图表实例
        /// </summary>
        /// <param name="excelChart"></param>
        /// <param name="sheet"></param>
        private void CreateChart(NPOIExcelChart excelChart, ISheet sheet, int startRow, int endRow)
        {
            if (_excelType != NPOIExcelType.XLS)
            {
                throw new NotImplementedException("只支持.xls文件作图");
            }

            IDrawing      drawing = sheet.CreateDrawingPatriarch();
            IClientAnchor anchor  = drawing.CreateAnchor(0, 0, 0, 0, 0, startRow, excelChart.Axis.Count, endRow);
            XSSFChart     chart   = drawing.CreateChart(anchor) as XSSFChart;

            chart.Title.String = excelChart.Title;

            IChartLegend legend = chart.GetOrCreateLegend();

            legend.Position = LegendPosition.TopRight;

            //坐标轴
            var axis = chart.ChartAxisFactory.CreateCategoryAxis(AxisPosition.Bottom);

            axis.IsVisible = true;
            //值轴
            var data = chart.ChartAxisFactory.CreateValueAxis(AxisPosition.Left);

            data.IsVisible = true;
            data.Crosses   = (AxisCrosses.AutoZero);


            IChartDataSource <string> xs = DataSources.FromArray <string>(excelChart.Axis.ToArray());

            switch (excelChart.ExcelChartType)
            {
            case NPOIExcelChartType.Bar:
                var chartBarData = chart.ChartDataFactory.CreateBarChartData <string, double>();
                foreach (var item in excelChart.Data)
                {
                    var curData   = DataSources.FromArray <double>(item.Value.ToArray());
                    var curSeries = chartBarData.AddSeries(xs, curData);
                    curSeries.SetTitle(item.Key);
                }
                chart.Plot(chartBarData, axis, data);
                return;

            case NPOIExcelChartType.Line:
                var chartLineData = chart.ChartDataFactory.CreateLineChartData <string, double>();
                foreach (var item in excelChart.Data)
                {
                    var curData   = DataSources.FromArray <double>(item.Value.ToArray());
                    var curSeries = chartLineData.AddSeries(xs, curData);
                    curSeries.SetTitle(item.Key);
                }
                chart.Plot(chartLineData, axis, data);
                break;

            default:
                break;
            }
        }
Beispiel #10
0
        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);
            }
        }
Beispiel #11
0
        public TestXSSFChartAxis()
        {
            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);

            axis = chart.GetChartAxisFactory().CreateValueAxis(AxisPosition.Bottom);
        }
        private IManualLayout GetEmptyLayout()
        {
            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);
            IChartLegend  legend  = chart.GetOrCreateLegend();

            return(legend.GetManualLayout());
        }
Beispiel #13
0
        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
        }
Beispiel #14
0
        public void TestLegendPositionAccessMethods()
        {
            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);
            IChartLegend  legend  = chart.GetOrCreateLegend();

            legend.Position = LegendPosition.TopRight;
            Assert.AreEqual(LegendPosition.TopRight, legend.Position);
        }
Beispiel #15
0
        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();
        }
Beispiel #16
0
        public void Test_setOverlay_defaultChartLegend_expectOverlayInitialValueSetToFalse()
        {
            // Arrange
            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);
            IChartLegend  legend  = chart.GetOrCreateLegend();

            // Act

            // Assert
            Assert.IsFalse(legend.IsOverlay);
        }
Beispiel #17
0
        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);
        }
Beispiel #18
0
        /// <summary>
        /// 添加批注
        /// </summary>
        /// <param name="exCell">单元格</param>
        /// <param name="commentAuthor">批注作者</param>
        /// <param name="commentContent">批注内容</param>
        public static void AddComment(ICell exCell, string commentAuthor, string commentContent)
        {
            IWorkbook book = exCell.Sheet.Workbook;
            //IDrawing draw = exCell.Sheet.DrawingPatriarch ?? exCell.Sheet.CreateDrawingPatriarch();
            IDrawing draw    = exCell.Sheet.CreateDrawingPatriarch();
            IComment comment = draw.CreateCellComment(draw.CreateAnchor(0, 0, 255, 255, exCell.ColumnIndex + 1, exCell.RowIndex + 1,
                                                                        exCell.ColumnIndex + 3, exCell.RowIndex + 4));

            comment.Author = commentAuthor;
            comment.String = book.GetCreationHelper().CreateRichTextString(commentContent);
            //用于解决XSSF批注不显示的问题
            comment.String.ApplyFont(book.CreateFont());
            //comment.Column = exCell.ColumnIndex;
            //comment.Row = exCell.RowIndex;
            exCell.CellComment = comment;
        }
Beispiel #19
0
        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);
        }
Beispiel #20
0
        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);
        }
Beispiel #21
0
        /// <summary>
        /// 插入图片
        /// </summary>
        /// <param name="inputSheet"></param>
        /// <param name="lstInputRequest"></param>
        /// <returns></returns>
        public static bool TryInsertPictures(ISheet inputSheet, List <PictureInsertRequest> lstInputRequest)
        {
            try
            {
                //获取/创建
                IDrawing patriarch = inputSheet.DrawingPatriarch;

                if (null == patriarch)
                {
                    patriarch = inputSheet.CreateDrawingPatriarch();
                }

                foreach (var oneRequest in lstInputRequest)
                {
                    //第一步:将图片读入二进制数组
                    MemoryStream mStream = new MemoryStream();
                    oneRequest.UseImage.Save(mStream, System.Drawing.Imaging.ImageFormat.Png);
                    byte[] bytePic = mStream.GetBuffer();

                    //第二步:图片加载进workbook
                    int pictureIdx = inputSheet.Workbook.AddPicture(bytePic, PictureType.PNG);

                    //第三步:设置锚点
                    IClientAnchor anchor = patriarch.CreateAnchor(0, 0, 0, 0, oneRequest.StartColumn, oneRequest.StartRow, oneRequest.EndColumn, oneRequest.EndRow);

                    //第四步:创建图片
                    IPicture pict = patriarch.CreatePicture(anchor, pictureIdx);
                }

                return(true);
            }
            catch (Exception)
            {
                return(false);
            }
        }
Beispiel #22
0
    public static void drawWaterRemarkPath(HSSFWorkbook wb, string waterRemarkPath)
    {
        int startXCol         = 0; // 水印起始列
        int startYRow         = 2; // 水印起始行
        int betweenXCol       = 1; // 水印横向之间间隔多少列
        int betweenYRow       = 2; // 水印纵向之间间隔多少行
        int XCount            = 0; // 横向共有水印多少个
        int YCount            = 0; // 纵向共有水印多少个
        int waterRemarkWidth  = 0; // 水印图片宽度为多少列
        int waterRemarkHeight = 0; // 水印图片高度为多少行

                                   // 校验传入的水印图片格式
                if(!waterRemarkPath.EndsWith("png") && !waterRemarkPath.EndsWith("PNG"))
        {
                        //throw new RuntimeException("向Excel上面打印水印,目前支持png格式的图片。");
        }

        // 加载图片
        FileStream   fs         = new FileStream(waterRemarkPath, FileMode.Open, FileAccess.Read); //将图片以文件流的形式进行保存
        BinaryReader br         = new BinaryReader(fs);
        byte[]       imgBytesIn = br.ReadBytes((int)fs.Length);                                    //将流读入到字节数组中

        for (int i = 0; i < wb.NumberOfSheets; i++)
        {     // wb.getNumberOfSheets()
            ISheet sheet = wb.GetSheetAt(0);
            sheet.ProtectSheet("Yelioa123");
            try
            {
                XCount = (sheet.GetRow(0).LastCellNum);

                if (XCount >= 0 && XCount <= 5)
                {
                    XCount = 1;
                }

                if (XCount >= 5)
                {
                    XCount = XCount / 2;
                }
            }
            catch (Exception e)
            {
                XCount = 1;
            }
            try
            {
                YCount = sheet.LastRowNum / (betweenYRow);     // 纵向共有水印多少个
                if (YCount < 4)
                {
                    YCount = 2;
                }
            }
            catch (Exception e)
            {
                YCount = 50;
            }

            // 开始打水印
            IDrawing drawing = sheet.CreateDrawingPatriarch();
            // 按照共需打印多少行水印进行循环
            for (int yCount = 0; yCount < YCount; yCount++)
            {
                // 按照每行需要打印多少个水印进行循环
                for (int xCount = 0; xCount < XCount; xCount++)
                {
                    // 创建水印图片位置
                    int xIndexInteger = startXCol + (xCount * 2) + (xCount * betweenXCol);
                    int yIndexInteger = startYRow + (yCount * 1) + (yCount * betweenYRow);

                    /*参数定义: 第一个参数是(x轴的开始节点); 第二个参数是(是y轴的开始节点); 第三个参数是(是x轴的结束节点); 第四个参数是(是y轴的结束节点);
                     *                         第五个参数是(是从Excel的第几列开始插入图片,从0开始计数); 第六个参数是(是从excel的第几行开始插入图片,从0开始计数);
                     *                         第七个参数是(图片宽度,共多少列); 第8个参数是(图片高度,共多少行);
                     *                     */
                    IClientAnchor anchor = drawing.CreateAnchor(0, 0, 0, 0, xIndexInteger, yIndexInteger,
                                                                xIndexInteger + waterRemarkWidth, yIndexInteger + waterRemarkHeight);
                    IPicture pic = drawing.CreatePicture(anchor,
                                                         wb.AddPicture(imgBytesIn, PictureType.PNG));
                    pic.Resize();
                }
            }
        }
            
    }
Beispiel #23
0
        /// <summary>
        /// 填充数据到表格
        /// </summary>
        protected override void FillBody()
        {
            if (null == _ScrapMerge)
            {
                return;
            }

            //
            // 填写填表人
            //
            TempRow = Sheet.GetRow(2);// CreateRow(2);
            TempRow.GetCell(5).SetCellValue(_ScrapMerge.FillPerson);

            //
            // 填写学校名称
            //
            TempRow = Sheet.GetRow(3);
            TempRow.GetCell(1).SetCellValue("测试学校");

            //
            // 填写信息化负责人和电话
            //
            TempRow = Sheet.GetRow(4);
            TempRow.GetCell(1).SetCellValue(_ScrapMerge.People);
            TempRow.GetCell(4).SetCellValue(_ScrapMerge.Tel);

            //
            // 填写报表详情
            //
            TempRow = Sheet.GetRow(5);
            var aaa = (DateTime.Now.Year.ToString() + "年度学校信息化设备报废明细表").ToString();

            TempRow.GetCell(0).SetCellValue(aaa);

            //
            // 填写名称及品牌行
            //
            TempRow = Sheet.GetRow(7);
            TempRow.GetCell(1).SetCellValue(_ScrapMerge.AssetsName + _ScrapMerge.ModelSpecification);
            TempRow.GetCell(2).SetCellValue(_ScrapMerge.Counts);
            TempRow.GetCell(3).SetCellValue(_ScrapMerge.GetsDate.ToShortDateString());
            TempRow.GetCell(4).SetCellValue(_ScrapMerge.UsedYears);
            TempRow.GetCell(5).SetCellValue(_ScrapMerge.DeadYears);

            //
            // 填写图片上方规格及品牌型号
            //
            TempRow = Sheet.GetRow(24);
            TempRow.GetCell(2).SetCellValue(_ScrapMerge.AssetsName + _ScrapMerge.ModelSpecification);

            //
            // 分辨四大类
            //
            switch (_ScrapMerge.type)
            {
            case 1:    // 计算机
            {
                //
                // 填写CPU行
                //
                TempRow = Sheet.GetRow(9);
                TempRow.GetCell(1).SetCellValue(_ScrapMerge.dcpu);
                TempRow.GetCell(2).SetCellValue(_ScrapMerge.dzhuban);
                TempRow.GetCell(3).SetCellValue(_ScrapMerge.dyingpan);
                TempRow.GetCell(4).SetCellValue(_ScrapMerge.dneicun);
                TempRow.GetCell(5).SetCellValue(_ScrapMerge.dxianka);
                TempRow.GetCell(6).SetCellValue(_ScrapMerge.dguangqu);

                //
                // 填写CPU行
                //
                TempRow = Sheet.GetRow(11);
                TempRow.GetCell(1).SetCellValue(_ScrapMerge.dxianshiqi);
                TempRow.GetCell(2).SetCellValue(_ScrapMerge.dxianshiqichicun);
                TempRow.GetCell(3).SetCellValue(_ScrapMerge.dxianshiqileixing);
                TempRow.GetCell(4).SetCellValue(_ScrapMerge.djianpan);
                TempRow.GetCell(5).SetCellValue(_ScrapMerge.dshubiao);

                //
                // 时间行
                //
                TempRow = Sheet.GetRow(14);
                var i = ("学校(公章)" + DateTime.Now.ToLongDateString().ToString()).ToString();
                TempRow.GetCell(4).SetCellValue(i);

                //插入计算机类图片2
                //第一步:读取图片到byte数组   (方法二)
                byte[] bytes1 = System.IO.File.ReadAllBytes(@_ScrapMerge.PhotoSrcD);


                //第二步:将图片添加到workbook中  指定图片格式 返回图片所在workbook->Picture数组中的索引地址(从1开始)
                int pictureIdx1 = Workbook.AddPicture(bytes1, PictureType.JPEG);

                //第三步:在sheet中创建画部
                IDrawing patriarch1 = Sheet.CreateDrawingPatriarch();

                //第四步:设置锚点 (在起始单元格的X坐标0-1023,Y的坐标0-255,在终止单元格的X坐标0-1023,Y的坐标0-255,起始单元格行数,列数,终止单元格行数,列数)
                IClientAnchor anchor1 = patriarch1.CreateAnchor(0, 0, 0, 0, 1, 42, 6, 57);


                //第五步:创建图片
                IPicture pict1 = patriarch1.CreatePicture(anchor1, pictureIdx1);
                break;
            }

            case 2:    //服务器
            {
                //
                // 填写CPU行
                //
                TempRow = Sheet.GetRow(9);
                TempRow.GetCell(1).SetCellValue(_ScrapMerge.fcpu);
                TempRow.GetCell(2).SetCellValue(_ScrapMerge.fzhuban);
                TempRow.GetCell(3).SetCellValue(_ScrapMerge.fyingpan);
                TempRow.GetCell(4).SetCellValue(_ScrapMerge.fneicun);
                TempRow.GetCell(5).SetCellValue(_ScrapMerge.fxianka);
                TempRow.GetCell(6).SetCellValue(_ScrapMerge.fguangqu);

                //
                // 时间行
                //
                TempRow = Sheet.GetRow(12);
                var i = ("学校(公章)" + DateTime.Now.ToLongDateString().ToString()).ToString();
                TempRow.GetCell(4).SetCellValue(i);
                break;
            }

            case 3:    // 投影仪
            {
                //
                // 填写显示技术行
                //
                TempRow = Sheet.GetRow(9);
                TempRow.GetCell(1).SetCellValue(_ScrapMerge.txianshijishu);
                TempRow.GetCell(2).SetCellValue(_ScrapMerge.tzuigaoxianshifenbianlv);
                TempRow.GetCell(3).SetCellValue(_ScrapMerge.tbiaochengguangliangdu);
                TempRow.GetCell(4).SetCellValue(_ScrapMerge.tbiaochengduibidu);

                //
                // 时间行
                //
                TempRow = Sheet.GetRow(12);
                var i = ("学校(公章)" + DateTime.Now.ToLongDateString().ToString()).ToString();
                TempRow.GetCell(4).SetCellValue(i);
                break;
            }

            case 4:    // 其他
            {
                //
                // 填写主要参数行
                //
                TempRow = Sheet.GetRow(9);
                TempRow.GetCell(1).SetCellValue(_ScrapMerge.qzhuyaocanshu1);
                TempRow.GetCell(2).SetCellValue(_ScrapMerge.qzhuyaocanshu2);
                TempRow.GetCell(3).SetCellValue(_ScrapMerge.qzhuyaocanshu3);
                TempRow.GetCell(4).SetCellValue(_ScrapMerge.qzhuyaocanshu4);
                TempRow.GetCell(5).SetCellValue(_ScrapMerge.qzhuyaocanshu5);
                TempRow.GetCell(6).SetCellValue(_ScrapMerge.qzhuyaocanshu6);

                //
                // 时间行
                //
                TempRow = Sheet.GetRow(12);
                var i = ("学校(公章)" + DateTime.Now.ToLongDateString().ToString()).ToString();
                TempRow.GetCell(4).SetCellValue(i);
                break;
            }

            default:
                break;
            }

            //*****************************************************************
            //说明:插入图片

            //1.创建EXCEL中的Workbook
            //IWorkbook myworkbook = new HSSFWorkbook();                    //已有(Workbook)

            //2.创建Workbook中的Sheet
            //ISheet mysheet = myworkbook.CreateSheet("sheet1");            //已有(Sheet)

            //第一步:读取图片到byte数组   (方法一)
            //HttpWebRequest request = (HttpWebRequest)WebRequest.Create(_ScrapMerge.PhotoSrc);     // 有异常,图片路径格式不对

            //byte[] bytes;
            //using (Stream stream = request.GetResponse().GetResponseStream())
            //{
            //    using (MemoryStream mstream = new MemoryStream())
            //    {
            //        int count = 0;
            //        byte[] buffer = new byte[1024];
            //        int readNum = 0;
            //        while ((readNum = stream.Read(buffer, 0, 1024)) > 0)
            //        {
            //            count = count + readNum;
            //            mstream.Write(buffer, 0, 1024);
            //        }
            //        mstream.Position = 0;
            //        using (BinaryReader br = new BinaryReader(mstream))
            //        {

            //            bytes = br.ReadBytes(count);
            //        }
            //    }
            //}

            //第一步:读取图片到byte数组   (方法二)
            byte[] bytes = System.IO.File.ReadAllBytes(@_ScrapMerge.PhotoSrc);


            //第二步:将图片添加到workbook中  指定图片格式 返回图片所在workbook->Picture数组中的索引地址(从1开始)
            int pictureIdx = Workbook.AddPicture(bytes, PictureType.JPEG);

            //第三步:在sheet中创建画部
            IDrawing patriarch = Sheet.CreateDrawingPatriarch();

            //第四步:设置锚点 (在起始单元格的X坐标0-1023,Y的坐标0-255,在终止单元格的X坐标0-1023,Y的坐标0-255,起始单元格行数,列数,终止单元格行数,列数)
            IClientAnchor anchor = patriarch.CreateAnchor(0, 0, 0, 0, 1, 26, 6, 42);


            //第五步:创建图片
            IPicture pict = patriarch.CreatePicture(anchor, pictureIdx);

            //6.保存
            //FileStream file = new FileStream(@"E:\myworkbook11.xls", FileMode.Create);
            //Workbook.Write(file);

            //using (FileStream fs = new FileStream(savePath, FileMode.OpenOrCreate))
            //{
            //    Workbook.Write(fs);
            //    fs.Close();                     // 释放
            //}

            //
            // 将临时文件保存到指定路径
            //
            //scrapMergeExport.Save(savePath);
            //*****************************************************************


            Thread.Sleep(10);
        }
Beispiel #24
0
        /// <summary>
        /// 根据excel实体导出excel ,文件名不需要加后缀
        /// </summary>
        /// <param name="excelEntity"></param>
        /// <param name="fileName"></param>
        public static void ExcelEntityToExcel(Dictionary <string, ExcelEntity> excelEntity, string fileName)
        {
            if (excelEntity == null)
            {
                return;
            }

            IWorkbook workbook = null;

            CellEntity firstCellEntity = excelEntity.First().Value.CellEntities.FirstOrDefault();
            string     fileExtend      = "";//文件后缀

            if (firstCellEntity != null)
            {
                if (firstCellEntity.Cell is HSSFCell)
                {
                    workbook   = new HSSFWorkbook();
                    fileExtend = ".xls";
                }
                else if (firstCellEntity.Cell is XSSFCell)
                {
                    workbook   = new XSSFWorkbook();
                    fileExtend = ".xlsx";
                }
            }

            foreach (var sheetName in excelEntity.Keys)
            {
                ISheet            sheet     = workbook.CreateSheet(sheetName);
                List <CellEntity> tempList  = excelEntity[sheetName].CellEntities.OrderBy(x => x.FirstRow).ThenBy(x => x.FirstCol).ToList();
                CellEntity        firstCell = tempList.First();
                if (firstCell == null)
                {
                    continue;
                }

                int startRow = firstCell.FirstRow;
                int startCol = firstCell.FirstCol;
                int rowCount = excelEntity[sheetName].CellEntities.Select(x => x.FirstRow).Distinct().Count();
                int colCount = excelEntity[sheetName].CellEntities.Select(x => x.FirstCol).Distinct().Count();

                for (int rowIndex = startRow; rowIndex < startRow + rowCount; rowIndex++)
                {//创建单元格
                    IRow row = sheet.CreateRow(rowIndex);
                    for (int colIndex = startCol; colIndex < startCol + colCount; colIndex++)
                    {
                        CellEntity entity = excelEntity[sheetName].CellEntities.Find(x => x.FirstRow == rowIndex && x.FirstCol == colIndex);
                        if (entity != null)
                        {
                            ICell cell = row.CreateCell(colIndex);

                            if (entity.Cell != null)
                            {
                                if (entity.IsMerge)
                                {//合并单元格
                                    sheet.AddMergedRegion(new CellRangeAddress(entity.FirstRow, entity.FirstRow + entity.RowSpan - 1, entity.FirstCol, entity.FirstCol + entity.ColSpan - 1));
                                }

                                cell.SetCellType(entity.Cell.CellType);

                                switch (entity.Cell.CellType)
                                {
                                case CellType.Numeric:
                                    cell.SetCellValue(entity.Cell.NumericCellValue);
                                    break;

                                case CellType.Boolean:
                                    cell.SetCellValue(entity.Cell.BooleanCellValue);
                                    break;

                                case CellType.Formula:
                                case CellType.Blank:
                                case CellType.String:
                                case CellType.Unknown:
                                case CellType.Error:
                                default:
                                    cell.SetCellValue(entity.Cell.ToString());
                                    break;
                                }
                            }

                            //cell.CellStyle = workbook.CreateCellStyle();
                            //cell.CellStyle.CloneStyleFrom(entity.Cell.CellStyle);
                        }
                    }
                }

                if (excelEntity[sheetName].Pictures != null && excelEntity[sheetName].Pictures.Count > 0)
                {
                    //插入图片
                    foreach (var item in excelEntity[sheetName].Pictures)
                    {
                        int           pictureIdx = workbook.AddPicture(item.PictureData, PictureType.JPEG);
                        IDrawing      patriarch  = sheet.CreateDrawingPatriarch();
                        IClientAnchor anchor     = patriarch.CreateAnchor(0, 0, 0, 0, item.MinCol, item.MinRow, item.MaxCol, item.MaxRow);
                        IPicture      pict       = patriarch.CreatePicture(anchor, pictureIdx);
                    }
                }

                //转为字节数组
                MemoryStream stream = new MemoryStream();
                workbook.Write(stream);
                byte[] buf = stream.ToArray();

                //保存为Excel文件
                using (FileStream fs = new FileStream(fileName + fileExtend, FileMode.Create, FileAccess.Write))
                {
                    fs.Write(buf, 0, buf.Length);
                    fs.Flush();
                }
            }
        }
Beispiel #25
0
        protected override void Writing(object sender, WriteEventArgs args)
        {
            Cell   cell    = args.Entity as Cell;
            ISheet exSheet = args.ExSheet;

            if (cell == null)
            {
                return;
            }

            object tmpObject = this.GetValue();

            if (tmpObject == null)
            {
                return;
            }

            IWorkbook book = exSheet.Workbook;
            //IDrawing draw = exSheet.DrawingPatriarch ?? exSheet.CreateDrawingPatriarch();
            IDrawing draw = exSheet.DrawingPatriarch ?? exSheet.CreateDrawingPatriarch();//只能有一个实例,否则只有最后一个对象生效

            int  rowIndex = this.RowIndex;
            int  colIndex = this.ColIndex;
            IRow exRow    = exSheet.GetRow(rowIndex) ?? exSheet.CreateRow(rowIndex);
            //if (exRow != null)
            //{
            ICell exCell = exRow.GetCell(this.ColIndex) ?? exRow.CreateCell(this.ColIndex);

            //if (exCell != null)
            //{
            //object tmpObject = sheet.IsDynamic ? sheet.GetValue(cell,sheetName) : dt.Rows[cell.DataIndex][cell.Field];
            if (cell.ValueAppend)
            {
                tmpObject = exCell.StringCellValue + tmpObject;
            }
            if (tmpObject.GetType() == typeof(byte[]))//处理图片
            {
                CellRangeAddress region = NPOIExcelUtil.GetRange(exCell);

                Image image = new Bitmap(new MemoryStream(tmpObject as byte[]));
                Size  size  = image.Size;
                if (size.IsEmpty)
                {
                    return;
                }

                IClientAnchor anchor = region != null?
                                       draw.CreateAnchor(0, 0, 0, 0, region.FirstColumn, region.FirstRow, region.LastColumn + 1, region.LastRow + 1) :
                                           draw.CreateAnchor(20, 20, 0, 0, colIndex, rowIndex, colIndex + this.ColCount, rowIndex + this.RowCount);

                IPicture pic = draw.CreatePicture(anchor, book.AddPicture((byte[])tmpObject, PictureType.JPEG));

                switch (cell.FillType)
                {
                case FillType.Origin:
                    pic.Resize();
                    break;

                case FillType.Scale:
                    float width = 0, height = 0;
                    for (int i = anchor.Col1; i < anchor.Col2; i++)
                    {
                        width += exSheet.GetColumnWidth(i) / 256f * 12;
                    }
                    for (int i = anchor.Row1; i < anchor.Row2; i++)
                    {
                        IRow row = exSheet.GetRow(i);
                        height += row != null ? row.HeightInPoints : exSheet.DefaultRowHeightInPoints;
                    }
                    float factor = Math.Min(width / (size.Width * 0.75f), height / (size.Height * 0.75f));
                    pic.Resize(factor);
                    break;

                default:
                    break;
                }
            }
            else
            {
                exCell.SetCellValue((tmpObject ?? "").ToString());
            }
            //}
            //}
        }
Beispiel #26
0
        public void GetClientAnchor()
        {
            IWorkbook wb = _testDataProvider.CreateWorkbook();

            ISheet          sheet   = wb.CreateSheet();
            IRow            row     = sheet.CreateRow(10);
            ICell           cell    = row.CreateCell(5);
            ICreationHelper factory = wb.GetCreationHelper();

            IDrawing Drawing = sheet.CreateDrawingPatriarch();

            double r_mul, c_mul;

            if (sheet is HSSFSheet)
            {
                double rowheight = Units.ToEMU(row.HeightInPoints) / Units.EMU_PER_PIXEL;
                r_mul = 256.0 / rowheight;
                double colwidth = sheet.GetColumnWidthInPixels(2);
                c_mul = 1024.0 / colwidth;
            }
            else
            {
                r_mul = c_mul = Units.EMU_PER_PIXEL;
            }

            int dx1  = (int)Math.Round(10 * c_mul);
            int dy1  = (int)Math.Round(10 * r_mul);
            int dx2  = (int)Math.Round(3 * c_mul);
            int dy2  = (int)Math.Round(4 * r_mul);
            int col1 = cell.ColumnIndex + 1;
            int row1 = row.RowNum;
            int col2 = cell.ColumnIndex + 2;
            int row2 = row.RowNum + 1;

            IClientAnchor anchor  = Drawing.CreateAnchor(dx1, dy1, dx2, dy2, col1, row1, col2, row2);
            IComment      comment = Drawing.CreateCellComment(anchor);

            comment.Visible  = (/*setter*/ true);
            cell.CellComment = (/*setter*/ comment);

            anchor = comment.ClientAnchor;
            Assert.AreEqual(dx1, anchor.Dx1);
            Assert.AreEqual(dy1, anchor.Dy1);
            Assert.AreEqual(dx2, anchor.Dx2);
            Assert.AreEqual(dy2, anchor.Dy2);
            Assert.AreEqual(col1, anchor.Col1);
            Assert.AreEqual(row1, anchor.Row1);
            Assert.AreEqual(col2, anchor.Col2);
            Assert.AreEqual(row2, anchor.Row2);

            anchor           = factory.CreateClientAnchor();
            comment          = Drawing.CreateCellComment(anchor);
            cell.CellComment = (/*setter*/ comment);
            anchor           = comment.ClientAnchor;

            if (sheet is HSSFSheet)
            {
                Assert.AreEqual(0, anchor.Col1);
                Assert.AreEqual(0, anchor.Dx1);
                Assert.AreEqual(0, anchor.Row1);
                Assert.AreEqual(0, anchor.Dy1);
                Assert.AreEqual(0, anchor.Col2);
                Assert.AreEqual(0, anchor.Dx2);
                Assert.AreEqual(0, anchor.Row2);
                Assert.AreEqual(0, anchor.Dy2);
            }
            else
            {
                // when anchor is Initialized without parameters, the comment anchor attributes default to
                // "1, 15, 0, 2, 3, 15, 3, 16" ... see XSSFVMLDrawing.NewCommentShape()
                Assert.AreEqual(1, anchor.Col1);
                Assert.AreEqual(15 * Units.EMU_PER_PIXEL, anchor.Dx1);
                Assert.AreEqual(0, anchor.Row1);
                Assert.AreEqual(2 * Units.EMU_PER_PIXEL, anchor.Dy1);
                Assert.AreEqual(3, anchor.Col2);
                Assert.AreEqual(15 * Units.EMU_PER_PIXEL, anchor.Dx2);
                Assert.AreEqual(3, anchor.Row2);
                Assert.AreEqual(16 * Units.EMU_PER_PIXEL, anchor.Dy2);
            }
        }
Beispiel #27
0
        /// <summary>
        /// 按标准配置填值
        /// </summary>
        /// <param name="exCell">Excel单元格</param>
        /// <param name="field">字段类型</param>
        /// <param name="value"></param>
        /// <returns>操作状态:是否成功</returns>
        public static bool SetCellValue(ICell exCell, Field field, object value)
        {
            ICellStyle exCellStyle = null;
            Type       dataType    = value.GetType();
            IWorkbook  book        = exCell.Sheet.Workbook;

            if (dataType == typeof(string))
            {
                if (field.Type == FieldType.Formula)
                {
                    exCell.SetCellFormula(value as string);
                }
                else if (field.Type == FieldType.Text)
                {
                    exCell.SetCellValue(value as string);
                }
            }
            else if (ParseUtil.IsNumberType(dataType) && field.Type == FieldType.Numeric)
            {
                if (!string.IsNullOrEmpty(field.Format))
                {
                    exCellStyle            = exCell.CellStyle ?? book.CreateCellStyle();
                    exCellStyle.DataFormat = book.CreateDataFormat().GetFormat(field.Format);
                    exCell.CellStyle       = exCellStyle;
                }
                exCell.SetCellValue(double.Parse(value.ToString()));
            }
            else if ((dataType == typeof(DateTime) || dataType == typeof(DateTime?)) && field.Type == FieldType.Datetime)
            {
                //如不给日期指定格式会显示成数字,因此强制使用默认格式:"yyyy/mm/dd"
                string tmpStr = string.IsNullOrEmpty(field.Format) ? DEFAULT_DATEFORMAT : field.Format;
                exCellStyle            = exCell.CellStyle ?? book.CreateCellStyle();
                exCellStyle.DataFormat = book.CreateDataFormat().GetFormat(field.Format);
                exCell.CellStyle       = exCellStyle;
                exCell.SetCellValue((DateTime)value);
            }
            else if ((dataType == typeof(bool) || dataType == typeof(bool?)) && field.Type == FieldType.Boolean)
            {
                exCell.SetCellValue((bool)value);
            }
            else if (dataType == typeof(byte[]) && field.Type == FieldType.Picture)
            {
                //IDrawing draw = exCell.Sheet.DrawingPatriarch ?? exCell.Sheet.CreateDrawingPatriarch();//XSSF未实现DrawingPatriarch接口
                IDrawing draw = exCell.Sheet.CreateDrawingPatriarch();
                IPicture pic  = draw.CreatePicture(draw.CreateAnchor(0, 0, 0, 0, exCell.ColumnIndex, exCell.RowIndex,
                                                                     exCell.ColumnIndex + field.ColSpan, exCell.RowIndex + 1),
                                                   book.AddPicture((byte[])value, PictureType.JPEG));
            }
            else if ((dataType == typeof(byte) || dataType == typeof(byte?)) && field.Type == FieldType.Error)
            {
                exCell.SetCellErrorValue((byte)value);
            }
            else
            {
                /**
                 * (1) 如有转换器:想办法将DataType转换成FieldType与之匹配的数据类型
                 * (2) 否则,默认用字符串类型
                 */
                return(false);
            }
            return(true);
        }
Beispiel #28
0
        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);
            }
        }