/// <summary>
/// Create empty rows and cols to improve performance.
/// </summary>
/// <param name="rowCount"></param>
/// <param name="colCount"></param>
internal void CreateEmptyCells(int rowCount, int colCount)
{
if (Rows.Count != 0)
{
throw new InvalidOperationException("Must be called before rows are filled");
}
XmlNode sheetDataNode = WorksheetXml.SelectSingleNode("//d:sheetData", NameSpaceManager);
for (int rowNum = 1; rowNum <= rowCount; rowNum++)
{
// Add element
XmlElement rowElement = WorksheetXml.CreateElement("row", ExcelPackage.schemaMain);
rowElement.SetAttribute("r", rowNum.ToString());
sheetDataNode.AppendChild(rowElement);
ExcelRow row = new ExcelRow(this, rowElement);
Rows.Add(rowNum, row);
for (int colNum = 1; colNum <= colCount; colNum++)
{
XmlElement cellElement = WorksheetXml.CreateElement("c", ExcelPackage.schemaMain);
cellElement.SetAttribute(ExcelWorksheet.tempColumnNumberTag, colNum.ToString());
rowElement.AppendChild(cellElement);
ExcelCell cell = new ExcelCell(this, cellElement, rowNum, colNum);
row.Cells.Add(colNum, cell);
}
}
}
void ShiftRows(int start, int change)
{
Dictionary <int, ExcelRow> newRows = new Dictionary <int, ExcelRow>();
foreach (int rowNum in Rows.Keys)
{
ExcelRow row = Rows[rowNum];
int newRowNum = (rowNum >= start) ? rowNum + 1 : rowNum;
newRows.Add(newRowNum, row);
}
_rows = newRows;
}
/// <summary>
/// Copies the current row to a new worksheet
/// </summary>
/// <param name="added">The worksheet where the copy will be created</param>
internal void Clone(ExcelWorksheet added)
{
ExcelRow newRow = added.Row(Row);
newRow.Collapsed = Collapsed;
newRow.Height = Height;
newRow.Hidden = Hidden;
newRow.OutlineLevel = OutlineLevel;
newRow.PageBreak = PageBreak;
newRow.Phonetic = Phonetic;
newRow._styleName = _styleName;
newRow.StyleID = StyleID;
}
// TODO: implement freeze pane.
// TODO: implement page margin properties
#endregion // END Worksheet Public Properties
#region Worksheet Public Methods
/// <summary>
/// Provides access to an individual row within the worksheet so you can set its properties.
/// </summary>
/// <param name="rowNum">The row number in the worksheet</param>
/// <returns></returns>
public ExcelRow Row(int rowNum)
{
ExcelRow row;
if (Rows.TryGetValue(rowNum, out row))
{
return(row);
}
row = new ExcelRow(this, rowNum);
Rows.Add(rowNum, row);
return(row);
}
/// <summary>
/// Provides access to an individual cell within the worksheet.
/// </summary>
/// <param name="rowNum">The row number in the worksheet</param>
/// <param name="colNum">The column number in the worksheet</param>
/// <returns></returns>
public ExcelCell Cell(int rowNum, int colNum)
{
ExcelRow row = Row(rowNum);
ExcelCell cell;
if (row.Cells.TryGetValue(colNum, out cell))
{
return(cell);
}
cell = new ExcelCell(this, rowNum, colNum);
row.Cells.Add(colNum, cell);
return(cell);
}
Dictionary <int, ExcelRow> ReadData()
{
Dictionary <int, ExcelRow> rows = new Dictionary <int, ExcelRow>();
foreach (XmlElement rowElement in WorksheetXml.SelectNodes("//d:sheetData/d:row", NameSpaceManager))
{
int rowNum = Convert.ToInt32(rowElement.Attributes.GetNamedItem("r").Value);
ExcelRow row = new ExcelRow(this, rowElement);
rows.Add(rowNum, row);
// Get all cells for the row
foreach (XmlElement cellElement in rowElement.SelectNodes("./d:c", NameSpaceManager))
{
int colNum = Convert.ToInt32(cellElement.Attributes[ExcelWorksheet.tempColumnNumberTag].Value);
ExcelCell cell = new ExcelCell(this, cellElement, rowNum, colNum);
row.Cells.Add(colNum, cell);
}
}
return(rows);
}
///<summary>
/// Hide or show the current row
///</summary>
///<param name="row">Current row</param>
///<param name="hide">Hide or show</param>
///<returns>Current row</returns>
public static ExcelRow Hide(this ExcelRow row, bool hide)
{
row.Hidden = hide;
return(row);
}
///<summary>
/// Hide the current row
///</summary>
///<param name="row">Current row</param>
///<returns>Current row</returns>
public static ExcelRow Hide(this ExcelRow row)
{
return(row.Hide(true));
}
private void SetStyleAddress(StyleBase sender, Style.StyleChangeEventArgs e, ExcelAddressBase address, ExcelWorksheet ws, ref Dictionary <int, int> styleCashe)
{
if (address.Start.Column == 0 || address.Start.Row == 0)
{
throw (new Exception("error address"));
}
//Columns
else if (address.Start.Row == 1 && address.End.Row == ExcelPackage.MaxRows)
{
ExcelColumn column;
//Get the startcolumn
ulong colID = ExcelColumn.GetColumnID(ws.SheetID, address.Start.Column);
if (!ws._columns.ContainsKey(colID))
{
column = ws.Column(address.Start.Column);
}
else
{
column = ws._columns[colID] as ExcelColumn;
}
var index = ws._columns.IndexOf(colID);
while (column.ColumnMin <= address.End.Column)
{
if (column.ColumnMax > address.End.Column)
{
var newCol = ws.CopyColumn(column, address.End.Column + 1);
newCol.ColumnMax = column.ColumnMax;
column.ColumnMax = address.End.Column;
}
if (styleCashe.ContainsKey(column.StyleID))
{
column.StyleID = styleCashe[column.StyleID];
}
else
{
ExcelXfs st = CellXfs[column.StyleID];
int newId = st.GetNewID(CellXfs, sender, e.StyleClass, e.StyleProperty, e.Value);
styleCashe.Add(column.StyleID, newId);
column.StyleID = newId;
}
index++;
if (index >= ws._columns.Count)
{
break;
}
else
{
column = (ws._columns[index] as ExcelColumn);
}
}
if (column._columnMax < address.End.Column)
{
var newCol = ws.Column(column._columnMax + 1) as ExcelColumn;
newCol._columnMax = address.End.Column;
if (styleCashe.ContainsKey(newCol.StyleID))
{
newCol.StyleID = styleCashe[newCol.StyleID];
}
else
{
ExcelXfs st = CellXfs[column.StyleID];
int newId = st.GetNewID(CellXfs, sender, e.StyleClass, e.StyleProperty, e.Value);
styleCashe.Add(newCol.StyleID, newId);
newCol.StyleID = newId;
}
//column._columnMax = address.End.Column;
}
//Set for individual cells in the spann. We loop all cells here since the cells are sorted with columns first.
foreach (ExcelCell cell in ws._cells)
{
if (cell.Column >= address.Start.Column &&
cell.Column <= address.End.Column)
{
if (styleCashe.ContainsKey(cell.StyleID))
{
cell.StyleID = styleCashe[cell.StyleID];
}
else
{
ExcelXfs st = CellXfs[cell.StyleID];
int newId = st.GetNewID(CellXfs, sender, e.StyleClass, e.StyleProperty, e.Value);
styleCashe.Add(cell.StyleID, newId);
cell.StyleID = newId;
}
}
}
}
//Rows
else if (address.Start.Column == 1 && address.End.Column == ExcelPackage.MaxColumns)
{
for (int rowNum = address.Start.Row; rowNum <= address.End.Row; rowNum++)
{
ExcelRow row = ws.Row(rowNum);
if (row.StyleID == 0 && ws._columns.Count > 0)
{
//TODO: We should loop all columns here and change each cell. But for now we take style of column A.
foreach (ExcelColumn column in ws._columns)
{
row.StyleID = column.StyleID;
break; //Get the first one and break.
}
}
if (styleCashe.ContainsKey(row.StyleID))
{
row.StyleID = styleCashe[row.StyleID];
}
else
{
ExcelXfs st = CellXfs[row.StyleID];
int newId = st.GetNewID(CellXfs, sender, e.StyleClass, e.StyleProperty, e.Value);
styleCashe.Add(row.StyleID, newId);
row.StyleID = newId;
}
}
//Get Start Cell
ulong rowID = ExcelRow.GetRowID(ws.SheetID, address.Start.Row);
int index = ws._cells.IndexOf(rowID);
index = ~index;
while (index < ws._cells.Count)
{
var cell = ws._cells[index] as ExcelCell;
if (cell.Row > address.End.Row)
{
break;
}
if (styleCashe.ContainsKey(cell.StyleID))
{
cell.StyleID = styleCashe[cell.StyleID];
}
else
{
ExcelXfs st = CellXfs[cell.StyleID];
int newId = st.GetNewID(CellXfs, sender, e.StyleClass, e.StyleProperty, e.Value);
styleCashe.Add(cell.StyleID, newId);
cell.StyleID = newId;
}
index++;
}
}
else //Cellrange
{
for (int col = address.Start.Column; col <= address.End.Column; col++)
{
for (int row = address.Start.Row; row <= address.End.Row; row++)
{
ExcelCell cell = ws.Cell(row, col);
if (styleCashe.ContainsKey(cell.StyleID))
{
cell.StyleID = styleCashe[cell.StyleID];
}
else
{
ExcelXfs st = CellXfs[cell.StyleID];
int newId = st.GetNewID(CellXfs, sender, e.StyleClass, e.StyleProperty, e.Value);
styleCashe.Add(cell.StyleID, newId);
cell.StyleID = newId;
}
}
}
}
}
private void CloneCells(ExcelWorksheet Copy, ExcelWorksheet added)
{
bool sameWorkbook = (Copy.Workbook == _pck.Workbook);
bool doAdjust = _pck.DoAdjustDrawings;
_pck.DoAdjustDrawings = false;
added.MergedCells.List.AddRange(Copy.MergedCells.List);
//Formulas
foreach (IRangeID f in Copy._formulaCells)
{
added._formulaCells.Add(f);
}
//Shared Formulas
foreach (int key in Copy._sharedFormulas.Keys)
{
added._sharedFormulas.Add(key, Copy._sharedFormulas[key]);
}
Dictionary <int, int> styleCashe = new Dictionary <int, int>();
//Cells
foreach (ExcelCell cell in Copy._cells)
{
if (sameWorkbook) //Same workbook == same styles
{
added._cells.Add(cell.Clone(added));
}
else
{
ExcelCell addedCell = cell.Clone(added);
if (styleCashe.ContainsKey(cell.StyleID))
{
addedCell.StyleID = styleCashe[cell.StyleID];
}
else
{
addedCell.StyleID = added.Workbook.Styles.CloneStyle(Copy.Workbook.Styles, cell.StyleID);
if (cell.StyleName != "") //Named styles
{
if (!Copy.Workbook.Styles.NamedStyles.ExistsKey(cell.StyleName))
{
var ns = Copy.Workbook.Styles.CreateNamedStyle(cell.StyleName);
ns.StyleXfId = addedCell.StyleID;
}
}
styleCashe.Add(cell.StyleID, addedCell.StyleID);
}
added._cells.Add(addedCell);
}
}
//Rows
foreach (ExcelRow row in Copy._rows)
{
row.Clone(added);
if (!sameWorkbook) //Same workbook == same styles
{
ExcelRow addedRow = added.Row(row.Row) as ExcelRow;
if (styleCashe.ContainsKey(row.StyleID))
{
addedRow.StyleID = styleCashe[row.StyleID];
}
else
{
addedRow.StyleID = added.Workbook.Styles.CloneStyle(Copy.Workbook.Styles, addedRow.StyleID);
if (row.StyleName != "") //Named styles
{
if (!Copy.Workbook.Styles.NamedStyles.ExistsKey(row.StyleName))
{
var ns = Copy.Workbook.Styles.CreateNamedStyle(row.StyleName);
ns.StyleXfId = addedRow.StyleID;
}
}
styleCashe.Add(row.StyleID, addedRow.StyleID);
}
}
}
//Columns
foreach (ExcelColumn col in Copy._columns)
{
col.Clone(added);
if (!sameWorkbook) //Same workbook == same styles
{
ExcelColumn addedCol = added.Column(col.ColumnMin) as ExcelColumn;
if (styleCashe.ContainsKey(col.StyleID))
{
addedCol.StyleID = styleCashe[col.StyleID];
}
else
{
addedCol.StyleID = added.Workbook.Styles.CloneStyle(Copy.Workbook.Styles, addedCol.StyleID);
if (col.StyleName != "") //Named styles
{
if (!Copy.Workbook.Styles.NamedStyles.ExistsKey(col.StyleName))
{
var ns = Copy.Workbook.Styles.CreateNamedStyle(col.StyleName);
ns.StyleXfId = addedCol.StyleID;
}
}
styleCashe.Add(col.StyleID, addedCol.StyleID);
}
}
}
added._package.DoAdjustDrawings = doAdjust;
}
public ExcelRow InsertRow(int rowPosition, ExcelRow copyStylesFromRow)
{
this.Worksheet.InsertRow(rowPosition, 1, copyStylesFromRow.Row);
return Worksheet.Row(rowPosition);
}
Dictionary<int, ExcelRow> ReadData()
{
Dictionary<int, ExcelRow> rows = new Dictionary<int,ExcelRow>();
foreach (XmlElement rowElement in WorksheetXml.SelectNodes("//d:sheetData/d:row", NameSpaceManager))
{
int rowNum = Convert.ToInt32(rowElement.Attributes.GetNamedItem("r").Value);
ExcelRow row = new ExcelRow(this, rowElement);
rows.Add(rowNum, row);
// Get all cells for the row
foreach (XmlElement cellElement in rowElement.SelectNodes("./d:c", NameSpaceManager))
{
int colNum = Convert.ToInt32(cellElement.Attributes[ExcelWorksheet.tempColumnNumberTag].Value);
ExcelCell cell = new ExcelCell(this, cellElement, rowNum, colNum);
row.Cells.Add(colNum, cell);
}
}
return rows;
}
/// <summary>
/// Create empty rows and cols to improve performance.
/// </summary>
/// <param name="rowCount"></param>
/// <param name="colCount"></param>
internal void CreateEmptyCells(int rowCount, int colCount)
{
if (Rows.Count != 0) { throw new InvalidOperationException("Must be called before rows are filled"); }
XmlNode sheetDataNode = WorksheetXml.SelectSingleNode("//d:sheetData", NameSpaceManager);
for (int rowNum = 1; rowNum <= rowCount; rowNum++)
{
// Add element
XmlElement rowElement = WorksheetXml.CreateElement("row", ExcelPackage.schemaMain);
rowElement.SetAttribute("r", rowNum.ToString());
sheetDataNode.AppendChild(rowElement);
ExcelRow row = new ExcelRow(this, rowElement);
Rows.Add(rowNum, row);
for(int colNum = 1; colNum <= colCount; colNum++)
{
XmlElement cellElement = WorksheetXml.CreateElement("c", ExcelPackage.schemaMain);
cellElement.SetAttribute(ExcelWorksheet.tempColumnNumberTag, colNum.ToString());
rowElement.AppendChild(cellElement);
ExcelCell cell = new ExcelCell(this, cellElement, rowNum, colNum);
row.Cells.Add(colNum, cell);
}
}
}
/// <summary>
/// Provides access to an individual row within the worksheet so you can set its properties.
/// </summary>
/// <param name="rowNum">The row number in the worksheet</param>
/// <returns></returns>
public ExcelRow Row(int rowNum)
{
ExcelRow row;
if (Rows.TryGetValue(rowNum, out row)) { return row; }
row = new ExcelRow(this, rowNum);
Rows.Add(rowNum, row);
return row;
}
/*
* public static Dictionary<string, Dictionary<string, Classes.MaltType>> LoadMalts(string file, out string error)
* {
* error = "";
* Dictionary<string, Dictionary<string, Classes.MaltType>> ret = new Dictionary<string, Dictionary<string, MaltType>>();
* try
* {
* SpreadsheetInfo.SetLicense("FREE-LIMITED-KEY");
* ExcelFile ef = ExcelFile.Load(file);
*
* //StringBuilder sb = new StringBuilder();
* //int colcnt = 11;
*
* foreach (GemBox.Spreadsheet.ExcelWorksheet sheet in ef.Worksheets)
* {
* //string fane = sheet.Name;
* ret.Add(sheet.Name, new Dictionary<string, Classes.MaltType>());
* int r_index = 0;
* bool firstRow = true;
* foreach (GemBox.Spreadsheet.ExcelRow row in sheet.Rows)
* {
* r_index++;
* if (firstRow)
* {
* firstRow = false;
* continue;
* }
* string line = "";
* for (int c_index = 0; c_index < MaltType.EXCEL_ROW_COUNT; c_index++)
* {
* ExcelCell cell = row.Cells[0, c_index];
* if (cell.Value != null)
* {
* line += cell.Value.ToString();
* }
* if (c_index < MaltType.EXCEL_ROW_COUNT - 1)
* {
* line += "|";
* }
* }
*
* Classes.MaltType mt = new Classes.MaltType(line);
* if (mt.ParsedOk)
* {
* if (!ret[sheet.Name].ContainsKey(mt.Malttype.ToLower()))
* {
* ret[sheet.Name].Add(mt.Malttype.ToLower(), mt);
* }
* }
* }
* }
* }
* catch (Exception ex)
* {
* error = ex.Message;
* }
* return ret;
* }
*/
public static Dictionary <string, Dictionary <string, Classes.MaltType> > LoadMalts_EEPLUS(string file, out string error)
{
error = "";
Dictionary <string, Dictionary <string, Classes.MaltType> > ret = new Dictionary <string, Dictionary <string, MaltType> >();
try
{
//SpreadsheetInfo.SetLicense("FREE-LIMITED-KEY");
//ExcelFile ef = ExcelFile.Load(file);
FileInfo newFile = new FileInfo(file);
ExcelPackage pck = new ExcelPackage(newFile);
foreach (var sheet in pck.Workbook.Worksheets)
{
ret.Add(sheet.Name, new Dictionary <string, Classes.MaltType>());
int r_index = 0;
bool firstRow = true;
//bool done_rows = false;
//int i = 0;
//while (done_rows)
//{
// string tt = sheet.Cells[i, 0].ToString();
// i++;
//}
for (int row = sheet.Dimension.Start.Row; row <= sheet.Dimension.End.Row; row++)
{
if (row == sheet.Dimension.Start.Row)
{
continue; // skip header
}
string line = "";
int columns_end = (sheet.Dimension.End.Column <= MaltType.EXCEL_ROW_COUNT) ? sheet.Dimension.End.Column : MaltType.EXCEL_ROW_COUNT;
for (int c_index = sheet.Dimension.Start.Column; c_index <= columns_end; c_index++)
{
OfficeOpenXml.ExcelRow e_row = sheet.Row(row);
ExcelRange cell = sheet.Cells[row, c_index];
if (cell.Value != null)
{
line += cell.Value.ToString();
}
if (c_index < MaltType.EXCEL_ROW_COUNT)
{
line += "|";
}
}
Classes.MaltType mt = new Classes.MaltType(line);
if (mt.ParsedOk)
{
if (!ret[sheet.Name].ContainsKey(mt.Malttype.ToLower()))
{
ret[sheet.Name].Add(mt.Malttype.ToLower(), mt);
}
}
}
}
}
catch (Exception ex)
{
error = ex.Message;
}
return(ret);
}
