public HelperSheet(SettlementHistoryWorkbook parent, Doc.Worksheet worksheet, string sheetName) { _parent = parent; _worksheet = worksheet; this.SheetName = sheetName; var rows = worksheet.GetFirstChild <Doc.SheetData>().Elements <Doc.Row>(); }
public IEnumerable <HelperRow> GetRows() { foreach (var row in _worksheet.GetFirstChild <Doc.SheetData>().Elements <Doc.Row>()) { yield return(new HelperRow(_parent, SheetName, row)); } }
private static void ReadExcelData(ClientContext clientContextobj, string FileName) { const string lstDocName = "Documents"; try { DataTable datatable = new DataTable("TempExcelDataTable"); List list = clientContextobj.Web.Lists.GetByTitle(lstDocName); clientContextobj.Load(list.RootFolder); clientContextobj.ExecuteQuery(); string fileServerRelativeUrl = list.RootFolder.ServerRelativeUrl + "/" + "xlsheet.xlsx"; SP.File fileobj = clientContextobj.Web.GetFileByServerRelativeUrl(fileServerRelativeUrl); ClientResult <System.IO.Stream> clientresult = fileobj.OpenBinaryStream(); clientContextobj.Load(fileobj); clientContextobj.ExecuteQuery(); using (System.IO.MemoryStream mstream = new System.IO.MemoryStream()) { if (clientresult != null) { clientresult.Value.CopyTo(mstream); using (SpreadsheetDocument document = SpreadsheetDocument.Open(mstream, false)) { WorkbookPart WBPart = document.WorkbookPart; IEnumerable <Sheet> sheets = document.WorkbookPart.Workbook.GetFirstChild <DocumentFormat.OpenXml.Spreadsheet.Sheets>().Elements <Sheet>(); string relationshipId = sheets.First().Id.Value; WorksheetPart WBPart1 = (WorksheetPart)document.WorkbookPart.GetPartById(relationshipId); DocumentFormat.OpenXml.Spreadsheet.Worksheet worksheet = WBPart1.Worksheet; SheetData sheetdata = worksheet.GetFirstChild <SheetData>(); IEnumerable <Row> rows = sheetdata.Descendants <Row>(); foreach (Cell cellvalue in rows.ElementAt(0)) { string str = GetCellValue(document, cellvalue); datatable.Columns.Add(str); } foreach (Row row in rows) { if (row != null) { DataRow datarow = datatable.NewRow(); for (int i = 0; i < row.Descendants <Cell>().Count(); i++) { datarow[i] = GetCellValue(document, row.Descendants <Cell>().ElementAt(i)); } datatable.Rows.Add(datarow); } } datatable.Rows.RemoveAt(0); } } } ReadData(datatable); } catch (Exception e) { Console.WriteLine(e.Message); } Console.ReadKey(); }
private static DocumentFormat.OpenXml.Spreadsheet.Cell InsertCellInWorksheet(string columnName, uint rowIndex, WorksheetPart worksheetPart) { DocumentFormat.OpenXml.Spreadsheet.Worksheet worksheet = worksheetPart.Worksheet; SheetData sheetData = worksheet.GetFirstChild <SheetData>(); string cellReference = columnName + rowIndex; DocumentFormat.OpenXml.Spreadsheet.Row lastRow = sheetData.Elements <DocumentFormat.OpenXml.Spreadsheet.Row>().LastOrDefault(); // If the worksheet does not contain a row with the specified row index, insert one. DocumentFormat.OpenXml.Spreadsheet.Row row; if (sheetData.Elements <DocumentFormat.OpenXml.Spreadsheet.Row>().Where(r => r.RowIndex == rowIndex).Count() != 0) { row = sheetData.Elements <DocumentFormat.OpenXml.Spreadsheet.Row>().Where(r => r.RowIndex == rowIndex).First(); //set auto height -- don't know how this line is worked sheetData.InsertAfter(new DocumentFormat.OpenXml.Spreadsheet.Row() { RowIndex = (lastRow.RowIndex + 1) }, lastRow); } else { row = new DocumentFormat.OpenXml.Spreadsheet.Row() { RowIndex = rowIndex }; sheetData.Append(row); } // If there is not a cell with the specified column name, insert one. if (row.Elements <DocumentFormat.OpenXml.Spreadsheet.Cell>().Where(c => c.CellReference.Value == columnName + rowIndex).Count() > 0) { return(row.Elements <DocumentFormat.OpenXml.Spreadsheet.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. DocumentFormat.OpenXml.Spreadsheet.Cell refCell = null; foreach (DocumentFormat.OpenXml.Spreadsheet.Cell cell in row.Elements <DocumentFormat.OpenXml.Spreadsheet.Cell>()) { if (string.Compare(cell.CellReference.Value, cellReference, true) > 0) { refCell = cell; break; } } DocumentFormat.OpenXml.Spreadsheet.Cell newCell = new DocumentFormat.OpenXml.Spreadsheet.Cell() { CellReference = cellReference }; row.InsertBefore(newCell, refCell); worksheet.Save(); return(newCell); } }
/// <summary> /// Initializes a new instance of the <see cref="OpenXmlExcelWriteManager" /> class. /// </summary> public OpenXmlExcelWriteManager(OpenXmlSpreadsheet.Worksheet worksheet) { if (worksheet == null) { throw new ArgumentNullException("worksheet"); } this.worksheet = worksheet; this.sheetData = worksheet.GetFirstChild <OpenXmlSpreadsheet.SheetData>(); this.columnLetterStore = new Dictionary <uint, string>(); this.excelColumns = new Dictionary <uint, OpenXmlSpreadsheet.Column>(); this.excelRows = new Dictionary <uint, OpenXmlSpreadsheet.Row>(); }
public List <List <string> > Sheet(string sheetName, bool IgnoreEmptyRows) { GetSheetPart(sheetName, out SharedStringTablePart stringTable, out WorksheetPart wsPart); W worksheet = wsPart.Worksheet; IEnumerable <Row> rows = worksheet.GetFirstChild <SheetData>().Elements <Row>(); uint firstRowNum = rows.Select(r => r.RowIndex).Min(); uint lastRowNum = rows.Select(r => r.RowIndex).Max(); var columns = rows.SelectMany(r => r.Elements <Cell>()).Distinct().OrderBy(c => ColumnComparer.GetColumnName(c.CellReference), new ColumnComparer()).Select(c => ColumnComparer.GetColumnName(c.CellReference)); string firstColumn = columns.First(); string lastColumn = columns.Last(); return(SheetRange(sheetName, firstRowNum, lastRowNum, firstColumn, lastColumn, IgnoreEmptyRows, stringTable, wsPart)); }
/// <summary> /// Merges all cells that have been marked to be merged in the cellinfos dictionary. /// </summary> /// <param name="worksheet">The worksheet.</param> /// <param name="totalRowCount">The total number of rows in the worksheet.</param> /// <param name="totalColCount">The total number of columns in the worksheet.</param> /// <param name="cellInfos">A dictionary of cell information, keyed by row and column index.</param> private static void MergeCells(OpenXmlSpreadsheet.Worksheet worksheet, uint totalRowCount, uint totalColCount, LayeredCellsDictionary cellInfos) { OpenXmlSpreadsheet.SheetData sheetData = worksheet.GetFirstChild <OpenXmlSpreadsheet.SheetData>(); // Process the collection of merged cells. for (uint worksheetRow = 1; worksheetRow <= totalRowCount; worksheetRow++) { for (uint worksheetCol = 1; worksheetCol <= totalColCount; worksheetCol++) { // Get cellInfo, if it has a MergeTo then merge it. var currentCoOrdinate = new System.Drawing.Point((int)worksheetCol, (int)worksheetRow); ExcelCellInfo cellInfo = cellInfos[currentCoOrdinate].CellInfo; if (cellInfo.MergeTo != null) { sheetData.MergeCells(cellInfo.Cell, cellInfo.MergeTo.Cell); } } } }
public static xdr.WorksheetDrawing GetWorksheetDrawing(this x.Worksheet ws) { var drawingsPart = ws.WorksheetPart.GetPartsOfType <DrawingsPart>().FirstOrDefault(); if (drawingsPart == null) { var count = ws.WorksheetPart.Parts.Count(); drawingsPart = ws.WorksheetPart.AddNewPart <DrawingsPart>("rId" + (count + 1)); } var drawingsPartId = ws.WorksheetPart.GetIdOfPart(drawingsPart); var worksheetDrawing = drawingsPart.WorksheetDrawing; if (worksheetDrawing == null) { worksheetDrawing = new xdr.WorksheetDrawing(); worksheetDrawing.AddNamespaceDeclaration("xdr", "http://schemas.openxmlformats.org/drawingml/2006/spreadsheetDrawing"); worksheetDrawing.AddNamespaceDeclaration("a", "http://schemas.openxmlformats.org/drawingml/2006/main"); drawingsPart.WorksheetDrawing = worksheetDrawing; } var drawing = ws.GetFirstChild <x.Drawing>(); if (drawing == null) { drawing = new x.Drawing() { Id = drawingsPartId }; ws.Append(drawing); } var wbPart = ws.WorksheetPart.ParentPartOfType <WorkbookPart>(); wbPart.Workbook.Save(); return(worksheetDrawing); }
/// <summary> /// Sets a cell value. The row and the cell are created if they do not exist. If the cell exists, the contents of the cell is overwritten /// </summary> /// <param name="spreadsheet">Spreadsheet to use</param> /// <param name="worksheet">Worksheet to use</param> /// <param name="columnIndex">Index of the column</param> /// <param name="rowIndex">Index of the row</param> /// <param name="valueType">Type of the value</param> /// <param name="value">The actual value</param> /// <param name="styleIndex">Index of the style to use. Null if no style is to be defined</param> /// <param name="save">Save the worksheet?</param> /// <returns>True if succesful</returns> private static bool SetCellValue(DocumentFormat.OpenXml.Packaging.SpreadsheetDocument spreadsheet, DocumentFormat.OpenXml.Spreadsheet.Worksheet worksheet, uint columnIndex, uint rowIndex, DocumentFormat.OpenXml.Spreadsheet.CellValues valueType, string value, uint?styleIndex, bool save = true) { DocumentFormat.OpenXml.Spreadsheet.SheetData sheetData = worksheet.GetFirstChild <DocumentFormat.OpenXml.Spreadsheet.SheetData>(); DocumentFormat.OpenXml.Spreadsheet.Row row; DocumentFormat.OpenXml.Spreadsheet.Row previousRow = null; DocumentFormat.OpenXml.Spreadsheet.Cell cell; DocumentFormat.OpenXml.Spreadsheet.Cell previousCell = null; DocumentFormat.OpenXml.Spreadsheet.Columns columns; DocumentFormat.OpenXml.Spreadsheet.Column previousColumn = null; string cellAddress = ExcelProc.ColumnNameFromIndex(columnIndex) + rowIndex; // Check if the row exists, create if necessary if (sheetData.Elements <DocumentFormat.OpenXml.Spreadsheet.Row>().Where(item => item.RowIndex == rowIndex).Count() != 0) { row = sheetData.Elements <DocumentFormat.OpenXml.Spreadsheet.Row>().Where(item => item.RowIndex == rowIndex).First(); } else { row = new DocumentFormat.OpenXml.Spreadsheet.Row() { RowIndex = rowIndex }; //sheetData.Append(row); for (uint counter = rowIndex - 1; counter > 0; counter--) { previousRow = sheetData.Elements <DocumentFormat.OpenXml.Spreadsheet.Row>().Where(item => item.RowIndex == counter).FirstOrDefault(); if (previousRow != null) { break; } } sheetData.InsertAfter(row, previousRow); } // Check if the cell exists, create if necessary if (row.Elements <DocumentFormat.OpenXml.Spreadsheet.Cell>().Where(item => item.CellReference.Value == cellAddress).Count() > 0) { cell = row.Elements <DocumentFormat.OpenXml.Spreadsheet.Cell>().Where(item => item.CellReference.Value == cellAddress).First(); } else { // Find the previous existing cell in the row for (uint counter = columnIndex - 1; counter > 0; counter--) { previousCell = row.Elements <DocumentFormat.OpenXml.Spreadsheet.Cell>().Where(item => item.CellReference.Value == ExcelProc.ColumnNameFromIndex(counter) + rowIndex).FirstOrDefault(); if (previousCell != null) { break; } } cell = new DocumentFormat.OpenXml.Spreadsheet.Cell() { CellReference = cellAddress }; row.InsertAfter(cell, previousCell); } // Check if the column collection exists columns = worksheet.Elements <DocumentFormat.OpenXml.Spreadsheet.Columns>().FirstOrDefault(); if (columns == null) { columns = worksheet.InsertAt(new DocumentFormat.OpenXml.Spreadsheet.Columns(), 0); } // Check if the column exists if (columns.Elements <DocumentFormat.OpenXml.Spreadsheet.Column>().Where(item => item.Min == columnIndex).Count() == 0) { // Find the previous existing column in the columns for (uint counter = columnIndex - 1; counter > 0; counter--) { previousColumn = columns.Elements <DocumentFormat.OpenXml.Spreadsheet.Column>().Where(item => item.Min == counter).FirstOrDefault(); if (previousColumn != null) { break; } } columns.InsertAfter( new DocumentFormat.OpenXml.Spreadsheet.Column() { Min = columnIndex, Max = columnIndex, CustomWidth = true, Width = 9 }, previousColumn); } // Add the value cell.CellValue = new DocumentFormat.OpenXml.Spreadsheet.CellValue(value); if (styleIndex != null) { cell.StyleIndex = styleIndex; } if (valueType != DocumentFormat.OpenXml.Spreadsheet.CellValues.Date) { cell.DataType = new DocumentFormat.OpenXml.EnumValue <DocumentFormat.OpenXml.Spreadsheet.CellValues>(valueType); } if (save) { worksheet.Save(); } return(true); }
//Get Excel file Details public static void GetExcelFileDetails(ClientContext ClientContext) { List Empoyeelist = ClientContext.Web.Lists.GetByTitle("UserDocuments"); CamlQuery CamlQuery1 = new CamlQuery(); CamlQuery1.ViewXml = "<View><RowLimit></RowLimit></View>"; ListItemCollection EmpCollection = Empoyeelist.GetItems(CamlQuery1); ClientContext.Load(EmpCollection); ClientContext.ExecuteQuery(); Microsoft.SharePoint.Client.File ExcelFile = EmpCollection[0].File; ClientContext.Load(ExcelFile); ClientContext.ExecuteQuery(); var FilePath1 = EmpCollection[0].File.ServerRelativeUrl; var FileInfo1 = Microsoft.SharePoint.Client.File.OpenBinaryDirect(ClientContext, FilePath1); if (ExcelFile != null) { // DiskFilePath = LocalFilePath + EmpCollection[0].File.Name; try { var fileName = Path.Combine(ApplicationConfiguration.LocalFilePath + DiskFilePath, (string)EmpCollection[0].File.Name); DiskFilePath = EmpCollection[0].File.Name; if (System.IO.File.Exists(fileName)) { System.IO.File.Delete(fileName); } /****************Creates File in the specified path*****************/ using (var FileStream1 = System.IO.File.Create(fileName)) { FileInfo1.Stream.CopyTo(FileStream1); FileInfo1.Stream.Close(); FileStream1.Dispose(); } } catch (Exception exc) { Console.WriteLine("Exception exc : " + exc.Message); ErrorWriteToLog.WriteToLogFile(exc); } } string StrErrorMsg = string.Empty; /*************************DataSet Different Approcach***************************************/ /*************************DataSet Different Approcach***************************************/ try { ExcelDataTable = new System.Data.DataTable("ExcelFileDataTable"); ClientResult <System.IO.Stream> Data = ExcelFile.OpenBinaryStream(); ClientContext.Load(ExcelFile); ClientContext.ExecuteQuery(); using (System.IO.MemoryStream mStream = new System.IO.MemoryStream()) { if (Data != null) { Data.Value.CopyTo(mStream); using (SpreadsheetDocument Document1 = SpreadsheetDocument.Open(mStream, false)) { IEnumerable <Sheet> Sheets1 = Document1.WorkbookPart.Workbook.GetFirstChild <Sheets>().Elements <Sheet>(); string RelationshipId = Sheets1.First().Id.Value; WorksheetPart WorksheetPart1 = (WorksheetPart)Document1.WorkbookPart.GetPartById(RelationshipId); Worksheet WorkSheet1 = WorksheetPart1.Worksheet; SheetData SheetData1 = WorkSheet1.GetFirstChild <SheetData>(); IEnumerable <Row> Rows = SheetData1.Descendants <Row>(); foreach (Cell Cell1 in Rows.ElementAt(0)) { string StrCellValue = GetCellValue(ClientContext, Document1, Cell1); ExcelDataTable.Columns.Add(StrCellValue); } foreach (Row RowLoop in Rows) { if (RowLoop != null) { DataRow DataRow1 = ExcelDataTable.NewRow(); for (int iterator = 0; iterator < RowLoop.Descendants <Cell>().Count(); iterator++) { DataRow1[iterator] = GetCellValue(ClientContext, Document1, RowLoop.Descendants <Cell>().ElementAt(iterator)); } ExcelDataTable.Rows.Add(DataRow1); } } ExcelDataTable.Rows.RemoveAt(0); } } } } catch (Exception e) { Console.WriteLine("Exception exx " + e); ErrorWriteToLog.WriteToLogFile(e); } }
public override string Parse(Stream stream, string fileName, ExecuteArgs param) { // bool flag = false; stream.Position = 0; using (var xl = SpreadsheetDocument.Open(stream, true)) { //IEnumerable<DocumentFormat.OpenXml.Packaging.SharedStringTablePart> sp = xl.WorkbookPart.GetPartsOfType<DocumentFormat.OpenXml.Packaging.SharedStringTablePart>(); foreach (WorksheetPart part in xl.WorkbookPart.WorksheetParts) { var sharedStrings = ReadStringTable(xl.WorkbookPart.SharedStringTablePart); Excel.Worksheet worksheet = part.Worksheet; Excel.SheetData sd = worksheet.GetFirstChild <Excel.SheetData>(); var results = FindParsedCells(sharedStrings, sd); foreach (Excel.Cell cell in results) { string val = ReadCell(cell, sharedStrings); Regex re = new Regex("#.[^#]*#", RegexOptions.IgnoreCase); MatchCollection mc = re.Matches(val); foreach (Match m in mc) { object res = ParseString(param, m.Value.Trim("#<>".ToCharArray())); if (res != null) { //flag = true; Excel.Row newRow = null; if (res is QResult query) { var sref = CellReference.Parse(cell.CellReference.Value); int count = 0; foreach (object[] dataRow in query.Values) { count++; int col = sref.Col; newRow = GetRow(sd, sref.Row, newRow == null, cell.Parent as Excel.Row); foreach (object kvp in dataRow) { Excel.Cell ncell = GetCell(newRow, kvp, col, sref.Row, 0, sharedStrings); if (ncell.Parent == null) { newRow.Append(ncell); } col++; } sref.Row++; } if (newRow != null) { uint rcount = newRow.RowIndex.Value; foreach (var item in newRow.ElementsAfter()) { if (item is Excel.Row) { rcount++; ((Excel.Row)item).RowIndex = rcount; foreach (var itemCell in item.ChildElements) { if (itemCell is Excel.Cell) { var reference = CellReference.Parse(((Excel.Cell)itemCell).CellReference); reference.Row = (int)rcount; ((Excel.Cell)itemCell).CellReference = reference.ToString(); } } } } } } else { val = val.Replace(m.Value, res.ToString()); WriteCell(cell, val, sharedStrings); } } } } WriteStringTable(xl.WorkbookPart.SharedStringTablePart, sharedStrings); } } stream.Flush(); return(stream is FileStream fileStream ? fileStream.Name : null); }
private static spd.Row GetRow(spd.Worksheet worksheet, uint rowIndex) { return(worksheet.GetFirstChild <spd.SheetData>(). Elements <spd.Row>().ElementAt((int)rowIndex)); }
//Print Excel file Details public static void printExcelFileDetails(ClientContext clientContext) { List emplist = clientContext.Web.Lists.GetByTitle("UserDocuments"); CamlQuery camlQuery = new CamlQuery(); camlQuery.ViewXml = "<View><RowLimit></RowLimit></View>"; ListItemCollection empcoll = emplist.GetItems(camlQuery); clientContext.Load(empcoll); clientContext.ExecuteQuery(); Microsoft.SharePoint.Client.File excelFile = empcoll[0].File; clientContext.Load(excelFile); clientContext.ExecuteQuery(); var filepath = empcoll[0].File.ServerRelativeUrl; var fileInfo = Microsoft.SharePoint.Client.File.OpenBinaryDirect(clientContext, filepath); Console.WriteLine("file path :" + filepath); Console.WriteLine("file info :" + fileInfo); Console.WriteLine("file name :" + excelFile.Name); /***MyMyMy*****/ Microsoft.SharePoint.Client.File createfileinvs = empcoll[0].File; if (createfileinvs != null) { FileInformation fileInfor = Microsoft.SharePoint.Client.File.OpenBinaryDirect(clientContext, createfileinvs.ServerRelativeUrl); var fileName = Path.Combine(@"D:\DharanendraAssessment13-oct-2018\SharePointCSOMAssessment\SharePointCSOMAssessment", (string)empcoll[0].File.Name); using (var fileStream = System.IO.File.Create(fileName)) { fileInfo.Stream.CopyTo(fileStream); } } /***MyMyMy*****/ /***************************************************************************************/ bool isError = true; string strErrorMsg = string.Empty; const string lstDocName = "Documents"; try { dataTable = new System.Data.DataTable("EmployeeExcelDataTable"); //List list = clientContext.Web.Lists.GetByTitle(lstDocName); //clientContext.Load(list.RootFolder); //clientContext.ExecuteQuery(); //string fileServerRelativeUrl = list.RootFolder.ServerRelativeUrl + "/" + fileName; //Microsoft.SharePoint.Client.File file = clientContext.Web.GetFileByServerRelativeUrl(fileServerRelativeUrl); ClientResult <System.IO.Stream> data = excelFile.OpenBinaryStream(); clientContext.Load(excelFile); clientContext.ExecuteQuery(); using (System.IO.MemoryStream mStream = new System.IO.MemoryStream()) { if (data != null) { data.Value.CopyTo(mStream); using (SpreadsheetDocument document = SpreadsheetDocument.Open(mStream, false)) { WorkbookPart workbookPart = document.WorkbookPart; IEnumerable <Sheet> sheets = document.WorkbookPart.Workbook.GetFirstChild <Sheets>().Elements <Sheet>(); string relationshipId = sheets.First().Id.Value; WorksheetPart worksheetPart = (WorksheetPart)document.WorkbookPart.GetPartById(relationshipId); Worksheet workSheet = worksheetPart.Worksheet; SheetData sheetData = workSheet.GetFirstChild <SheetData>(); IEnumerable <Row> rows = sheetData.Descendants <Row>(); foreach (Cell cell in rows.ElementAt(0)) { string str = GetCellValue(clientContext, document, cell); dataTable.Columns.Add(str); } foreach (Row row in rows) { if (row != null) { DataRow dataRow = dataTable.NewRow(); for (int i = 0; i < row.Descendants <Cell>().Count(); i++) { dataRow[i] = GetCellValue(clientContext, document, row.Descendants <Cell>().ElementAt(i)); Console.WriteLine("Cell data :" + GetCellValue(clientContext, document, row.Descendants <Cell>().ElementAt(i))); } dataTable.Rows.Add(dataRow); } } dataTable.Rows.RemoveAt(0); } } } // UpdateSPList(clientContext, dataTable, fileName); isError = false; } catch (Exception e) { throw; } finally { if (isError) { //Logging } } /***************************************************************************************/ }
public void LoadSpreadsheet(int worksheetId, string fileName) { // Create new stopwatch System.Diagnostics.Stopwatch stopwatch = new System.Diagnostics.Stopwatch(); // Begin timing stopwatch.Start(); string cellAddress = ""; string sheetName = Path.GetFileName(fileName).Replace(".xlsx", ""); sheetName = Path.GetFileName(fileName).Replace(".xls", ""); savedCells = new Dictionary <string, Cell>(); allCells = new Dictionary <string, Cell>(); string workbookPath = fileName; using (SpreadsheetDocument spreadsheetDocument = SpreadsheetDocument.Open(fileName, false)) { var sheets = spreadsheetDocument.WorkbookPart.Workbook.Descendants <Sheet>(); int?colIndex = 0; foreach (Sheet sheet in sheets) { WorksheetPart worksheetPart = (WorksheetPart)spreadsheetDocument.WorkbookPart.GetPartById(sheet.Id); DocumentFormat.OpenXml.Spreadsheet.Worksheet worksheet = worksheetPart.Worksheet; SharedStringTablePart sharedStringTablePart1; if (spreadsheetDocument.WorkbookPart.GetPartsCountOfType <SharedStringTablePart>() > 0) { sharedStringTablePart1 = spreadsheetDocument.WorkbookPart.GetPartsOfType <SharedStringTablePart>().First(); } else//This is only needed to compile. If we don't have string table that means there are no strings { sharedStringTablePart1 = spreadsheetDocument.WorkbookPart.AddNewPart <SharedStringTablePart>(); } var rows = worksheet.GetFirstChild <SheetData>().Elements <Row>(); foreach (var r in rows) { if (r.RowIndex <= 30)//30 rows { foreach (DocumentFormat.OpenXml.Spreadsheet.Cell rCell in r.Elements <DocumentFormat.OpenXml.Spreadsheet.Cell>()) { colIndex = GetColumnIndex(rCell.CellReference.ToString()); if (colIndex <= 12) { Cell c = new Cell(); cellAddress = rCell.CellReference.ToString().Replace("$", ""); c.readOnly = true;//until updated c.isDerived = false; c.row = int.Parse(r.RowIndex.ToString()); c.column = (int)colIndex; c.cellAddress = cellAddress; c.value = rCell.CellValue.Text; if (cellAddress == "A3") { Console.WriteLine("A3"); } //Add Formulas if (rCell.CellFormula != null) { if (rCell.CellFormula.SharedIndex != null) { c.sharedForumlaIndex = int.Parse(rCell.CellFormula.SharedIndex.ToString()); if (rCell.CellFormula.Text != "") { c.isSharedForumla = true; } } c.fullFormula = rCell.CellFormula.Text; c.formula = "=" + //Add = (string)rCell.CellFormula.Text.Replace("$", ""); //Remove any $ string regex = @"\$?(?:\bXF[A-D]|X[A-E][A-Z]|[A-W][A-Z]{2}|[A-Z]{2}|[A-Z])\$?(?:104857[0-6]|10485[0-6]\d|1048[0-4]\d{2}|104[0-7]\d{3}|10[0-3]\d{4}|[1-9]\d{1,5}|[1-9])d?\b([:\s]\$?(?:\bXF[A-D]|X[A-E][A-Z]|[A-W][A-Z]{2}|[A-Z]{2}|[A-Z])\$?(?:104857[0-6]|10485[0-6]\d|1048[0-4]\d{2}|104[0-7]\d{3}|10[0-3]\d{4}|[1-9]\d{1,5}|[1-9])d?\b)?"; Match match = Regex.Match(c.formula, regex, RegexOptions.IgnoreCase); while (match.Success && c.formula.IndexOf(":") != -1) { if (match.Groups[0].Value.IndexOf(":") != -1) { //// Finally, we get the Group value and display it. string key = match.Groups[0].Value; string cellRef = key.Replace("$", ""); string newCells = RangeToSeries(key); c.formula = c.formula.Replace(key, newCells); } match = Regex.Match(c.formula, regex, RegexOptions.IgnoreCase); } } else //String (use shared string table) { c.value = sharedStringTablePart1.SharedStringTable.ChildElements[int.Parse(c.value)].InnerText; } allCells.Add(cellAddress, c); } } } } } Console.WriteLine(); } ReplaceSharedFormulas(ref allCells); Console.WriteLine("Loaded from Excel:" + stopwatch.Elapsed); //while (savedCells.Count < 360) //{ foreach (Cell rCell in allCells.Values) { if (!savedCells.ContainsKey(rCell.cellAddress)) { if (rCell.value == null) //Blank? { savedCells.Add(rCell.cellAddress, rCell); } else if (rCell.formula == null) //Input { rCell.readOnly = true; //until updated (May be label, May be input) rCell.isDerived = false; savedCells.Add(rCell.cellAddress, rCell); } else //Formula { bool allCellsHere = true; foreach (string s in GetCellReferences((string)rCell.formula)) { if (!savedCells.ContainsKey(s)) { allCellsHere = false; break; } else { if (savedCells[s].formula == null) //Is an input { savedCells[s].empty = false; savedCells[s].readOnly = false; savedCells[s].hasDependent = true; } //c.empty = false;//this cell impacts others } } if (allCellsHere) { rCell.formula = rCell.formula.Replace("$", ""); rCell.readOnly = true; rCell.isDerived = true; savedCells.Add(rCell.cellAddress, rCell); } } } } //} Console.WriteLine("Organized Cells:" + stopwatch.Elapsed); Dictionary <string, Cell> savedCellsTrimmed = new Dictionary <string, Cell>(); List <int> rowsToRemove = new List <int>(); bool includeRow = false; for (int k = 30; k > 0; k--) { foreach (Cell c in savedCells.Values) { if (c.row == k) { if (c.value != "" || c.hasDependent) { includeRow = true; break; //include row; } } } if (includeRow) { break; } else { rowsToRemove.Add(k); } } foreach (Cell c in savedCells.Values) { if (!rowsToRemove.Contains(c.row)) { savedCellsTrimmed.Add(c.cellAddress, c); } } savedCells = savedCellsTrimmed; DataTable dt = new DataTable(); dt.Columns.Add("worksheet_id"); dt.Columns.Add("row"); dt.Columns.Add("column"); dt.Columns.Add("cell_address"); dt.Columns.Add("value"); dt.Columns.Add("formula"); dt.Columns.Add("data_type"); dt.Columns.Add("format"); dt.Columns.Add("read_only"); dt.Columns.Add("is_derived"); dt.Columns.Add("has_dependent"); dt.Columns.Add("fore_color"); dt.Columns.Add("back_color"); dt.Columns.Add("font_family"); dt.Columns.Add("italics"); dt.Columns.Add("bold"); dt.Columns.Add("underline"); dt.Columns.Add("font_size"); foreach (Cell c in savedCells.Values) { DataRow row = dt.NewRow(); row[0] = worksheetId; row[1] = c.row; row[2] = c.column; row[3] = c.cellAddress; row[4] = c.value; row[5] = c.formula; row[6] = c.dataType; row[7] = c.format; row[8] = c.readOnly; row[9] = c.isDerived; row[10] = c.hasDependent; row[11] = c.ForeColor; row[12] = c.BackColor; row[13] = c.FontFamily; row[14] = c.Italics; row[15] = c.Bold; row[16] = c.Underline; row[17] = c.FontSize; dt.Rows.Add(row); } SqlConnection cn = new SqlConnection(ConnectionString); cn.Open(); using (SqlBulkCopy copy = new SqlBulkCopy(cn)) { copy.ColumnMappings.Add(0, 0); copy.ColumnMappings.Add(1, 1); copy.ColumnMappings.Add(2, 2); copy.ColumnMappings.Add(3, 3); copy.ColumnMappings.Add(4, 4); copy.ColumnMappings.Add(5, 5); copy.ColumnMappings.Add(6, 6); copy.ColumnMappings.Add(7, 7); copy.ColumnMappings.Add(8, 8); copy.ColumnMappings.Add(9, 9); copy.ColumnMappings.Add(10, 10); copy.ColumnMappings.Add(11, 11); copy.ColumnMappings.Add(12, 12); copy.ColumnMappings.Add(13, 13); copy.ColumnMappings.Add(14, 14); copy.ColumnMappings.Add(15, 15); copy.ColumnMappings.Add(16, 16); copy.ColumnMappings.Add(17, 17); copy.DestinationTableName = "worksheet_cell"; copy.WriteToServer(dt); } Console.WriteLine("Loaded DB:" + stopwatch.Elapsed); }
private static OpenXmlSpread.Row GetRow(DocumentFormat.OpenXml.Spreadsheet.Worksheet worksheet, uint rowIndex) { return(worksheet.GetFirstChild <OpenXmlSpread.SheetData>().Elements <OpenXmlSpread.Row>() .Where(r => r.RowIndex == rowIndex).First()); }