/**
  * Gets the fully qualified cell reference given the column, row
  * external sheet reference etc
  *
  * @param sheet the sheet
  * @param column the column
  * @param row the row
  * @param workbook the workbook
  * @return the cell reference in the form 'Sheet 1'!A1
  */
 public static string getCellReference(int sheet,
                                       int column,
                                       int row,
                                       WritableWorkbook workbook)
 {
     return(CSharpJExcel.Jxl.Biff.CellReferenceHelper.getCellReference
                (sheet, column, row, (CSharpJExcel.Jxl.Biff.Formula.ExternalSheet)workbook));
 }
 /**
  * Gets the fully qualified cell reference given the column, row
  * external sheet reference etc
  *
  * @param sheet the sheet
  * @param column the column
  * @param row the row
  * @param workbook the workbook
  * @param buf the buffer
  */
 public static void getCellReference(int sheet,
                                     int column,
                                     int row,
                                     WritableWorkbook workbook,
                                     StringBuilder buf)
 {
     CSharpJExcel.Jxl.Biff.CellReferenceHelper.getCellReference
         (sheet, column, row, (CSharpJExcel.Jxl.Biff.Formula.ExternalSheet)workbook, buf);
 }
Esempio n. 3
0
        /**
         * Reads in the inputFile and creates a writable copy of it called outputFile
         *
         * @exception IOException
         * @exception BiffException
         */
        public void readWrite()
        {
            Console.WriteLine("Reading...");
            Workbook w1 = Workbook.getWorkbook(inputWorkbook);

            Console.WriteLine("Copying...");
            WritableWorkbook w2 = Workbook.createWorkbook(outputWorkbook, w1);

            if (inputWorkbook.Name == "jxlrwtest.xls")
            {
                modify(w2);
            }

            w2.write();
            w2.close();
            Console.WriteLine("Done");
        }
Esempio n. 4
0
        public byte[] Render(Rdl.Render.GenericRender report, bool renderAll)
        {
            _report = report;
            MemoryStream ms = new MemoryStream();

            report.SetSizes(renderAll);

            _workbook = Workbook.createWorkbook(ms);
            //_workbook = Workbook.createWorkbook(new System.IO.FileInfo(@"c:\foo.xls"));
            _ws = _workbook.createSheet("Sheet 1", 0);

            RecurseBuildRowsCols(report.BodyContainer, 0, 0, renderAll);
            _rows.Add(0);
            _rows.Sort(delegate(decimal d1, decimal d2) { return(decimal.Compare(d1, d2)); });
            _cols.Add(0);
            _cols.Sort(delegate(decimal d1, decimal d2) { return(decimal.Compare(d1, d2)); });

            for (int i = 1; i < _rows.Count; i++)
            {
                _ws.setRowView(i - 1, (int)((_rows[i] - _rows[i - 1]) * _lineHeight));
            }

            for (int i = 1; i < _cols.Count; i++)
            {
                _ws.setColumnView(i - 1, (int)((_cols[i] - _cols[i - 1]) * _colWidth));
            }

            formats = new WritableCellFormat[report.StyleList.Count];
            for (int i = 0; i < report.StyleList.Count; i++)
            {
                if (report.StyleList[i] is TextStyle)
                {
                    formats[i] = GetWritableFormat((TextStyle)report.StyleList[i]);
                }
            }

            RecurseRender(report.BodyContainer, 0, 0, renderAll);

            _workbook.write();
            _workbook.close();
            //BIFF8Writer.WriteWorkbookToStream(_workbook, ms);
            return(ms.ToArray());
        }
