public DataTable GetWorksheetToDataTable(Worksheet worksheet, SpreadsheetDocument doc)
{
WorkbookPart workbookPart = doc.WorkbookPart;
SheetData sheetData = worksheet.GetFirstChild <SheetData>();
DataTable dataTable = new DataTable();
int numOfColumn = sheetData.ElementAt(0).ChildElements.Count();
for (int rcnt = 0; rcnt < sheetData.ChildElements.Count; ++rcnt)
{
List <string> rowList = new List <string>();
var cnt = sheetData.ElementAt(rcnt).ChildElements.Count();
for (int rcnt2 = 0; rcnt2 < numOfColumn; ++rcnt2)
{
Cell currentCell = (Cell)sheetData.ElementAt(rcnt).ChildElements.ElementAt(rcnt2);
if (currentCell.DataType != null)
{
if (currentCell.DataType == CellValues.SharedString)
{
int id;
if (Int32.TryParse(currentCell.InnerText, out id))
{
SharedStringItem sharedStringItem = workbookPart.SharedStringTablePart.
SharedStringTable.Elements <SharedStringItem>()
.ElementAt(id);
if (sharedStringItem.Text != null)
{
if (rcnt == 0)
{
dataTable.Columns.Add(sharedStringItem.Text.Text);
}
else
{
rowList.Add(sharedStringItem.Text.Text);
}
}
}
}
}
else
{
if (rcnt != 0)
{
rowList.Add(currentCell.InnerText);
}
}
}
if (rcnt != 0)
{
dataTable.Rows.Add(rowList.ToArray());
}
}
return(dataTable);
}
public IEnumerable <string> GetCellValues(int rowIndex)
{
if (rowIndex >= RowsCount)
{
return(null);
}
var row = _sheetData.ElementAt(rowIndex);
return(row.OfType <Cell>().Select(GetCellValue));
}
/// <summary>
/// Gets the cell at the specified coordinates or creates it if a cell does not exist there.
/// </summary>
/// <param name="sheetData">The sheet data.</param>
/// <param name="columnIndex">Index of the column.</param>
/// <param name="rowIndex">Index of the row.</param>
/// <returns>
/// The new cell.
/// </returns>
public static Cell GetCell(this SheetData sheetData, uint columnIndex, uint rowIndex)
{
while (rowIndex > sheetData.OfType <Row>().Count())
{
sheetData.AddRow();
}
var row = (Row)sheetData.ElementAt((int)rowIndex - 1);
while (columnIndex > row.OfType <Cell>().Count())
{
var paddingCell = new Cell();
paddingCell.SetCellReference((uint)row.OfType <Cell>().Count() + 1, rowIndex);
row.Append(paddingCell);
}
Cell cell = (Cell)row.ElementAt((int)columnIndex - 1);
return(cell);
}
//open excel file
public string openExcel(int row, int col)
{
DataTable dtTable = new DataTable();
//open excel file
SpreadsheetDocument doc = SpreadsheetDocument.Open(filepath, false);
//create the object for workbook part
WorkbookPart workbookPart = doc.WorkbookPart;
Sheets thesheetcollection = workbookPart.Workbook.GetFirstChild <Sheets>();
foreach (Sheet thesheet in thesheetcollection.OfType <Sheet>())
{
// statement to get the worksheet object by using the sheet id
Worksheet theWorksheet = ((WorksheetPart)workbookPart.GetPartById(thesheet.Id)).Worksheet;
SheetData thesheetdata = theWorksheet.GetFirstChild <SheetData>();
DocumentFormat.OpenXml.Spreadsheet.Cell thecurrentcell = (DocumentFormat.OpenXml.Spreadsheet.Cell)thesheetdata.ElementAt(row).ChildElements.ElementAt(col);
if (thecurrentcell.DataType != null && thecurrentcell.DataType == CellValues.SharedString)
{
int id;
Int32.TryParse(thecurrentcell.InnerText, out id);
SharedStringItem item = workbookPart.SharedStringTablePart.SharedStringTable.Elements <SharedStringItem>().ElementAt(id);
cellValue = item.InnerText;
}
else
{
cellValue = thecurrentcell.InnerText;
}
}
return(cellValue);
}
// ------------------------------------------------------------------------------------------
/// <summary>
/// Outputs the content of all the cells in an Excel file on-screen.
/// </summary>
/// <param name="filename">The full path to where the Excel file is located</param>
static public void ReadExcelFileCellByCell(string filename)
{
// Open the Excel file using Open XML SDK (a Microsoft library)
using SpreadsheetDocument document = SpreadsheetDocument.Open(filename, false);
// Reference the Workbook
WorkbookPart workbookPart = document.WorkbookPart;
// Reference the Sheets collection
Sheets sheetCollection = workbookPart.Workbook.GetFirstChild <Sheets>();
// Loop through the Sheets collection
foreach (Sheet sheet in sheetCollection.OfType <Sheet>())
{
// Reference a worksheet via its ID
Worksheet worksheet = ((WorksheetPart)workbookPart.GetPartById(sheet.Id)).Worksheet;
// Reference the data in the Sheet
SheetData sheetData = worksheet.GetFirstChild <SheetData>();
// Count the number of Rows
int rowCount = sheetData.ChildElements.Count();
// Iterate through the Rows
for (int row = 0; row < rowCount; row++)
{
List <string> rowList = new List <string>();
// Count the number of Columns
int columnCount = sheetData.ElementAt(row).ChildElements.Count();
// Iterate through the Columns
for (int column = 0; column < columnCount; column++)
{
string currentCellValue = string.Empty;
// Reference the current Cell
Cell currentCell = (Cell)sheetData.ElementAt(row).ChildElements.ElementAt(column);
// Check if the Cell has data
if (currentCell.DataType != null)
{
// If so, check if it is a Shared String (common string) like Column Headers
if (currentCell.DataType == CellValues.SharedString)
{
int sharedStringID;
// ----------------------------------------------------------------------
// Internally, Excel creates a "normalized table" to store string values so they
// are not stored repeatedly within a Spreadsheet. So you have to use the
// Shared String ID to get the text equivalent.
// ----------------------------------------------------------------------
// Let's see if we can parse a number out the Shared String ID
if (Int32.TryParse(currentCell.InnerText, out sharedStringID))
{
// If we can, great, then let's turn that ID into its text equivalent
SharedStringItem item = workbookPart.SharedStringTablePart.SharedStringTable.Elements <SharedStringItem>().ElementAt(sharedStringID);
// Check if we got a value
if (item.Text != null)
{
// Are we on the first row?
if (row == 0)
{
// If so, then we are probably just dealing with Column Headers
Console.WriteLine(currentCell.CellReference + " (Text 1) = " + item.Text.Text);
}
else
{
// We are dealing with other Shared Strings that are not Column Headers
Console.WriteLine(currentCell.CellReference + " (Text 2) = " + item.Text.Text);
}
}
}
}
else
{
// Check to see we are not dealing with Column Headers (just normal cell data)
if (row != 0)
{
// If so, then simply output the value (text) contained within the Cell
Console.WriteLine(currentCell.CellReference + " (InnerText B) = " + currentCell.InnerText);
}
}
}
}
}
}
}
static string ReadExcelJSON()
{
try
{
DataTable dtTable = new DataTable();
//Lets open the existing excel file and read through its content . Open the excel using openxml sdk
using (SpreadsheetDocument doc = SpreadsheetDocument.Open("test.xlsx", false))
{
//create the object for workbook part
WorkbookPart workbookPart = doc.WorkbookPart;
Sheets thesheetcollection = workbookPart.Workbook.GetFirstChild <Sheets>();
//using for each loop to get the sheet from the sheetcollection
foreach (Sheet thesheet in thesheetcollection.OfType <Sheet>())
{
//statement to get the worksheet object by using the sheet id
Worksheet theWorksheet = ((WorksheetPart)workbookPart.GetPartById(thesheet.Id)).Worksheet;
SheetData thesheetdata = theWorksheet.GetFirstChild <SheetData>();
for (int rCnt = 0; rCnt < thesheetdata.ChildElements.Count(); rCnt++)
{
List <string> rowList = new List <string>();
for (int rCnt1 = 0; rCnt1
< thesheetdata.ElementAt(rCnt).ChildElements.Count(); rCnt1++)
{
Cell thecurrentcell = (Cell)thesheetdata.ElementAt(rCnt).ChildElements.ElementAt(rCnt1);
//statement to take the integer value
string currentcellvalue = string.Empty;
if (thecurrentcell.DataType != null)
{
if (thecurrentcell.DataType == CellValues.SharedString)
{
int id;
if (Int32.TryParse(thecurrentcell.InnerText, out id))
{
SharedStringItem item = workbookPart.SharedStringTablePart.SharedStringTable.Elements <SharedStringItem>().ElementAt(id);
if (item.Text != null)
{
//first row will provide the column name.
if (rCnt == 0)
{
dtTable.Columns.Add(item.Text.Text);
}
else
{
rowList.Add(item.Text.Text);
}
}
else if (item.InnerText != null)
{
currentcellvalue = item.InnerText;
}
else if (item.InnerXml != null)
{
currentcellvalue = item.InnerXml;
}
}
}
}
else
{
if (rCnt != 0)//reserved for column values
{
rowList.Add(thecurrentcell.InnerText);
}
}
}
if (rCnt != 0)//reserved for column values
{
dtTable.Rows.Add(rowList.ToArray());
}
}
}
return(JsonConvert.SerializeObject(dtTable));
}
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
throw;
}
}
// ------------------------------------------------------------------------------------------
/// <summary>
/// Returns the values of a specific cell value (for example 'ImageLink') in an Excel file.
/// </summary>
/// <param name="filename">The full path to where the Excel file is located</param>
/// <param name="cellValueToLookFor">The cell value to look for (like 'ImageLink')</param>
public List <string> ExtractProductImageURLs(string filename, string cellValueToLookFor)
{
List <string> result = new List <string>();
// Check to ensure the file exists
bool fileExists = File.Exists(filename);
if (fileExists == true)
{
// Open the Excel file using Open XML SDK (a Microsoft library)
using SpreadsheetDocument spreadhSheet = SpreadsheetDocument.Open(filename, false);
// Reference the Workbooks
WorkbookPart workbookPart = spreadhSheet.WorkbookPart;
// Reference the Sheets collection
Sheets sheetCollection = workbookPart.Workbook.GetFirstChild <Sheets>();
// Loop through the Sheets collection
foreach (Sheet sheet in sheetCollection.OfType <Sheet>())
{
// Reference a worksheet via its ID
Worksheet workSheet = ((WorksheetPart)workbookPart.GetPartById(sheet.Id)).Worksheet;
// Reference the data in the Sheet
SheetData sheetData = workSheet.GetFirstChild <SheetData>();
// Count the number of Rows
int rowCount = sheetData.ChildElements.Count();
// Iterate through the Rows
for (int row = 1; row < rowCount; row++)
{
List <string> rowList = new List <string>();
// Count the number of Columns
int columnCount = sheetData.ElementAt(row).ChildElements.Count();
// Iterate through the Columns
for (int column = 0; column < columnCount; column++)
{
string currentCellValue = string.Empty;
// Reference the current Cell
Cell currentCell = (Cell)sheetData.ElementAt(row).ChildElements.ElementAt(column);
// Are we in a cell with the Cell Value that we are looking for?
if (currentCell.CellValue.InnerText == cellValueToLookFor)
{
// If so, extract the Cell Formula
string cellFormula = currentCell.CellFormula.InnerText;
// ----------------------------------------------------------------------
// The Cell Formula is in this format:
// =HYPERLINK("http://Vehiclepartimages.com/pmdt/DMT/images/96010.jpg","ImageLink")
// ----------------------------------------------------------------------
// Split it by the double quotes
string[] arrFormula = cellFormula.Split("\"");
if (arrFormula.Length > 0)
{
// The URL itself will be in the second index
string URL = arrFormula[1];
// Store the URL in our result accumulator
result.Add(URL);
}
}
}
}
}
}
return(result);
}
private void CheckForErrors()
{
for (int i = 0; i < 3; i++)
{
if (pathes[i] == null || pathes[i].Count == 0)
{
criticalErrors += "Не выбраны все необходимые файлы" + Environment.NewLine;
return;
}
}
for (int i = 0; i < 4; i += 3)
{
foreach (string s in pathes[i])
{
if (Path.GetExtension(s) != ".csv" && Path.GetExtension(s) != ".xlsx")
{
criticalErrors += "Файл ексопрт линков имеет некорректное расширение, измените путь на формат .csv или .xlsx" + Environment.NewLine;
return;
}
}
}
foreach (string s in pathes[1])
{
if (Path.GetExtension(s) != ".xlsx" && Path.GetExtension(s) != ".csv")
{
criticalErrors += "Файл PD имеет некорректное расширение, измените путь на формат .xlsx или .csv" + Environment.NewLine;
return;
}
}
foreach (string s in pathes[2])
{
if (Path.GetExtension(s) != ".xlsx" && Path.GetExtension(s) != ".csv")
{
criticalErrors += "Файл ID имеет некорректное расширение, измените путь на формат .csv или .xlsx" + Environment.NewLine;
return;
}
}
foreach (string path in pathes[1])
{
if (Path.GetExtension(path) == ".xlsx")
{
using (SpreadsheetDocument spreadsheetDocument = SpreadsheetDocument.Open(path, false))
{
WorkbookPart workbookPart = spreadsheetDocument.WorkbookPart;
if (workbookPart.Workbook.Sheets.Count() < 2)
{
criticalErrors += "В файле продукт даты нет необходимых листов" + Environment.NewLine;
return;
}
Sheet sheet = workbookPart.Workbook.Descendants <Sheet>().ElementAt(0);
Worksheet worksheet = ((WorksheetPart)workbookPart.GetPartById(sheet.Id)).Worksheet;
SheetData sheetData = worksheet.Elements <SheetData>().First();
int headerColumnCount = sheetData.ElementAt(0).ChildElements.Count;
if (headerColumnCount != 11)
{
criticalErrors += "Некорректное количество колонок на первом листе продукт даты" + Environment.NewLine;
return;
}
Sheet sheet2 = workbookPart.Workbook.Descendants <Sheet>().ElementAt(1);
Worksheet worksheet2 = ((WorksheetPart)workbookPart.GetPartById(sheet2.Id)).Worksheet;
SheetData sheetData2 = worksheet2.Elements <SheetData>().First();
int headerColumnCount2 = sheetData2.ElementAt(0).ChildElements.Count;
if (headerColumnCount2 != 5)
{
criticalErrors += "Некорректное количество колонок на втором листе продукт даты" + Environment.NewLine;
return;
}
if (criticalErrors != null)
{
return;
}
}
for (int i = 1; i < fileReader.PData.PDData1.Count; i++)
{
if (fileReader.PData.PDData1[i][0] == "")
{
criticalErrors += "Пустое значение на первом листе продукт даты в колонке Brand, строка " + i + Environment.NewLine;
}
if (fileReader.PData.PDData1[i][1] == "")
{
criticalErrors += "Пустое значение на первом листе продукт даты в колонке SKU, строка " + i + Environment.NewLine;
}
if (fileReader.PData.PDData1[i][5] == "" || fileReader.PData.PDData1[i][6] == "")
{
criticalErrors += "Пустое значение на первом листе продукт даты в колонках CategoryName or SubtypeName, строка " + i + Environment.NewLine;
}
}
if (criticalErrors != null)
{
return;
}
}
}
HashSet <string> differentRegistrBrand = new HashSet <string>();
for (int i = 1; i < fileReader.PData.PDData1.Count - 2; i++)
{
if (fileReader.PData.PDData1[i][0].ToLower() == fileReader.PData.PDData1[i + 1][0].ToLower() &&
fileReader.PData.PDData1[i][0] != fileReader.PData.PDData1[i + 1][0])
{
differentRegistrBrand.Add(fileReader.PData.PDData1[i][0]);
}
}
if (differentRegistrBrand.Count != 0)
{
otherErrors += "Некоторые бренды содержат разный регистр букв, что может привести к ошибкам (отвалится MMY если один и тот же бренд записан с разным регистром букв в фитмент и продукт дате)" +
Environment.NewLine + "Бренды в которых есть такая ошибка: " + Environment.NewLine;
foreach (string s in differentRegistrBrand)
{
otherErrors += s + Environment.NewLine;
}
}
if (skuFromPDCheck == true)
{
List <List <string> > exportLinks = fileReader.ExportLinks.ToList();
int brandKayPosition = exportLinks[0].Count - 1;
int pDataBrandKayPosition = fileReader.PData.PDData1[0].Count - 1;
HashSet <string> allSKU = new HashSet <string>(StringComparer.OrdinalIgnoreCase);
for (int i = 1; i < exportLinks.Count; i++)
{
allSKU.Add(exportLinks[i][brandKayPosition]);
}
HashSet <string> missingSKU = new HashSet <string>();
for (int i = 1; i < fileReader.PData.PDData1.Count - 1; i++)
{
if (!allSKU.Contains(fileReader.PData.PDData1[i][pDataBrandKayPosition]))
{
missingSKU.Add(fileReader.PData.PDData1[i][1] + " - " + fileReader.PData.PDData1[i][0]);
}
}
if (missingSKU.Count != 0)
{
otherErrors += "Согласно выбраному чекбоксу собрать финиш файл по SKU указаным в продукт дате." + Environment.NewLine + "Не все ску были залиты на сайт, ску которые есть в файле пд и нет в експорт линках:" + Environment.NewLine;
foreach (string s in missingSKU)
{
otherErrors += s + Environment.NewLine;
}
}
}
}
public static DataTable Run(
[HttpTrigger(AuthorizationLevel.Anonymous, "post", Route = null)] HttpRequest req,
ILogger log)
{
var file = req.Form.Files["file"];
{
try
{
DataTable dtTable = new DataTable();
var filestream = file.OpenReadStream();
//Lets open the existing excel file and read through its content . Open the excel using openxml sdk
using (SpreadsheetDocument doc = SpreadsheetDocument.Open(filestream, false))
{
WorkbookPart workbookPart = doc.WorkbookPart;
Sheets thesheetcollection = workbookPart.Workbook.GetFirstChild <Sheets>();
foreach (Sheet thesheet in thesheetcollection.OfType <Sheet>())
{
Worksheet theWorksheet = ((WorksheetPart)workbookPart.GetPartById(thesheet.Id)).Worksheet;
SheetData thesheetdata = theWorksheet.GetFirstChild <SheetData>();
for (int rCnt = 0; rCnt < thesheetdata.ChildElements.Count(); rCnt++)
{
List <string> rowList = new List <string>();
for (int rCnt1 = 0; rCnt1
< thesheetdata.ElementAt(rCnt).ChildElements.Count(); rCnt1++)
{
Cell thecurrentcell = (Cell)thesheetdata.ElementAt(rCnt).ChildElements.ElementAt(rCnt1);
string currentcellvalue = string.Empty;
if (thecurrentcell.DataType != null)
{
if (thecurrentcell.DataType == CellValues.SharedString)
{
int id;
if (Int32.TryParse(thecurrentcell.InnerText, out id))
{
SharedStringItem item = workbookPart.SharedStringTablePart.SharedStringTable.Elements <SharedStringItem>().ElementAt(id);
if (item.Text != null)
{
if (rCnt == 0)
{
dtTable.Columns.Add(item.Text.Text);
}
else
{
rowList.Add(item.Text.Text);
}
}
else if (item.InnerText != null)
{
currentcellvalue = item.InnerText;
}
else if (item.InnerXml != null)
{
currentcellvalue = item.InnerXml;
}
}
}
}
else
{
if (rCnt != 0)
{
rowList.Add(thecurrentcell.InnerText);
}
}
}
if (rCnt != 0)
{
dtTable.Rows.Add(rowList.ToArray());
}
}
}
// return JsonConvert.SerializeObject(dtTable);
return(dtTable);
}
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
throw;
}
}
}
public void Main()
{
string user, password, path;
// クラウドサービスのリストを取得する
List <string> listCloudService = new List <string>();
int index = -1;
string systemPath = Path.GetDirectoryName(System.Reflection.Assembly.GetExecutingAssembly().Location).ToString();
if (!(File.Exists(systemPath + "\\CloudServices_Information\\CloudServices.xlsx")))
{
//log.WriteLog("ファイルエクセルクラウドサービス情報が存在しません", null);
Console.WriteLine("ファイルエクセルクラウドサービス情報が存在しません");
return;
}
// Excelファイル情報クラウドサービスへのパス
path = systemPath + "\\CloudServices_Information\\CloudServices.xlsx";
SpreadsheetDocument doc = SpreadsheetDocument.Open(path, false);
WorkbookPart workbookPart = doc.WorkbookPart;
Worksheet theWorksheet = ((WorksheetPart)workbookPart.GetPartById("rId1")).Worksheet;
SheetData thesheetdata = theWorksheet.GetFirstChild <SheetData>();
var lastRow = thesheetdata.Descendants <Row>().LastOrDefault();
var lastRowToInt = Int32.Parse(lastRow.RowIndex);
Cell theCellUser = (Cell)thesheetdata.ElementAt(1).ChildElements.ElementAt(4);
SharedStringItem textUser = workbookPart.SharedStringTablePart.SharedStringTable.Elements <SharedStringItem>().ElementAt(Int32.Parse(theCellUser.InnerText));
user = textUser.Text.Text;
Cell theCellPassword = (Cell)thesheetdata.ElementAt(1).ChildElements.ElementAt(5);
SharedStringItem textPassword = workbookPart.SharedStringTablePart.SharedStringTable.Elements <SharedStringItem>().ElementAt(Int32.Parse(theCellPassword.InnerText));
password = textPassword.Text.Text;
// クラウドサービスのステータスのリストを取得する
for (int i = 1; i < lastRowToInt - 1; i++)
{
Cell theCellCheck = (Cell)thesheetdata.ElementAt(i).ChildElements.ElementAt(1);
Int32.TryParse(theCellCheck.InnerText, out index);
string textCheck = workbookPart.SharedStringTablePart.SharedStringTable.Elements <SharedStringItem>().ElementAt(index).InnerText;
if ((String.Compare("〇", textCheck)) == 0)
{
Cell theCellCloudService = (Cell)thesheetdata.ElementAt(i).ChildElements.ElementAt(2);
SharedStringItem textCloudService = workbookPart.SharedStringTablePart.SharedStringTable.Elements <SharedStringItem>().ElementAt(Int32.Parse(theCellCloudService.InnerText));
listCloudService.Add(textCloudService.Text.Text);
}
}
var stateStoping = "stop";
MyTestCaseTest test_case = new MyTestCaseTest();
test_case.SetUp();
test_case.StartAzure(user, password);
for (int i = 0; i < listCloudService.Count; i++)
{
if (test_case.TestCases(listCloudService[i], stateStoping) == true)
{
//log.WriteLog(stateStoping + " " + listCloudService[i], "OK");
Console.WriteLine(stateStoping + " " + listCloudService[i] + " " + "OK");
}
else
{
//log.WriteLog(stateStoping + " " + listCloudService[i], "ERROR");
Console.WriteLine(stateStoping + " " + listCloudService[i] + " " + "ERROR");
}
}
test_case.TearDown();
}
//Em teste
public static DataTable CreateDataTableFromStreamBeta(Stream stream, string sheetName = null)
{
//Sheet sheet = null;
//IEnumerable<Sheet> sheets = workbookPart.Workbook.Descendants<Sheet>();
//if (!string.IsNullOrEmpty(sheetName))
//{
// sheet = sheets.FirstOrDefault(x => x.Name == sheetName);
//}
//else
//{
// sheet = sheets.FirstOrDefault();
//}
//if (sheet == null)
//{
// //throw new ArgumentException("sheetName");
// throw new InvalidOperationException("Erro na folha de dados");
//}
//dt.TableName = sheet.Name;
try
{
DataTable dtTable = new DataTable();
//Lets open the existing excel file and read through its content . Open the excel using openxml sdk
using (SpreadsheetDocument doc = SpreadsheetDocument.Open(stream, true))
{
//create the object for workbook part
WorkbookPart workbookPart = doc.WorkbookPart;
Sheet thesheet = null;
IEnumerable <Sheet> sheets = workbookPart.Workbook.Descendants <Sheet>();
if (!string.IsNullOrEmpty(sheetName))
{
thesheet = sheets.FirstOrDefault(x => x.Name == sheetName);
}
else
{
thesheet = sheets.FirstOrDefault();
}
if (thesheet == null)
{
//throw new ArgumentException("sheetName");
throw new InvalidOperationException("Erro na folha de dados");
}
//Sheets thesheetcollection = workbookPart.Workbook.GetFirstChild<Sheets>();
//using for each loop to get the sheet from the sheetcollection
//foreach (Sheet thesheet in thesheetcollection.OfType<Sheet>())
//{
//statement to get the worksheet object by using the sheet id
Worksheet theWorksheet = ((WorksheetPart)workbookPart.GetPartById(thesheet.Id)).Worksheet;
SheetData thesheetdata = theWorksheet.GetFirstChild <SheetData>();
for (int rCnt = 0; rCnt < thesheetdata.ChildElements.Count(); rCnt++)
{
List <string> rowList = new List <string>();
for (int rCnt1 = 0; rCnt1
< thesheetdata.ElementAt(rCnt).ChildElements.Count(); rCnt1++)
{
Cell thecurrentcell = (Cell)thesheetdata.ElementAt(rCnt).ChildElements.ElementAt(rCnt1);
//statement to take the integer value
string currentcellvalue = string.Empty;
if (thecurrentcell.DataType != null)
{
if (thecurrentcell.DataType == CellValues.SharedString)
{
int id;
if (Int32.TryParse(thecurrentcell.InnerText, out id))
{
SharedStringItem item = workbookPart.SharedStringTablePart.SharedStringTable.Elements <SharedStringItem>().ElementAt(id);
if (item.Text != null)
{
//first row will provide the column name.
if (rCnt == 0)
{
dtTable.Columns.Add(item.Text.Text);
}
else
{
rowList.Add(item.Text.Text);
}
}
else if (item.InnerText != null)
{
currentcellvalue = item.InnerText;
}
else if (item.InnerXml != null)
{
currentcellvalue = item.InnerXml;
}
}
}
}
else
{
if (rCnt != 0)//reserved for column values
{
rowList.Add(thecurrentcell.InnerText);
}
}
}
if (rCnt != 0)//reserved for column values
{
dtTable.Rows.Add(rowList.ToArray());
}
}
}
return(dtTable);
//}
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
throw;
}
}
static DataTable ReadExcelasJSON(string fileName)
{
DataTable dtTable = new DataTable();
try
{
using (SpreadsheetDocument doc = SpreadsheetDocument.Open(fileName, false))
{
WorkbookPart workbookPart = doc.WorkbookPart;
Sheets thesheetcollection = workbookPart.Workbook.GetFirstChild <Sheets>();
string sheetId = "";
foreach (Sheet thesheet in thesheetcollection)
{
if (thesheet.Name == "Исходник")
{
sheetId = thesheet.Id;
break;
}
}
//using for each loop to get the sheet from the sheetcollection
//foreach (Sheet thesheet in thesheetcollection.OfType<Sheet>())
//{
//statement to get the worksheet object by using the sheet id
//Worksheet theWorksheet = ((WorksheetPart)workbookPart.GetPartById(thesheet.Id)).Worksheet;
Worksheet theWorksheet = ((WorksheetPart)workbookPart.GetPartById(sheetId)).Worksheet;
SheetData thesheetdata = theWorksheet.GetFirstChild <SheetData>();
IEnumerable <Row> rows = thesheetdata.Descendants <Row>();
//Выделяет все символы из строки
Regex regex = new Regex(@"\D+");
//Выделяет все цифры из строки
Regex regex1 = new Regex(@"\d+");
//Поиск колонки с признаком классификатора ОС
string[] rowHeaders = new string[] { "Власник/Орендодавець" };
int?startRowIndex = null;
int ozColumnIndex = 0;
//string[] vechicleType = new string[] {
// "АТП-2004 ТОВ".Replace(" ",""),
// "ТОВ Кернел-Трейд".Replace(" ", ""),
// "ПРАТ Кропивницький ОЕЗ".Replace(" ", ""),
// "СТОВ Дружба - Нова".Replace(" ", ""),
// "СТОВ Придніпровський край".Replace(" ", ""),
// "Коритня-Агро КМ ТОВ".Replace(" ", ""),
// "Приколотнянский МЭЗ ТОВ".Replace(" ", ""),
// "Агро Інвест Україна, ТОВ".Replace(" ", ""),
// "СТОВ Дружба-Нова".Replace(" ", ""),
// "Вовчанський ОЕЗ ПРАТ".Replace(" ", ""),
// "Юнігрейн-Агро ТОВ, Семенівка".Replace(" ", ""),
// "Вовчанський ОЕЗ ПРАТ".Replace(" ", "")
//};
List <string> passengerCellValue = new List <string>();
List <int> rowsToProc = new List <int>();
//Поиск первой строки с данными
for (int rCnt = 0; rCnt < thesheetdata.ChildElements.Count(); rCnt++)
{
for (int rCnt1 = 0; rCnt1 < thesheetdata.ElementAt(rCnt).ChildElements.Count(); rCnt1++)
{
Cell thecurrentcell = (Cell)thesheetdata.ElementAt(rCnt).ChildElements.ElementAt(rCnt1);
int id;
if (Int32.TryParse(thecurrentcell.InnerText, out id))
{
SharedStringItem item = workbookPart.SharedStringTablePart.SharedStringTable.Elements <SharedStringItem>().ElementAt(id);
if (item.Text != null)
{
if (Array.IndexOf(rowHeaders, item.Text.Text) > -1)
{
startRowIndex = rCnt;
ozColumnIndex = rCnt1;
//rowsToProc.Add(rCnt);
//passengerCellValue = thecurrentcell.InnerText;
break;
}
}
}
}
if (startRowIndex != null)
{
break;
}
}
//rowHeaders = new string[] { "Кластер", "Назва підприємства", "Назва основного засобу", "Класифікатор ОЗ", "Дата випуску", "Державний номер", "Стан ОЗ", "Модель" };
rowHeaders = new string[] { "Власник/Орендодавець" };
SharedStringTablePart stringTablePart = workbookPart.SharedStringTablePart;
List <int> columns = new List <int>();
//Поиск номеров колонок для получения данных
int idx = 0;
foreach (Cell c in rows.ElementAt((int)startRowIndex).Elements <Cell>())
{
if (c.CellValue != null)
{
if (c.DataType != null && c.DataType.Value == CellValues.SharedString)
{
if (Array.IndexOf(rowHeaders, stringTablePart.SharedStringTable.ChildElements[Int32.Parse(c.CellValue.InnerXml)].InnerText) > -1)
{
columns.Add(idx);// regex.Match(c.CellReference).Value);
}
}
}
idx++;
}
for (int rCnt = 0; rCnt < thesheetdata.ChildElements.Count(); rCnt++)
{
try
{
Cell thecurrentcell = (Cell)thesheetdata.ElementAt(rCnt).ChildElements.ElementAt(ozColumnIndex);
int id;
if (Int32.TryParse(thecurrentcell.InnerText, out id))
{
SharedStringItem item = workbookPart.SharedStringTablePart.SharedStringTable.Elements <SharedStringItem>().ElementAt(id);
//if (Array.IndexOf(vechicleType.ToArray(), item.Text.Text.Replace("\"", "").Replace(" ", "")) > -1 && item.Text.Text.IndexOf("Дружба")>-1)
//{
// int fkjgk = 0;
// //rowsToProc.Add(rCnt);
// //passengerCellValue.Add(thecurrentcell.InnerText);
//}
//if (Array.IndexOf(vechicleType.ToArray(), item.Text.Text.Replace("\"","").Replace(" ", "")) < 0)
//{
rowsToProc.Add(rCnt);
passengerCellValue.Add(thecurrentcell.InnerText);
//}
}
}
catch (Exception)
{
//throw;
}
}
for (int rCnt = 0; rCnt < rowsToProc.Count(); rCnt++)
{
List <string> rowList = new List <string>();
for (int c = 0; c < columns.Count(); c++)
{
Cell thecurrentcell = (Cell)thesheetdata.ElementAt(rowsToProc[rCnt]).ChildElements.ElementAt(columns[c]);
//statement to take the integer value
string currentcellvalue = string.Empty;
if (thecurrentcell.DataType != null)
{
if (thecurrentcell.DataType == CellValues.SharedString)
{
int id;
if (Int32.TryParse(thecurrentcell.InnerText, out id))
{
SharedStringItem item = workbookPart.SharedStringTablePart.SharedStringTable.Elements <SharedStringItem>().ElementAt(id);
if (item.Text != null)
{
//first row will provide the column name.
if (rCnt == 0)
{
dtTable.Columns.Add(item.Text.Text);
}
else
{
rowList.Add(item.Text.Text);
}
}
else
{
rowList.Add("");
}
}
}
}
else
{
if (rCnt != 0)//reserved for column values
{
rowList.Add(thecurrentcell.InnerText);
}
}
}
if (rCnt != 0)//reserved for column values
{
dtTable.Rows.Add(rowList.ToArray());
}
}
}
}
catch (Exception)
{
throw;
}
return(dtTable);
}
static DataTable ReadExcelasJSON(string fileName)
{
DataTable dtTable = new DataTable();
try
{
using (SpreadsheetDocument doc = SpreadsheetDocument.Open(fileName, false))
{
WorkbookPart workbookPart = doc.WorkbookPart;
Sheets thesheetcollection = workbookPart.Workbook.GetFirstChild <Sheets>();
string sheetId = "";
foreach (Sheet thesheet in thesheetcollection)
{
if (thesheet.Name == "Исходник")
{
sheetId = thesheet.Id;
break;
}
}
//using for each loop to get the sheet from the sheetcollection
//foreach (Sheet thesheet in thesheetcollection.OfType<Sheet>())
//{
//statement to get the worksheet object by using the sheet id
//Worksheet theWorksheet = ((WorksheetPart)workbookPart.GetPartById(thesheet.Id)).Worksheet;
Worksheet theWorksheet = ((WorksheetPart)workbookPart.GetPartById(sheetId)).Worksheet;
SheetData thesheetdata = theWorksheet.GetFirstChild <SheetData>();
IEnumerable <Row> rows = thesheetdata.Descendants <Row>();
//Выделяет все символы из строки
Regex regex = new Regex(@"\D+");
//Выделяет все цифры из строки
Regex regex1 = new Regex(@"\d+");
//Поиск колонки с признаком классификатора ОС
string[] rowHeaders = new string[] { "Класифікатор ОЗ" };
int?startRowIndex = null;
int ozColumnIndex = 0;
string[] vechicleType = new string[] { "Легкові", "Автобуси", "Вантажопасажирські" };
List <string> passengerCellValue = new List <string>();
List <int> rowsToProc = new List <int>();
//Поиск первой строки с данными
for (int rCnt = 0; rCnt < thesheetdata.ChildElements.Count(); rCnt++)
{
for (int rCnt1 = 0; rCnt1 < thesheetdata.ElementAt(rCnt).ChildElements.Count(); rCnt1++)
{
Cell thecurrentcell = (Cell)thesheetdata.ElementAt(rCnt).ChildElements.ElementAt(rCnt1);
int id;
if (Int32.TryParse(thecurrentcell.InnerText, out id))
{
SharedStringItem item = workbookPart.SharedStringTablePart.SharedStringTable.Elements <SharedStringItem>().ElementAt(id);
if (item.Text != null)
{
if (Array.IndexOf(rowHeaders, item.Text.Text) > -1)
{
startRowIndex = rCnt;
ozColumnIndex = rCnt1;
rowsToProc.Add(rCnt);
//passengerCellValue = thecurrentcell.InnerText;
break;
}
}
}
}
if (startRowIndex != null)
{
break;
}
}
//for (int rCnt = (int) startRowIndex; rCnt < rows.Count()- startRowIndex; rCnt ++)
//{
// List<string> rowList = new List<string>();
// for (int cCnt = 0; cCnt < thesheetdata.ElementAt(rCnt).ChildElements.Count(); cCnt++)
// {
// Cell thecurrentcell = (Cell)thesheetdata.ElementAt(rCnt).ChildElements.ElementAt(cCnt);
// if (thecurrentcell.DataType!=null)
// {
// if (thecurrentcell.DataType==CellValues.SharedString)
// {
// int id;
// if (Int32.TryParse(thecurrentcell.InnerText,out id))
// {
// SharedStringItem item = workbookPart.SharedStringTablePart.SharedStringTable.Elements<SharedStringItem>().ElementAt(id);
// if (item.Text!=null)
// {
// if (rCnt==startRowIndex)
// {
// dt.Columns.Add(item.Text.Text);
// }
// else
// {
// rowList.Add(item.Text.Text);
// }
// }
// else
// {
// rowList.Add("");
// }
// }
// }
// }
// if (rCnt!=startRowIndex)
// {
// dt.Rows.Add(rowList.ToArray());
// }
// }
//}
//return dt;
rowHeaders = new string[] { "Кластер", "Назва підприємства", "Назва основного засобу", "Класифікатор ОЗ", "Дата випуску", "Державний номер", "Стан ОЗ", "Модель", "М.В.О.", "Власник/Орендодавець" };
SharedStringTablePart stringTablePart = workbookPart.SharedStringTablePart;
List <int> columns = new List <int>();
//Поиск номеров колонок для получения данных
int idx = 0;
foreach (Cell c in rows.ElementAt((int)startRowIndex).Elements <Cell>())
{
if (c.CellValue != null)
{
if (c.DataType != null && c.DataType.Value == CellValues.SharedString)
{
if (Array.IndexOf(rowHeaders, stringTablePart.SharedStringTable.ChildElements[Int32.Parse(c.CellValue.InnerXml)].InnerText) > -1)
{
columns.Add(idx);// regex.Match(c.CellReference).Value);
}
}
}
idx++;
}
for (int rCnt = 0; rCnt < thesheetdata.ChildElements.Count(); rCnt++)
{
try
{
Cell thecurrentcell = (Cell)thesheetdata.ElementAt(rCnt).ChildElements.ElementAt(ozColumnIndex);
int id;
if (Int32.TryParse(thecurrentcell.InnerText, out id))
{
SharedStringItem item = workbookPart.SharedStringTablePart.SharedStringTable.Elements <SharedStringItem>().ElementAt(id);
if (Array.IndexOf(vechicleType.ToArray(), item.Text.Text) > -1)
{
rowsToProc.Add(rCnt);
//passengerCellValue.Add(thecurrentcell.InnerText);
}
}
}
catch (Exception)
{
//throw;
}
}
//for (int rCnt = 0; rCnt < thesheetdata.ChildElements.Count(); rCnt++)
//{
// try
// {
// Cell thecurrentcell = (Cell)thesheetdata.ElementAt(rCnt).ChildElements.ElementAt(ozColumnIndex);
// //if (thecurrentcell.InnerText == passengerCellValue)
// // {
// // rowsToProc.Add(rCnt);
// // }
// }
// catch (Exception)
// {
// //throw;
// }
//}
//int kejkej = 0;
//if (c.CellValue != null)
//{
// if (c.DataType != null && c.DataType.Value == CellValues.SharedString)
// {
// if (Array.IndexOf(rowHeaders, stringTablePart.SharedStringTable.ChildElements[Int32.Parse(c.CellValue.InnerXml)].InnerText) > -1)
// {
// columns.Add(stringTablePart.SharedStringTable.ChildElements[Int32.Parse(c.CellValue.InnerXml)].InnerText, regex.Match(c.CellReference).Value);
// }
// }
//}
for (int rCnt = 0; rCnt < rowsToProc.Count(); rCnt++)
{
List <string> rowList = new List <string>();
//int[] columns = new int[] { 1, 2, 4, 6, 9, 12, 13, 15, 20, 21, 31, 32, 33, 34, 36, 41 };
//foreach (var item in collection)
//{
//}
for (int c = 0; c < columns.Count(); c++)
//for (int rCnt1 = 0; rCnt1 < thesheetdata.ElementAt(rCnt).ChildElements.Count()-10; rCnt1++)
{
//Cell thecurrentcell = (Cell)thesheetdata.ElementAt(rCnt).ChildElements.ElementAt(rCnt1);
Cell thecurrentcell = (Cell)thesheetdata.ElementAt(rowsToProc[rCnt]).ChildElements.ElementAt(columns[c]);
//statement to take the integer value
string currentcellvalue = string.Empty;
if (thecurrentcell.DataType != null)
{
if (thecurrentcell.DataType == CellValues.SharedString)
{
int id;
if (Int32.TryParse(thecurrentcell.InnerText, out id))
{
SharedStringItem item = workbookPart.SharedStringTablePart.SharedStringTable.Elements <SharedStringItem>().ElementAt(id);
if (item.Text != null)
{
//first row will provide the column name.
if (rCnt == 0)
{
dtTable.Columns.Add(item.Text.Text);
}
else
{
rowList.Add(item.Text.Text);
}
}
else
{
rowList.Add("");
}
//else if (item.InnerText != null)
//{
// currentcellvalue = item.InnerText;
//}
//else if (item.InnerXml != null)
//{
// currentcellvalue = item.InnerXml;
//}
}
}
}
else
{
if (rCnt != 0)//reserved for column values
{
rowList.Add(thecurrentcell.InnerText);
}
}
}
if (rCnt != 0)//reserved for column values
{
dtTable.Rows.Add(rowList.ToArray());
}
}
// return JsonConvert.SerializeObject(dtTable);
int jhj = 0;
}
}
catch (Exception)
{
throw;
}
return(dtTable);
}
public static void AddDataSpreadsheetWorkBook(string filepath, string col, uint rowNum, string text, CellValues type = CellValues.Number)
{
OpenSettings openSettings = new OpenSettings();
openSettings.MarkupCompatibilityProcessSettings =
new MarkupCompatibilityProcessSettings(
MarkupCompatibilityProcessMode.ProcessAllParts,
FileFormatVersions.Office2013
);
SpreadsheetDocument spreadsheet = SpreadsheetDocument.Open(filepath, true, openSettings);
WorkbookPart workbookPart = spreadsheet.GetPartsOfType <WorkbookPart>().First();
//workbookPart.Workbook = new Workbook();
WorksheetPart worksheetPart = workbookPart.GetPartsOfType <WorksheetPart>().First();
//worksheetPart.Worksheet = new Worksheet(new SheetData());
//Sheets sheets = spreadsheet.WorkbookPart.Workbook.GetFirstChild<Sheets>();
//Sheet sheet = new Sheet() { Id = spreadsheet.WorkbookPart.GetIdOfPart(worksheetPart), SheetId = 1, Name = "mySheet" };
//sheets.Append(sheet);
// Adding data:
SheetData sheetData = worksheetPart.Worksheet.GetFirstChild <SheetData>();
// Add a row:
Row row;
//row = new Row() { RowIndex = 1 };
//sheetData.Append(row);
try
{
row = (Row)sheetData.ElementAt((int)rowNum);
}
catch (ArgumentOutOfRangeException ex)
{
row = new Row()
{
RowIndex = new UInt32Value((uint)rowNum)
};
sheetData.Append(row);
}
//Row Row2nd = (Row)sheetData.ElementAt(100);
// Find the A1 cell in the first Cell
Cell refCell = null;
string cellLocation = col + rowNum.ToString();
foreach (Cell cell in row.Elements <Cell>())
{
if (string.Compare(cell.CellReference.Value, cellLocation, true) > 0)
{
refCell = cell;
break;
}
}
Cell newCell = new Cell()
{
CellReference = cellLocation
};
row.InsertBefore(newCell, refCell);
//row.InsertBefore()
// Set the cell value:
newCell.CellValue = new CellValue(text);
//Can be Shared String here:
newCell.DataType = new EnumValue <CellValues>(type);
workbookPart.Workbook.Save();
spreadsheet.Close();
}