public void FontUnderlineNameRotation()
{
string newFont = "Times New Roman";
short newRotation = 45;
XlsDocumentDelegate docDelegate = delegate(XlsDocument doc)
{
Worksheet sheet = doc.Workbook.Worksheets.Add("Sheet1");
Cell cell = sheet.Cells.Add(1, 1, "test");
Assert.AreEqual(UnderlineTypes.Default, cell.Font.Underline,
"Old underline type");
Assert.AreEqual("Arial", cell.Font.FontName, "Old font name");
Assert.AreEqual(0, cell.Rotation, "Old cell rotation");
cell.Font.FontName = newFont;
Assert.AreEqual(newFont, cell.Font.FontName, "New font name");
cell.Font.Underline = UnderlineTypes.Double;
Assert.AreEqual(UnderlineTypes.Double, cell.Font.Underline,
"New underline type");
Assert.AreEqual(newFont, cell.Font.FontName, "New font name");
cell.Rotation = newRotation;
Assert.AreEqual(45, cell.Rotation, "New cell rotation");
Assert.AreEqual(UnderlineTypes.Double, cell.Font.Underline,
"New underline type");
Assert.AreEqual(newFont, cell.Font.FontName, "New font name");
};
string fileName = WriteDocument(docDelegate);
AssertPropertyViaExcelOle(1, 1, 1, fileName, CellProperties.Font_Name, newFont, "Font name");
AssertPropertyViaExcelOle(1, 1, 1, fileName, CellProperties.Font_Underline, ((int)ExcelUnderlineStyles.Double).ToString(), "Font underline type");
AssertPropertyViaExcelOle(1, 1, 1, fileName, CellProperties.Rotation, newRotation.ToString(),
"Cell Rotation");
}
public void MergeA1toC1andB2toC2()
{
XlsDocumentDelegate docDelegate = delegate(XlsDocument doc)
{
Worksheet sheet = doc.Workbook.Worksheets.Add("Sheet1");
sheet.Cells.Add(1, 1, "ValA1");
sheet.Cells.Add(1, 2, "ValB1");
sheet.Cells.Add(1, 3, "ValC1");
sheet.Cells.Add(2, 1, "ValA2");
sheet.Cells.Add(2, 2, "ValB2");
sheet.Cells.Add(2, 3, "ValC2");
sheet.Cells.Merge(1, 1, 1, 3);
sheet.Cells.Merge(2, 2, 2, 3);
};
string fileName = WriteDocument(docDelegate);
AssertPropertyViaExcelOle(1, 1, 1, fileName, CellProperties.MergeAreaCount, (3).ToString(),
"A1 should have MergeArea with 3 cells");
AssertPropertyViaExcelOle(1, 1, 2, fileName, CellProperties.MergeAreaCount, (3).ToString(),
"B1 should have MergeArea with 3 cells");
AssertPropertyViaExcelOle(1, 1, 3, fileName, CellProperties.MergeAreaCount, (3).ToString(),
"C1 should have MergeArea with 3 cells");
AssertPropertyViaExcelOle(1, 2, 1, fileName, CellProperties.MergeAreaCount, (1).ToString(),
"A2 should have MergeArea with 1 cells");
AssertPropertyViaExcelOle(1, 2, 2, fileName, CellProperties.MergeAreaCount, (2).ToString(),
"B2 should have MergeArea with 2 cells");
AssertPropertyViaExcelOle(1, 2, 3, fileName, CellProperties.MergeAreaCount, (2).ToString(),
"C2 should have MergeArea with 2 cells");
}
public void FontUnderlineStyles()
{
XlsDocumentDelegate docDelegate = delegate(XlsDocument doc)
{
Worksheet sheet = doc.Workbook.Worksheets.Add("Sheet1");
Assert.AreEqual(17, doc.Workbook.XFs.Count, "XF count before setting UnderlineSingle");
sheet.Cells.Add(1, 1, "test").Font.Underline = UnderlineTypes.Single;
Assert.AreEqual(18, doc.Workbook.XFs.Count, "XF count after setting UnderlineSingle");
sheet.Cells.Add(1, 2, "test").Font.Underline = UnderlineTypes.SingleAccounting;
Assert.AreEqual(19, doc.Workbook.XFs.Count, "XF count after setting UnderlineSingle");
sheet.Cells.Add(1, 3, "test").Font.Underline = UnderlineTypes.Double;
Assert.AreEqual(20, doc.Workbook.XFs.Count, "XF count after setting UnderlineDouble");
sheet.Cells.Add(1, 4, "test").Font.Underline = UnderlineTypes.DoubleAccounting;
Assert.AreEqual(21, doc.Workbook.XFs.Count, "XF count after setting UnderlineDoubleAccounting");
sheet.Cells.Add(1, 5, "test").Font.Underline = UnderlineTypes.None;
Assert.AreEqual(21, doc.Workbook.XFs.Count, "XF count after setting UnderlineNone");
sheet.Cells.Add(1, 6, "test");
Assert.AreEqual(21, doc.Workbook.XFs.Count, "XF count after setting no formatting");
};
string fileName = WriteDocument(docDelegate);
AssertPropertyViaExcelOle(1, 1, 1, fileName, CellProperties.Font_Underline, ExcelUnderlineStyles.Single.ToString(), "Font is UnderlineSingle");
AssertPropertyViaExcelOle(1, 1, 2, fileName, CellProperties.Font_Underline, ExcelUnderlineStyles.SingleAccounting.ToString(), "Font is UnderlineSingleAccounting");
AssertPropertyViaExcelOle(1, 1, 3, fileName, CellProperties.Font_Underline, ExcelUnderlineStyles.Double.ToString(), "Font is UnderlineDouble");
AssertPropertyViaExcelOle(1, 1, 4, fileName, CellProperties.Font_Underline, ExcelUnderlineStyles.DoubleAccounting.ToString(), "Font is UnderlineDoubleAccounting");
AssertPropertyViaExcelOle(1, 1, 5, fileName, CellProperties.Font_Underline, ExcelUnderlineStyles.None.ToString(), "Font is UnderlineNone");
AssertPropertyViaExcelOle(1, 1, 6, fileName, CellProperties.Font_Underline, ExcelUnderlineStyles.None.ToString(), "Font is UnderlineNone");
}
public void SetPatternBackgroundColor()
{
//http://www.mvps.org/dmcritchie/excel/colors.htm
XlsDocument theDoc = null;
Color colorA1 = Colors.Red;
Color colorA2 = Colors.Green;
Color colorA3 = Colors.Blue;
XlsDocumentDelegate docDelegate = delegate(XlsDocument doc)
{
theDoc = doc;
Worksheet sheet = doc.Workbook.Worksheets.Add("Sheet1");
Cell cell;
cell = sheet.Cells.Add(1, 1, "red");
cell.Pattern = FillPattern.Percent50;
cell.PatternBackgroundColor = colorA1;
cell = sheet.Cells.Add(1, 2, "green");
cell.Pattern = FillPattern.Percent50;
cell.PatternBackgroundColor = colorA2;
cell = sheet.Cells.Add(1, 3, "blue");
cell.Pattern = FillPattern.Percent50;
cell.PatternBackgroundColor = colorA3;
};
string fileName = WriteDocument(docDelegate);
AssertPropertyViaExcelOle(1, 1, 1, fileName, CellProperties.PatternBackgroundColor, GetExcelVbaColorIndex(theDoc, colorA1).ToString(),
"Cell A1 should be red");
AssertPropertyViaExcelOle(1, 1, 2, fileName, CellProperties.PatternBackgroundColor, GetExcelVbaColorIndex(theDoc, colorA2).ToString(),
"Cell A2 should be green");
AssertPropertyViaExcelOle(1, 1, 3, fileName, CellProperties.PatternBackgroundColor, GetExcelVbaColorIndex(theDoc, colorA3).ToString(),
"Cell A3 should be Blue");
}
public static string Write1Cell(object value)
{
XlsDocumentDelegate docDelegate = delegate(XlsDocument doc)
{
Worksheet sheet = doc.Workbook.Worksheets.Add("Sheet1");
sheet.Cells.Add(1, 1, value);
};
return(WriteDocument(docDelegate));
}
public void FontBold()
{
XlsDocumentDelegate docDelegate = delegate(XlsDocument doc)
{
Assert.AreEqual(17, doc.Workbook.XFs.Count, "XF Count before setting Bold");
Worksheet sheet = doc.Workbook.Worksheets.Add("Sheet1");
sheet.Cells.Add(1, 1, "test").Font.Bold = true;
Assert.AreEqual(18, doc.Workbook.XFs.Count, "XF Count after setting Bold");
};
string fileName = WriteDocument(docDelegate);
AssertPropertyViaExcelOle(fileName, CellProperties.Font_Bold, true.ToString(), "Font is bold");
}
public void WriteTextWithoutSharedStrings()
{
string text = "abc";
XlsDocumentDelegate docDelegate = delegate(XlsDocument doc)
{
Assert.IsFalse(doc.Workbook.ShareStrings, "ShareSettings value");
Worksheet sheet = doc.Workbook.Worksheets.Add("Sheet1");
sheet.Cells.Add(1, 1).Value = text;
Assert.AreEqual(text, sheet.Rows[1].CellAtCol(1).Value, "Cell value from XlsDocument");
};
string fileName = WriteDocument(docDelegate);
AssertPropertyViaExcelOle(fileName, CellProperties.Text, text, "Cell value from Excel via OLE");
}
public void StoreAndRetrieveSharedString()
{
string text = "abc";
XlsDocumentDelegate docDelegate = delegate(XlsDocument doc)
{
doc.Workbook.ShareStrings = true;
Worksheet sheet = doc.Workbook.Worksheets.Add("Sheet1");
sheet.Cells.Add(1, 1, text);
Assert.AreEqual(text, sheet.Rows[1].CellAtCol(1).Value, "Shared String Value retrieved from XlsDocument");
};
string fileName = WriteDocument(docDelegate);
AssertPropertyViaExcelOle(fileName, CellProperties.Value, text,
"Shared String Value retrieved via Excel OLE");
}
public void DefaultPatternColors()
{
XlsDocument theDoc = null;
XlsDocumentDelegate docDelegate = delegate(XlsDocument doc)
{
theDoc = doc;
Worksheet sheet = doc.Workbook.Worksheets.Add("Sheet1");
Cell cell = sheet.Cells.Add(1, 1, "black background");
cell.Pattern = FillPattern.Percent75;
};
string fileName = WriteDocument(docDelegate);
AssertPropertyViaExcelOle(1, 1, 1, fileName, CellProperties.PatternBackgroundColor, "-4105", "Default Pattern Background Color Index");
AssertPropertyViaExcelOle(1, 1, 1, fileName, CellProperties.PatternColor, "-4105", "Default Pattern Color Index");
}
public void WriteDateTime()
{
DateTime dateTime = DateTime.Now;
XlsDocumentDelegate docDelegate = delegate(XlsDocument doc)
{
Worksheet sheet = doc.Workbook.Worksheets.Add("Sheet1");
Cell cell = sheet.Cells.Add(1, 1, dateTime);
Assert.AreEqual(dateTime.ToOADate(), cell.Value, "Cell value");
};
string fileName = WriteDocument(docDelegate);
double dateTimeDouble = Math.Round(dateTime.ToOADate(), 2);
string dateTimeString = dateTimeDouble.ToString();
AssertPropertyViaExcelOle(fileName, CellProperties.Text, dateTimeString, "Date value (text)");
}
public void FontName()
{
XlsDocumentDelegate docDelegate = delegate(XlsDocument doc)
{
Worksheet sheet = doc.Workbook.Worksheets.Add("Sheet1");
sheet.Cells.Add(1, 1, "test");
Assert.AreEqual(17, doc.Workbook.XFs.Count, "XF count before setting FontName");
Cell cell = sheet.Cells.Add(1, 2, "test");
string newFont = "Times New Roman";
cell.Font.FontName = newFont;
Assert.AreEqual(newFont, cell.Font.FontName, "New font name");
Assert.AreEqual(18, doc.Workbook.XFs.Count, "XF count after setting FontName");
};
string fileName = WriteDocument(docDelegate);
AssertPropertyViaExcelOle(1, 1, 1, fileName, CellProperties.Font_Name, "Arial", "Font Name");
AssertPropertyViaExcelOle(1, 1, 2, fileName, CellProperties.Font_Name, "Times New Roman", "Font Name");
}
public static string WriteDocument(XlsDocumentDelegate docDelegate)
{
string path = Environment.CurrentDirectory;
if (!path.EndsWith("\\"))
{
path += "\\";
}
string fileName = "writedocument";
XlsDocument xls = new XlsDocument();
xls.FileName = fileName;
if (docDelegate != null)
{
docDelegate(xls);
}
xls.Save(path, true);
return(string.Format("{0}{1}.xls", path, fileName));
}
public void SstContinueRecordNoSplit()
{
// This is an example of the CONTINUE record where the spillover
// string is not actually split across two SST records. Once the
// first record is filled up with 632 strings, the 633th string
// is placed in the CONTINUE record.
//
// This means that there are 632 whole 10-char strings (13 bytes
// per 10-char string * 632 = 8216) in the first record with 1
// whole 10-char string in the CONTINUE record.
XlsDocumentDelegate continueDocDelegate = delegate(XlsDocument doc)
{
doc.Workbook.ShareStrings = true;
Cells cells = doc.Workbook.Worksheets.Add("Sheet1").Cells;
for (int i = 0; i < 633; i++)
{
cells.Add(i + 1, 1, (i + 1000000001).ToString());
}
Assert.AreEqual(633, doc.Workbook.SharedStringTable.CountUnique, "Unique values in SST");
};
string continueFile = WriteDocument(continueDocDelegate);
AssertPropertyViaExcelOle(1, 633, 1, continueFile, CellProperties.Value, "1000000633", "Read last string after CONTINUE");
List <Record> continueWorkbookRecords = null;
Workbook.BytesReadCallback bytesReadCallback = delegate(List <Record> records)
{
continueWorkbookRecords = records;
};
new XlsDocument(continueFile, bytesReadCallback);
Assert.IsNotNull(continueWorkbookRecords, "Workbook records list");
Record sst = GetSstRecord(continueWorkbookRecords);
Assert.IsNotNull(sst, "SST Record");
Assert.AreEqual(1, sst.Continues.Count, "SST Continues");
}
public void SstTwoContinueRecords()
{
//8,216 bytes available for string records in SST Record
//(before CONTINUE record is necessary)
//8,223 bytes available for string records in CONTINUE Record
//(before next CONTINUE record is necessary)
//A 7-char single-byte (compressed unicode) text string, plus
//the 3 bytes at the beginning of the Unicode
//string data, gives 10 bytes per 7-char string record.
//This gives us 821 7-char strings, the 822nd
//spilling 4 bytes across to the first CONTINUE record.
//Then another 821 (1,643 total), the 1,644th spilling 1 byte
//over to the second CONTINUE Record.
int stringsToWrite = 821;
XlsDocumentDelegate docDelegate = delegate(XlsDocument doc)
{
doc.Workbook.ShareStrings = true;
Cells cells = doc.Workbook.Worksheets.Add("Sheet1").Cells;
for (int i = 0; i < stringsToWrite; i++)
{
cells.Add(i + 1, 1, (i + 1000001).ToString());
}
Assert.AreEqual(stringsToWrite, doc.Workbook.SharedStringTable.CountUnique, "Unique values in SST");
};
string fileName = WriteDocument(docDelegate);
List <Record> workbookRecords = null;
int continueRecordCount = 0;
Workbook.BytesReadCallback bytesReadCallback = delegate(List <Record> records)
{
workbookRecords = records;
};
new XlsDocument(fileName, bytesReadCallback);
Assert.IsNotNull(workbookRecords, "Workbook records list");
Record sst = GetSstRecord(workbookRecords);
Assert.AreEqual(0, sst.Continues.Count, "SST CONTINUE Records");
//reset
workbookRecords = null;
continueRecordCount = 0;
stringsToWrite = 822;
fileName = WriteDocument(docDelegate);
new XlsDocument(fileName, bytesReadCallback);
Assert.IsNotNull(workbookRecords, "Workbook records list");
sst = GetSstRecord(workbookRecords);
Assert.AreEqual(1, sst.Continues.Count, "SST CONTINUE Records");
//reset
workbookRecords = null;
continueRecordCount = 0;
stringsToWrite = 1643;
fileName = WriteDocument(docDelegate);
new XlsDocument(fileName, bytesReadCallback);
Assert.IsNotNull(workbookRecords, "Workbook records list");
sst = GetSstRecord(workbookRecords);
Assert.AreEqual(1, sst.Continues.Count, "SST CONTINUE Records");
//reset
workbookRecords = null;
continueRecordCount = 0;
stringsToWrite = 1644;
fileName = WriteDocument(docDelegate);
new XlsDocument(fileName, bytesReadCallback);
Assert.IsNotNull(workbookRecords, "Workbook records list");
sst = GetSstRecord(workbookRecords);
Assert.AreEqual(2, sst.Continues.Count, "SST CONTINUE Records");
AssertPropertyViaExcelOle(1, 1644, 1, fileName, CellProperties.Value, (stringsToWrite + 1000000).ToString(),
"Last String value via Excel OLE");
}
public void SstContinueRecord()
{
//8,216 bytes available for data in SST Record
//(before CONTINUE record is necessary)
//A 7-char single-byte (compressed unicode) text string, plus
//the 3 bytes at the beginning of the Unicode
//string data, gives 10 bytes per 7-char string.
//This gives us 821 7-char strings, the 822nd
//spilling across to a CONTINUE record.
XlsDocumentDelegate noContinueDocDelegate = delegate(XlsDocument doc)
{
doc.Workbook.ShareStrings = true;
Cells cells = doc.Workbook.Worksheets.Add("Sheet1").Cells;
for (int i = 0; i < 821; i++)
{
cells.Add(i + 1, 1, (i + 1000001).ToString());
}
Assert.AreEqual(821, doc.Workbook.SharedStringTable.CountUnique, "Unique values in SST");
};
string noContinueFile = WriteDocument(noContinueDocDelegate);
AssertPropertyViaExcelOle(1, 821, 1, noContinueFile, CellProperties.Value, "1000821", "Read last string before CONTINUE");
List <Record> noContinueWorkbookRecords = null;
Workbook.BytesReadCallback bytesReadCallback = delegate(List <Record> records)
{
noContinueWorkbookRecords = records;
};
new XlsDocument(noContinueFile, bytesReadCallback);
Assert.IsNotNull(noContinueWorkbookRecords, "Workbook records list");
Record sst = GetSstRecord(noContinueWorkbookRecords);
Assert.IsNotNull(sst, "SST Record");
Assert.AreEqual(0, sst.Continues.Count, "SST Continues");
XlsDocumentDelegate continueDoc = delegate(XlsDocument doc)
{
doc.Workbook.ShareStrings = true;
Cells cells = doc.Workbook.Worksheets.Add("Sheet1").Cells;
for (int i = 0; i < 822; i++)
{
cells.Add(i + 1, 1, (i + 1000001).ToString());
}
Assert.AreEqual(822, doc.Workbook.SharedStringTable.CountUnique, "Unique values in SST");
};
string continueFile = WriteDocument(continueDoc);
AssertPropertyViaExcelOle(1, 822, 1, continueFile, CellProperties.Value, "1000822", "Read string split over to CONTINUE");
List <Record> continueWorkbookRecords = null;
bytesReadCallback = delegate(List <Record> records)
{
continueWorkbookRecords = records;
};
new XlsDocument(continueFile, bytesReadCallback);
Assert.IsNotNull(continueWorkbookRecords, "Workbook records list");
sst = GetSstRecord(continueWorkbookRecords);
Assert.IsNotNull(sst, "SST Record");
Assert.AreEqual(1, sst.Continues.Count, "SST CONTINUE Records");
}
public static string WriteDocument(XlsDocumentDelegate docDelegate)
{
string path = Environment.CurrentDirectory;
if (!path.EndsWith("\\"))
path += "\\";
string fileName = "writedocument";
XlsDocument xls = new XlsDocument();
xls.FileName = fileName;
if (docDelegate != null)
docDelegate(xls);
xls.Save(path, true);
return string.Format("{0}{1}.xls", path, fileName);
}
