/// <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); }