public void TestProcessRecords()
{
bool[] wascalled = { false, }; // hack to pass boolean by ref into inner class
ERFListener listener = new ERFListener1(ref wascalled);
ArrayList param = new ArrayList();
param.Add(BOFRecord.sid);
EventRecordFactory factory = new EventRecordFactory(listener, param);
BOFRecord bof = new BOFRecord();
bof.Build = ((short)0);
bof.BuildYear = ((short)1999);
bof.RequiredVersion = (123);
bof.Type = (BOFRecord.TYPE_WORKBOOK);
bof.Version = ((short)0x06);
bof.HistoryBitMask = (BOFRecord.HISTORY_MASK);
EOFRecord eof = EOFRecord.instance;
byte[] bytes = new byte[bof.RecordSize + eof.RecordSize];
int offset = 0;
offset = bof.Serialize(offset, bytes);
offset = eof.Serialize(offset, bytes);
factory.ProcessRecords(new MemoryStream(bytes));
Assert.IsTrue(wascalled[0], "The record listener must be called");
}
void LoadFromStream(Stream stream)
{
stream.Seek(0, SeekOrigin.Begin);
// The easiest way to do this is to just XOR the whole file, but let's try something a bit smarter
// First read the first 4 bytes to do a prelim correctness test
byte[] buff = new byte[4];
stream.Read(buff, 0, 4);
if ((BitConverter.ToUInt32(buff, 0) ^ tpfxor) != filemagic)
{
throw new FormatException("Incorrect header");
}
EOFRecordOff = FindEOFRecord(stream);
EOFStrct = new EOFRecord(stream, EOFRecordOff);
PopulateEntries(stream);
}
/// <summary>
/// Construct the zip object. Reads in the file list and uses it to populate the entries var
/// </summary>
/// <param name="filename">The file to read in</param>
public ZipReader(String filename)
{
_filename = filename;
using (FileStream fs = new FileStream(_filename, FileMode.Open, FileAccess.Read))
{
// The easiest way to do this is to just XOR the whole file, but let's try something a bit smarter
// First read the first 4 bytes to do a prelim correctness test
byte[] buff = new byte[4];
fs.Read(buff, 0, 4);
if ((BitConverter.ToUInt32(buff, 0) ^ tpfxor) != filemagic)
{
throw new FormatException("Incorrect header");
}
EOFRecordOff = FindEOFRecord(fs);
EOFStrct = new EOFRecord(fs, EOFRecordOff);
PopulateEntries(fs);
}
}
void LoadFromStream(Stream stream)
{
stream.Seek(0, SeekOrigin.Begin);
// The easiest way to do this is to just XOR the whole file, but let's try something a bit smarter
// First read the first 4 bytes to do a prelim correctness test
byte[] buff = new byte[4];
stream.Read(buff, 0, 4);
if ((BitConverter.ToUInt32(buff, 0) ^ tpfxor) != filemagic)
throw new FormatException("Incorrect header");
EOFRecordOff = FindEOFRecord(stream);
EOFStrct = new EOFRecord(stream, EOFRecordOff);
PopulateEntries(stream);
}
/**
* Writes out this sheet to the output file. First it writes out
* the standard workbook information required by excel, before calling
* the write method on each sheet individually
*
* @exception IOException
*/
public override void write()
{
// Perform some preliminary sheet check before we start writing out
// the workbook
WritableSheetImpl wsi = null;
for (int i = 0; i < getNumberOfSheets(); i++)
{
wsi = (WritableSheetImpl)getSheet(i);
// Check the merged records. This has to be done before the
// globals are written out because some more XF formats might be created
wsi.checkMergedBorders();
// Check to see if there are any predefined names
Range range = wsi.getSettings().getPrintArea();
if (range != null)
{
addNameArea(BuiltInName.PRINT_AREA,
wsi,
range.getTopLeft().getColumn(),
range.getTopLeft().getRow(),
range.getBottomRight().getColumn(),
range.getBottomRight().getRow(),
false);
}
// Check to see if print titles by row were set
Range rangeR = wsi.getSettings().getPrintTitlesRow();
Range rangeC = wsi.getSettings().getPrintTitlesCol();
if (rangeR != null && rangeC != null)
{
addNameArea(BuiltInName.PRINT_TITLES,
wsi,
rangeR.getTopLeft().getColumn(),
rangeR.getTopLeft().getRow(),
rangeR.getBottomRight().getColumn(),
rangeR.getBottomRight().getRow(),
rangeC.getTopLeft().getColumn(),
rangeC.getTopLeft().getRow(),
rangeC.getBottomRight().getColumn(),
rangeC.getBottomRight().getRow(),
false);
}
// Check to see if print titles by row were set
else if (rangeR != null)
{
addNameArea(BuiltInName.PRINT_TITLES,
wsi,
rangeR.getTopLeft().getColumn(),
rangeR.getTopLeft().getRow(),
rangeR.getBottomRight().getColumn(),
rangeR.getBottomRight().getRow(),
false);
}
// Check to see if print titles by column were set
else if (rangeC != null)
{
addNameArea(BuiltInName.PRINT_TITLES,
wsi,
rangeC.getTopLeft().getColumn(),
rangeC.getTopLeft().getRow(),
rangeC.getBottomRight().getColumn(),
rangeC.getBottomRight().getRow(),
false);
}
}
// Rationalize all the XF and number formats
if (!settings.getRationalizationDisabled())
rationalize();
// Write the workbook globals
BOFRecord bof = new BOFRecord(BOFRecord.workbookGlobals);
outputFile.write(bof);
// Must immediatly follow the BOF record
if (settings.getTemplate())
{
// Only write record if we are a template
TemplateRecord trec = new TemplateRecord();
outputFile.write(trec);
}
InterfaceHeaderRecord ihr = new InterfaceHeaderRecord();
outputFile.write(ihr);
MMSRecord mms = new MMSRecord(0, 0);
outputFile.write(mms);
InterfaceEndRecord ier = new InterfaceEndRecord();
outputFile.write(ier);
WriteAccessRecord wr = new WriteAccessRecord(settings.getWriteAccess());
outputFile.write(wr);
CodepageRecord cp = new CodepageRecord();
outputFile.write(cp);
DSFRecord dsf = new DSFRecord();
outputFile.write(dsf);
if (settings.getExcel9File())
{
// Only write record if we are a template
// We are not excel 2000, should we still set the flag
Excel9FileRecord e9rec = new Excel9FileRecord();
outputFile.write(e9rec);
}
TabIdRecord tabid = new TabIdRecord(getNumberOfSheets());
outputFile.write(tabid);
if (containsMacros)
{
ObjProjRecord objproj = new ObjProjRecord();
outputFile.write(objproj);
}
if (buttonPropertySet != null)
outputFile.write(buttonPropertySet);
FunctionGroupCountRecord fgcr = new FunctionGroupCountRecord();
outputFile.write(fgcr);
// do not support password protected workbooks
WindowProtectRecord wpr = new WindowProtectRecord
(settings.getWindowProtected());
outputFile.write(wpr);
ProtectRecord pr = new ProtectRecord(wbProtected);
outputFile.write(pr);
PasswordRecord pw = new PasswordRecord(null);
outputFile.write(pw);
Prot4RevRecord p4r = new Prot4RevRecord(false);
outputFile.write(p4r);
Prot4RevPassRecord p4rp = new Prot4RevPassRecord();
outputFile.write(p4rp);
// If no sheet is identified as being selected, then select
// the first one
bool sheetSelected = false;
WritableSheetImpl wsheet = null;
int selectedSheetIndex = 0;
for (int i = 0; i < getNumberOfSheets() && !sheetSelected; i++)
{
wsheet = (WritableSheetImpl)getSheet(i);
if (wsheet.getSettings().isSelected())
{
sheetSelected = true;
selectedSheetIndex = i;
}
}
if (!sheetSelected)
{
wsheet = (WritableSheetImpl)getSheet(0);
wsheet.getSettings().setSelected(true);
selectedSheetIndex = 0;
}
Window1Record w1r = new Window1Record(selectedSheetIndex);
outputFile.write(w1r);
BackupRecord bkr = new BackupRecord(false);
outputFile.write(bkr);
HideobjRecord ho = new HideobjRecord(settings.getHideobj());
outputFile.write(ho);
NineteenFourRecord nf = new NineteenFourRecord(false);
outputFile.write(nf);
PrecisionRecord pc = new PrecisionRecord(false);
outputFile.write(pc);
RefreshAllRecord rar = new RefreshAllRecord(settings.getRefreshAll());
outputFile.write(rar);
BookboolRecord bb = new BookboolRecord(true);
outputFile.write(bb);
// Write out all the fonts used
fonts.write(outputFile);
// Write out the cell formats used within this workbook
formatRecords.write(outputFile);
// Write out the palette, if it exists
if (formatRecords.getPalette() != null)
outputFile.write(formatRecords.getPalette());
// Write out the uses elfs record
UsesElfsRecord uer = new UsesElfsRecord();
outputFile.write(uer);
// Write out the boundsheet records. Keep a handle to each one's
// position so we can write in the stream offset later
int[] boundsheetPos = new int[getNumberOfSheets()];
Sheet sheet = null;
for (int i = 0; i < getNumberOfSheets(); i++)
{
boundsheetPos[i] = outputFile.getPos();
sheet = getSheet(i);
BoundsheetRecord br = new BoundsheetRecord(sheet.getName());
if (sheet.getSettings().isHidden())
br.setHidden();
if (((WritableSheetImpl)sheets[i]).isChartOnly())
br.setChartOnly();
outputFile.write(br);
}
if (countryRecord == null)
{
CountryCode lang = CountryCode.getCountryCode(settings.getExcelDisplayLanguage());
if (lang == CountryCode.UNKNOWN)
{
//logger.warn("Unknown country code " +
// settings.getExcelDisplayLanguage() +
// " using " + CountryCode.USA.getCode());
lang = CountryCode.USA;
}
CountryCode region = CountryCode.getCountryCode(settings.getExcelRegionalSettings());
countryRecord = new CountryRecord(lang, region);
if (region == CountryCode.UNKNOWN)
{
//logger.warn("Unknown country code " +
// settings.getExcelDisplayLanguage() +
// " using " + CountryCode.UK.getCode());
region = CountryCode.UK;
}
}
outputFile.write(countryRecord);
// Write out the names of any add in functions
if (addInFunctionNames != null && addInFunctionNames.Length > 0)
{
// Write out the supbook record
// SupbookRecord supbook = new SupbookRecord();
// outputFile.write(supbook);
for (int i = 0; i < addInFunctionNames.Length; i++)
{
ExternalNameRecord enr = new ExternalNameRecord(addInFunctionNames[i]);
outputFile.write(enr);
}
}
if (xctRecords != null)
{
for (int i = 0; i < xctRecords.Length; i++)
outputFile.write(xctRecords[i]);
}
// Write out the external sheet record, if it exists
if (externSheet != null)
{
//Write out all the supbook records
for (int i = 0; i < supbooks.Count; i++)
{
SupbookRecord supbook = (SupbookRecord)supbooks[i];
outputFile.write(supbook);
}
outputFile.write(externSheet);
}
// Write out the names, if any exists
if (names != null)
{
for (int i = 0; i < names.Count; i++)
{
NameRecord n = (NameRecord)names[i];
outputFile.write(n);
}
}
// Write out the mso drawing group, if it exists
if (drawingGroup != null)
drawingGroup.write(outputFile);
sharedStrings.write(outputFile);
EOFRecord eof = new EOFRecord();
outputFile.write(eof);
// Write out the sheets
for (int i = 0; i < getNumberOfSheets(); i++)
{
// first go back and modify the offset we wrote out for the
// boundsheet record
outputFile.setData(IntegerHelper.getFourBytes(outputFile.getPos()),boundsheetPos[i] + 4);
wsheet = (WritableSheetImpl)getSheet(i);
wsheet.write();
}
}
/// <summary>
/// Construct the zip object. Reads in the file list and uses it to populate the entries var
/// </summary>
/// <param name="filename">The file to read in</param>
public ZipReader(String filename)
{
_filename = filename;
using (FileStream fs = new FileStream(_filename, FileMode.Open, FileAccess.Read))
{
// The easiest way to do this is to just XOR the whole file, but let's try something a bit smarter
// First read the first 4 bytes to do a prelim correctness test
byte[] buff = new byte[4];
fs.Read(buff, 0, 4);
if ((BitConverter.ToUInt32(buff, 0) ^ tpfxor) != filemagic)
throw new FormatException("Incorrect header");
EOFRecordOff = FindEOFRecord(fs);
EOFStrct = new EOFRecord(fs, EOFRecordOff);
PopulateEntries(fs);
}
}
/// <summary>
/// 获取旧版excel数据至datatable中
/// </summary>
/// <param name="fullfilename"></param>
/// <param name="headerRowIndex"></param>
/// <returns></returns>
public static DataTable GetDataOnSheet(string fullfilename, int headerRowIndex)
{
DataTable dt = new DataTable();
dt.TableName = Path.GetFileNameWithoutExtension(fullfilename);
using (var stream = new FileStream(fullfilename, FileMode.Open, FileAccess.Read, FileShare.ReadWrite))
{
using (var ris = new RecordInputStream(stream))
{
BOFRecordType fileType;
int version;
while (ris.HasNextRecord)
{
int sid = ris.GetNextSid();
ris.NextRecord();
switch (sid)
{
case BOFRecord.biff2_sid:
//文件头
{
BOFRecord record = new BOFRecord(ris);
fileType = record.Type;
version = record.Version;
}
break;
case EOFRecord.sid:
//文件尾
{
EOFRecord record = new EOFRecord(ris);
}
break;
case 30: //0x1E
{
//FORMAT —— 仅biff2和biff3中有效
//FormatRecord
ris.ReadFully(new byte[ris.Remaining]);
}
break;
case ColumnWidthRecord.biff2_sid: // 36://0x24
{
//COLWIDTH —— 仅biff2中有效
ColumnWidthRecord record = new ColumnWidthRecord(ris);
//ris.ReadFully(new byte[ris.Remaining]);
int columnCount = record.LastColumnIndex - record.FirstColumnIndex;
for (int i = 0; i < columnCount; i++)
{
DataColumn dc = new DataColumn();
dt.Columns.Add(dc);
}
}
break;
case FontRecord.sid: //0x31
{
//FontRecord biff2中与biff5之后版本不一样
ris.ReadFully(new byte[ris.Remaining]);
}
break;
// 0x36 TABLEOP —— 仅biff2中有效
//Top left cell of a multiple operations table
case PrintGridlinesRecord.sid:
//行结束 - 换行 [todo 确认，只有列头结束才有]
{
PrintGridlinesRecord record = new PrintGridlinesRecord(ris);
}
break;
case OldNumberRecord.biff2_sid:
{
OldNumberRecord record = new OldNumberRecord(ris);
int dtRowIndex = record.Row - headerRowIndex - 1;
if (dt.Rows.Count > dtRowIndex)
{
dt.Rows[dtRowIndex][record.Column] = record.Value;
}
else
{
DataRow dr = dt.NewRow();
dt.Rows.Add(dr);
dr[record.Column] = record.Value;
}
}
break;
case OldLabelRecord.biff2_sid:
{
OldLabelRecord record = new OldLabelRecord(ris);
record.SetCodePage(new CodepageRecord()
{
Codepage = (short)CodePageUtil.CP_GBK
});
if (record.Row == headerRowIndex)
{
//列头
//DataColumn dc = new DataColumn(record.Value);
//dt.Columns.Add(dc);
dt.Columns[record.Column].ColumnName = record.Value;
}
else if (record.Row > headerRowIndex)
{
int dtRowIndex = record.Row - headerRowIndex - 1;
if (dt.Rows.Count > dtRowIndex)
{
dt.Rows[dtRowIndex][record.Column] = record.Value;
}
else
{
DataRow dr = dt.NewRow();
dt.Rows.Add(dr);
dr[record.Column] = record.Value;
}
}
}
break;
case OldFormulaRecord.biff2_sid:
{
OldFormulaRecord record = new OldFormulaRecord(ris);
var value = record.Value;
}
break;
case OldStringRecord.biff2_sid:
{
OldStringRecord record = new OldStringRecord(ris);
record.SetCodePage(new CodepageRecord()
{
Codepage = (short)CodePageUtil.CP_GBK
});
var value = record.GetString();
}
break;
default:
ris.ReadFully(new byte[ris.Remaining]);
break;
}
}
}
}
return(dt);
}
public static List <T> GetObjDataOnSheet <T>(string fullfilename, int headerRowIndex)
{
List <T> dt = new List <T>();
PropertyInfo[] props = typeof(T).GetProperties();
Dictionary <string, string> pairs = new Dictionary <string, string>();
foreach (var item in props)
{
var ignoreattrs = item.GetCustomAttributes(typeof(ColumnIgnoreAttribute), false).Cast <ColumnIgnoreAttribute>();
if (ignoreattrs.Count() > 0)
{
continue;
}
string key = item.Name;
string value = key;
var attrs = item.GetCustomAttributes(typeof(ColumnHeaderAttribute), false).Cast <ColumnHeaderAttribute>();
if (attrs.Count() > 0)
{
value = attrs.First().Name;
}
pairs.Add(key, value);
}
Dictionary <int, string> indexes = new Dictionary <int, string>();
using (var stream = new FileStream(fullfilename, FileMode.Open, FileAccess.Read, FileShare.ReadWrite))
{
using (var ris = new RecordInputStream(stream))
{
BOFRecordType fileType;
int version;
while (ris.HasNextRecord)
{
int sid = ris.GetNextSid();
ris.NextRecord();
switch (sid)
{
case BOFRecord.biff2_sid:
//文件头
{
BOFRecord record = new BOFRecord(ris);
fileType = record.Type;
version = record.Version;
}
break;
case EOFRecord.sid:
//文件尾
{
EOFRecord record = new EOFRecord(ris);
}
break;
case 30: //0x1E
{
//FORMAT —— 仅biff2和biff3中有效
//FormatRecord
ris.ReadFully(new byte[ris.Remaining]);
}
break;
case ColumnWidthRecord.biff2_sid: // 36://0x24
{
//COLWIDTH —— 仅biff2中有效
ColumnWidthRecord record = new ColumnWidthRecord(ris);
//ris.ReadFully(new byte[ris.Remaining]);
//int columnCount = record.LastColumnIndex - record.FirstColumnIndex;
//for (int i = 0; i < columnCount; i++)
//{
// DataColumn dc = new DataColumn();
// dt.Columns.Add(dc);
//}
}
break;
case FontRecord.sid: //0x31
{
//FontRecord biff2中与biff5之后版本不一样
ris.ReadFully(new byte[ris.Remaining]);
}
break;
// 0x36 TABLEOP —— 仅biff2中有效
//Top left cell of a multiple operations table
case PrintGridlinesRecord.sid:
//行结束 - 换行 [todo 确认，只有列头结束才有]
{
PrintGridlinesRecord record = new PrintGridlinesRecord(ris);
}
break;
case OldNumberRecord.biff2_sid:
{
OldNumberRecord record = new OldNumberRecord(ris);
if (!indexes.ContainsKey(record.Column))
{
break;
}
int dtRowIndex = record.Row - headerRowIndex - 1;
if (dt.Count() > dtRowIndex)
{
//dt[dtRowIndex][record.Column] = record.Value;
T dataRow = dt[dtRowIndex];
var propsel = from c in props where c.Name.ToLower().Equals(indexes[record.Column].ToLower()) select c;
if (propsel == null || propsel.Count() <= 0)
{
continue;
}
PropertyInfo prop = propsel.FirstOrDefault();
prop.SetValue(dataRow, record.Value, null);
}
else
{
T dataRow = System.Activator.CreateInstance <T>();
dt.Add(dataRow);
//DataRow dr = dt.NewRow();
//dt.Rows.Add(dr);
//dr[record.Column] = record.Value;
var propsel = from c in props where c.Name.ToLower().Equals(indexes[record.Column].ToLower()) select c;
if (propsel == null || propsel.Count() <= 0)
{
continue;
}
PropertyInfo prop = propsel.FirstOrDefault();
prop.SetValue(dataRow, record.Value, null);
}
}
break;
case OldLabelRecord.biff2_sid:
{
OldLabelRecord record = new OldLabelRecord(ris);
record.SetCodePage(new CodepageRecord()
{
Codepage = (short)CodePageUtil.CP_GBK
});
if (record.Row == headerRowIndex)
{
//列头
//DataColumn dc = new DataColumn(record.Value);
//dt.Columns.Add(dc);
//dt.Columns[record.Column].ColumnName = ;
string hearName = record.Value;
var pair = pairs.Where(p => p.Value == hearName);
if (pair.Count() > 0)
{
indexes.Add(record.Column, pair.First().Key);
}
}
else if (record.Row > headerRowIndex)
{
if (!indexes.ContainsKey(record.Column))
{
break;
}
int dtRowIndex = record.Row - headerRowIndex - 1;
if (dt.Count > dtRowIndex)
{
//dt.Rows[dtRowIndex][record.Column] = record.Value;
T dataRow = dt[dtRowIndex];
var propsel = from c in props where c.Name.ToLower().Equals(indexes[record.Column].ToLower()) select c;
if (propsel == null || propsel.Count() <= 0)
{
continue;
}
PropertyInfo prop = propsel.FirstOrDefault();
prop.SetValue(dataRow, record.Value, null);
}
else
{
T dataRow = System.Activator.CreateInstance <T>();
dt.Add(dataRow);
//DataRow dr = dt.NewRow();
//dt.Rows.Add(dr);
//dr[record.Column] = record.Value;
var propsel = from c in props where c.Name.ToLower().Equals(indexes[record.Column].ToLower()) select c;
if (propsel == null || propsel.Count() <= 0)
{
continue;
}
PropertyInfo prop = propsel.FirstOrDefault();
prop.SetValue(dataRow, record.Value, null);
}
}
}
break;
case OldFormulaRecord.biff2_sid:
{
OldFormulaRecord record = new OldFormulaRecord(ris);
var value = record.Value;
}
break;
case OldStringRecord.biff2_sid:
{
OldStringRecord record = new OldStringRecord(ris);
record.SetCodePage(new CodepageRecord()
{
Codepage = (short)CodePageUtil.CP_GBK
});
var value = record.GetString();
}
break;
default:
ris.ReadFully(new byte[ris.Remaining]);
break;
}
}
}
}
return(dt);
}
/**
* Writes out this sheet. First writes out the standard sheet
* information then writes out each row in turn.
* Once all the rows have been written out, it retrospectively adjusts
* the offset references in the file
*
* @exception IOException
*/
public void write()
{
Assert.verify(rows != null);
// This worksheet consists of just one chart, so write it and return
if (chartOnly)
{
drawingWriter.write(outputFile);
return;
}
BOFRecord bof = new BOFRecord(BOFRecord.sheet);
outputFile.write(bof);
// Compute the number of blocks of 32 rows that will be needed
int numBlocks = numRows / 32;
if (numRows - numBlocks * 32 != 0)
numBlocks++;
int indexPos = outputFile.getPos();
// Write the index record out now in order to serve as a place holder
// The bof passed in is the bof of the workbook, not this sheet
IndexRecord indexRecord = new IndexRecord(0, numRows, numBlocks);
outputFile.write(indexRecord);
if (settings.getAutomaticFormulaCalculation())
{
CalcModeRecord cmr = new CalcModeRecord(CalcModeRecord.automatic);
outputFile.write(cmr);
}
else
{
CalcModeRecord cmr = new CalcModeRecord(CalcModeRecord.manual);
outputFile.write(cmr);
}
CalcCountRecord ccr = new CalcCountRecord(0x64);
outputFile.write(ccr);
RefModeRecord rmr = new RefModeRecord();
outputFile.write(rmr);
IterationRecord itr = new IterationRecord(false);
outputFile.write(itr);
DeltaRecord dtr = new DeltaRecord(0.001);
outputFile.write(dtr);
SaveRecalcRecord srr = new SaveRecalcRecord(settings.getRecalculateFormulasBeforeSave());
outputFile.write(srr);
PrintHeadersRecord phr = new PrintHeadersRecord(settings.getPrintHeaders());
outputFile.write(phr);
PrintGridLinesRecord pglr = new PrintGridLinesRecord(settings.getPrintGridLines());
outputFile.write(pglr);
GridSetRecord gsr = new GridSetRecord(true);
outputFile.write(gsr);
GuttersRecord gutr = new GuttersRecord();
gutr.setMaxColumnOutline(maxColumnOutlineLevel + 1);
gutr.setMaxRowOutline(maxRowOutlineLevel + 1);
outputFile.write(gutr);
DefaultRowHeightRecord drhr = new DefaultRowHeightRecord
(settings.getDefaultRowHeight(),
settings.getDefaultRowHeight() !=
SheetSettings.DEFAULT_DEFAULT_ROW_HEIGHT);
outputFile.write(drhr);
if (maxRowOutlineLevel > 0)
workspaceOptions.setRowOutlines(true);
if (maxColumnOutlineLevel > 0)
workspaceOptions.setColumnOutlines(true);
workspaceOptions.setFitToPages(settings.getFitToPages());
outputFile.write(workspaceOptions);
if (rowBreaks.Count > 0)
{
int[] rb = new int[rowBreaks.Count];
for (int i = 0; i < rb.Length; i++)
rb[i] = (int)rowBreaks[i];
HorizontalPageBreaksRecord hpbr = new HorizontalPageBreaksRecord(rb);
outputFile.write(hpbr);
}
if (columnBreaks.Count > 0)
{
int[] rb = new int[columnBreaks.Count];
for (int i = 0; i < rb.Length; i++)
rb[i] = (int)columnBreaks[i];
VerticalPageBreaksRecord hpbr = new VerticalPageBreaksRecord(rb);
outputFile.write(hpbr);
}
HeaderRecord header = new HeaderRecord(settings.getHeader().ToString());
outputFile.write(header);
FooterRecord footer = new FooterRecord(settings.getFooter().ToString());
outputFile.write(footer);
HorizontalCentreRecord hcr = new HorizontalCentreRecord(settings.isHorizontalCentre());
outputFile.write(hcr);
VerticalCentreRecord vcr = new VerticalCentreRecord(settings.isVerticalCentre());
outputFile.write(vcr);
// Write out the margins if they don't equal the default
if (settings.getLeftMargin() != settings.getDefaultWidthMargin())
{
MarginRecord mr = new LeftMarginRecord(settings.getLeftMargin());
outputFile.write(mr);
}
if (settings.getRightMargin() != settings.getDefaultWidthMargin())
{
MarginRecord mr = new RightMarginRecord(settings.getRightMargin());
outputFile.write(mr);
}
if (settings.getTopMargin() != settings.getDefaultHeightMargin())
{
MarginRecord mr = new TopMarginRecord(settings.getTopMargin());
outputFile.write(mr);
}
if (settings.getBottomMargin() != settings.getDefaultHeightMargin())
{
MarginRecord mr = new BottomMarginRecord(settings.getBottomMargin());
outputFile.write(mr);
}
if (plsRecord != null)
outputFile.write(plsRecord);
SetupRecord setup = new SetupRecord(settings);
outputFile.write(setup);
if (settings.isProtected())
{
ProtectRecord pr = new ProtectRecord(settings.isProtected());
outputFile.write(pr);
ScenarioProtectRecord spr = new ScenarioProtectRecord(settings.isProtected());
outputFile.write(spr);
ObjectProtectRecord opr = new ObjectProtectRecord(settings.isProtected());
outputFile.write(opr);
if (settings.getPassword() != null)
{
PasswordRecord pw = new PasswordRecord(settings.getPassword());
outputFile.write(pw);
}
else if (settings.getPasswordHash() != 0)
{
PasswordRecord pw = new PasswordRecord(settings.getPasswordHash());
outputFile.write(pw);
}
}
indexRecord.setDataStartPosition(outputFile.getPos());
DefaultColumnWidth dcw = new DefaultColumnWidth(settings.getDefaultColumnWidth());
outputFile.write(dcw);
// Get a handle to the normal styles
WritableCellFormat normalStyle = sheet.getWorkbook().getStyles().getNormalStyle();
WritableCellFormat defaultDateFormat = sheet.getWorkbook().getStyles().getDefaultDateFormat();
// Write out all the column formats
foreach (ColumnInfoRecord cir in columnFormats)
{
// Writing out the column info with index 0x100 causes excel to crash
if (cir.getColumn() < 0x100)
outputFile.write(cir);
XFRecord xfr = cir.getCellFormat();
if (xfr != normalStyle && cir.getColumn() < 0x100)
{
// Make this the format for every cell in the column
Cell[] cells = getColumn(cir.getColumn());
for (int i = 0; i < cells.Length; i++)
{
if (cells[i] != null &&
(cells[i].getCellFormat() == normalStyle ||
cells[i].getCellFormat() == defaultDateFormat))
{
// The cell has no overriding format specified, so
// set it to the column default
((WritableCell)cells[i]).setCellFormat(xfr);
}
}
}
}
// Write out the auto filter
if (autoFilter != null)
autoFilter.write(outputFile);
DimensionRecord dr = new DimensionRecord(numRows, numCols);
outputFile.write(dr);
// Write out all the rows, in blocks of 32
for (int block = 0; block < numBlocks; block++)
{
DBCellRecord dbcell = new DBCellRecord(outputFile.getPos());
int blockRows = System.Math.Min(32, numRows - block * 32);
bool firstRow = true;
// First write out all the row records
for (int i = block * 32; i < block * 32 + blockRows; i++)
{
if (rows[i] != null)
{
rows[i].write(outputFile);
if (firstRow)
{
dbcell.setCellOffset(outputFile.getPos());
firstRow = false;
}
}
}
// Now write out all the cells
for (int i = block * 32; i < block * 32 + blockRows; i++)
{
if (rows[i] != null)
{
dbcell.addCellRowPosition(outputFile.getPos());
rows[i].writeCells(outputFile);
}
}
// Now set the current file position in the index record
indexRecord.addBlockPosition(outputFile.getPos());
// Set the position of the file pointer and write out the DBCell
// record
dbcell.setPosition(outputFile.getPos());
outputFile.write(dbcell);
}
// Do the drawings and charts if enabled
if (!workbookSettings.getDrawingsDisabled())
drawingWriter.write(outputFile);
Window2Record w2r = new Window2Record(settings);
outputFile.write(w2r);
// Handle the frozen panes
if (settings.getHorizontalFreeze() != 0 || settings.getVerticalFreeze() != 0)
{
PaneRecord pr = new PaneRecord(settings.getHorizontalFreeze(),settings.getVerticalFreeze());
outputFile.write(pr);
// Handle the selection record. First, there will always be a top left
SelectionRecord sr = new SelectionRecord(SelectionRecord.upperLeft, 0, 0);
outputFile.write(sr);
// Top right
if (settings.getHorizontalFreeze() != 0)
{
sr = new SelectionRecord(SelectionRecord.upperRight, settings.getHorizontalFreeze(), 0);
outputFile.write(sr);
}
// Bottom left
if (settings.getVerticalFreeze() != 0)
{
sr = new SelectionRecord(SelectionRecord.lowerLeft, 0, settings.getVerticalFreeze());
outputFile.write(sr);
}
// Bottom right
if (settings.getHorizontalFreeze() != 0 &&
settings.getVerticalFreeze() != 0)
{
sr = new SelectionRecord(SelectionRecord.lowerRight,settings.getHorizontalFreeze(),settings.getVerticalFreeze());
outputFile.write(sr);
}
Weird1Record w1r = new Weird1Record();
outputFile.write(w1r);
}
else
{
// No frozen panes - just write out the selection record for the
// whole sheet
SelectionRecord sr = new SelectionRecord(SelectionRecord.upperLeft, 0, 0);
outputFile.write(sr);
}
// Handle the zoom factor
if (settings.getZoomFactor() != 100)
{
SCLRecord sclr = new SCLRecord(settings.getZoomFactor());
outputFile.write(sclr);
}
// Now write out all the merged cells
mergedCells.write(outputFile);
// Write out all the hyperlinks
foreach (WritableHyperlink hlr in hyperlinks)
outputFile.write(hlr);
if (buttonPropertySet != null)
outputFile.write(buttonPropertySet);
// Write out the data validations
if (dataValidation != null || validatedCells.Count > 0)
writeDataValidation();
// Write out the conditional formats
if (conditionalFormats != null && conditionalFormats.Count > 0)
{
foreach (ConditionalFormat cf in conditionalFormats)
cf.write(outputFile);
}
EOFRecord eof = new EOFRecord();
outputFile.write(eof);
// Now the various cross reference offsets have been calculated,
// retrospectively set the values in the output file
outputFile.setData(indexRecord.getData(), indexPos + 4);
}