/** * Dumps out the contents of the excel file */ private void display(WorkbookSettings ws,TextWriter os) { Record r = null; bool found = false; while (reader.hasNext() && !found) { r = reader.next(); if (r.getType() == CSharpJExcel.Jxl.Biff.Type.WRITEACCESS) { found = true; } } if (!found) { Console.WriteLine("Warning: could not find write access record"); return; } byte[] data = r.getData(); string s = null; s = StringHelper.getString(data, data.Length, 0, ws); os.WriteLine(s); }
public static byte[] getEncodedURL(string s,WorkbookSettings ws) { if (s.StartsWith("http:")) return getURL(s,ws); else if (s.StartsWith("https:")) // CML return getURL(s, ws); else return getFile(s,ws); }
public WriteAccess(FileInfo file, TextWriter os) { WorkbookSettings ws = new WorkbookSettings(); Stream fis = new FileStream(file.FullName,FileMode.Open,FileAccess.Read); CSharpJExcel.Jxl.Read.Biff.File f = new CSharpJExcel.Jxl.Read.Biff.File(fis, ws); reader = new BiffRecordReader(f); display(ws,os); fis.Close(); }
/** * A factory method which takes input an excel file and reads input the contents. * * @param inStream an open stream which inStream the the excel 97 spreadsheet to parse * @param ws the settings for the workbook * @return a workbook instance * @exception IOException * @exception BiffException */ public static Workbook getWorkbook(Stream inStream, WorkbookSettings ws) { CSharpJExcel.Jxl.Read.Biff.File dataFile = new CSharpJExcel.Jxl.Read.Biff.File(inStream, ws); Workbook workbook = new WorkbookParser(dataFile, ws); workbook.parse(); return(workbook); }
/** * Uses the JExcelAPI to create a spreadsheet * * @exception IOException * @exception WriteException */ public void write() { WorkbookSettings ws = new WorkbookSettings(); ws.setLocale(new CultureInfo("en")); workbook = Workbook.createWorkbook(new FileInfo(filename), ws); WritableSheet s2 = workbook.createSheet("Number Formats", 0); WritableSheet s3 = workbook.createSheet("Date Formats", 1); WritableSheet s1 = workbook.createSheet("Label Formats", 2); WritableSheet s4 = workbook.createSheet("Borders", 3); WritableSheet s5 = workbook.createSheet("Labels", 4); WritableSheet s6 = workbook.createSheet("Formulas", 5); WritableSheet s7 = workbook.createSheet("Images", 6); // WritableSheet s8 = workbook.createSheet // ("'Illegal chars in name !*%^?': which exceeds max name length",7); // Modify the colour palette to bright red for the lime colour workbook.setColourRGB(Colour.LIME, 0xff, 0, 0); // Add a named range to the workbook workbook.addNameArea("namedrange", s4, 1, 11, 5, 14); workbook.addNameArea("validation_range", s1, 4, 65, 9, 65); workbook.addNameArea("formulavalue", s6, 1, 45, 1, 45); // Add a print area to the "Labels" sheet s5.getSettings().setPrintArea(4, 4, 15, 35); writeLabelFormatSheet(s1); writeNumberFormatSheet(s2); writeDateFormatSheet(s3); writeBordersSheet(s4); writeLabelsSheet(s5); writeFormulaSheet(s6); writeImageSheet(s7); workbook.write(); workbook.close(); }
/** * Constructs this object from the raw data. Used when reading in a * format record * * @param t the raw data * @param ws the workbook settings * @param dummy dummy overload */ public FontRecord(Record t,WorkbookSettings ws,Biff7 dummy) : base(t) { byte[] data = getRecord().getData(); pointHeight = IntegerHelper.getInt(data[0],data[1]) / EXCEL_UNITS_PER_POINT; colourIndex = IntegerHelper.getInt(data[4],data[5]); boldWeight = IntegerHelper.getInt(data[6],data[7]); scriptStyle = IntegerHelper.getInt(data[8],data[9]); underlineStyle = data[10]; fontFamily = data[11]; initialized = false; if ((data[2] & 0x02) != 0) italic = true; if ((data[2] & 0x08) != 0) struckout = true; int numChars = data[14]; name = StringHelper.getString(data,numChars,15,ws); }
/** * Constructs this object from the raw data. Used when reading in a * format record * * @param t the raw data * @param ws the workbook settings */ public FontRecord(Record t,WorkbookSettings ws) : base(t) { byte[] data = getRecord().getData(); pointHeight = IntegerHelper.getInt(data[0],data[1]) / EXCEL_UNITS_PER_POINT; colourIndex = IntegerHelper.getInt(data[4],data[5]); boldWeight = IntegerHelper.getInt(data[6],data[7]); scriptStyle = IntegerHelper.getInt(data[8],data[9]); underlineStyle = data[10]; fontFamily = data[11]; characterSet = data[12]; initialized = false; if ((data[2] & 0x02) != 0) italic = true; if ((data[2] & 0x08) != 0) struckout = true; int numChars = data[14]; if (data[15] == 0) name = StringHelper.getString(data,numChars,16,ws); else if (data[15] == 1) name = StringHelper.getUnicodeString(data,numChars,16); else { // Some font names don't have the unicode indicator name = StringHelper.getString(data,numChars,15,ws); } }
/** * Gets the bytes of the specified string. This will simply return the ASCII * values of the characters in the string * * @param s the string to convert into bytes * @return the ASCII values of the characters in the string */ public static byte[] getBytes(string s,WorkbookSettings ws) { try { System.Text.ASCIIEncoding enc = new System.Text.ASCIIEncoding(); return enc.GetBytes(s); // CML - not sure this is right //System.Text.Encoding encoding = System.Text.Encoding.GetEncoding(ws.getEncoding()); //return encoding.GetEncoder().GetBytes(s); // return s.getBytes(ws.getEncoding()); } catch (Exception e) { // fail silently return null; } }
/** * Gets a string from the data array using the character encoding for * this workbook * * @param pos The start position of the string * @param length The number of bytes to transform into a string * @param d The byte data * @param ws the workbook settings * @return the string built up from the raw bytes */ public static string getString(byte[] d,int length,int pos,WorkbookSettings ws) { if (length == 0) return string.Empty; // Reduces number of new Strings try { // convert local encoding to UTF-8 then decode them.... string encoding = ws.getEncoding(); if (encoding == null) encoding = "us-ascii"; byte [] b = Encoding.Convert(Encoding.GetEncoding(encoding),Encoding.UTF8,d,pos,length); return Encoding.UTF8.GetString(b,0,b.Length); // byte[] b = new byte[length]; // System.Array.Copy(d, pos, b, 0, length); // return new string(b, ws.getEncoding()); } catch (Exception e) { //logger.warn(e.ToString()); return string.Empty; } }
/** * A factory method which takes input an excel file and reads input the contents. * * @param inStream an open stream which inStream the the excel 97 spreadsheet to parse * @param ws the settings for the workbook * @return a workbook instance * @exception IOException * @exception BiffException */ public static Workbook getWorkbook(Stream inStream, WorkbookSettings ws) { CSharpJExcel.Jxl.Read.Biff.File dataFile = new CSharpJExcel.Jxl.Read.Biff.File(inStream,ws); Workbook workbook = new WorkbookParser(dataFile,ws); workbook.parse(); return workbook; }
/** * Called by the cell value when the cell features are added to the sheet */ public void setCell(int col, int row, ExternalSheet es, WorkbookMethods nt, WorkbookSettings ws) { // If this is part of an extended cells validation, then do nothing // as this will already have been called and parsed when the top left // cell was added if (hasExtendedCellsValidation) { return; } row1 = row; row2 = row; column1 = col; column2 = col; formula1 = new FormulaParser(formula1String, es,nt,ws, ParseContext.DATA_VALIDATION); formula1.parse(); if (formula2String != null) { formula2 = new FormulaParser(formula2String, es,nt,ws, ParseContext.DATA_VALIDATION); formula2.parse(); } }
/** * Constructor */ public DVParser(byte[] data, ExternalSheet es, WorkbookMethods nt, WorkbookSettings ws) { Assert.verify(nt != null); wasCopied = false; int options = IntegerHelper.getInt(data[0],data[1],data[2],data[3]); int typeVal = options & 0xf; type = DVType.getType(typeVal); int errorStyleVal = (options & 0x70) >> 4; errorStyle = ErrorStyle.getErrorStyle(errorStyleVal); int conditionVal = (options & 0xf00000) >> 20; condition = Condition.getCondition(conditionVal); stringListGiven = (options & STRING_LIST_GIVEN_MASK) != 0; emptyCellsAllowed = (options & EMPTY_CELLS_ALLOWED_MASK) != 0; suppressArrow = (options & SUPPRESS_ARROW_MASK) != 0; showPrompt = (options & SHOW_PROMPT_MASK) != 0; showError = (options & SHOW_ERROR_MASK) != 0; int pos = 4; int length = IntegerHelper.getInt(data[pos],data[pos + 1]); if (length > 0 && data[pos + 2] == 0) { promptTitle = StringHelper.getString(data,length,pos + 3,ws); pos += length + 3; } else if (length > 0) { promptTitle = StringHelper.getUnicodeString(data,length,pos + 3); pos += length * 2 + 3; } else { pos += 3; } length = IntegerHelper.getInt(data[pos],data[pos + 1]); if (length > 0 && data[pos + 2] == 0) { errorTitle = StringHelper.getString(data,length,pos + 3,ws); pos += length + 3; } else if (length > 0) { errorTitle = StringHelper.getUnicodeString(data,length,pos + 3); pos += length * 2 + 3; } else { pos += 3; } length = IntegerHelper.getInt(data[pos],data[pos + 1]); if (length > 0 && data[pos + 2] == 0) { promptText = StringHelper.getString(data,length,pos + 3,ws); pos += length + 3; } else if (length > 0) { promptText = StringHelper.getUnicodeString(data,length,pos + 3); pos += length * 2 + 3; } else { pos += 3; } length = IntegerHelper.getInt(data[pos],data[pos + 1]); if (length > 0 && data[pos + 2] == 0) { errorText = StringHelper.getString(data,length,pos + 3,ws); pos += length + 3; } else if (length > 0) { errorText = StringHelper.getUnicodeString(data,length,pos + 3); pos += length * 2 + 3; } else { pos += 3; } int formula1Length = IntegerHelper.getInt(data[pos],data[pos + 1]); pos += 4; int formula1Pos = pos; pos += formula1Length; int formula2Length = IntegerHelper.getInt(data[pos],data[pos + 1]); pos += 4; int formula2Pos = pos; pos += formula2Length; pos += 2; row1 = IntegerHelper.getInt(data[pos],data[pos + 1]); pos += 2; row2 = IntegerHelper.getInt(data[pos],data[pos + 1]); pos += 2; column1 = IntegerHelper.getInt(data[pos],data[pos + 1]); pos += 2; column2 = IntegerHelper.getInt(data[pos],data[pos + 1]); pos += 2; hasExtendedCellsValidation = (row1 == row2 && column1 == column2) ? false : true; // Do the formulas try { // First, create a temporary blank cell for any formula relative // references EmptyCell tmprt = new EmptyCell(column1,row1); if (formula1Length != 0) { byte[] tokens = new byte[formula1Length]; System.Array.Copy(data,formula1Pos,tokens,0,formula1Length); formula1 = new FormulaParser(tokens,tmprt,es,nt,ws, ParseContext.DATA_VALIDATION); formula1.parse(); } if (formula2Length != 0) { byte[] tokens = new byte[formula2Length]; System.Array.Copy(data,formula2Pos,tokens,0,formula2Length); formula2 = new FormulaParser(tokens,tmprt,es,nt,ws, ParseContext.DATA_VALIDATION); formula2.parse(); } } catch (FormulaException e) { //logger.warn(e.Message + " for cells " + // CellReferenceHelper.getCellReference(column1,row1) + "-" + // CellReferenceHelper.getCellReference(column2,row2)); } }
/** * Constructs this object from the raw data * * @param t the raw data * @param bt the biff type */ public XFRecord(Record t,WorkbookSettings ws,BiffType bt) : base(t) { biffType = bt; byte[] data = getRecord().getData(); fontIndex = IntegerHelper.getInt(data[0],data[1]); formatIndex = IntegerHelper.getInt(data[2],data[3]); date = false; number = false; // Compare against the date formats for (int i = 0; i < dateFormats.Length && date == false; i++) { if (formatIndex == dateFormats[i]) { date = true; dateFormat = javaDateFormats[i]; } } // Compare against the number formats for (int i = 0; i < numberFormats.Length && number == false; i++) { if (formatIndex == numberFormats[i]) { number = true; DecimalFormat df = new DecimalFormat((DecimalFormat)javaNumberFormats[i]); // TODO: CML - need to support DecimalFormatSymbols equivalent -- for now, we use the default from en-us //DecimalFormatSymbols symbols = new DecimalFormatSymbols(ws.getLocale()); //df.setDecimalFormatSymbols(symbols); numberFormat = df; //numberFormat = javaNumberFormats[i]; } } // Initialize the parent format and the type int cellAttributes = IntegerHelper.getInt(data[4],data[5]); parentFormat = (cellAttributes & 0xfff0) >> 4; int formatType = cellAttributes & 0x4; xfFormatType = formatType == 0 ? cell : style; locked = ((cellAttributes & 0x1) != 0); hidden = ((cellAttributes & 0x2) != 0); if (xfFormatType == cell && (parentFormat & 0xfff) == 0xfff) { // Something is screwy with the parent format - set to zero parentFormat = 0; //logger.warn("Invalid parent format found - ignoring"); } initialized = false; read = true; formatInfoInitialized = false; copied = false; }
private static byte[] getFile(string s,WorkbookSettings ws) { ByteArray byteArray = new ByteArray(); int pos = 0; if (s[1] == ':') { // we have a drive letter byteArray.add(msDosDriveLetter); byteArray.add((byte)s[0]); pos = 2; } else if (s[pos] == '\\' || s[pos] == '/') { byteArray.add(sameDrive); } while (s[pos] == '\\' || s[pos] == '/') { pos++; } while (pos < s.Length) { int nextSepIndex1 = s.IndexOf('/',pos); int nextSepIndex2 = s.IndexOf('\\',pos); int nextSepIndex = 0; string nextFileNameComponent = null; if (nextSepIndex1 != -1 && nextSepIndex2 != -1) { // choose the smallest (ie. nearest) separator nextSepIndex = System.Math.Min(nextSepIndex1,nextSepIndex2); } else if (nextSepIndex1 == -1 || nextSepIndex2 == -1) { // chose the maximum separator nextSepIndex = System.Math.Max(nextSepIndex1,nextSepIndex2); } if (nextSepIndex == -1) { // no more separators nextFileNameComponent = s.Substring(pos); pos = s.Length; } else { nextFileNameComponent = s.Substring(pos,nextSepIndex); pos = nextSepIndex + 1; } if (nextFileNameComponent.Equals(".")) { // current directory - do nothing } else if (nextFileNameComponent.Equals("..")) { // parent directory byteArray.add(parentDirectory); } else { // add the filename component byteArray.add(StringHelper.getBytes(nextFileNameComponent, ws)); } if (pos < s.Length) { byteArray.add(endOfSubdirectory); } } return byteArray.getBytes(); }
/** * Creates a writable workbook. When the workbook inStream closed, * it will be streamed directly to the output stream. In this * manner, a generated excel spreadsheet can be passed from * a servlet to the browser over HTTP * * @param os the output stream * @param ws the configuration for this workbook * @return the writable workbook * @exception IOException */ public static WritableWorkbook createWorkbook(Stream os, WorkbookSettings ws) { WritableWorkbook w = new WritableWorkbookImpl(os, false, ws); return w; }
/** * A factory method which takes input an excel file and reads input the contents. * * @exception IOException * @exception BiffException * @param file the excel 97 spreadsheet to parse * @param ws the settings for the workbook * @return a workbook instance */ public static Workbook getWorkbook(FileInfo file,WorkbookSettings ws) { Stream fis = new FileStream(file.FullName,FileMode.Open); // Always close down the input stream, regardless of whether or not the // file can be parsed. Thanks to Steve Hahn for this CSharpJExcel.Jxl.Read.Biff.File dataFile = null; try { dataFile = new CSharpJExcel.Jxl.Read.Biff.File(fis, ws); } catch (IOException e) { throw e; } catch (BiffException e) { throw e; } finally { fis.Close(); } Workbook workbook = new WorkbookParser(dataFile,ws); workbook.parse(); return workbook; }
/** * Creates a writable workbook. When the workbook inStream closed, * it will be streamed directly to the output stream. In this * manner, a generated excel spreadsheet can be passed from * a servlet to the browser over HTTP * * @param os the output stream * @param ws the configuration for this workbook * @return the writable workbook * @exception IOException */ public static WritableWorkbook createWorkbook(Stream os, WorkbookSettings ws) { WritableWorkbook w = new WritableWorkbookImpl(os, false, ws); return(w); }
/** * Creates a writable workbook with the given file name * * @param file the file to copy from * @param ws the global workbook settings * @return a writable workbook * @exception IOException */ public static WritableWorkbook createWorkbook(FileInfo file,WorkbookSettings ws) { Stream fos = new FileStream(file.FullName,FileMode.Create); WritableWorkbook w = new WritableWorkbookImpl(fos,true,ws); return w; }
/** * Constructor used to create writable data validations */ public DataValidation(uint objId, ExternalSheet es, WorkbookMethods wm, WorkbookSettings ws) { workbook = wm; externalSheet = es; workbookSettings = ws; validitySettings = new ArrayList(); comboBoxObjectId = objId; copied = false; }
/** * Copy constructor used to copy from read to write */ public DataValidation(DataValidation dv, ExternalSheet es, WorkbookMethods wm, WorkbookSettings ws) { workbook = wm; externalSheet = es; workbookSettings = ws; copied = true; validityList = new DataValidityListRecord(dv.getDataValidityList()); validitySettings = new ArrayList(); DataValiditySettingsRecord[] settings = dv.getDataValiditySettings(); for (int i = 0; i < settings.Length; i++) validitySettings.Add(new DataValiditySettingsRecord(settings[i],externalSheet,workbook,workbookSettings)); }
private static byte[] getURL(string s,WorkbookSettings ws) { ByteArray byteArray = new ByteArray(); byteArray.add(unencodedUrl); byteArray.add((byte)s.Length); byteArray.add(StringHelper.getBytes(s,ws)); return byteArray.getBytes(); }