public SpreadBook(SpreadsheetDocument workbook)
{
this.doc = workbook;
CurrentSheet = 0;
// IEnumerable<WorksheetPart> WorksheetParts { get; }
}
static Dictionary <int, CellFormat> ReadStyleSheet(Spreadsheet document, Package.SpreadsheetDocument importDocument)
{
Dictionary <int, CellFormat> cellFormats = new Dictionary <int, CellFormat>();
var importedStyleSheet = new Excel.Stylesheet();
if (importDocument.WorkbookPart.WorkbookStylesPart != null)
{
importedStyleSheet.Load(importDocument.WorkbookPart.WorkbookStylesPart);
}
int index = 0;
foreach (Excel.CellFormat item in importedStyleSheet.CellFormats)
{
if (item.ApplyFill != null && item.ApplyFill.Value && item.FillId != null)
{
int fillID = (int)(uint)item.FillId.Value;
var fill = (Excel.Fill)importedStyleSheet.Fills.ElementAt(fillID);
if (fill.PatternFill.ForegroundColor != null)
{
string fillColor = fill.PatternFill.ForegroundColor.Rgb;
cellFormats.Add(index, document.CellFormats().CellFormat(fillColor));
}
}
index++;
}
return(cellFormats);
}
protected override DataTable ReadFile()
{
string outputPath = CopyFileToAppData();
DataTable dataTable = new DataTable();
using (DocumentFormat.OpenXml.Packaging.SpreadsheetDocument spreadsheetDocument = DocumentFormat.OpenXml.Packaging.SpreadsheetDocument.Open(outputPath, false))
{
DocumentFormat.OpenXml.Packaging.WorksheetPart worksheetPart = GetWorksheetPartByName(spreadsheetDocument, _sheetName);
DocumentFormat.OpenXml.Spreadsheet.SheetData sheetData = worksheetPart.Worksheet.Elements <DocumentFormat.OpenXml.Spreadsheet.SheetData>().First();
IEnumerable <Row> rows = sheetData.Descendants <Row>();
foreach (Cell cell in rows.ElementAt(0))
{
dataTable.Columns.Add(GetCellValue(spreadsheetDocument, cell));
}
for (int i = 1; i < rows.Count() - 1; i++)
{
DataRow tempRow = dataTable.NewRow();
for (int j = 0; j < rows.ElementAt(i).Descendants <Cell>().Count(); j++)
{
tempRow[j] = GetCellValue(spreadsheetDocument, rows.ElementAt(i).Descendants <Cell>().ElementAt(j));
}
dataTable.Rows.Add(tempRow);
}
}
return(dataTable);
}
/// <summary>
/// Sets a column heading to a cell
/// </summary>
/// <param name="spreadsheet">Spreadsheet to use</param>
/// <param name="worksheet">Worksheet to use</param>
/// <param name="columnIndex">Index of the column</param>
/// <param name="rowIndex">Index of the row</param>
/// <param name="stringValue">String value to set</param>
/// <param name="useSharedString">Use shared strings? If true and the string isn't found in shared strings, it will be added</param>
/// <param name="save">Save the worksheet</param>
/// <returns>True if succesful</returns>
public static bool SetColumnHeadingValue(DocumentFormat.OpenXml.Packaging.SpreadsheetDocument spreadsheet, DocumentFormat.OpenXml.Spreadsheet.Worksheet worksheet, uint columnIndex, string stringValue, bool useSharedString, bool save = true)
{
string columnValue = stringValue;
DocumentFormat.OpenXml.Spreadsheet.CellValues cellValueType;
// Add the shared string if necessary
if (useSharedString)
{
if (ExcelProc.IndexOfSharedString(spreadsheet, stringValue) == -1)
{
ExcelProc.AddSharedString(spreadsheet, stringValue, true);
}
columnValue = ExcelProc.IndexOfSharedString(spreadsheet, stringValue).ToString();
cellValueType = DocumentFormat.OpenXml.Spreadsheet.CellValues.SharedString;
}
else
{
cellValueType = DocumentFormat.OpenXml.Spreadsheet.CellValues.String;
}
return(SetCellValue(spreadsheet, worksheet, columnIndex, 1, cellValueType, columnValue, 4, save));
//return SetCellValue(spreadsheet, worksheet, columnIndex, 2, cellValueType, columnValue, 4, save);
}
/// <summary>
/// Adds a predefined style from the given xml
/// </summary>
/// <param name="spreadsheet">Spreadsheet to use</param>
/// <param name="xml">Style definition as xml</param>
/// <returns>True if succesful</returns>
public static bool AddPredefinedStyles(DocumentFormat.OpenXml.Packaging.SpreadsheetDocument spreadsheet, string xml)
{
spreadsheet.WorkbookPart.WorkbookStylesPart.Stylesheet.InnerXml = xml;
spreadsheet.WorkbookPart.WorkbookStylesPart.Stylesheet.Save();
return(true);
}
public void Dispose()
{
if (doc != null)
{
doc.Dispose();
}
doc = null; // dispose
}
/// <summary>
/// Adds a list of strings to the shared strings table.
/// </summary>
/// <param name="spreadsheet">The spreadsheet</param>
/// <param name="stringList">Strings to add</param>
/// <returns></returns>
public static bool AddSharedStrings(DocumentFormat.OpenXml.Packaging.SpreadsheetDocument spreadsheet, System.Collections.Generic.List <string> stringList)
{
foreach (string item in stringList)
{
ExcelProc.AddSharedString(spreadsheet, item, false);
}
spreadsheet.WorkbookPart.SharedStringTablePart.SharedStringTable.Save();
return(true);
}
/// <summary>
/// Sets a cell value with double number
/// </summary>
/// <param name="spreadsheet">Spreadsheet to use</param>
/// <param name="worksheet">Worksheet to use</param>
/// <param name="columnIndex">Index of the column</param>
/// <param name="rowIndex">Index of the row</param>
/// <param name="doubleValue">Double value</param>
/// <param name="styleIndex">Style to use</param>
/// <param name="save">Save the worksheet</param>
/// <returns>True if succesful</returns>
public static bool SetCellValue(DocumentFormat.OpenXml.Packaging.SpreadsheetDocument spreadsheet, DocumentFormat.OpenXml.Spreadsheet.Worksheet worksheet, uint columnIndex, uint rowIndex, double doubleValue, uint?styleIndex, bool save = true)
{
#if EN_US_CULTURE
string columnValue = doubleValue.ToString();
#else
string columnValue = doubleValue.ToString().Replace(",", ".");
#endif
return(SetCellValue(spreadsheet, worksheet, columnIndex, rowIndex, DocumentFormat.OpenXml.Spreadsheet.CellValues.Number, columnValue, styleIndex, save));
}
public bool Open(String excelFileName)
{
_fileName = excelFileName;
byte[] bytes = System.IO.File.ReadAllBytes(excelFileName);
_stream = new MemoryStream(bytes);
_excel_file = SpreadsheetDocument.Open(_stream, false);
workbookPart = _excel_file.WorkbookPart;
sharedStringItem = workbookPart.SharedStringTablePart.SharedStringTable.Elements <SharedStringItem>();
return(true);
}
public Stream CreatePackage()
{
MemoryStream stream = new MemoryStream();
using (DocumentFormat.OpenXml.Packaging.SpreadsheetDocument package = DocumentFormat.OpenXml.Packaging.SpreadsheetDocument.Create(stream, SpreadsheetDocumentType.Workbook))
{
CreateParts(package);
}
return(stream);
}
/// <summary>
/// Class constructor
/// </summary>
/// <param name="document">Document to perform operations on</param>
/// <param name="fullName">Full path name of document</param>
public SpreadsheetDocument(OpenXmlSDK.SpreadsheetDocument document, string fullName)
: base()
{
Document = document;
FullName = fullName;
InnerContent = new SpreadsheetDocumentManager(this);
Worksheets = new WorksheetAccessor(this);
Chartsheets = new ChartsheetAccessor(this);
CustomProperties = new CustomPropertiesAccesor(this);
Tables = new SpreadSheetTableAccesor(this);
Style = new SpreadSheetStyleAccessor(this);
}
public void CreateExcel(string xlFilePath)
{
FileInfo xlFileInfo = new FileInfo(xlFilePath);
if (xlFileInfo.Exists)
{
xlFileInfo.Delete();
}
xlDoc = SpreadsheetDocument.Create(xlFilePath, SpreadsheetDocumentType.Workbook);
wbPart = xlDoc.AddWorkbookPart();
wbPart.Workbook = new Workbook();
}
protected virtual void Dispose(bool disposing)
{
if (disposing)
{
if (_excel_file != null)
{
_excel_file.Close();
_excel_file.Dispose();
_excel_file = null;
}
_stream?.Dispose();
}
}
//private int maxRowReadCount = 3;
public ExcelOLEClient()
{
try
{
_excel_file = null;
workbookPart = null;
sharedStringItem = null;
//_excel_file.Visible = false;
//_excel_file.DisplayAlerts = false; // 저장할 것인가 확인하지 않도록 설정
//workbooks = _excel_file.Worksheets;
}
catch (Exception)
{
//Dispose();
}
}
public void ReadExcelFromStream(Stream stream, bool isEditable)
{
try
{
m_objSpreadsheetDocument = SpreadsheetDocument.Open(stream, isEditable);
m_objWorkbookPart = m_objSpreadsheetDocument.WorkbookPart;
m_objWorksheetPart = m_objWorkbookPart.WorksheetParts.First();
m_objWorksheet = m_objWorksheetPart.Worksheet;
m_objSheetData = m_objWorksheet.Elements <SheetData>().First();
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
this.DisposeRead();
throw;
}
}
/// <summary>
/// Returns the index of a shared string.
/// </summary>
/// <param name="spreadsheet">Spreadsheet to use</param>
/// <param name="stringItem">String to search for</param>
/// <returns>Index of a shared string. -1 if not found</returns>
public static int IndexOfSharedString(DocumentFormat.OpenXml.Packaging.SpreadsheetDocument spreadsheet, string stringItem)
{
DocumentFormat.OpenXml.Spreadsheet.SharedStringTable sharedStringTable = spreadsheet.WorkbookPart.SharedStringTablePart.SharedStringTable;
bool found = false;
int index = 0;
foreach (DocumentFormat.OpenXml.Spreadsheet.SharedStringItem sharedString in sharedStringTable.Elements <DocumentFormat.OpenXml.Spreadsheet.SharedStringItem>())
{
if (sharedString.InnerText == stringItem)
{
found = true;
break;
}
index++;
}
return(found ? index : -1);
}
/// <summary>
/// Add a single string to shared strings table.
/// Shared string table is created if it doesn't exist.
/// </summary>
/// <param name="spreadsheet">Spreadsheet to use</param>
/// <param name="stringItem">string to add</param>
/// <param name="save">Save the shared string table</param>
/// <returns></returns>
public static bool AddSharedString(DocumentFormat.OpenXml.Packaging.SpreadsheetDocument spreadsheet, string stringItem, bool save = true)
{
DocumentFormat.OpenXml.Spreadsheet.SharedStringTable sharedStringTable = spreadsheet.WorkbookPart.SharedStringTablePart.SharedStringTable;
if (0 == sharedStringTable.Where(item => item.InnerText == stringItem).Count())
{
sharedStringTable.AppendChild(
new DocumentFormat.OpenXml.Spreadsheet.SharedStringItem(
new DocumentFormat.OpenXml.Spreadsheet.Text(stringItem)));
// Save the changes
if (save)
{
sharedStringTable.Save();
}
}
return(true);
}
static void ReadSheet(Spreadsheet document, Package.SpreadsheetDocument importDocument, Excel.Sheet importSheet,
Dictionary <string, string> sharedStringTable, Dictionary <int, CellFormat> cellFormats)
{
Sheet sheet = document.Sheets.Sheet(importSheet.Name);
var importWorksheet = new Excel.Worksheet();
importWorksheet.Load((Package.WorksheetPart)importDocument.WorkbookPart.GetPartById(importSheet.Id));
var sheetData = (Excel.SheetData)importWorksheet.Elements <Excel.SheetData>().First();
foreach (var importColumn in sheetData.Elements <Excel.Column>())
{
ReadColumn();
}
foreach (var importRow in sheetData.Elements <Excel.Row>())
{
ReadRow(sheet, importRow, sharedStringTable, cellFormats);
}
}
private static OpenXmlPackaging.WorksheetPart GetWorksheetPartByName(
DocumentFormat.OpenXml.Packaging.SpreadsheetDocument document, string sheetName)
{
IEnumerable <OpenXmlSpread.Sheet> sheets =
document.WorkbookPart.Workbook.GetFirstChild <DocumentFormat.OpenXml.Spreadsheet.Sheets>()
.Elements <OpenXmlSpread.Sheet>().Where(s => s.Name == sheetName);
if (sheets.Count() == 0)
{
throw new Exception(String.Format("A planilha {0} não existe", sheetName));
}
string relationshipId = sheets.First().Id.Value;
DocumentFormat.OpenXml.Packaging.WorksheetPart worksheetPart =
(DocumentFormat.OpenXml.Packaging.WorksheetPart)
document.WorkbookPart.GetPartById(relationshipId);
return(worksheetPart);
}
static Dictionary <string, string> ReadSharedStringTable(Package.SpreadsheetDocument importDocument)
{
var result = new Dictionary <string, string>();
var importSharedStringTable = new Excel.SharedStringTable();
if (importDocument.WorkbookPart.SharedStringTablePart != null)
{
importSharedStringTable.Load(importDocument.WorkbookPart.SharedStringTablePart);
}
int index = 0;
foreach (var item in importSharedStringTable.Elements <Excel.SharedStringItem>())
{
result.Add(index.ToString(), item.Text != null ? item.Text.Text : null);
index++;
}
return(result);
}
public virtual void Open(string fileName)
{
Dispose();
using (var stream = File.OpenRead(fileName))
{
// {"The supplied data appears to be in the Office 2007+ XML. You are calling the part
// of POI that deals with OLE2 Office Documents. You need to call a different part of POI
// to process this data (eg XSSF instead of HSSF)"}
FileName = fileName;
// System.IO.Packaging.Package
doc = SpreadsheetDocument.Open(stream, false);
}
CurrentSheet = 0;
CurrentRow = 0;
CurrentColumn = 0;
Name = Path.GetFileNameWithoutExtension(FileName);
}
/// <summary>
/// Creates the workbook
/// </summary>
/// <returns>Spreadsheet created</returns>
public static DocumentFormat.OpenXml.Packaging.SpreadsheetDocument CreateWorkbook(string fileName)
{
DocumentFormat.OpenXml.Packaging.SpreadsheetDocument spreadSheet = null;
DocumentFormat.OpenXml.Packaging.SharedStringTablePart sharedStringTablePart;
DocumentFormat.OpenXml.Packaging.WorkbookStylesPart workbookStylesPart;
try
{
// Create the Excel workbook
spreadSheet = DocumentFormat.OpenXml.Packaging.SpreadsheetDocument.Create(fileName, DocumentFormat.OpenXml.SpreadsheetDocumentType.Workbook, false);
// Create the parts and the corresponding objects
// Workbook
spreadSheet.AddWorkbookPart();
spreadSheet.WorkbookPart.Workbook = new DocumentFormat.OpenXml.Spreadsheet.Workbook();
spreadSheet.WorkbookPart.Workbook.Save();
// Shared string table
sharedStringTablePart = spreadSheet.WorkbookPart.AddNewPart <DocumentFormat.OpenXml.Packaging.SharedStringTablePart>();
sharedStringTablePart.SharedStringTable = new DocumentFormat.OpenXml.Spreadsheet.SharedStringTable();
sharedStringTablePart.SharedStringTable.Save();
// Sheets collection
spreadSheet.WorkbookPart.Workbook.Sheets = new DocumentFormat.OpenXml.Spreadsheet.Sheets();
spreadSheet.WorkbookPart.Workbook.Save();
// Stylesheet
workbookStylesPart = spreadSheet.WorkbookPart.AddNewPart <DocumentFormat.OpenXml.Packaging.WorkbookStylesPart>();
workbookStylesPart.Stylesheet = new DocumentFormat.OpenXml.Spreadsheet.Stylesheet();
workbookStylesPart.Stylesheet.Save();
}
catch (System.Exception exception)
{
// System.Windows.MessageBox.Show(exception.Message, "Excel OpenXML basics", System.Windows.MessageBoxButton.OK, System.Windows.MessageBoxImage.Hand);
throw exception;
}
return(spreadSheet);
}
/// <summary>
/// Adds a new worksheet to the workbook
/// </summary>
/// <param name="spreadsheet">Spreadsheet to use</param>
/// <param name="name">Name of the worksheet</param>
/// <returns>True if succesful</returns>
public static bool AddWorksheet(DocumentFormat.OpenXml.Packaging.SpreadsheetDocument spreadsheet, string name)
{
DocumentFormat.OpenXml.Spreadsheet.Sheets sheets = spreadsheet.WorkbookPart.Workbook.GetFirstChild <DocumentFormat.OpenXml.Spreadsheet.Sheets>();
DocumentFormat.OpenXml.Spreadsheet.Sheet sheet;
DocumentFormat.OpenXml.Packaging.WorksheetPart worksheetPart;
// Add the worksheetpart
worksheetPart = spreadsheet.WorkbookPart.AddNewPart <DocumentFormat.OpenXml.Packaging.WorksheetPart>();
worksheetPart.Worksheet = new DocumentFormat.OpenXml.Spreadsheet.Worksheet(new DocumentFormat.OpenXml.Spreadsheet.SheetData());
worksheetPart.Worksheet.Save();
// Add the sheet and make relation to workbook
sheet = new DocumentFormat.OpenXml.Spreadsheet.Sheet()
{
Id = spreadsheet.WorkbookPart.GetIdOfPart(worksheetPart),
SheetId = (uint)(spreadsheet.WorkbookPart.Workbook.Sheets.Count() + 1),
Name = name
};
sheets.Append(sheet);
spreadsheet.WorkbookPart.Workbook.Save();
return(true);
}
public void DisposeRead()
{
if (m_objWorksheet != null)
{
Marshal.FinalReleaseComObject(m_objWorksheet);
}
if (m_objWorksheetPart != null)
{
Marshal.FinalReleaseComObject(m_objWorksheetPart);
}
if (m_objWorkbookPart != null)
{
Marshal.FinalReleaseComObject(m_objWorkbookPart);
}
if (m_objSpreadsheetDocument != null)
{
Marshal.FinalReleaseComObject(m_objSpreadsheetDocument);
int intGeneration = GC.GetGeneration(m_objSpreadsheetDocument);
m_objSpreadsheetDocument = null;
GC.Collect(intGeneration);
}
GC.Collect();
GC.Collect();
}
/// <summary>
/// Sets a cell value. The row and the cell are created if they do not exist. If the cell exists, the contents of the cell is overwritten
/// </summary>
/// <param name="spreadsheet">Spreadsheet to use</param>
/// <param name="worksheet">Worksheet to use</param>
/// <param name="columnIndex">Index of the column</param>
/// <param name="rowIndex">Index of the row</param>
/// <param name="valueType">Type of the value</param>
/// <param name="value">The actual value</param>
/// <param name="styleIndex">Index of the style to use. Null if no style is to be defined</param>
/// <param name="save">Save the worksheet?</param>
/// <returns>True if succesful</returns>
private static bool SetCellValue(DocumentFormat.OpenXml.Packaging.SpreadsheetDocument spreadsheet, DocumentFormat.OpenXml.Spreadsheet.Worksheet worksheet, uint columnIndex, uint rowIndex, DocumentFormat.OpenXml.Spreadsheet.CellValues valueType, string value, uint?styleIndex, bool save = true)
{
DocumentFormat.OpenXml.Spreadsheet.SheetData sheetData = worksheet.GetFirstChild <DocumentFormat.OpenXml.Spreadsheet.SheetData>();
DocumentFormat.OpenXml.Spreadsheet.Row row;
DocumentFormat.OpenXml.Spreadsheet.Row previousRow = null;
DocumentFormat.OpenXml.Spreadsheet.Cell cell;
DocumentFormat.OpenXml.Spreadsheet.Cell previousCell = null;
DocumentFormat.OpenXml.Spreadsheet.Columns columns;
DocumentFormat.OpenXml.Spreadsheet.Column previousColumn = null;
string cellAddress = ExcelProc.ColumnNameFromIndex(columnIndex) + rowIndex;
// Check if the row exists, create if necessary
if (sheetData.Elements <DocumentFormat.OpenXml.Spreadsheet.Row>().Where(item => item.RowIndex == rowIndex).Count() != 0)
{
row = sheetData.Elements <DocumentFormat.OpenXml.Spreadsheet.Row>().Where(item => item.RowIndex == rowIndex).First();
}
else
{
row = new DocumentFormat.OpenXml.Spreadsheet.Row()
{
RowIndex = rowIndex
};
//sheetData.Append(row);
for (uint counter = rowIndex - 1; counter > 0; counter--)
{
previousRow = sheetData.Elements <DocumentFormat.OpenXml.Spreadsheet.Row>().Where(item => item.RowIndex == counter).FirstOrDefault();
if (previousRow != null)
{
break;
}
}
sheetData.InsertAfter(row, previousRow);
}
// Check if the cell exists, create if necessary
if (row.Elements <DocumentFormat.OpenXml.Spreadsheet.Cell>().Where(item => item.CellReference.Value == cellAddress).Count() > 0)
{
cell = row.Elements <DocumentFormat.OpenXml.Spreadsheet.Cell>().Where(item => item.CellReference.Value == cellAddress).First();
}
else
{
// Find the previous existing cell in the row
for (uint counter = columnIndex - 1; counter > 0; counter--)
{
previousCell = row.Elements <DocumentFormat.OpenXml.Spreadsheet.Cell>().Where(item => item.CellReference.Value == ExcelProc.ColumnNameFromIndex(counter) + rowIndex).FirstOrDefault();
if (previousCell != null)
{
break;
}
}
cell = new DocumentFormat.OpenXml.Spreadsheet.Cell()
{
CellReference = cellAddress
};
row.InsertAfter(cell, previousCell);
}
// Check if the column collection exists
columns = worksheet.Elements <DocumentFormat.OpenXml.Spreadsheet.Columns>().FirstOrDefault();
if (columns == null)
{
columns = worksheet.InsertAt(new DocumentFormat.OpenXml.Spreadsheet.Columns(), 0);
}
// Check if the column exists
if (columns.Elements <DocumentFormat.OpenXml.Spreadsheet.Column>().Where(item => item.Min == columnIndex).Count() == 0)
{
// Find the previous existing column in the columns
for (uint counter = columnIndex - 1; counter > 0; counter--)
{
previousColumn = columns.Elements <DocumentFormat.OpenXml.Spreadsheet.Column>().Where(item => item.Min == counter).FirstOrDefault();
if (previousColumn != null)
{
break;
}
}
columns.InsertAfter(
new DocumentFormat.OpenXml.Spreadsheet.Column()
{
Min = columnIndex,
Max = columnIndex,
CustomWidth = true,
Width = 9
}, previousColumn);
}
// Add the value
cell.CellValue = new DocumentFormat.OpenXml.Spreadsheet.CellValue(value);
if (styleIndex != null)
{
cell.StyleIndex = styleIndex;
}
if (valueType != DocumentFormat.OpenXml.Spreadsheet.CellValues.Date)
{
cell.DataType = new DocumentFormat.OpenXml.EnumValue <DocumentFormat.OpenXml.Spreadsheet.CellValues>(valueType);
}
if (save)
{
worksheet.Save();
}
return(true);
}
/// <summary>
/// Sets a cell value with boolean value
/// </summary>
/// <param name="spreadsheet">Spreadsheet to use</param>
/// <param name="worksheet">Worksheet to use</param>
/// <param name="columnIndex">Index of the column</param>
/// <param name="rowIndex">Index of the row</param>
/// <param name="boolValue">Boolean value</param>
/// <param name="styleIndex">Style to use</param>
/// <param name="save">Save the worksheet</param>
/// <returns>True if succesful</returns>
public static bool SetCellValue(DocumentFormat.OpenXml.Packaging.SpreadsheetDocument spreadsheet, DocumentFormat.OpenXml.Spreadsheet.Worksheet worksheet, uint columnIndex, uint rowIndex, bool boolValue, uint?styleIndex, bool save = true)
{
string columnValue = boolValue ? "1" : "0";
return(SetCellValue(spreadsheet, worksheet, columnIndex, rowIndex, DocumentFormat.OpenXml.Spreadsheet.CellValues.Boolean, columnValue, styleIndex, save));
}
/// <summary>
/// Creates a new OpenXmlPackage on the given stream.
/// </summary>
/// <param name="stream">The stream on which the concrete OpenXml package will be created.</param>
/// <returns>A new instance of OpenXmlPackage.</returns>
protected override OpenXmlPackage CreateClone(Stream stream)
{
return(SpreadsheetDocument.Create(stream, DocumentType, OpenSettings.AutoSave));
}
/// <summary>
/// Creates a new instance of OpenXmlPackage on the specified instance
/// of Package.
/// </summary>
/// <param name="package">The specified instance of Package.</param>
/// <returns>A new instance of OpenXmlPackage.</returns>
protected override OpenXmlPackage CreateClone(Package package)
{
return(SpreadsheetDocument.Create(package, DocumentType, OpenSettings.AutoSave));
}
/// <summary>
/// Adds the basic styles to the workbook
/// </summary>
/// <param name="spreadsheet">Spreadsheet to use</param>
/// <returns>True if succesful</returns>
public static bool AddBasicStyles(DocumentFormat.OpenXml.Packaging.SpreadsheetDocument spreadsheet)
{
DocumentFormat.OpenXml.Spreadsheet.Stylesheet stylesheet = spreadsheet.WorkbookPart.WorkbookStylesPart.Stylesheet;
// Numbering formats (x:numFmts)
stylesheet.InsertAt <DocumentFormat.OpenXml.Spreadsheet.NumberingFormats>(new DocumentFormat.OpenXml.Spreadsheet.NumberingFormats(), 0);
// Currency
stylesheet.GetFirstChild <DocumentFormat.OpenXml.Spreadsheet.NumberingFormats>().InsertAt <DocumentFormat.OpenXml.Spreadsheet.NumberingFormat>(
new DocumentFormat.OpenXml.Spreadsheet.NumberingFormat()
{
NumberFormatId = 164,
FormatCode = "#,##0.00"
+ "\\ \"" + System.Globalization.CultureInfo.CurrentUICulture.NumberFormat.CurrencySymbol + "\""
}, 0);
// Fonts (x:fonts)
stylesheet.InsertAt <DocumentFormat.OpenXml.Spreadsheet.Fonts>(new DocumentFormat.OpenXml.Spreadsheet.Fonts(), 1);
stylesheet.GetFirstChild <DocumentFormat.OpenXml.Spreadsheet.Fonts>().InsertAt <DocumentFormat.OpenXml.Spreadsheet.Font>(
new DocumentFormat.OpenXml.Spreadsheet.Font()
{
FontSize = new DocumentFormat.OpenXml.Spreadsheet.FontSize()
{
Val = 11
},
FontName = new DocumentFormat.OpenXml.Spreadsheet.FontName()
{
Val = "Calibri"
}
}, 0);
// Fills (x:fills)
stylesheet.InsertAt <DocumentFormat.OpenXml.Spreadsheet.Fills>(new DocumentFormat.OpenXml.Spreadsheet.Fills(), 2);
stylesheet.GetFirstChild <DocumentFormat.OpenXml.Spreadsheet.Fills>().InsertAt <DocumentFormat.OpenXml.Spreadsheet.Fill>(
new DocumentFormat.OpenXml.Spreadsheet.Fill()
{
PatternFill = new DocumentFormat.OpenXml.Spreadsheet.PatternFill()
{
PatternType = new DocumentFormat.OpenXml.EnumValue <DocumentFormat.OpenXml.Spreadsheet.PatternValues>()
{
Value = DocumentFormat.OpenXml.Spreadsheet.PatternValues.None
}
}
}, 0);
stylesheet.GetFirstChild <DocumentFormat.OpenXml.Spreadsheet.Fills>().InsertAt <DocumentFormat.OpenXml.Spreadsheet.Fill>(
new DocumentFormat.OpenXml.Spreadsheet.Fill()
{
PatternFill = new DocumentFormat.OpenXml.Spreadsheet.PatternFill()
{
PatternType = new DocumentFormat.OpenXml.EnumValue <DocumentFormat.OpenXml.Spreadsheet.PatternValues>()
{
Value = DocumentFormat.OpenXml.Spreadsheet.PatternValues.Gray125
}
}
}, 1);
// Borders (x:borders)
stylesheet.InsertAt <DocumentFormat.OpenXml.Spreadsheet.Borders>(new DocumentFormat.OpenXml.Spreadsheet.Borders(), 3);
stylesheet.GetFirstChild <DocumentFormat.OpenXml.Spreadsheet.Borders>().InsertAt <DocumentFormat.OpenXml.Spreadsheet.Border>(
new DocumentFormat.OpenXml.Spreadsheet.Border()
{
LeftBorder = new DocumentFormat.OpenXml.Spreadsheet.LeftBorder(),
RightBorder = new DocumentFormat.OpenXml.Spreadsheet.RightBorder(),
TopBorder = new DocumentFormat.OpenXml.Spreadsheet.TopBorder(),
BottomBorder = new DocumentFormat.OpenXml.Spreadsheet.BottomBorder(),
DiagonalBorder = new DocumentFormat.OpenXml.Spreadsheet.DiagonalBorder()
}, 0);
// Cell style formats (x:CellStyleXfs)
stylesheet.InsertAt <DocumentFormat.OpenXml.Spreadsheet.CellStyleFormats>(new DocumentFormat.OpenXml.Spreadsheet.CellStyleFormats(), 4);
stylesheet.GetFirstChild <DocumentFormat.OpenXml.Spreadsheet.CellStyleFormats>().InsertAt <DocumentFormat.OpenXml.Spreadsheet.CellFormat>(
new DocumentFormat.OpenXml.Spreadsheet.CellFormat()
{
NumberFormatId = 0,
FontId = 0,
FillId = 0,
BorderId = 0
}, 0);
// Cell formats (x:CellXfs)
stylesheet.InsertAt <DocumentFormat.OpenXml.Spreadsheet.CellFormats>(new DocumentFormat.OpenXml.Spreadsheet.CellFormats(), 5);
// General text
stylesheet.GetFirstChild <DocumentFormat.OpenXml.Spreadsheet.CellFormats>().InsertAt <DocumentFormat.OpenXml.Spreadsheet.CellFormat>(
new DocumentFormat.OpenXml.Spreadsheet.CellFormat()
{
FormatId = 0,
NumberFormatId = 0
}, 0);
// Date
stylesheet.GetFirstChild <DocumentFormat.OpenXml.Spreadsheet.CellFormats>().InsertAt <DocumentFormat.OpenXml.Spreadsheet.CellFormat>(
new DocumentFormat.OpenXml.Spreadsheet.CellFormat()
{
ApplyNumberFormat = true,
FormatId = 0,
NumberFormatId = 22,
FontId = 0,
FillId = 0,
BorderId = 0
},
1);
// Currency
stylesheet.GetFirstChild <DocumentFormat.OpenXml.Spreadsheet.CellFormats>().InsertAt <DocumentFormat.OpenXml.Spreadsheet.CellFormat>(
new DocumentFormat.OpenXml.Spreadsheet.CellFormat()
{
ApplyNumberFormat = true,
FormatId = 0,
NumberFormatId = 164,
FontId = 0,
FillId = 0,
BorderId = 0
},
2);
// Percentage
stylesheet.GetFirstChild <DocumentFormat.OpenXml.Spreadsheet.CellFormats>().InsertAt <DocumentFormat.OpenXml.Spreadsheet.CellFormat>(
new DocumentFormat.OpenXml.Spreadsheet.CellFormat()
{
ApplyNumberFormat = true,
FormatId = 0,
NumberFormatId = 10,
FontId = 0,
FillId = 0,
BorderId = 0
},
3);
stylesheet.Save();
return(true);
}
/// <summary>
/// Opens the cloned OpenXml package on the given file.
/// </summary>
/// <param name="path">The path and file name of the target OpenXml package.</param>
/// <param name="isEditable">In ReadWrite mode. False for Read only mode.</param>
/// <param name="openSettings">The advanced settings for opening a document.</param>
/// <returns>A new instance of OpenXmlPackage.</returns>
protected override OpenXmlPackage OpenClone(string path, bool isEditable, OpenSettings openSettings)
{
return(SpreadsheetDocument.Open(path, isEditable, openSettings));
}