Esempio n. 5
0
        /**
         * Imports any names defined on the source sheet to the destination workbook
         */
        private void importNames()
        {
            WorkbookParser   fromWorkbook = (WorkbookParser)fromSheet.getWorkbook();
            WritableWorkbook toWorkbook   = toSheet.getWorkbook();
            int fromSheetIndex            = fromWorkbook.getIndex(fromSheet);

            CSharpJExcel.Jxl.Read.Biff.NameRecord[] nameRecords = fromWorkbook.getNameRecords();
            string[] names = toWorkbook.getRangeNames();

            for (int i = 0; i < nameRecords.Length; i++)
            {
                CSharpJExcel.Jxl.Read.Biff.NameRecord.NameRange[] nameRanges = nameRecords[i].getRanges();

                for (int j = 0; j < nameRanges.Length; j++)
                {
                    int nameSheetIndex = fromWorkbook.getExternalSheetIndex(nameRanges[j].getExternalSheet());

                    if (fromSheetIndex == nameSheetIndex)
                    {
                        string name = nameRecords[i].getName();
                        if (System.Array.BinarySearch(names, name) < 0)
                        {
                            toWorkbook.addNameArea(name,
                                                   toSheet,
                                                   nameRanges[j].getFirstColumn(),
                                                   nameRanges[j].getFirstRow(),
                                                   nameRanges[j].getLastColumn(),
                                                   nameRanges[j].getLastRow());
                        }
                        else
                        {
                            //logger.warn("Named range " + name + " is already present in the destination workbook");
                        }
                    }
                }
            }
        }
Esempio n. 6
0
        // ===========================================================================
        // 功能區
        // ===========================================================================

        /// <summary>
        ///     產生Excel
        /// </summary>
        /// <param name="exportConfigInfo"> </param>
        /// <param name="exportDataSet"> </param>
        /// <param name="outString"></param>
        public virtual void export(ExportConfigInfo exportConfigInfo, ExportDataSet exportDataSet, Stream outString)
        {
            configInfo = exportConfigInfo;

            try
            {
                // =========================================================
                // 建立 Workbook
                // =========================================================
                WritableWorkbook writableWorkbook = Workbook.createWorkbook(outString);

                for (int sheetlIndex = 0; sheetlIndex < exportConfigInfo.SheetList.Count; sheetlIndex++)
                {
                    // =====================================================
                    // 建立 sheet
                    // =====================================================
                    // 取得 sheetlInfo 設定
                    SheetlInfo sheetlInfo = exportConfigInfo.SheetList[sheetlIndex];

                    // 取得 sheetName
                    string sheetName = (ExcelStringUtil.IsEmpty(sheetlInfo.SheetName))
                        ? "Sheet" + (sheetlIndex + 1)
                        : sheetlInfo.SheetName;

                    // 建立 sheet
                    WritableSheet writableSheet = writableWorkbook.createSheet(sheetName, sheetlIndex);

                    // 版面設定
                    // setPageSetup Parameters:
                    // p - the page orientation
                    // ps - the paper size
                    // hm - the header margin, in inches
                    // fm - the footer margin, in inches
                    writableSheet.setPageSetup(PageOrientation.LANDSCAPE, exportConfigInfo.PaperSize, 0, 0);
                    writableSheet.getSettings().setLeftMargin(0);
                    writableSheet.getSettings().setRightMargin(0);

                    // =====================================================
                    // 處理前準備
                    // =====================================================
                    // 列指標
                    int targetRowIndex = 0;
                    // 紀錄已使用的儲存格 (cell)
                    var usedCells = new Dictionary <int, HashSet <string> >();
                    // 紀錄欄(column)的最大欄寬
                    var maxWidthMap = new Dictionary <string, int>();

                    // =====================================================
                    // 資訊
                    // =====================================================
                    foreach (var entry in sheetlInfo.PartInfoMap)
                    {
                        if (entry.Value == null)
                        {
                            return;
                        }

                        // 內容為 context
                        if (entry.Key.StartsWith(Constant.ELEMENT_CONTEXT))
                        {
                            //取得 context 設定檔設定資料
                            var contextInfo = (ContextInfo)entry.Value;
                            //取得 匯出資料
                            Dictionary <string, object> dataMap = exportDataSet.getContext(contextInfo.DataId);

                            targetRowIndex = WriteContext(
                                writableSheet,
                                contextInfo,
                                targetRowIndex,
                                dataMap,
                                usedCells,
                                maxWidthMap);
                        }

                        //內容為 detail
                        if (entry.Key.StartsWith(Constant.ELEMENT_DETAIL))
                        {
                            //取得 context 設定檔設定資料
                            var detailInfo = (DetailInfo)entry.Value;
                            //取得 匯出資料
                            var columnDataSetList = exportDataSet.getDetail(detailInfo.DataId);

                            targetRowIndex = EnterWriteDetail(
                                writableSheet,
                                detailInfo,
                                targetRowIndex,
                                columnDataSetList,
                                usedCells,
                                maxWidthMap);
                        }
                    }

                    // =====================================================
                    // 設定欄寬
                    // =====================================================
                    // 取得最大欄位 index
                    int maxColIndex = maxWidthMap[KEY_MAX_COL];
                    for (int colIndex = 0; colIndex <= maxColIndex; colIndex++)
                    {
                        // 取得欄寬
                        int colWidth = 0;
                        //取得 MAP 中的值 (tr、td 設定)
                        if (maxWidthMap.ContainsKey(colIndex + ""))
                        {
                            colWidth = maxWidthMap[colIndex + ""];
                        }
                        //若 tr td 未設定時,取 style 設定
                        if (colWidth == 0)
                        {
                            colWidth = Convert.ToInt32(ExcelStringUtil.SafeTrim(exportConfigInfo.StyleInfo.Width, "0"));
                        }

                        // 以上都未設定時使用預設值
                        //if (colWidth == 0)
                        //{
                        //    colWidth = Convert.ToInt32(Constant.DEFAULT_WIDTH);
                        //}

                        if (colWidth > 0)
                        {
                            writableSheet.setColumnView(colIndex, colWidth);
                        }
                    }
                }

                writableWorkbook.write();
                writableWorkbook.close();
            }
            catch (Exception e)
            {
                throw new ExcelOperateException("EXCEL 檔案匯出處理錯誤! \r\n" + e.Message + "\r\n" + e.StackTrace);
            }
        }
