/**
* 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();
}
