-
-
Save kzelda/2facdff2d924349fe96c37eab0e9ee47 to your computer and use it in GitHub Desktop.
Read and Write Excel using open xml in c#
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
using DocumentFormat.OpenXml.Packaging; | |
using DocumentFormat.OpenXml.Spreadsheet; | |
using System; | |
using System.Collections.Generic; | |
using System.Data; | |
using System.Linq; | |
using System.Text; | |
using System.Threading.Tasks; | |
namespace ReadWriteExcelUsingOpenXml.Shared | |
{ | |
public class Helper | |
{ | |
public static DataTable ReadExcelSheet(string fname, bool firstRowIsHeader = true) | |
{ | |
List<string> Headers = new List<string>(); | |
DataTable dt = new DataTable(); | |
using (SpreadsheetDocument doc = SpreadsheetDocument.Open(fname, false)) | |
{ | |
//Read the first Sheets | |
Sheet sheet = doc.WorkbookPart.Workbook.Sheets.GetFirstChild<Sheet>(); | |
Worksheet worksheet = (doc.WorkbookPart.GetPartById(sheet.Id.Value) as WorksheetPart).Worksheet; | |
IEnumerable<Row> rows = worksheet.GetFirstChild<SheetData>().Descendants<Row>(); | |
int counter = 0; | |
foreach (Row row in rows) | |
{ | |
counter = counter + 1; | |
//Read the first row as header | |
if (counter == 1) | |
{ | |
var j = 1; | |
foreach (Cell cell in row.Descendants<Cell>()) | |
{ | |
var colunmName = firstRowIsHeader ? GetCellValue(doc, cell) : "Field" + j++; | |
Console.WriteLine(colunmName); | |
Headers.Add(colunmName); | |
dt.Columns.Add(colunmName); | |
} | |
} | |
else | |
{ | |
dt.Rows.Add(); | |
int i = 0; | |
foreach (Cell cell in row.Descendants<Cell>()) | |
{ | |
dt.Rows[dt.Rows.Count - 1][i] = GetCellValue(doc, cell); | |
i++; | |
} | |
} | |
} | |
} | |
return dt; | |
} | |
public static void CreateExcelFile(DataTable table, string destination) | |
{ | |
var ds = new DataSet(); | |
ds.Tables.Add(table); | |
ExportDSToExcel(ds, destination); | |
} | |
private static string GetCellValue(SpreadsheetDocument doc, Cell cell) | |
{ | |
string value = cell.CellValue.InnerText; | |
if (cell.DataType != null && cell.DataType.Value == CellValues.SharedString) | |
{ | |
return doc.WorkbookPart.SharedStringTablePart.SharedStringTable.ChildElements.GetItem(int.Parse(value)).InnerText; | |
} | |
return value; | |
} | |
public static void ExportDSToExcel(DataSet ds, string destination) | |
{ | |
// https://stackoverflow.com/questions/11811143/export-datatable-to-excel-with-open-xml-sdk-in-c-sharp | |
using (var workbook = SpreadsheetDocument.Create(destination, DocumentFormat.OpenXml.SpreadsheetDocumentType.Workbook)) | |
{ | |
var workbookPart = workbook.AddWorkbookPart(); | |
workbook.WorkbookPart.Workbook = new DocumentFormat.OpenXml.Spreadsheet.Workbook(); | |
workbook.WorkbookPart.Workbook.Sheets = new DocumentFormat.OpenXml.Spreadsheet.Sheets(); | |
uint sheetId = 1; | |
foreach (DataTable table in ds.Tables) | |
{ | |
var sheetPart = workbook.WorkbookPart.AddNewPart<WorksheetPart>(); | |
var sheetData = new DocumentFormat.OpenXml.Spreadsheet.SheetData(); | |
sheetPart.Worksheet = new DocumentFormat.OpenXml.Spreadsheet.Worksheet(sheetData); | |
DocumentFormat.OpenXml.Spreadsheet.Sheets sheets = workbook.WorkbookPart.Workbook.GetFirstChild<DocumentFormat.OpenXml.Spreadsheet.Sheets>(); | |
string relationshipId = workbook.WorkbookPart.GetIdOfPart(sheetPart); | |
if (sheets.Elements<DocumentFormat.OpenXml.Spreadsheet.Sheet>().Count() > 0) | |
{ | |
sheetId = | |
sheets.Elements<DocumentFormat.OpenXml.Spreadsheet.Sheet>().Select(s => s.SheetId.Value).Max() + 1; | |
} | |
DocumentFormat.OpenXml.Spreadsheet.Sheet sheet = new DocumentFormat.OpenXml.Spreadsheet.Sheet() { Id = relationshipId, SheetId = sheetId, Name = table.TableName }; | |
sheets.Append(sheet); | |
DocumentFormat.OpenXml.Spreadsheet.Row headerRow = new DocumentFormat.OpenXml.Spreadsheet.Row(); | |
List<String> columns = new List<string>(); | |
foreach (DataColumn column in table.Columns) | |
{ | |
columns.Add(column.ColumnName); | |
DocumentFormat.OpenXml.Spreadsheet.Cell cell = new DocumentFormat.OpenXml.Spreadsheet.Cell(); | |
cell.DataType = DocumentFormat.OpenXml.Spreadsheet.CellValues.String; | |
cell.CellValue = new DocumentFormat.OpenXml.Spreadsheet.CellValue(column.ColumnName); | |
headerRow.AppendChild(cell); | |
} | |
sheetData.AppendChild(headerRow); | |
foreach (DataRow dsrow in table.Rows) | |
{ | |
DocumentFormat.OpenXml.Spreadsheet.Row newRow = new DocumentFormat.OpenXml.Spreadsheet.Row(); | |
foreach (String col in columns) | |
{ | |
DocumentFormat.OpenXml.Spreadsheet.Cell cell = new DocumentFormat.OpenXml.Spreadsheet.Cell(); | |
cell.DataType = DocumentFormat.OpenXml.Spreadsheet.CellValues.String; | |
cell.CellValue = new DocumentFormat.OpenXml.Spreadsheet.CellValue(dsrow[col].ToString()); // | |
newRow.AppendChild(cell); | |
} | |
sheetData.AppendChild(newRow); | |
} | |
} | |
} | |
} | |
} | |
} |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
Hello, kzelda i want to get empty cell in spreadsheet. please help me.