Esempio n. 7
0
        /**
         * If the inputFile was the test spreadsheet, then it modifies certain fields
         * of the writable copy
         *
         * @param w
         */
        private void modify(WritableWorkbook w)
        {
            Console.WriteLine("Modifying...");

            WritableSheet sheet = w.getSheet("modified");

            WritableCell         cell = null;
            CellFormat           cf   = null;
            Label                l    = null;
            WritableCellFeatures wcf  = null;

            // Change the format of cell B4 to be emboldened
            cell = sheet.getWritableCell(1, 3);
            WritableFont bold = new WritableFont(WritableFont.ARIAL,
                                                 WritableFont.DEFAULT_POINT_SIZE,
                                                 WritableFont.BOLD);

            cf = new WritableCellFormat(bold);
            cell.setCellFormat(cf);

            // Change the format of cell B5 to be underlined
            cell = sheet.getWritableCell(1, 4);
            WritableFont underline = new WritableFont(WritableFont.ARIAL,
                                                      WritableFont.DEFAULT_POINT_SIZE,
                                                      WritableFont.NO_BOLD,
                                                      false,
                                                      UnderlineStyle.SINGLE);

            cf = new WritableCellFormat(underline);
            cell.setCellFormat(cf);

            // Change the point size of cell B6 to be 10 point
            cell = sheet.getWritableCell(1, 5);
            WritableFont tenpoint = new WritableFont(WritableFont.ARIAL, 10);

            cf = new WritableCellFormat(tenpoint);
            cell.setCellFormat(cf);

            // Change the contents of cell B7 to read "Label - mod"
            cell = sheet.getWritableCell(1, 6);
            if (cell.getType() == CellType.LABEL)
            {
                Label lc = (Label)cell;
                lc.setString(lc.getString() + " - mod");
            }

            // Change cell B10 to display 7 dps
            cell = sheet.getWritableCell(1, 9);
            NumberFormat sevendps = new NumberFormat("#.0000000");

            cf = new WritableCellFormat(sevendps);
            cell.setCellFormat(cf);


            // Change cell B11 to display in the format 1e4
            cell = sheet.getWritableCell(1, 10);
            NumberFormat exp4 = new NumberFormat("0.####E0");

            cf = new WritableCellFormat(exp4);
            cell.setCellFormat(cf);

            // Change cell B12 to be normal display
            cell = sheet.getWritableCell(1, 11);
            cell.setCellFormat(WritableWorkbook.NORMAL_STYLE);

            // Change the contents of cell B13 to 42
            cell = sheet.getWritableCell(1, 12);
            if (cell.getType() == CellType.NUMBER)
            {
                Number n2 = (Number)cell;
                n2.setValue(42);
            }

            // Add 0.1 to the contents of cell B14
            cell = sheet.getWritableCell(1, 13);
            if (cell.getType() == CellType.NUMBER)
            {
                Number n3 = (Number)cell;
                n3.setValue(n3.getValue() + 0.1);
            }

            // Change the date format of cell B17 to be a custom format
            cell = sheet.getWritableCell(1, 16);
            DateFormat df = new DateFormat("dd MMM yyyy HH:mm:ss");

            cf = new WritableCellFormat(df);
            cell.setCellFormat(cf);

            // Change the date format of cell B18 to be a standard format
            cell = sheet.getWritableCell(1, 17);
            cf   = new WritableCellFormat(DateFormats.FORMAT9);
            cell.setCellFormat(cf);

            // Change the date in cell B19 to be 18 Feb 1998, 11:23:28
            cell = sheet.getWritableCell(1, 18);
            if (cell.getType() == CellType.DATE)
            {
                // TODO: fix this....
                //DateTime dt = (DateTime)cell;
                //Calendar cal = Calendar.getInstance();
                //cal.set(1998, 1, 18, 11, 23, 28);
                //Date d = cal.getTime();
                //dt.setDate(d);
            }

            // Change the value in B23 to be 6.8.  This should recalculate the
            // formula
            cell = sheet.getWritableCell(1, 22);
            if (cell.getType() == CellType.NUMBER)
            {
                Number n1 = (Number)cell;
                n1.setValue(6.8);
            }

            // Change the label in B30.  This will have the effect of making
            // the original string unreferenced
            cell = sheet.getWritableCell(1, 29);
            if (cell.getType() == CellType.LABEL)
            {
                l = (Label)cell;
                l.setString("Modified string contents");
            }
            // Insert a new row (number 35)
            sheet.insertRow(34);

            // Delete row 38 (39 after row has been inserted)
            sheet.removeRow(38);

            // Insert a new column (J)
            sheet.insertColumn(9);

            // Remove a column (L - M after column has been inserted)
            sheet.removeColumn(11);

            // Remove row 44 (contains a hyperlink), and then insert an empty
            // row just to keep the numbers consistent
            sheet.removeRow(43);
            sheet.insertRow(43);

            // Modify the hyperlinks
            WritableHyperlink[] hyperlinks = sheet.getWritableHyperlinks();

            for (int i = 0; i < hyperlinks.Length; i++)
            {
                WritableHyperlink wh = hyperlinks[i];
                if (wh.getColumn() == 1 && wh.getRow() == 39)
                {
                    try
                    {
                        // Change the hyperlink that begins in cell B40 to be a different API
                        wh.setURL(new Uri("http://www.andykhan.com/jexcelapi/index.html"));
                    }
                    catch (Exception e)
                    {
                        Console.WriteLine(e);
                    }
                }
                else if (wh.getColumn() == 1 && wh.getRow() == 40)
                {
                    wh.setFile(new FileInfo("../jexcelapi/docs/overview-summary.html"));
                }
                else if (wh.getColumn() == 1 && wh.getRow() == 41)
                {
                    wh.setFile(new FileInfo("d:/home/jexcelapi/docs/jxl/package-summary.html"));
                }
                else if (wh.getColumn() == 1 && wh.getRow() == 44)
                {
                    // Remove the hyperlink at B45
                    sheet.removeHyperlink(wh);
                }
            }

            // Change the background of cell F31 from blue to red
            WritableCell       c         = sheet.getWritableCell(5, 30);
            WritableCellFormat newFormat = new WritableCellFormat(c.getCellFormat());

            newFormat.setBackground(Colour.RED);
            c.setCellFormat(newFormat);

            // Modify the contents of the merged cell
            l = new Label(0, 49, "Modified merged cells");
            sheet.addCell(l);

            // Modify the chart data
            Number n = (Number)sheet.getWritableCell(0, 70);

            n.setValue(9);

            n = (Number)sheet.getWritableCell(0, 71);
            n.setValue(10);

            n = (Number)sheet.getWritableCell(0, 73);
            n.setValue(4);

            // Add in a cross sheet formula
            Formula f = new Formula(1, 80, "ROUND(COS(original!B10),2)");

            sheet.addCell(f);

            // Add in a formula from the named cells
            f = new Formula(1, 83, "value1+value2");
            sheet.addCell(f);

            // Add in a function formula using named cells
            f = new Formula(1, 84, "AVERAGE(value1,value1*4,value2)");
            sheet.addCell(f);

            // Copy sheet 1 to sheet 3
            //     w.copySheet(0, "copy", 2);

            // Use the cell deep copy feature
            Label label = new Label(0, 88, "Some copied cells", cf);

            sheet.addCell(label);

            label = new Label(0, 89, "Number from B9");
            sheet.addCell(label);

            WritableCell wc = sheet.getWritableCell(1, 9).copyTo(1, 89);

            sheet.addCell(wc);

            label = new Label(0, 90, "Label from B4 (modified format)");
            sheet.addCell(label);

            wc = sheet.getWritableCell(1, 3).copyTo(1, 90);
            sheet.addCell(wc);

            label = new Label(0, 91, "Date from B17");
            sheet.addCell(label);

            wc = sheet.getWritableCell(1, 16).copyTo(1, 91);
            sheet.addCell(wc);

            label = new Label(0, 92, "Boolean from E16");
            sheet.addCell(label);

            wc = sheet.getWritableCell(4, 15).copyTo(1, 92);
            sheet.addCell(wc);

            label = new Label(0, 93, "URL from B40");
            sheet.addCell(label);

            wc = sheet.getWritableCell(1, 39).copyTo(1, 93);
            sheet.addCell(wc);

            // Add some numbers for the formula copy
            for (int i = 0; i < 6; i++)
            {
                Number number = new Number(1, 94 + i, i + 1 + i / 8.0);
                sheet.addCell(number);
            }

            label = new Label(0, 100, "Formula from B27");
            sheet.addCell(label);

            wc = sheet.getWritableCell(1, 26).copyTo(1, 100);
            sheet.addCell(wc);

            label = new Label(0, 101, "A brand new formula");
            sheet.addCell(label);

            Formula formula = new Formula(1, 101, "SUM(B94:B96)");

            sheet.addCell(formula);

            label = new Label(0, 102, "A copy of it");
            sheet.addCell(label);

            wc = sheet.getWritableCell(1, 101).copyTo(1, 102);
            sheet.addCell(wc);

            // Remove the second image from the sheet
            WritableImage wi = sheet.getImage(1);

            sheet.removeImage(wi);

            wi = new WritableImage(1, 116, 2, 9, new FileInfo("resources/littlemoretonhall.png"));
            sheet.addImage(wi);

            // Add a list data validations
            label = new Label(0, 151, "Added drop down validation");
            sheet.addCell(label);

            Blank b = new Blank(1, 151);

            wcf = new WritableCellFeatures();
            ArrayList al = new ArrayList();

            al.Add("The Fellowship of the Ring");
            al.Add("The Two Towers");
            al.Add("The Return of the King");
            wcf.setDataValidationList(al);
            b.setCellFeatures(wcf);
            sheet.addCell(b);

            // Add a number data validation
            label = new Label(0, 152, "Added number validation 2.718 < x < 3.142");
            sheet.addCell(label);
            b   = new Blank(1, 152);
            wcf = new WritableCellFeatures();
            wcf.setNumberValidation(2.718, 3.142, WritableCellFeatures.BETWEEN);
            b.setCellFeatures(wcf);
            sheet.addCell(b);

            // Modify the text in the first cell with a comment
            cell = sheet.getWritableCell(0, 156);
            l    = (Label)cell;
            l.setString("Label text modified");

            cell = sheet.getWritableCell(0, 157);
            wcf  = cell.getWritableCellFeatures();
            wcf.setComment("modified comment text");

            cell = sheet.getWritableCell(0, 158);
            wcf  = cell.getWritableCellFeatures();
            wcf.removeComment();

            // Modify the validation contents of the row 173
            cell = sheet.getWritableCell(0, 172);
            wcf  = cell.getWritableCellFeatures();
            Range r        = wcf.getSharedDataValidationRange();
            Cell  botright = r.getBottomRight();

            sheet.removeSharedDataValidation(cell);
            al = new ArrayList();
            al.Add("Stanley Featherstonehaugh Ukridge");
            al.Add("Major Plank");
            al.Add("Earl of Ickenham");
            al.Add("Sir Gregory Parsloe-Parsloe");
            al.Add("Honoria Glossop");
            al.Add("Stiffy Byng");
            al.Add("Bingo Little");
            wcf.setDataValidationList(al);
            cell.setCellFeatures(wcf);
            sheet.applySharedDataValidation(cell, botright.getColumn() - cell.getColumn(), 1);            //botright.getRow() - cell.getRow());
        }