Skip to content

Instantly share code, notes, and snippets.

@kzelda
Forked from iampanya/ExcelHelper.cs
Created July 9, 2018 15:24
Show Gist options
  • Save kzelda/a0ba757a8704e6e12fcb54b2b1df262d to your computer and use it in GitHub Desktop.
Save kzelda/a0ba757a8704e6e12fcb54b2b1df262d to your computer and use it in GitHub Desktop.
using DocumentFormat.OpenXml;
using DocumentFormat.OpenXml.Packaging;
using DocumentFormat.OpenXml.Spreadsheet;
using System.Linq;
namespace ExcelHelper
{
public static class ExcelHelper
{
public static WorksheetPart GetWorksheetPartByName(SpreadsheetDocument document, string sheetName)
{
var sheets = document.WorkbookPart.Workbook.GetFirstChild<Sheets>()
.Elements<Sheet>().Where(s => s.Name == sheetName);
if (sheets.Count() == 0)
{
return null;
}
string id = sheets.First().Id.Value;
return (WorksheetPart)document.WorkbookPart.GetPartById(id);
}
public static Cell GetCell(Worksheet workSheet, string column, uint rowIndex)
{
string cellReference = column + rowIndex;
// 1. Get row
Row row = GetRow(workSheet, rowIndex);
// 2. Get Cell by cellReference
Cell cell = row.Elements<Cell>().Where(c => c.CellReference == cellReference).FirstOrDefault();
// 3. If cell not exist, create new one.
if(cell == null)
{
// Cells must be in sequential order according to CellReference. Determine where to insert the new cell.
Cell refCell = null;
foreach(Cell c in row.Elements<Cell>())
{
if (c.CellReference.Value.Length == cellReference.Length)
{
if (string.Compare(c.CellReference.Value, cellReference, true) > 0)
{
refCell = cell;
break;
}
}
}
cell = new Cell() { CellReference = cellReference };
row.InsertBefore(cell, refCell);
workSheet.Save();
}
// 4. Return Cell
return cell;
}
public static Row GetRow(Worksheet workSheet, uint rowIndex)
{
// 1. Get row by rowIndex
Row row = workSheet.GetFirstChild<SheetData>().Elements<Row>().Where(r => r.RowIndex == rowIndex).FirstOrDefault();
// 2. If row not exist, create new one
if(row == null)
{
row = new Row() { RowIndex = rowIndex };
var index = workSheet.GetFirstChild<SheetData>().Elements<Row>().Where(e => e.RowIndex < rowIndex).Count();
workSheet.GetFirstChild<SheetData>().InsertAt(row, index);
workSheet.Save();
}
// 3. Return row
return row;
}
// Given a column name, a row index, and a WorksheetPart, inserts a cell into the worksheet.
// If the cell already exists, returns it.
private static Cell InsertCellInWorksheet(string columnName, uint rowIndex, WorksheetPart worksheetPart)
{
Worksheet worksheet = worksheetPart.Worksheet;
SheetData sheetData = worksheet.GetFirstChild<SheetData>();
string cellReference = columnName + rowIndex;
// If the worksheet does not contain a row with the specified row index, insert one.
Row row;
if (sheetData.Elements<Row>().Where(r => r.RowIndex == rowIndex).Count() != 0)
{
row = sheetData.Elements<Row>().Where(r => r.RowIndex == rowIndex).First();
}
else
{
row = new Row() { RowIndex = rowIndex };
sheetData.Append(row);
}
// If there is not a cell with the specified column name, insert one.
if (row.Elements<Cell>().Where(c => c.CellReference.Value == columnName + rowIndex).Count() > 0)
{
return row.Elements<Cell>().Where(c => c.CellReference.Value == cellReference).First();
}
else
{
// Cells must be in sequential order according to CellReference. Determine where to insert the new cell.
Cell refCell = null;
foreach (Cell cell in row.Elements<Cell>())
{
if (string.Compare(cell.CellReference.Value, cellReference, true) > 0)
{
refCell = cell;
break;
}
}
Cell newCell = new Cell() { CellReference = cellReference };
row.InsertBefore(newCell, refCell);
worksheet.Save();
return newCell;
}
}
public static void UpdateCell(Worksheet workSheet, string column, uint rowIndex, string text)
{
Cell cell = GetCell(workSheet, column, rowIndex);
cell.CellValue = new CellValue(text);
cell.DataType = new EnumValue<CellValues>(CellValues.String);
workSheet.Save();
}
}
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment