public static void Write(string fileName, Workbook workbook)
{
try
{
if (fileName == null) throw new ArgumentNullException("fileName");
if (workbook == null) throw new ArgumentNullException("workbook");
FileInfo fi = new FileInfo(fileName);
if (fi.Exists)
fi.Delete();
// create the blank workbook
char[] base64CharArray = _EmptyXlsx
.Where(c => c != '\r' && c != '\n').ToArray();
byte[] byteArray =
System.Convert.FromBase64CharArray(base64CharArray,
0, base64CharArray.Length);
File.WriteAllBytes(fi.FullName, byteArray);
// open the workbook, and create the TableProperties sheet, populate it
using (SpreadsheetDocument sDoc = SpreadsheetDocument.Open(fi.FullName, true))
{
WorkbookPart workbookPart = sDoc.WorkbookPart;
XDocument wXDoc = workbookPart.GetXDocument();
XElement sheetElement = wXDoc
.Root
.Elements(S.sheets)
.Elements(S.sheet)
.Where(s => (string)s.Attribute(SSNoNamespace.name) == "Sheet1")
.FirstOrDefault();
if (sheetElement == null)
throw new SpreadsheetWriterInternalException();
string id = (string)sheetElement.Attribute(R.id);
sheetElement.Remove();
workbookPart.PutXDocument();
WorksheetPart sPart = (WorksheetPart)workbookPart.GetPartById(id);
workbookPart.DeletePart(sPart);
XDocument appXDoc = sDoc
.ExtendedFilePropertiesPart
.GetXDocument();
XElement vector = appXDoc
.Root
.Elements(EP.TitlesOfParts)
.Elements(VT.vector)
.FirstOrDefault();
if (vector != null)
{
vector.SetAttributeValue(SSNoNamespace.size, 0);
XElement lpstr = vector.Element(VT.lpstr);
lpstr.Remove();
}
XElement vector2 = appXDoc
.Root
.Elements(EP.HeadingPairs)
.Elements(VT.vector)
.FirstOrDefault();
XElement variant = vector2
.Descendants(VT.i4)
.FirstOrDefault();
if (variant != null)
variant.Value = "1";
sDoc.ExtendedFilePropertiesPart.PutXDocument();
if (workbook.Worksheets != null)
foreach (var worksheet in workbook.Worksheets)
AddWorksheet(sDoc, worksheet);
workbookPart.WorkbookStylesPart.PutXDocument();
}
}
catch (Exception e)
{
Console.WriteLine("Unhandled exception: {0} in {1}",
e.ToString(), e.Source);
throw e;
}
}
private void GenerateNewSpreadSheetDocument(SpreadsheetDocument document, string sheetName, string tableName)
{
List<SSW.Row> rowCollection = GetRowCollection(result);
// Create new work sheet to te document
List<SSW.Worksheet> workSheetCollection = new List<SSW.Worksheet>();
SSW.Worksheet workSheet = new SSW.Worksheet();
if (string.IsNullOrEmpty(sheetName))
workSheet.Name = DefaultSheetName;
else
workSheet.Name = this.sheetName;
workSheet.TableName = tableName;
workSheet.ColumnHeadings = columnHeadings;
workSheet.Rows = rowCollection;
workSheetCollection.Add(workSheet);
// Create work book
SSW.Workbook workBook = new SSW.Workbook();
workBook.Worksheets = workSheetCollection;
// Create Excel File
SSW.SpreadsheetWriter.Write(fileName, workBook);
}