private ExcelStreamReader(string fileName)
{
this.fileName = fileName;
this.doc = SpreadsheetDocument.Open(fileName, false);
this.workbookPart = doc.WorkbookPart;
this.sheets = GetSheets();
}
// Given a WorkbookPart, inserts a new worksheet.
private static WorksheetPart InsertWorksheet(WorkbookPart workbookPart)
{
// Add a new worksheet part to the workbook.
WorksheetPart newWorksheetPart = workbookPart.AddNewPart<WorksheetPart>();
newWorksheetPart.Worksheet = new Worksheet(new SheetData());
newWorksheetPart.Worksheet.Save();
Sheets sheets = workbookPart.Workbook.GetFirstChild<Sheets>();
string relationshipId = workbookPart.GetIdOfPart(newWorksheetPart);
// Get a unique ID for the new sheet.
uint sheetId = 1;
if (sheets.Elements<Sheet>().Count() > 0)
{
sheetId = sheets.Elements<Sheet>().Select(s => s.SheetId.Value).Max() + 1;
}
string sheetName = "Sheet" + sheetId;
// Append the new worksheet and associate it with the workbook.
Sheet sheet = new Sheet() { Id = relationshipId, SheetId = sheetId, Name = sheetName };
sheets.Append(sheet);
workbookPart.Workbook.Save();
return newWorksheetPart;
}
public OpenXLRow(WorkbookPart wbPart, WorksheetPart wsPart, List<string> columns, Row xRow)
{
this.WbPart = wbPart;
this.WsPart = wsPart;
this.Columns = columns;
this._cells = GetCells(xRow);
}
public static string GetCellValue(WorkbookPart wbPart, Cell theCell)
{
string value = theCell.InnerText;
if (theCell.DataType != null)
{
switch (theCell.DataType.Value)
{
case CellValues.SharedString:
var stringTable = wbPart.
GetPartsOfType<SharedStringTablePart>().FirstOrDefault();
if (stringTable != null)
{
value = stringTable.SharedStringTable.
ElementAt(int.Parse(value)).InnerText;
}
break;
case CellValues.Boolean:
switch (value)
{
case "0":
value = "FALSE";
break;
default:
value = "TRUE";
break;
}
break;
}
}
return value;
}
public static Sheet FindSheet(string sheetName, WorkbookPart workbookPart)
{
if (string.IsNullOrEmpty(sheetName))
{
return workbookPart.Workbook.Descendants<Sheet>().FirstOrDefault();
}
return workbookPart.Workbook.Descendants<Sheet>().Where(s => s.Name == sheetName).SingleOrDefault<Sheet>();
}
public string GetValueCell(ref WorkbookPart wbPart, ref WorksheetPart wsPart, string addressName)
{
string value = null;
Cell theCell = wsPart.Worksheet.Descendants<Cell>().
Where(c => c.CellReference == addressName).FirstOrDefault();
value = GetCleanValueCell(theCell, ref wbPart);
return value;
}
/// <summary>
/// Создает экземпляр класса для работы с текущим файлом
/// </summary>
/// <param name="filePath">Путь к документу</param>
/// <param name="removeAfterDestroy">Удалять ли файл после окончания работы с ним</param>
public ExcellWorker(string filePath, bool removeAfterDestroy)
{
_currentFilePath = filePath;
_currentDocument = SpreadsheetDocument.Open(filePath, true);
_currentWorkBookPart = _currentDocument.WorkbookPart;
_currentSheet = _currentWorkBookPart.Workbook.Descendants<Sheet>().FirstOrDefault();
RemoveAfterDestroy = removeAfterDestroy;
}
public static WorksheetPart GetWorksheetFromSheetName(WorkbookPart workbookPart, string sheetName)
{
var sheet = workbookPart.Workbook.Descendants<Sheet>().FirstOrDefault(s => s.Name == sheetName);
if (sheet == null)
{
return null;
}
return workbookPart.GetPartById(sheet.Id) as WorksheetPart;
}
public static SharedStringItem GetSharedStringValue(WorkbookPart workbookPart, Cell cell, ref int id)
{
if (int.TryParse(cell.InnerText, out id))
{
var text = workbookPart.SharedStringTablePart.SharedStringTable.Elements<SharedStringItem>().ElementAt(id);
return text;
}
return null;
}
static bool AddCellUnit(int columnIndex, ref int exportColumnIndex, SheetConfig Sheetcfg, Cell cell, WorkbookPart workbookPart, List<string> stylesList, List<CellUnit> cellUnitList)
{
if (columnIndex >= Sheetcfg.ColumnConfigData.Count)
goto Error;
var columnConfig = Sheetcfg.ColumnConfigData[columnIndex];
if (!columnConfig.Export)
return true;
var dataType = columnConfig.FieldType;
string cellValue = cell == null ? "" : GetCellValue(cell, workbookPart, stylesList);
CellUnit cellUnit = null;
switch (dataType)
{
case DesignerDataType.Int:
{
int intValue = 0;
if (!int.TryParse(cellValue, out intValue) && !string.IsNullOrEmpty(cellValue))
goto Error;
cellUnit = new CellUnitIntValue(exportColumnIndex, intValue);
}
break;
case DesignerDataType.Float:
{
float floatValue = 0.0f;
if (!float.TryParse(cellValue, out floatValue) && !string.IsNullOrEmpty(cellValue))
goto Error;
cellUnit = new CellUnitFloatValue(exportColumnIndex, floatValue);
}
break;
case DesignerDataType.String:
{
cellUnit = new CellUnitStringValue(exportColumnIndex, cellValue);
}
break;
case DesignerDataType.Bool:
{
decimal decimalValue = 0;
cellUnit = new CellUnitBoolValue(exportColumnIndex, decimal.TryParse(cellValue, out decimalValue) && decimalValue != 0);
}
break;
}
if (cellUnit.IsNeedWrite())
cellUnitList.Add(cellUnit);
++exportColumnIndex;
return true;
Error:
string excelFileName = "";
if (!dicOutputFileName.TryGetValue(Sheetcfg.Name, out excelFileName))
excelFileName = "";
Console.Error.WriteLine(string.Format(Resources.ExcelConfigFileInValid, excelFileName, Sheetcfg.Name));
return false;
}
public void Close()
{
if (_doc != null)
{
_doc.Close();
_expectSheets = null;
_wbPart = null;
_shareStringPart = null;
}
}
internal SheetCollection(ref DocumentFormat.OpenXml.Packaging.WorkbookPart workbookPart, IList <Spreadsheet.Sheet> list) : this(ref workbookPart)
{
using (IEnumerator <Spreadsheet.Sheet> enumerator = list.GetEnumerator())
{
while (enumerator.MoveNext())
{
this.Add(enumerator.Current);
}
}
}
public ExcelHelper Create(string ExcelFile)
{
System.IO.FileInfo fi = new System.IO.FileInfo(ExcelFile);
if (!fi.Directory.Exists)
fi.Directory.Create();
_doc = SpreadsheetDocument.Create(ExcelFile, SpreadsheetDocumentType.Workbook);
_wbPart = _doc.AddWorkbookPart();
_wbPart.Workbook = new Workbook();
_doc.WorkbookPart.Workbook.AppendChild<Sheets>(new Sheets());
return _instance;
}
protected string GetCellValue(WorkbookPart wbPart, Cell theCell)
{
if (theCell == null)
{
return null;
}
var value = theCell.InnerText;
if (theCell.DataType == null)
{
return value;
}
switch (theCell.DataType.Value)
{
case (CellValues.SharedString):
{
var stringTable = wbPart.GetPartsOfType<SharedStringTablePart>().FirstOrDefault();
if (stringTable != null)
{
value = stringTable.SharedStringTable.ElementAt(int.Parse(value)).InnerText;
}
break;
}
case (CellValues.Boolean):
{
switch (value)
{
case "0":
{
value = "FALSE";
break;
}
default:
{
value = "TRUE";
break;
}
}
break;
}
case (CellValues.Date):
{
if (!string.IsNullOrEmpty(value))
{
value = DateTime.FromOADate(Convert.ToDouble(value)).ToString(CultureInfo.CurrentCulture);
}
break;
}
}
return value;
}
// Generates content of workbookPart1.
private static void GenerateWorkbookPart1Content(WorkbookPart workbookPart1)
{
Workbook workbook1 = new Workbook();
workbook1.AddNamespaceDeclaration("r", "http://schemas.openxmlformats.org/officeDocument/2006/relationships");
Sheets sheets1 = new Sheets();
Sheet sheet1 = new Sheet() { Name = "Sheet1", SheetId = (UInt32Value)1U, Id = "rId1" };
sheets1.Append(sheet1);
workbook1.Append(sheets1);
workbookPart1.Workbook = workbook1;
}
public List<RPCFan> RetrieveFirstObject(WorkbookPart wbPart, Sheet theSheet)
{
string abc = "ABCDEFGHIJKLMNOPQRSTUVWXYZ";
WorksheetPart wsPart =
(WorksheetPart)(wbPart.GetPartById(theSheet.Id));
Cell theCell = wsPart.Worksheet.Descendants<Cell>().
Where(c => c != null).FirstOrDefault();
string first = FindCellInnerValue(wbPart, theCell);
var letra = first.Substring(0, 1);
int numeros = int.Parse(first.Substring(1));
int i = abc.IndexOf(letra);
List<RPCFan> retorno = new List<RPCFan>();
int countFans = 0;
int numeroscopy = numeros;
while (theCell != null)
{
string newRef = abc[i].ToString() + (numeroscopy++ + 1).ToString();
theCell = wsPart.Worksheet.Descendants<Cell>().
Where(c => c.CellReference == newRef).FirstOrDefault();
countFans++;
}
for (int ii = 0; ii < (countFans - 1); ii++)
{
string newRef;
i = abc.IndexOf(letra);
theCell = new Cell();
var fan = new RPCFan();
List<string> data = new List<string>();
while (theCell != null)
{
newRef = abc[i++].ToString() + (numeros + 1).ToString();
theCell = wsPart.Worksheet.Descendants<Cell>().
Where(c => c.CellReference == newRef).FirstOrDefault();
var value = FindCellInnerValue(wbPart, theCell, false);
data.Add(value);
//i++;
}
fan.YaEntro = false;
fan.LastCell = abc[i-2].ToString() + (numeros + 1).ToString();
fan.ID = data[0];
fan.Nombre = data[1];
fan.CardNum = data[2];
fan.DNI = data[3];
fan.Ingreso = data[4];
if (data[5]!="0")
fan.YaEntro = true;
retorno.Add(fan);
numeros++;
}
return retorno;
}
private void AddReportColumns(WorkbookPart workbookPart, WorksheetPart worksheetPart, List<string> columnNames)
{
for (int i = 0; i < columnNames.Count; i++)
{
string fieldName = columnNames[i];
WorkbookHelper.AddCellText(
workbookPart,
worksheetPart,
fieldName,
(uint)(i + 1), 1U);
}
}
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();
}
private string ReadExcelCell(Cell cell, WorkbookPart workbookPart)
{
var cellValue = cell.CellValue;
var text = (cellValue == null) ? cell.InnerText : cellValue.Text;
if ((cell.DataType != null) && (cell.DataType == CellValues.SharedString))
{
text = workbookPart.SharedStringTablePart.SharedStringTable
.Elements<SharedStringItem>().ElementAt(
Convert.ToInt32(cell.CellValue.Text)).InnerText;
}
return (text ?? string.Empty).Trim();
}
private static Dictionary<int, string> GetHeaders(SheetData sheetData, WorkbookPart workbookPart)
{
var headers = new Dictionary<int, string>();
var headerCells = sheetData.Elements<Row>().First().Elements<Cell>().ToList();
for (var i = 0; i < headerCells.Count; i++)
{
var cellValue = ExcelFileInceptor.GetCellValueAsString(workbookPart, headerCells[i]);
headers.Add(i, cellValue);
}
return headers;
}
private WorksheetPart wsSheet = null; // WorkSheetPart
#endregion Fields
#region Constructors
public LogGenerator(string fn)
{
//// get spreadsheet path from constructor
//path = folder;
//// File name is based on date and time
//DateTime now = DateTime.Now;
//// Construct the spreadsheet filename
//string fn = string.Format("{0}\\report_{1}-{2}-{3}_{4}{5}{6}.xlsx",
// path, now.Year, now.Month, now.Day, now.Hour, now.Minute, now.Second);
if (File.Exists(fn))
{
doc = SpreadsheetDocument.Open(fn, true);
}
else
{
//
// Create the Spreadsheet document
//
doc = SpreadsheetDocument.Create(fn, SpreadsheetDocumentType.Workbook);
//
// Add WoorkBookPart to the document
//
wbPart = doc.AddWorkbookPart();
wbPart.Workbook = new Workbook();
wbPart.Workbook.AddNamespaceDeclaration("x", "http://schemas.openxmlformats.org/spreadsheetml/2006/main");
//
// Add WorkSheetPart to the WorkBookPart
//
wsSheet = wbPart.AddNewPart<WorksheetPart>();
wsSheet.Worksheet = new Worksheet(new SheetData());
wsSheet.Worksheet.AddNamespaceDeclaration("x", "http://schemas.openxmlformats.org/spreadsheetml/2006/main");
//
// Add sheets to the WorkBook
//
sheets = doc.WorkbookPart.Workbook.AppendChild<Sheets>(new Sheets());
//
// Append new sheet and associate it with the workbook
//
sheet = new Sheet() { Id = doc.WorkbookPart.GetIdOfPart(wsSheet), SheetId = 1, Name = wsName };
sheet.AddNamespaceDeclaration("r", "http://schemas.openxmlformats.org/officeDocument/2006/relationships");
sheets.Append(sheet);
wbPart.Workbook.Save();
//CreatePackage(fn);
//if (File.Exists(fn))
//{
// TestXls();
//}
}
}
/// <summary>
/// 根据WorkbookPart获取所有SheetName
/// </summary>
/// <param name="workBookPart"></param>
/// <returns>SheetName集合</returns>
private List<string> GetSheetNames(WorkbookPart workBookPart)
{
List<string> sheetNames = new List<string>();
Sheets sheets = workBookPart.Workbook.Sheets;
foreach (Sheet sheet in sheets)
{
string sheetName = sheet.Name;
if (!string.IsNullOrEmpty(sheetName))
{
sheetNames.Add(sheetName);
}
}
return sheetNames;
}
private void AddReportLines(WorkbookPart workbookPart, WorksheetPart worksheetPart, Report report, uint startRow)
{
for (int iRow = 0; iRow < report.ReportLines.Count; iRow++)
{
for (int iCol = 0; iCol < report.ColumnNames.Count; iCol++)
{
WorkbookHelper.AddCellText(
workbookPart,
worksheetPart,
report[iRow][iCol].ToString(),
(uint)iCol + 1, (uint)iRow + startRow + 1);
}
}
}
/// <summary>
/// 根据WorkbookPart和sheetName获取该Sheet下所有Row数据
/// </summary>
/// <param name="workBookPart">WorkbookPart对象</param>
/// <param name="sheetName">SheetName</param>
/// <returns>该SheetName下的所有Row数据</returns>
public IEnumerable<Row> GetWorkBookPartRows(WorkbookPart workBookPart, string sheetName)
{
IEnumerable<Row> sheetRows = null;
//根据表名在WorkbookPart中获取Sheet集合
IEnumerable<Sheet> sheets = workBookPart.Workbook.Descendants<Sheet>().Where(s => s.Name == sheetName);
if (sheets.Count() == 0)
{
return null;//没有数据
}
WorksheetPart workSheetPart = workBookPart.GetPartById(sheets.First().Id) as WorksheetPart;
//获取Excel中得到的行
sheetRows = workSheetPart.Worksheet.Descendants<Row>();
return sheetRows;
}
static void SaveDocument(Package.WorkbookPart exportedWorkbookPart, Excel.Stylesheet styleSheet, Dictionary <CellFormat, uint> cellFormatList, Spreadsheet document)
{
var exportedWorkbook = new Excel.Workbook();
exportedWorkbookPart.Workbook = exportedWorkbook;
Excel.Sheets exportedSheets = new Excel.Sheets();
exportedWorkbook.AppendChild(exportedSheets);
uint sheetId = 1;
foreach (var sheet in document.Sheets)
{
SaveSheet(exportedWorkbookPart, styleSheet, cellFormatList, exportedSheets, sheet, sheetId++);
}
}
public override void Save(ResearchResult result)
{
if (!Directory.Exists(storageStr))
{
Directory.CreateDirectory(storageStr);
}
string fileName = storageStr + result.ResearchName;
if (File.Exists(fileName + ".xls") || File.Exists(fileName + ".xlsx"))
fileName += result.ResearchID;
document = SpreadsheetDocument.Create(fileName + ".xlsx", SpreadsheetDocumentType.Workbook);
workbook = document.AddWorkbookPart();
workbook.Workbook = new Workbook();
// Add a WorksheetPart to the WorkbookPart.
WorksheetPart worksheetPart = workbook.AddNewPart<WorksheetPart>();
worksheetPart.Worksheet = new Worksheet(new SheetData());
// Add Sheets to the Workbook.
Sheets sheets = document.WorkbookPart.Workbook.
AppendChild<Sheets>(new Sheets());
// Append a new worksheet and associate it with the workbook.
Sheet sheet = new Sheet()
{
Id = document.WorkbookPart.
GetIdOfPart(worksheetPart),
SheetId = 1,
Name = "mySheet"
};
sheets.Append(sheet);
/*SaveResearchInfo(result.ResearchID, result.ResearchName,
result.ResearchType, result.ModelType, result.RealizationCount,
result.Size, result.Edges);
SaveResearchParameters(result.ResearchParameterValues);
SaveGenerationParameters(result.GenerationParameterValues);
for (int i = 0; i < result.EnsembleResults.Count; ++i)
{
SaveEnsembleResult(result.EnsembleResults[i], i);
}*/
workbook.Workbook.Save();
document.Close();
}
/// <summary>
/// Creates the sheet
/// </summary>
/// <param name="sheets">The sheet for this item</param>
/// <param name="spreadsheetDocument">The spreadsheet containing the sheets</param>
/// <param name="workbookPart">The workbookpart associated with the spreadsheet</param>
public void CreateSheet(Sheets sheets, SpreadsheetDocument spreadsheetDocument, WorkbookPart workbookPart)
{
// Add a WorksheetPart to the WorkbookPart.
WorksheetPart worksheetPart = workbookPart.AddNewPart<WorksheetPart>();
worksheetPart.Worksheet = new Worksheet(new SheetData());
Sheet sheet = new Sheet
{
Id = spreadsheetDocument.WorkbookPart.GetIdOfPart(worksheetPart),
SheetId = 3U, // <-- Change for each sheet that is created
Name = _groupOrder.SheetName
};
sheets.Append(sheet);
//Add cells to the sheet
InsertTextIntoCells(spreadsheetDocument, worksheetPart);
}
public void CreateSheet(Sheets sheets, SpreadsheetDocument spreadsheetDocument, WorkbookPart workbookPart)
{
// Add a WorksheetPart to the WorkbookPart.
WorksheetPart worksheetPart = workbookPart.AddNewPart<WorksheetPart>();
worksheetPart.Worksheet = new Worksheet(new SheetData());
Sheet sheet = new Sheet
{
Id = spreadsheetDocument.WorkbookPart.GetIdOfPart(worksheetPart),
SheetId = 4U,
Name = _resources.SheetName
};
sheets.Append(sheet);
//Add cells to the sheet
InsertTextIntoCells(spreadsheetDocument, worksheetPart);
}
/// <summary>
/// Adds a given worksheet to the document
/// </summary>
/// <param name="worksheet">Worksheet document to add</param>
/// <returns>Worksheet part just added</returns>
public OpenXmlSDK.WorksheetPart Add(XDocument worksheet)
{
// Associates base content to a new worksheet part
OpenXmlSDK.WorkbookPart workbook = ((OpenXmlSDK.SpreadsheetDocument)parentDocument.Document).WorkbookPart;
OpenXmlSDK.WorksheetPart worksheetPart = workbook.AddNewPart <OpenXmlSDK.WorksheetPart>();
XDocument worksheetDocument = parentDocument.GetXDocument(worksheetPart);
if (worksheetDocument.Root == null)
{
worksheetDocument.Add(
new XElement("root")
);
}
worksheetDocument.Root.ReplaceWith(worksheet.Root);
// Associates the worksheet part to the workbook part
XDocument document = parentDocument.GetXDocument(((OpenXmlSDK.SpreadsheetDocument)parentDocument.Document).WorkbookPart);
int sheetId =
document.Root
.Element(ns + "sheets")
.Elements(ns + "sheet")
.Count() + 1;
int worksheetCount =
document.Root
.Element(ns + "sheets")
.Elements(ns + "sheet")
.Where(
t =>
t.Attribute("name").Value.StartsWith("sheet", StringComparison.OrdinalIgnoreCase)
)
.Count() + 1;
// Adds content to workbook document to reference worksheet document
document.Root
.Element(ns + "sheets")
.Add(
new XElement(ns + "sheet",
new XAttribute("name", string.Format("sheet{0}", worksheetCount)),
new XAttribute("sheetId", sheetId),
new XAttribute(relationshipsns + "id", workbook.GetIdOfPart(worksheetPart))
)
);
return(worksheetPart);
}
public static void AddCellText(WorkbookPart workbookPart,
WorksheetPart worksheetPart,
string text,
uint colIndex,
uint rowIndex)
{
string columnName = GetColumnIdentifier((int)colIndex);
// Insert the text into the SharedStringTablePart.
int index = InsertSharedStringItem(workbookPart, text);
// Insert cell A1 into the new worksheet.
Cell cell = InsertCellInWorksheet(worksheetPart, columnName, rowIndex);
// Set the value of cell A1.
cell.CellValue = new CellValue(index.ToString());
cell.DataType = new EnumValue<CellValues>(CellValues.SharedString);
}
/// <summary>
/// Creates an empty (base) SpreadsheetDocument
/// </summary>
private static SpreadsheetDocument Create(string filePath)
{
SpreadsheetDocument document = new SpreadsheetDocument(filePath, true);
OpenXmlSDK.WorkbookPart workbookPart = ((OpenXmlSDK.SpreadsheetDocument)document.Document).AddWorkbookPart();
XDocument workbookDocument = document.GetXDocument(workbookPart);
//Creates an empty workbook
XDocument workbookContent = CreateEmptyWorkbook();
if (workbookDocument.Root == null)
{
workbookDocument.Add(workbookContent.Root);
}
else
{
workbookDocument.Root.ReplaceWith(workbookContent.Root);
}
return(document);
}
public static object GetCellValue(WorkbookPart workbookPart, Cell theCell)
{
int a = 1;
if (theCell.DataType == null)
{
return theCell.CellValue.Text;
}
var s = theCell.DataType;
if (theCell.DataType.InnerText.Equals("str"))
{
return Convert.ToString(theCell.CellValue.Text);
}
if (theCell.DataType.InnerText.Equals("n"))
{
return Convert.ToDecimal(theCell.CellValue.Text);
}
return theCell.DataType == null ? theCell.CellValue.Text : workbookPart.SharedStringTablePart.SharedStringTable.Elements<SharedStringItem>().ElementAt(Convert.ToInt32(theCell.CellValue.Text)).InnerText;
}
public List<object[]> LerPlanilhaDados(Stream stream)
{
List<object[]> dadosPlanilha = new List<object[]>();
using (_document = SpreadsheetDocument.Open(stream, true))
{
try
{
_wbPart = _document.WorkbookPart;
_sharedStringPart = GetSharedStringPart();
if (SelectFirstSheet())
{
int indice = 0;
foreach (Row row in _activeWorksheet.Descendants<Row>())
{
indice++;
if (indice == 1) continue; // Pula o cabeçalho.
List<object> dadosLinha = new List<object>();
foreach (Cell celula in row.Descendants<Cell>())
dadosLinha.Add(GetCellValue(celula));
dadosPlanilha.Add(dadosLinha.ToArray());
}
}
}
catch
{
throw;
}
finally
{
_document.Close();
}
return dadosPlanilha;
}
}
public Worksheet AddSheet(WorkbookPart workbookPart, string name)
{
var sheets = workbookPart.Workbook.GetFirstChild<Sheets>();
// add single Sheet
var workSheetpart = workbookPart.AddNewPart<WorksheetPart>();
workSheetpart.Worksheet = new Worksheet(new SheetData());
workSheetpart.Worksheet.Save();
var sheet = new Sheet
{
Id = workbookPart.GetIdOfPart(workSheetpart),
SheetId = (uint)(workbookPart.Workbook.Sheets.Count() + 1),
Name = name
};
sheets.Append(sheet);
workbookPart.Workbook.Save();
return workSheetpart.Worksheet;
}
public WorkbookIndexer(WorkbookPart toIndex)
{
if (toIndex == null)
{
throw new ArgumentNullException("workbookPart");
}
foreach (Sheet sheet in toIndex.Workbook.Sheets)
{
var worksheet = (WorksheetPart)toIndex.GetPartById(sheet.Id);
if (worksheet != null)
{
this.worksheets.Add(new WorksheetIndexer(worksheet, sheet));
}
}
// TODO: This should be modified to create the shared string table part instead of just bailing on an empty worksheet.
this.SharedStringTable = toIndex.SharedStringTablePart != null ?
new SharedStringTableIndexer(toIndex.SharedStringTablePart) :
null;
}
internal Workbook(ref SpreadsheetDocument spreadsheetDocument)
{
this.spreadsheetDocument = spreadsheetDocument;
if (this.spreadsheetDocument.WorkbookPart == null)
{
this.workbookPart = this.spreadsheetDocument.AddWorkbookPart();
}
else
{
this.workbookPart = this.spreadsheetDocument.WorkbookPart;
}
if (this.spreadsheetDocument.WorkbookPart.Workbook == null)
{
this.workbookPart.Workbook = new DocumentFormat.OpenXml.Spreadsheet.Workbook();
}
this.sheetCollection = new Spreadsheet.SheetCollection(ref this.workbookPart);
this.InitializeWorksheets();
}
/*
* static Excel.SharedStringTable SaveSharedStringTable()
* {
* var exportedSharedStringTable = new Excel.SharedStringTable();
*
* return exportedSharedStringTable;
* }
*/
static void SaveSheet(Package.WorkbookPart exportedWorkbookPart, Excel.Stylesheet styleSheet, Dictionary <CellFormat, uint> cellFormatList, Excel.Sheets exportedSheets, Sheet sheet, uint sheetId)
{
var exportedWorksheetPart = exportedWorkbookPart.AddNewPart <Package.WorksheetPart>();
string relId = exportedWorkbookPart.GetIdOfPart(exportedWorksheetPart);
var exportedWorksheet = new Excel.Worksheet();
exportedWorksheetPart.Worksheet = exportedWorksheet;
var exportedColumns = new Excel.Columns();
exportedWorksheet.Append(exportedColumns);
var exportedSheetData = new Excel.SheetData();
exportedWorksheet.Append(exportedSheetData);
var exportedSheet = new Excel.Sheet()
{
Name = sheet.Name, Id = relId, SheetId = sheetId
};
if (sheet.Hidden)
{
exportedSheet.State = Excel.SheetStateValues.Hidden;
}
exportedSheets.Append(exportedSheet);
foreach (var column in sheet.Columns.OrderBy(r => r.Index))
{
SaveColumn(exportedColumns, column);
}
foreach (var row in sheet.Rows.OrderBy(r => r.Index))
{
SaveRow(exportedSheetData, styleSheet, cellFormatList, row);
}
exportedWorksheetPart.Worksheet.Save();
}
static Excel.Stylesheet SaveStyleSheet(Package.WorkbookPart exportedWorkbookPart, ref Dictionary <CellFormat, uint> cellFormatList, Spreadsheet document)
{
var exportedStyleSheetPart = exportedWorkbookPart.AddNewPart <Package.WorkbookStylesPart>();
cellFormatList = new Dictionary <CellFormat, uint>();
Excel.Stylesheet exportedStyleSheet = new Excel.Stylesheet();
exportedStyleSheetPart.Stylesheet = exportedStyleSheet;
exportedStyleSheet.CellFormats = new Excel.CellFormats();
exportedStyleSheet.CellFormats.Append(new Excel.CellFormat()
{
NumberFormatId = 0, FontId = 0, FillId = 0, BorderId = 0, FormatId = 0
});
exportedStyleSheet.Fills = new Excel.Fills();
exportedStyleSheet.Fills.Append(new Excel.Fill(new Excel.PatternFill()
{
PatternType = new OpenXml.EnumValue <Excel.PatternValues>(Excel.PatternValues.None)
}));
exportedStyleSheet.Fills.Append(new Excel.Fill(new Excel.PatternFill()
{
PatternType = new OpenXml.EnumValue <Excel.PatternValues>(Excel.PatternValues.Gray125)
}));
exportedStyleSheet.Fonts = new Excel.Fonts();
exportedStyleSheet.Fonts.Append(new Excel.Font()
{
FontSize = new Excel.FontSize()
{
Val = 11
},
Color = new Excel.Color()
{
Theme = 1
},
FontName = new Excel.FontName()
{
Val = "Calibri"
},
FontFamilyNumbering = new Excel.FontFamilyNumbering()
{
Val = 2
},
FontScheme = new Excel.FontScheme()
{
Val = new OpenXml.EnumValue <Excel.FontSchemeValues>(Excel.FontSchemeValues.Minor)
}
});
exportedStyleSheet.Borders = new Excel.Borders();
exportedStyleSheet.Borders.Append(new Excel.Border()
{
LeftBorder = new Excel.LeftBorder(),
RightBorder = new Excel.RightBorder(),
TopBorder = new Excel.TopBorder(),
BottomBorder = new Excel.BottomBorder(),
DiagonalBorder = new Excel.DiagonalBorder()
});
exportedStyleSheet.CellStyleFormats = new Excel.CellStyleFormats();
exportedStyleSheet.CellStyleFormats.Append(new Excel.CellFormat()
{
NumberFormatId = 0, FontId = 0, FillId = 0, BorderId = 0
});
exportedStyleSheet.CellStyles = new Excel.CellStyles();
exportedStyleSheet.CellStyles.Append(new Excel.CellStyle()
{
Name = "Normal", FormatId = 0, BuiltinId = 0
});
exportedStyleSheet.DifferentialFormats = new Excel.DifferentialFormats();
exportedStyleSheet.TableStyles = new Excel.TableStyles()
{
DefaultTableStyle = "TableStyleMedium9", DefaultPivotStyle = "PivotStyleLight16"
};
foreach (var cellFormat in document.CellFormats())
{
if (cellFormat.Count() > 0)
{
cellFormatList.Add(cellFormat, (uint)exportedStyleSheet.CellFormats.ChildElements.Count());
exportedStyleSheet.CellFormats.Append(new Excel.CellFormat()
{
NumberFormatId = 0,
FontId = 0,
BorderId = 0,
FormatId = 0,
FillId = (uint)exportedStyleSheet.Fills.ChildElements.Count(),
ApplyFill = true
});
exportedStyleSheet.Fills.Append(new Excel.Fill(new Excel.PatternFill()
{
PatternType = new OpenXml.EnumValue <Excel.PatternValues>(Excel.PatternValues.Solid),
ForegroundColor = new Excel.ForegroundColor()
{
Rgb = new OpenXml.HexBinaryValue(cellFormat.FillColor)
}
}));
}
}
exportedStyleSheet.Save();
return(exportedStyleSheet);
}
internal SheetCollection(ref DocumentFormat.OpenXml.Packaging.WorkbookPart workbookPart)
{
this.workbookPart = workbookPart;
sheets = this.workbookPart.Workbook.AppendChild(new DocumentFormat.OpenXml.Spreadsheet.Sheets());
this.list = new List <Spreadsheet.Sheet>();
}
/// <summary>
/// Creates a chartsheet part from given data
/// </summary>
/// <param name="chartType">Type of chart to generate</param>
/// <param name="values">Values to represent in the chart</param>
/// <param name="headerReference">Columns to be used as series</param>
/// <param name="categoryReference">Column to be used as category</param>
/// <returns>Chartsheet part with contents related</returns>
public OpenXmlSDK.ChartsheetPart Create(ChartType chartType, List <string> values, List <string> headerReference, string categoryReference)
{
//Creates base content and associates it to a new chartsheet part
XDocument chartsheet = CreateEmptyChartsheet();
OpenXmlSDK.WorkbookPart workbook = ((OpenXmlSDK.SpreadsheetDocument)parentDocument.Document).WorkbookPart;
OpenXmlSDK.ChartsheetPart chartsheetPart = workbook.AddNewPart <OpenXmlSDK.ChartsheetPart>();
XDocument chartsheetDocument = parentDocument.GetXDocument(chartsheetPart);
XDocument newChartsheetDocument = CreateEmptyChartsheet();
if (chartsheetDocument.Root == null)
{
chartsheetDocument.Add(
newChartsheetDocument.Root
);
}
else
{
chartsheetDocument.Root.ReplaceWith(newChartsheetDocument.Root);
}
//Creates a base drawings part and associates it to the chartsheet part
OpenXmlSDK.DrawingsPart drawingsPart = chartsheetPart.AddNewPart <OpenXmlSDK.DrawingsPart>();
XDocument drawingsDocument = parentDocument.GetXDocument(drawingsPart);
XDocument newDrawingDocument = CreateEmptyDrawing();
if (drawingsDocument.Root == null)
{
drawingsDocument.Add(
newDrawingDocument.Root
);
}
else
{
drawingsDocument.Root.ReplaceWith(newDrawingDocument.Root);
}
//Adds content to chartsheet document to reference drawing document
chartsheetDocument
.Element(ns + "chartsheet")
.Add(
new XElement(ns + "drawing",
new XAttribute(relationshipsns + "id", chartsheetPart.GetIdOfPart(drawingsPart))
)
);
//creates the chart part and associates it to the drawings part
OpenXmlSDK.ChartPart chartPart = drawingsPart.AddNewPart <OpenXmlSDK.ChartPart>();
XDocument chartDocument = parentDocument.GetXDocument(chartPart);
XDocument newChartDocument = CreateChart(chartType, values, headerReference, categoryReference);// CreateEmptyChart();
if (chartDocument.Root == null)
{
chartDocument.Add(
newChartDocument.Root
);
}
else
{
chartDocument.Root.ReplaceWith(newChartDocument.Root);
}
//Adds content to drawing document to reference chart document
drawingsDocument
.Descendants(drawingns + "graphicData")
.First()
.Add(
new XAttribute("uri", chartns),
new XElement(chartns + "chart",
new XAttribute(XNamespace.Xmlns + "c", chartns),
new XAttribute(XNamespace.Xmlns + "r", relationshipsns),
new XAttribute(relationshipsns + "id", drawingsPart.GetIdOfPart(chartPart))
)
);
//Associates the chartsheet part to the workbook part
XDocument document = parentDocument.GetXDocument(((OpenXmlSDK.SpreadsheetDocument)parentDocument.Document).WorkbookPart);
int sheetId = document.Root.Element(ns + "sheets").Elements(ns + "sheet").Count() + 1;
int chartsheetCount =
document.Root
.Element(ns + "sheets")
.Elements(ns + "sheet")
.Where(
t =>
t.Attribute("name").Value.StartsWith("chart")
)
.Count() + 1;
//Adds content to workbook document to reference chartsheet document
document.Root
.Element(ns + "sheets")
.Add(
new XElement(ns + "sheet",
new XAttribute("name", string.Format("chart{0}", chartsheetCount)),
new XAttribute("sheetId", sheetId),
new XAttribute(relationshipsns + "id", workbook.GetIdOfPart(chartsheetPart))
)
);
return(chartsheetPart);
}
