public static void ReplicateRow(SheetData sheetData, int refRowIndex, int count)
{
IEnumerable<Row> rows = sheetData.Descendants<Row>().Where(r => r.RowIndex.Value > refRowIndex);
foreach (Row row in rows)
IncrementIndexes(row, count);
Row refRow = GetRow(sheetData, refRowIndex);
for (int i = 0; i < count; i++)
{
Row newRow = (Row)refRow.Clone();
IncrementIndexes(newRow, i + 1);
sheetData.InsertAfter(newRow, GetRow(sheetData, refRowIndex + i));
}
}
public static void ReplicateRow(SheetData sheetData, int refRowIndex, int count)
{
Repository.Utility.WriteLog("ReplicateRow started", System.Diagnostics.EventLogEntryType.Information);
IEnumerable<Row> rows = sheetData.Descendants<Row>().Where(r => r.RowIndex.Value > refRowIndex);
foreach (Row row in rows)
IncrementIndexes(row, count);
Row refRow = GetRow(sheetData, refRowIndex);
for (int i = 0; i < count; i++)
{
Row newRow = (Row)refRow.Clone();
IncrementIndexes(newRow, i + 1);
//sheetData.InsertAt(newRow, i + 1);
sheetData.InsertAfter(newRow, GetRow(sheetData, refRowIndex + i));
}
Repository.Utility.WriteLog("ReplicateRow completed successfully", System.Diagnostics.EventLogEntryType.Information);
}
public LinkedListBasedSheetDataIndexer(SheetData sheetData)
{
if (sheetData == null)
{
throw new ArgumentNullException("sheetData");
}
long rowIndex = 0;
// Order this sucker first so we don't have to sort a Linked List.
foreach (var row in sheetData.Descendants<Row>().OrderBy(x => (uint)x.RowIndex))
{
var rowIndexer = new RowIndexer(row);
rowIndex = rowIndexer.RowIndex;
this.rows.AddLast(rowIndexer);
}
this.maxRowIndex = rowIndex;
this.SheetData = sheetData;
}
private IEnumerable <Row> GetRows(int firstRowIndex, int?lastRowIndex)
{
if (_sheetId == null)
{
return(Enumerable.Empty <Row>( ));
}
// Note: we are using the worksheet name as a unique ID, because Excel ensures it is unique within the document anyway.
string nameToFind = _sheetId;
Worksheet workSheet = ExcelHelpers.GetWorksheetByName(_document, nameToFind);
SheetData sheetData = workSheet?.GetFirstChild <SheetData>( );
IEnumerable <Row> rows = sheetData?.Descendants <Row>( )
?.Where(r => r.RowIndex?.Value >= firstRowIndex)
.TakeWhile(r => lastRowIndex == null || r.RowIndex?.Value <= lastRowIndex)
.Where(r => !ExcelHelpers.IsRowEmpty(_document, r));
if (rows == null)
{
return(Enumerable.Empty <Row>( ));
}
return(rows);
}
private static DataTable GetExcelDatatoDataTable()
{
var table = new DataTable();
using (SpreadsheetDocument spreadSheetDocument = SpreadsheetDocument.Open(@"F:\Study Materials\Projects\Practice\Report.xlsx", false))
{
WorkbookPart workbookPart = spreadSheetDocument.WorkbookPart;
IEnumerable <Sheet> sheets = spreadSheetDocument.WorkbookPart.Workbook.GetFirstChild <Sheets>().Elements <Sheet>();
string relationshipId = sheets.First().Id.Value;
WorksheetPart worksheetPart = (WorksheetPart)spreadSheetDocument.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))
{
table.Columns.Add(GetCellValue(spreadSheetDocument, cell));
}
foreach (Row row in rows) //this will also include your header row...
{
DataRow tempRow = table.NewRow();
for (int i = 0; i < row.Descendants <Cell>().Count(); i++)
{
tempRow[i] = GetCellValue(spreadSheetDocument, row.Descendants <Cell>().ElementAt(i));
}
table.Rows.Add(tempRow);
}
}
table.Rows.RemoveAt(0);
return(table);
}
public static void CheckCellValue(string filePath, string sheetName, string cellReference, bool isNumeric, string cellValue)
{
using (var document = SpreadsheetDocument.Open(filePath, true))
{
WorkbookPart workbookPart = document.WorkbookPart;
SharedStringTable sharedStringTable = workbookPart.SharedStringTablePart.SharedStringTable;
var sheet = document.WorkbookPart.Workbook.Descendants <Sheet>().Where(s => s.Name == sheetName).First();
WorksheetPart worksheetPart = (WorksheetPart)document.WorkbookPart.GetPartById(sheet.Id);
SheetData sheetData = worksheetPart.Worksheet.GetFirstChild <SheetData>();
var cell = sheetData.Descendants <Cell>().Where(x => x.CellReference.Value == cellReference).FirstOrDefault();
Assert.IsNotNull(cell);
if (isNumeric)
{
Assert.AreEqual(cellValue, cell.CellValue.InnerText);
}
else
{
Assert.AreEqual(cellValue, GetSharedStringItem(sharedStringTable, int.Parse(cell.CellValue.InnerText)));
}
}
}
/// <summary>
/// Returns string list containing first row from excel sheet.
/// </summary>
/// <param name="filePath"></param>
/// <returns></returns>
public static List <string> GetExcelColumnNames(string filePath)
{
DataTable dt = new DataTable();
using (SpreadsheetDocument spreadSheetDocument = SpreadsheetDocument.Open(filePath, false))
{
IEnumerable <Sheet> sheets = spreadSheetDocument.WorkbookPart.Workbook.GetFirstChild <Sheets>().Elements <Sheet>();
string relationshipId = sheets.First().Id.Value;
WorksheetPart worksheetPart = (WorksheetPart)spreadSheetDocument.WorkbookPart.GetPartById(relationshipId);
Worksheet workSheet = worksheetPart.Worksheet;
SheetData sheetData = workSheet.GetFirstChild <SheetData>();
IEnumerable <Row> rows = sheetData.Descendants <Row>();
foreach (var openXmlElement in rows.ElementAt(0))
{
var cell = (Cell)openXmlElement;
dt.Columns.Add(GetCellValue(spreadSheetDocument, cell));
}
return(dt.Columns.Cast <DataColumn>()
.Select(x => x.ColumnName)
.ToList());
}
}
/// <summary>
/// Adds all the specified values as new rows
/// </summary>
/// <param name="sheet">SheetData reference</param>
/// <param name="data">Object values</param>
/// <returns>Task State</returns>
public static async Task AddRows(this SheetData sheet, IEnumerable <IEnumerable <object> > data)
{
//Check whether the sheet reference or the sheet data is a non-valid to work with object value
if (sheet.IsNotValid() || data == null || !data.Any())
{
return;
}
//stablish wich is going to be the start index for data to set
uint startIndex = sheet
.Descendants <Row>()
.LastOrDefault()?.RowIndex.Value ?? 0;
startIndex++;
//Add all the entries
for (uint r = 0; r < data.Count(); r++)
{
//Create a new row
Row row = new Row()
{
RowIndex = startIndex + r
};
IEnumerable <object> d = data.ElementAt((int)r);
//Add all the entities as a row
for (uint c = 0; c < d.Count(); c++)
{
//Get the entity in turn
object e = d.ElementAt((int)c);
Cell cell = new Cell();
await cell.SetValue(e);
row.AppendChild(cell);
}
//Add the row to the data sheet
sheet.Append(row);
}
}
public List <ExcelSheet> Parse(ExcelUpload uploadModel)
{
List <ExcelSheet> returnSheets = new List <ExcelSheet>();
using (MemoryStream ms = new MemoryStream(uploadModel.ExcelFile))
{
SpreadsheetDocument spreadsheet = SpreadsheetDocument.Open(ms, false);
WorkbookPart wbPart = spreadsheet.WorkbookPart;
var sheets = wbPart.Workbook.Descendants <Sheet>();
foreach (Sheet sheet in sheets)
{
ExcelSheet newReturnSheet = new ExcelSheet();
WorksheetPart wsPart = (WorksheetPart)(wbPart.GetPartById(sheet.Id));
Worksheet worksheet = wsPart.Worksheet;
SheetData sheetData = worksheet.GetFirstChild <SheetData>();
newReturnSheet.SheetName = sheet.Name;
var firstRowCells = worksheet.Descendants <Cell>().Where(y => GetCellRow(y.CellReference.Value) == 1);
foreach (var firstRowCell in firstRowCells)
{
ExcelColumn newReturnColumn = new ExcelColumn()
{
Letter = GetCellLetter(firstRowCell.CellReference.Value),
Name = GetCellLetter(firstRowCell.CellReference.Value)
};
if (uploadModel.FirstRowIsHeader)
{
newReturnColumn.Name = GetCellValue(firstRowCell, wbPart);
}
newReturnSheet.Columns.Add(newReturnColumn);
}
var rows = sheetData.Descendants <Row>();
foreach (var row in rows)
{
ExcelRow newReturnRow = new ExcelRow()
{
RowNumber = (int)row.RowIndex.Value
};
if (uploadModel.FirstRowIsHeader && newReturnRow.RowNumber == 1)
{
continue;
}
foreach (ExcelColumn column in newReturnSheet.Columns)
{
var cellReference = string.Format("{0}{1}", column.Letter, newReturnRow.RowNumber);
Cell cell = worksheet.Descendants <Cell>().SingleOrDefault(y => y.CellReference.Value == cellReference);
ExcelCell newReturnCell = new ExcelCell()
{
Letter = column.Letter,
Value = GetCellValue(cell, wbPart)
};
newReturnRow.Cells.Add(newReturnCell);
}
newReturnSheet.Rows.Add(newReturnRow);
}
returnSheets.Add(newReturnSheet);
}
}
return(returnSheets);
}
internal void importByXLSX(string v)
{
DataTable dt = new DataTable();
List <Dictionary <String, String> > parsedExcelBanca = new List <Dictionary <string, string> >();
using (SpreadsheetDocument spreadSheetDocument = SpreadsheetDocument.Open(@"C:\Temp\movimenti.xlsx", false))
{
WorkbookPart workbookPart = spreadSheetDocument.WorkbookPart;
IEnumerable <Sheet> sheets = spreadSheetDocument.WorkbookPart.Workbook.GetFirstChild <Sheets>().Elements <Sheet>();
string relationshipId = sheets.First().Id.Value;
WorksheetPart worksheetPart = (WorksheetPart)spreadSheetDocument.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))
{
dt.Columns.Add(GetCellValue(spreadSheetDocument, cell));
}
foreach (Row row in rows) //this will also include your header row...
{
DataRow tempRow = dt.NewRow();
for (int i = 0; i < row.Descendants <Cell>().Count(); i++)
{
tempRow[i] = GetCellValue(spreadSheetDocument, row.Descendants <Cell>().ElementAt(i));
}
dt.Rows.Add(tempRow);
}
}
dt.Rows.RemoveAt(0); //...so i'm taking it out here.
foreach (DataRow r in dt.Rows)
{
//Data valuta;Descrizione operazione;Causale;Importo
Dictionary <String, String> excelLine = new Dictionary <string, string>();
String dd = r["Data"].ToString();
String[] splitDate = dd.Split(Char.Parse("/"));
DateTime dtdate = new DateTime(int.Parse(splitDate[2]), int.Parse(splitDate[1]), int.Parse(splitDate[0]));
String importo = (Decimal.Parse(r[@"Importo (€)"].ToString()) * -1).ToString();
excelLine.Add("data valuta", dtdate.ToString("yyyyMMdd"));
excelLine.Add("descrizione operazione", r["descrizione"].ToString());
excelLine.Add("causale", "NEXI IMPORT");
excelLine.Add("importo", importo);
parsedExcelBanca.Add(excelLine);
}
using (FinanceDB dbctx = new FinanceDB())
{
dbctx.Configuration.ValidateOnSaveEnabled = false;
DateTime baseline = DateTime.ParseExact(
dbctx.AppConfiguration.Where(x => x._namespace == "FinancialNoteImport" && x.name == "baselineNexi").SingleOrDefault().value,
"yyyy-MM-dd", CultureInfo.InvariantCulture);
foreach (var l in parsedExcelBanca)
{
writeEntryToDB(l, baseline);
}
}
//StringBuilder strBld = new StringBuilder();
//using (FinanceDB dbctx = new FinanceDB())
//{
// dbctx.Configuration.ValidateOnSaveEnabled = false;
// dbctx.Database.Log = s => System.Diagnostics.Debug.WriteLine(s);
// DateTime baseline = DateTime.ParseExact(
// dbctx.AppConfiguration.Where(x => x._namespace == "FinancialNoteImport" && x.name == "baselineNexi").SingleOrDefault().value,
// "yyyy-MM-dd", CultureInfo.InvariantCulture);
// if (dt.Rows.Count > 0)
// {
// strBld.AppendLine("INSERT [dbo].[Raw_Finance]([amount], [valueDate], [description], [notes], [appliedTaxes], [appliedWelfare], [appliedSavings], [appliedAi], [appliedFree], [feeType], [n_sequence], [loadDate])");
// strBld.AppendLine("VALUES");
// }
// foreach (System.Data.DataRow r in dt.Rows)
// {
// string values = "(@amount, @valueDate, @description, @notes, NULL, NULL, NULL, NULL, NULL, NULL, NULL, GETDATE()),";
// values = values.Replace("@amount", "'" + (Decimal.Parse(r[@"Importo (€)"].ToString()) * -1).ToString() + "'");
// String dd = r["Data"].ToString();
// String[] splitDate = dd.Split(Char.Parse("/"));
// DateTime dtdate = new DateTime(int.Parse(splitDate[2]), int.Parse(splitDate[1]), int.Parse(splitDate[0]));
// values = values.Replace("@valueDate", "'" + dtdate.ToString("yyyyMMdd") + "'");
// values = values.Replace("@description", "'" + r["Descrizione"].ToString() + "'");
// values = values.Replace("@notes", "'NEXI IMPORT'");
// if (dtdate >= baseline)
// strBld.AppendLine(values);
// }
//}
//using (SqlConnection sconn = new SqlConnection(sqlStrBld.ToString()))
//{
// sconn.Open();
// using (var cmd = sconn.CreateCommand())
// {
// cmd.CommandType = System.Data.CommandType.Text;
// cmd.CommandText = strBld.ToString().Trim().TrimEnd(Char.Parse(","));
// int affected = cmd.ExecuteNonQuery();
// Console.WriteLine("Imported {0} records from stage table.", affected);
// }
//}
}
static void Main(string[] args)
{
DataTable dt = new DataTable();
using (SpreadsheetDocument spreadSheetDocument = SpreadsheetDocument.Open(Settings.Default.FilePath, false))
{
WorkbookPart workbookPart = spreadSheetDocument.WorkbookPart;
IEnumerable <Sheet> sheets = spreadSheetDocument.WorkbookPart.Workbook.GetFirstChild <Sheets>().Elements <Sheet>();
string relationshipId = sheets.First().Id.Value;
WorksheetPart worksheetPart = (WorksheetPart)spreadSheetDocument.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))
{
dt.Columns.Add(GetCellValue(spreadSheetDocument, cell));
}
foreach (Row row in rows) //this will also include your header row...
{
DataRow tempRow = dt.NewRow();
int columnIndex = 0;
foreach (Cell cell in row.Descendants <Cell>())
{
// Gets the column index of the cell with data
int cellColumnIndex = (int)GetColumnIndexFromName(GetColumnName(cell.CellReference));
cellColumnIndex--; //zero based index
if (columnIndex < cellColumnIndex)
{
do
{
tempRow[columnIndex] = ""; //Insert blank data here;
columnIndex++;
}while (columnIndex < cellColumnIndex);
}
tempRow[columnIndex] = GetCellValue(spreadSheetDocument, cell);
columnIndex++;
}
dt.Rows.Add(tempRow);
}
}
dt.Rows.RemoveAt(0);
Console.ForegroundColor = ConsoleColor.Green;
Console.WriteLine("Enter your user name (ex: [email protected]):");
string _targetUserName = Console.ReadLine();
Console.ForegroundColor = ConsoleColor.Green;
Console.WriteLine("Enter your password:");
SecureString _targetPassword = GetPasswordFromConsole();
ClientContext ctx = new ClientContext(Settings.Default.SiteURL);
ctx.Credentials = new SharePointOnlineCredentials(_targetUserName, _targetPassword);
ctx.ExecuteQuery();
List targetList = ctx.Web.Lists.GetByTitle(Settings.Default.ListName);
ctx.Load(targetList);
ctx.ExecuteQuery();
foreach (DataRow dr in dt.Rows)
{
addNewRecord(dr, targetList, ctx);
}
}
public static Row CreateRow(this Worksheet worksheet, Row refRow)
{
SheetData sheetData = worksheet.GetFirstChild <SheetData>();
uint newRowIndex = 0;
var newRow = new Row()
{
RowIndex = refRow.RowIndex.Value
};
var cells = refRow.Elements <Cell>();
newRow.Height = new DoubleValue(refRow.Height);
newRow.CustomHeight = new BooleanValue(refRow.CustomHeight);
foreach (Cell cell in cells)
{
Cell newCell = (Cell)cell.CloneNode(true);
newCell.StyleIndex = new UInt32Value(cell.StyleIndex);
newRow.Append(newCell);
}
IEnumerable <Row> rows = sheetData.Descendants <Row>().Where(r => r.RowIndex.Value >= refRow.RowIndex.Value);
foreach (Row row in rows)
{
newRowIndex = System.Convert.ToUInt32(row.RowIndex.Value + 1);
foreach (var cell in row.Elements <Cell>())
{
// Update the references for reserved cells.
string cellReference = cell.CellReference.Value;
cell.CellReference = new StringValue(cellReference.Replace(row.RowIndex.Value.ToString(), newRowIndex.ToString()));
}
row.RowIndex = new UInt32Value(newRowIndex);
}
sheetData.InsertBefore(newRow, refRow);
// process merge cell in cloned rows
var mcells = worksheet.GetFirstChild <MergeCells>();
if (mcells != null)
{
//处理所有动态行以下的merg
var clonedMergeCells = mcells.Elements <MergeCell>().
Where(m => Utility.GetRowIndex(m.Reference.Value.Split(':')[0]) >= newRow.RowIndex.Value).ToList <MergeCell>();
foreach (var cmCell in clonedMergeCells)
{
cmCell.Reference.Value = Utility.AddRow(cmCell.Reference.Value);
}
//增加新的merg
var newMergeCells = new List <MergeCell>();
var rowMergeCells = mcells.Elements <MergeCell>().
Where(m => Utility.GetRowIndex(m.Reference.Value.Split(':')[0]) == refRow.RowIndex).ToList <MergeCell>();
foreach (var mc in rowMergeCells)
{
newMergeCells.Add(new MergeCell()
{
Reference = mc.Reference.Value.Replace(refRow.RowIndex.Value.ToString(), (newRow.RowIndex.Value).ToString())
});
}
uint count = mcells.Count.Value;
mcells.Count = new UInt32Value(count + (uint)newMergeCells.Count);
mcells.Append(newMergeCells.ToArray());
}
return(newRow);
}
public static DataSet GetDataSet(string path, string fileName, ref string errorMessage, ref string sessionKey)
{
ICacheManager cacheManager = new MemoryCacheManager();
DataSet ds = new DataSet();
PagingDataTable dt = null;
try {
string inputFileName = System.IO.Path.Combine(path, fileName);
using (SpreadsheetDocument myWorkbook = SpreadsheetDocument.Open(inputFileName, false)) {
//Access the main Workbook part, which contains data
WorkbookPart workbookPart = myWorkbook.WorkbookPart;
WorksheetPart worksheetPart = null;
List <Sheet> sheets = workbookPart.Workbook.Descendants <Sheet>().ToList();
foreach (var ss in sheets)
{
dt = new PagingDataTable();
dt.TableName = ss.Name;
worksheetPart = (WorksheetPart)workbookPart.GetPartById(ss.Id);
SharedStringTablePart stringTablePart = workbookPart.SharedStringTablePart;
if (worksheetPart != null)
{
string relationshipId = sheets.First().Id.Value;
Worksheet workSheet = worksheetPart.Worksheet;
SheetData sheetData = workSheet.GetFirstChild <SheetData>();
IEnumerable <Row> rows = sheetData.Descendants <Row>();
if (rows.ToArray().Count() > 0)
{
foreach (Cell cell in rows.ElementAt(0))
{
dt.Columns.Add(GetCellValue(myWorkbook, cell));
}
int rowIndex = 0;
foreach (Row row in rows) //this will also include your header row...
{
if (rowIndex > 0)
{
DataRow tempRow = dt.NewRow();
int columnIndex = 0;
foreach (Cell cell in row.Descendants <Cell>())
{
// Gets the column index of the cell with data
int cellColumnIndex = (int)GetColumnIndexFromName(GetColumnName(cell.CellReference));
cellColumnIndex--; //zero based index
if (columnIndex < cellColumnIndex)
{
do
{
try {
tempRow[columnIndex] = ""; //Insert blank data here;
} catch { }
columnIndex++;
}while (columnIndex < cellColumnIndex);
}
try {
tempRow[columnIndex] = GetCellValue(myWorkbook, cell);
} catch { }
columnIndex++;
}
bool isAllColumnBlank = true;
foreach (DataColumn col in dt.Columns)
{
if (string.IsNullOrEmpty(Convert.ToString(tempRow[col.ColumnName])) == false)
{
isAllColumnBlank = false;
break;
}
}
if (isAllColumnBlank == false)
{
dt.Rows.Add(tempRow);
}
}
rowIndex++;
}
dt.Columns.Add(new DataColumn {
DataType = typeof(int),
//AutoIncrement = true,
//AutoIncrementSeed = 1,
//AutoIncrementStep = 1,
ColumnName = "RowNumber",
//AllowDBNull = false,
});
dt.Columns.Add(new DataColumn {
ColumnName = "ImportError",
});
rowIndex = 1;
foreach (DataRow row in dt.Rows)
{
row["RowNumber"] = rowIndex;
rowIndex++;
}
ds.Tables.Add(dt);
}
}
}
}
Guid guid = System.Guid.NewGuid();
sessionKey = string.Format(EXCELDATABASE_BY_KEY, guid);
cacheManager.Set(sessionKey, ds, 120);
} catch (Exception ex) {
errorMessage = ex.Message.ToString();
} finally {
UploadFileHelper.DeleteFile("TempPath", fileName);
}
return(ds);
}
/// <summary>
/// Read in a new translation locale
/// </summary>
/// <returns>The translation</returns>
/// <remarks>This actually loads all data on a single pass for all translations</remarks>
/// <param name="stream">The file stream of the code</param>
public Dictionary <string, string> Read(Stream stream)
{
// Get language code from provided stub
string code = string.Empty;
using (var streamReader = new StreamReader(stream))
{
code = streamReader.ReadToEnd().Trim();
}
// If theres no code we have to bail, this shouldnt happen, but ya.
if (code == string.Empty)
{
throw new InvalidDataException("No Locale Found In Stub");
}
// Check if we have it from the last parse
if (LocalizationCache.Locales.ContainsKey(code) && LocalizationCache.Locales[code].Count > 0)
{
return(LocalizationCache.Locales[code]);
}
// --- From this point we effectively only do this once --
// Read in our spreadsheet, caching it just in case after the first time through
if (_spreadsheet == null)
{
_spreadsheet = SpreadsheetDocument.Open(GetType().Assembly.GetManifestResourceStream("Galileo.Localization.Locales.strings.xlsx"), false);
WorkbookPart workbookPart = _spreadsheet.WorkbookPart;
IEnumerable <Sheet> sheets = _spreadsheet.WorkbookPart.Workbook.GetFirstChild <Sheets>().Elements <Sheet>();
string relationshipId = sheets.First().Id.Value;
WorksheetPart worksheetPart = (WorksheetPart)_spreadsheet.WorkbookPart.GetPartById(relationshipId);
Worksheet workSheet = worksheetPart.Worksheet;
_sheetData = workSheet.GetFirstChild <SheetData>();
}
// Get all rows (this seems stupid, but we use it later)
IEnumerable <Row> rows = _sheetData.Descendants <Row>();
// Create our column index the first time through
if (_columnLookup == null)
{
_columnLookup = new Dictionary <string, string>();
int columnIndex = 1;
foreach (Cell cell in rows.ElementAt(0))
{
string columnHeader = GetCellValue(cell);
if (!columnHeader.StartsWith(CommentPrefix, StringComparison.Ordinal))
{
_columnLookup.Add(
GetColumnIdentifier(cell),
columnHeader);
}
columnIndex++;
}
}
// Create Key Locale
if (!LocalizationCache.Locales.ContainsKey(TranslationLocale))
{
LocalizationCache.Locales.Add(TranslationLocale, new Dictionary <string, string>());
}
// Building time
foreach (Row row in rows)
{
// Find out key
string key = GetCellValue(row.Descendants <Cell>().First());
// Allow comments in rows
if (key.StartsWith(CommentPrefix, StringComparison.Ordinal))
{
continue;
}
// Add to base key debug language
if (!LocalizationCache.Locales[TranslationLocale].ContainsKey(key))
{
LocalizationCache.Locales[TranslationLocale].Add(key, key);
}
for (int i = 0; i < row.Descendants <Cell>().Count(); i++)
{
Cell workingCell = row.Descendants <Cell>().ElementAt(i);
string columnID = GetColumnIdentifier(workingCell);
string cellValue = GetCellValue(workingCell);
if (string.IsNullOrEmpty(cellValue))
{
continue;
}
// Is an item
if (_columnLookup.ContainsKey(columnID))
{
// Check our cache has the right key
if (!LocalizationCache.Locales.ContainsKey(_columnLookup[columnID]))
{
LocalizationCache.Locales.Add(_columnLookup[columnID], new Dictionary <string, string>());
}
if (LocalizationCache.Locales[_columnLookup[columnID]].ContainsKey(key))
{
LocalizationCache.Locales[_columnLookup[columnID]][key] = cellValue;
}
else
{
LocalizationCache.Locales[_columnLookup[columnID]].Add(key, cellValue);
}
}
}
}
return(LocalizationCache.Locales[code]);
}
public void UpdateInvoiceNumberInSheet()
{
var firstInvoice = orderReports[0];
var invoiceNumber = string.Format("{0}-{1}", firstInvoice.Orders[0].OrderDate.ToString("MMyy"), firstInvoice.Orders[0].SchoolCode);
var cellInvoiceNumber = sheetData.Descendants <Cell>().First(c => c.CellReference == "J2");
cellInvoiceNumber.InlineString = new InlineString {
Text = new Text {
Text = invoiceNumber
}
};
cellInvoiceNumber.DataType = CellValues.InlineString;
}
public static DataTable ExcelWorksheetToDataTable(string pathFilename, string worksheetName, string scriptName)
{
DataTable dt = new DataTable(worksheetName);
using (SpreadsheetDocument document = SpreadsheetDocument.Open(pathFilename, false))
{
// Find the sheet with the supplied name, and then use that
// Sheet object to retrieve a reference to the first worksheet.
Sheet theSheet = document.WorkbookPart.Workbook.Descendants <Sheet>().Where(s => s.Name == worksheetName).FirstOrDefault();
if (theSheet == null)
{
throw new Exception("Couldn't find the worksheet: " + worksheetName);
}
// Retrieve a reference to the worksheet part.
WorksheetPart wsPart = (WorksheetPart)(document.WorkbookPart.GetPartById(theSheet.Id));
Worksheet workSheet = wsPart.Worksheet;
string dimensions = workSheet.SheetDimension.Reference.InnerText; // Get the dimensions of this worksheet, eg "B2:F4"
int numOfColumns = 0;
int numOfRows = 2;
CalculateDataTableSize(dimensions, ref numOfColumns, ref numOfRows);
System.Diagnostics.Trace.WriteLine(string.Format("The worksheet \"{0}\" has dimensions \"{1}\", so we need a DataTable of size {2}x{3}.", worksheetName, dimensions, numOfColumns, numOfRows));
SheetData sheetData = workSheet.GetFirstChild <SheetData>();
IEnumerable <Row> rows = sheetData.Descendants <Row>();
string[,] cellValues = new string[numOfColumns, 2];
int colInx = 0;
int rowInx = 0;
string value = "";
SharedStringTablePart stringTablePart = document.WorkbookPart.SharedStringTablePart;
// Iterate through each row of OpenXML data
foreach (Row row in rows)
{
if (row.Descendants <Cell>().Count() != 0)
{
Cell cell1 = row.Descendants <Cell>().ElementAt(0);
if (cell1.CellValue == null || cell1.CellReference == null)
{
continue;
}
String scriptActual = "";
if (cell1.DataType != null && cell1.DataType.Value == CellValues.SharedString)
{
scriptActual = stringTablePart.SharedStringTable.ChildElements[Int32.Parse(cell1.CellValue.InnerXml)].InnerText;
}
if (scriptActual.Equals("$Start") || scriptActual.Equals(scriptName))
{
for (int i = 0; i < row.Descendants <Cell>().Count(); i++)
{
// *DON'T* assume there's going to be one XML element for each item in each row...
Cell cell = row.Descendants <Cell>().ElementAt(i);
if (cell.CellValue == null || cell.CellReference == null)
{
continue; // eg when an Excel cell contains a blank string
}
// Convert this Excel cell's CellAddress into a 0-based offset into our array (eg "G13" -> [6, 12])
colInx = GetColumnIndexByName(cell.CellReference); // eg "C" -> 2 (0-based)
//rowInx = GetRowIndexFromCellAddress(cell.CellReference) - 1; // Needs to be 0-based
// Fetch the value in this cell
value = cell.CellValue.InnerXml;
if (cell.DataType != null && cell.DataType.Value == CellValues.SharedString)
{
value = stringTablePart.SharedStringTable.ChildElements[Int32.Parse(value)].InnerText;
}
cellValues[colInx, rowInx] = value;
}
rowInx++;
}
// dt.Rows.Add(dataRow);
}
}
// Copy the array of strings into a DataTable
for (int col = 0; col < numOfColumns; col++)
{
dt.Columns.Add("Column_" + col.ToString());
}
for (int row = 0; row < 2; row++)
{
DataRow dataRow = dt.NewRow();
for (int col = 0; col < numOfColumns; col++)
{
dataRow.SetField(col, cellValues[col, row]);
}
dt.Rows.Add(dataRow);
}
#if DEBUG
// Write out the contents of our DataTable to the Output window (for debugging)
string str = "";
for (rowInx = 0; rowInx < 2; rowInx++)
{
for (colInx = 0; colInx < numOfColumns; colInx++)
{
object val = dt.Rows[rowInx].ItemArray[colInx];
str += (val == null) ? "" : val.ToString();
str += "\t";
}
str += "\n";
}
System.Diagnostics.Trace.WriteLine(str);
#endif
return(dt);
}
}
public static List <ApproverExcelModel> GetDataTableFromSpreadsheet(Stream MyExcelStream, bool ReadOnly, HRProfile hRProfile)
{
List <ApproverExcelModel> dt = new List <ApproverExcelModel>();
using (SpreadsheetDocument sDoc = SpreadsheetDocument.Open(MyExcelStream, ReadOnly)) {
WorkbookPart workbookPart = sDoc.WorkbookPart;
IEnumerable <Sheet> sheets = sDoc.WorkbookPart.Workbook.GetFirstChild <Sheets>().Elements <Sheet>();
string relationshipId = sheets.First().Id.Value;
WorksheetPart worksheetPart = (WorksheetPart)sDoc.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))
{
//dt.Add(GetCellValue(sDoc, cell));
}
ApproverExcelModel approverExcelModel;
Debug.WriteLine("rows length = " + rows.Count());
foreach (Row row in rows)
{
approverExcelModel = new ApproverExcelModel();
//approverExcelModel.entrykey = ( int )row.RowIndex.Value;
for (int i = 0; i < row.Descendants <Cell>().Count(); i++)
{
Debug.WriteLine("i = " + i);
//approverExcelModel = new ApproverExcelModel();
switch (i)
{
case 0: //approvername
approverExcelModel.approvername = GetCellValue(sDoc, row.Descendants <Cell>().ElementAt(i));
break;
case 1: //approverid
approverExcelModel.approverid = GetCellValue(sDoc, row.Descendants <Cell>().ElementAt(i));
break;
case 2: //role
approverExcelModel.role = GetCellValue(sDoc, row.Descendants <Cell>().ElementAt(i));
break;
case 3: //roleid
approverExcelModel.roleid = int.Parse(GetCellValue(sDoc, row.Descendants <Cell>().ElementAt(i)));
break;
case 4: //unitcode
approverExcelModel.unitcode = GetCellValue(sDoc, row.Descendants <Cell>().ElementAt(i));
break;
case 5: //unitname
approverExcelModel.unitname = GetCellValue(sDoc, row.Descendants <Cell>().ElementAt(i));
break;
case 6: //deptcode
approverExcelModel.deptcode = GetCellValue(sDoc, row.Descendants <Cell>().ElementAt(i));
break;
case 7: //deptname
approverExcelModel.deptname = GetCellValue(sDoc, row.Descendants <Cell>().ElementAt(i));
break;
case 8: //groupcode
approverExcelModel.groupcode = GetCellValue(sDoc, row.Descendants <Cell>().ElementAt(i));
break;
case 9: //groupname
approverExcelModel.groupname = GetCellValue(sDoc, row.Descendants <Cell>().ElementAt(i));
break;
case 10: //supergroupcode
approverExcelModel.supergroupcode = GetCellValue(sDoc, row.Descendants <Cell>().ElementAt(i));
break;
case 11: //supergroupname
approverExcelModel.supergroupname = GetCellValue(sDoc, row.Descendants <Cell>().ElementAt(i));
break;
case 12: //comments
approverExcelModel.comments = GetCellValue(sDoc, row.Descendants <Cell>().ElementAt(i));
//approverExcelModel.HRProfile = hRProfile;
approverExcelModel.hrstaffname = hRProfile.name;
approverExcelModel.hrstaffnumber = hRProfile.employee_number;
approverExcelModel.entrykey = approverExcelModel.approverid + "_" + approverExcelModel.roleid.ToString() + "_" + approverExcelModel.unitcode + approverExcelModel.deptcode + "_" + approverExcelModel.groupcode + "_" + approverExcelModel.supergroupcode;
dt.Add(approverExcelModel);
break;
}
}
}
}
return(dt);
}
public static ModelTypeUploadModel ReadExcel(UploadFileImportModel model)
{
ModelTypeUploadModel modelTypeUpload = new ModelTypeUploadModel();
List <List <string> > rowValues = new List <List <string> >();
List <ModelTypeTempSheetModel> sheetModels = new List <ModelTypeTempSheetModel>();
ModelTypeTempSheetModel sheetModel;
using (SpreadsheetDocument spreadsheetDocument = SpreadsheetDocument.Open(model.FileName, false))
{
WorkbookPart workbookPart = spreadsheetDocument.WorkbookPart;
int sheetCount = 0;
foreach (Sheet sheet in workbookPart.Workbook.Sheets)
{
sheetModel = new ModelTypeTempSheetModel();
WorksheetPart worksheetPart = (WorksheetPart)(workbookPart.GetPartById(sheet.Id));
Worksheet worksheet = workbookPart.WorksheetParts.First().Worksheet;
SheetData sheetData = worksheet.GetFirstChild <SheetData>();
List <Row> rows = sheetData.Descendants <Row>().ToList();
List <string> preRow = new List <string>();
List <string> cellValues = new List <string>();
int indexMainEquipStart = 0;
int indexPNoStart = 0;
int indexTypeStart = 0;
int indexVinStart = 0;
int indexEngineSerialNoStart = 0;
int indexErrorDescriptionStart = 0;
int inedexRowOfHeader_Start = 6;
int indexRowOfHeader_End = 8;
// Assign header value
foreach (Cell cell in rows.ElementAt(5).Descendants <Cell>())
{
if (new[] { "A6", "B6", "C6", "E6", "F6" }.Contains(cell.CellReference.Value))
{
string value = GetCellValue(workbookPart, sheet, cell.CellReference);
switch (cell.CellReference.Value)
{
case "A6":
sheetModel.YM = value;
break;
case "B6":
sheetModel.Model = value;
break;
case "C6":
sheetModel.Door = value;
break;
case "E6":
sheetModel.Plant = value;
break;
case "F6":
sheetModel.Status = value;
break;
}
}
}
// Find column header index
for (var i = inedexRowOfHeader_Start; i < indexRowOfHeader_End; i++)
{
foreach (Cell cell in rows.ElementAt(i).Descendants <Cell>())
{
if (GetCellValue(workbookPart, sheet, cell.CellReference) == "MAIN EQUIPMENT")
{
indexMainEquipStart = GetColumnIndex(cell.CellReference);
}
if (GetCellValue(workbookPart, sheet, cell.CellReference) == "P.No.")
{
indexPNoStart = GetColumnIndex(cell.CellReference);
}
if (GetCellValue(workbookPart, sheet, cell.CellReference) == "TYPE")
{
indexTypeStart = GetColumnIndex(cell.CellReference);
}
if (GetCellValue(workbookPart, sheet, cell.CellReference) == "VIN")
{
indexVinStart = GetColumnIndex(cell.CellReference);
}
if (GetCellValue(workbookPart, sheet, cell.CellReference) == "ENGINE SERIAL No.")
{
indexEngineSerialNoStart = GetColumnIndex(cell.CellReference);
}
if (GetCellValue(workbookPart, sheet, cell.CellReference) == "Error Description")
{
indexErrorDescriptionStart = GetColumnIndex(cell.CellReference);
}
}
}
ModelTypeTempRowModel modelTypeTempRowModel;
ModelTypeTempEngineModel engineModel;
List <ModelTypeTempEquipmentModel> equipmentModels;
ModelTypeTempEquipmentModel equipmentModel;
List <ModelTypeTempTypeModel> typeModels;
ModelTypeTempTypeModel typeModel;
for (var i = 9; i < rows.Count; i++)
{
modelTypeTempRowModel = new ModelTypeTempRowModel();
equipmentModels = new List <ModelTypeTempEquipmentModel>();
typeModel = new ModelTypeTempTypeModel();
typeModels = new List <ModelTypeTempTypeModel>();
cellValues = new List <string>();
engineModel = new ModelTypeTempEngineModel();
modelTypeTempRowModel.RowNo = i + 1;
foreach (Cell cell in rows.ElementAt(i).Cast <Cell>())
{
string currentColumn = GetColumnName(cell.CellReference);
int currentIndex = GetColumnIndex(cell.CellReference);
string currentCellValue = GetCellValue(workbookPart, sheet, cell.CellReference);
int sequence = 1;
#region Engine
if (new[] { "A", "B", "C", "D", "E", "F", "G", "H", "I", "J" }.Contains(GetColumnName(cell.CellReference)))
{
#region Replace Value
if (cell.CellReference == "A" + (i + 1))
{
if (string.IsNullOrEmpty(currentCellValue))
{
currentCellValue = preRow[0];
}
}
if (cell.CellReference == "B" + (i + 1))
{
if (string.IsNullOrEmpty(currentCellValue))
{
currentCellValue = preRow[1];
}
}
if (cell.CellReference == "C" + (i + 1))
{
if (string.IsNullOrEmpty(currentCellValue))
{
currentCellValue = preRow[2];
}
}
if (cell.CellReference == "D" + (i + 1))
{
if (string.IsNullOrEmpty(currentCellValue))
{
currentCellValue = preRow[3];
}
}
if (cell.CellReference == "E" + (i + 1))
{
if (string.IsNullOrEmpty(currentCellValue))
{
currentCellValue = preRow[4];
}
}
#endregion
switch (GetColumnName(cell.CellReference))
{
case "A":
engineModel.SS = currentCellValue;
break;
case "B":
engineModel.DISP = currentCellValue;
break;
case "C":
engineModel.COMCARB = currentCellValue;
break;
case "D":
engineModel.Grade = currentCellValue;
break;
case "E":
engineModel.Mis = currentCellValue;
break;
case "F":
engineModel.ModelCode01 = currentCellValue;
break;
case "G":
engineModel.ModelCode02 = currentCellValue;
break;
case "H":
engineModel.ModelCode03 = currentCellValue;
break;
case "I":
engineModel.ModelCode04 = currentCellValue;
break;
case "J":
engineModel.ModelCode05 = currentCellValue;
break;
}
}
#endregion
#region MAIN EQUIPMENT
string columnEndGetEquipment = GetColumnName(GetEndColumnMergeCell(workbookPart, sheet, "K7"));
int indexMainEquipEnd = GetColumnIndex(columnEndGetEquipment);
if (currentIndex >= indexMainEquipStart && currentIndex <= indexMainEquipEnd) // Start K Column
{
equipmentModel = new ModelTypeTempEquipmentModel
{
EquipmentName = GetCellValue(workbookPart, sheet, currentColumn + 9),
EquipmentValue = currentCellValue,
Sequence = sequence
};
sequence++;
equipmentModels.Add(equipmentModel);
}
#endregion
#region PNo
if (currentIndex == indexPNoStart)
{
modelTypeTempRowModel.PNo = currentCellValue;
}
#endregion
#region TYPE
if (currentIndex >= indexTypeStart && currentIndex <= indexVinStart - 1)
{
typeModel = new ModelTypeTempTypeModel
{
ModelType = GetCellValue(workbookPart, sheet, currentColumn + 9),
ModelCode = currentCellValue,
Sequence = sequence
};
typeModels.Add(typeModel);
}
#endregion
#region VIN
if (currentIndex == indexVinStart)
{
modelTypeTempRowModel.VIN = currentCellValue;
}
#endregion
cellValues.Add(currentCellValue);
}
// End Cell
preRow = cellValues;
rowValues.Add(cellValues);
modelTypeTempRowModel.ModelTypeTempEngines.Add(engineModel);
modelTypeTempRowModel.ModelTypeTempEquipmentModels.AddRange(equipmentModels);
modelTypeTempRowModel.ModelTypeTempTypeModels.AddRange(typeModels);
sheetModel.ModelTypeTempRowModels.Add(modelTypeTempRowModel);
}
//End Row
sheetModel.SheetNo = ++sheetCount;
modelTypeUpload.ModelTypeTempSheetModels.Add(sheetModel);
}
}
modelTypeUpload.UploadStatusID = 44; // wait edit
return(modelTypeUpload);
}
// Retrieving
public static DataTable GetWorkSheetData(SpreadsheetDocument ss, String sheetName, bool includeHeaderRow, int headerRowOffset = 0)
{
DataTable dt = new DataTable();
WorksheetPart wsp;
if (sheetName != null)
{
wsp = GetWorkSheetPartByName(ss, sheetName);
}
else
{
wsp = GetFirstWorkSheetPart(ss);
}
if (wsp != null)
{
Worksheet ws = wsp.Worksheet;
SheetData sheet_data = ws.GetFirstChild <SheetData>();
IEnumerable <Row> rows = sheet_data.Descendants <Row>();
foreach (Cell cell in rows.ElementAt(0))
{
String ColumnName = GetCellValue(ss, cell);
if (!dt.Columns.Contains(ColumnName))
{
dt.Columns.Add(ColumnName);
}
}
// Fill data table with Excel data.
// This implementation includes blank cells from Excel datasets, meaning the datatable will not
// skew when encoutering skipped elements in the XML file (blank cells are null, and are not included in the XML).
foreach (Row row in rows)
{
DataRow tempRow = dt.NewRow();
int columnIndex = 0;
foreach (Cell cell in row.Descendants <Cell>())
{
// Gets the column index of the cell with data
int cellColumnIndex = ColumnNames.IndexOf(GetColumnName(cell.CellReference));
// (int)GetColumnIndexFromName(GetColumnName(cell.CellReference));
if (columnIndex < cellColumnIndex)
{
do
{
if (tempRow.ItemArray.Length > columnIndex)
{
tempRow[columnIndex] = String.Empty;
}
else
{
break;
}
columnIndex++;
}while (columnIndex < cellColumnIndex);
}
String cell_value = GetCellValue(ss, cell);
if (columnIndex < tempRow.ItemArray.Length)
{
tempRow[columnIndex] = cell_value;
}
columnIndex++;
}
dt.Rows.Add(tempRow);
}
if (headerRowOffset != 0)
{
for (int row = 1; row < (headerRowOffset + 1); row++)
{
dt.Rows.RemoveAt(0);
}
// Attempt to set new DataTable header names (as the headers would have been set earlier, without regard for the header offset)
if (dt.Rows.Count > 0)
{
for (int j = 0; j < dt.Columns.Count; j++)
{
String ColumnName = dt.Rows[0][j].ToString();
if (String.IsNullOrEmpty(ColumnName)) // give dummny name if necessary
{
ColumnName = j.ToString();
}
dt.Columns[j].ColumnName = ColumnName;
}
}
}
if (!includeHeaderRow)
{
dt.Rows.RemoveAt(0);
}
}
return(dt);
}
public static List <SetupExcelModel> GetDataTableFromSpreadsheet(Stream MyExcelStream, bool ReadOnly, BHSingleSetupModel bHSingleSetupModel, HRProfile hRProfile)
{
List <SetupExcelModel> dt = new List <SetupExcelModel>();
using (SpreadsheetDocument sDoc = SpreadsheetDocument.Open(MyExcelStream, ReadOnly)) {
WorkbookPart workbookPart = sDoc.WorkbookPart;
IEnumerable <Sheet> sheets = sDoc.WorkbookPart.Workbook.GetFirstChild <Sheets>().Elements <Sheet>();
string relationshipId = sheets.First().Id.Value;
WorksheetPart worksheetPart = (WorksheetPart)sDoc.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))
{
//dt.Add(GetCellValue(sDoc, cell));
}
SetupExcelModel setupExcelModel;
Debug.WriteLine("rows length = " + rows.Count());
foreach (Row row in rows)
{
setupExcelModel = new SetupExcelModel();
setupExcelModel.Id = (int)row.RowIndex.Value;
for (int i = 0; i < row.Descendants <Cell>().Count(); i++)
{
Debug.WriteLine("i = " + i);
switch (i)
{
case 0: //StaffBranch
setupExcelModel.StaffBranch = GetCellValue(sDoc, row.Descendants <Cell>().ElementAt(i));
break;
case 1: //StaffBranchCode
setupExcelModel.StaffBranchCode = GetCellValue(sDoc, row.Descendants <Cell>().ElementAt(i));
break;
case 2: //StaffNumber
setupExcelModel.StaffNumber = GetCellValue(sDoc, row.Descendants <Cell>().ElementAt(i));
break;
case 3: //StaffName
setupExcelModel.StaffName = GetCellValue(sDoc, row.Descendants <Cell>().ElementAt(i));
break;
case 4: //StaffRole--SelectedAppraisalPeriod--SetupAppPeriod--HRProfile--Comments
setupExcelModel.StaffRole = GetCellValue(sDoc, row.Descendants <Cell>().ElementAt(i));
setupExcelModel.SelectedAppraisalPeriod = bHSingleSetupModel.SelectedAppraisalPeriod;
setupExcelModel.SetupAppPeriod = bHSingleSetupModel.SetupAppPeriod;
setupExcelModel.HRProfile = hRProfile;
setupExcelModel.Comments = bHSingleSetupModel.Comments;
dt.Add(setupExcelModel);
break;
}
}
}
}
return(dt);
}
/// <summary>
/// Read Data from selected excel file into DataTable
/// </summary>
/// <param name="filename">Excel File Path</param>
/// <returns></returns>
///
private DataTable ReadExcelFile(string filename, string tablename, string[] fieldstotrim)
{
// Initialize an instance of DataTable
DataTable dt = new DataTable();
dt.TableName = tablename;
int rowcounter = 0;
try
{
// Use SpreadSheetDocument class of Open XML SDK to open excel file
using (SpreadsheetDocument spreadsheetDocument = SpreadsheetDocument.Open(filename, false))
{
// Get Workbook Part of Spread Sheet Document
WorkbookPart workbookPart = spreadsheetDocument.WorkbookPart;
// Get all sheets in spread sheet document
IEnumerable <Sheet> sheetcollection = spreadsheetDocument.WorkbookPart.Workbook.GetFirstChild <Sheets>().Elements <Sheet>();
// Get relationship Id
string relationshipId = sheetcollection.First().Id.Value;
// Get sheet1 Part of Spread Sheet Document
WorksheetPart worksheetPart = (WorksheetPart)spreadsheetDocument.WorkbookPart.GetPartById(relationshipId);
// Get Data in Excel file
SheetData sheetData = worksheetPart.Worksheet.Elements <SheetData>().First();
IEnumerable <Row> rowcollection = sheetData.Descendants <Row>();
if (rowcollection.Count() == 0)
{
return(dt);
}
// Add columns
foreach (Cell cell in rowcollection.ElementAt(0))
{
dt.Columns.Add(GetValueOfCell(spreadsheetDocument, cell));
}
// Add rows into DataTable
foreach (Row row in rowcollection)
{
DataRow temprow = dt.NewRow();
int columnIndex = 0;
string columnname = "";
foreach (Cell cell in row.Descendants <Cell>())
{
// Get Cell Column Index
columnname = GetColumnName(cell.CellReference);
int cellColumnIndex = GetColumnIndex(columnname);
if (columnIndex < cellColumnIndex)
{
do
{
try
{
temprow[columnIndex] = string.Empty;
columnIndex++;
}
catch (Exception x)
{
System.Diagnostics.Debug.Print(x.Message);
}
}while (columnIndex < cellColumnIndex);
}
if (fieldstotrim != null && fieldstotrim.Where(f => f.Equals(columnname)).Count() == 1)
{
temprow[columnIndex] = GetValueOfCell(spreadsheetDocument, cell).Replace(" ", "");
}
else
{
temprow[columnIndex] = GetValueOfCell(spreadsheetDocument, cell);
}
columnIndex++;
if (columnIndex >= 7)
{
break;
}
}
// Add the row to DataTable
// the rows include header row
dt.Rows.Add(temprow);
rowcounter++;
}
}
// Here remove header row
dt.Rows.RemoveAt(0);
return(dt);
}
catch (IOException ex)
{
throw new IOException(ex.Message);
}
}
/// <summary>
/// Gets work sheet rows.
/// </summary>
/// <param name="worksheet">The work sheet.</param>
/// <returns>The work sheet rows.</returns>
internal IEnumerable <Row> GetWorkSheetRows(Worksheet worksheet)
{
SheetData sheetData = worksheet.GetFirstChild <SheetData>();
return(sheetData?.Descendants <Row>());
}
private static void ReadExcelData(ClientContext clientContext, string fileName)
{
bool IsError = true;
string strErrorMsg = string.Empty;
const string lstDocName = "Documents";
try
{
DataTable dataTable = new DataTable("ExcelDataTable");
List list = clientContext.Web.Lists.GetByTitle(lstDocName);
clientContext.Load(list.RootFolder);
clientContext.ExecuteQuery();
string fileServerRelativeUrl = list.RootFolder.ServerRelativeUrl + "/" + "ExcelFile.xlsx";
SP.File file = clientContext.Web.GetFileByServerRelativeUrl(fileServerRelativeUrl);
ClientResult <System.IO.Stream> data = file.OpenBinaryStream();
clientContext.Load(file);
clientContext.ExecuteQuery();
using (System.IO.MemoryStream mStream = new System.IO.MemoryStream())
{
if (data != null)
{
data.Value.CopyTo(mStream);
using (SpreadsheetDocument documnet = SpreadsheetDocument.Open(mStream, false))
{
WorkbookPart workbookpart = documnet.WorkbookPart;
IEnumerable <Sheet> sheets = documnet.WorkbookPart.Workbook.GetFirstChild <Sheets>().Elements <Sheet>();
string relationshipId = sheets.First().Id.Value;
WorksheetPart worksheetPart = (WorksheetPart)documnet.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, documnet, 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, documnet, row.Descendants <Cell>().ElementAt(i));
}
dataTable.Rows.Add(dataRow);
}
}
dataTable.Rows.RemoveAt(0);
}
}
}
UpdateSPList(clientContext, dataTable, fileName);
IsError = false;
}
catch (Exception e)
{
IsError = true;
Console.WriteLine(e.Message);
Console.WriteLine("second catch block");
}
finally
{
if (IsError)
{
//Logging
}
}
}
/// <summary>
/// Adjust the column width to its best fit
/// </summary>
/// <param name="document">SpreadSheetDocument reference</param>
public static void AutoAdjustWidth(this SpreadsheetDocument document)
{
if (document.IsNotValid())
{
return;
}
//Maximum digit with per cell ~96dpi (most common display)
double max = 8;
//Add the columns for each worksheet that has any valid sheet data
document.WorkbookPart.WorksheetParts
.Select(wsp => wsp.Worksheet)
.Where(wsp => wsp.Descendants <SheetData>().Any(sd => sd.Descendants <Row>().Any()))
.ToList()
.ForEach(ws =>
{
//Current SheetData reference
SheetData sd = ws.GetFirstChild <SheetData>();
//Find the widest row
Row row = sd.Descendants <Row>().First();
//Array of lengths (max length per cell per row)
int[] lengths = new int[row.Descendants <Cell>().Count()];
for (int i = 0; i < lengths.Length; i++)
{
lengths[i] = 0;
}
//Find the widest cell in all rows
foreach (Row r in sd.Descendants <Row>())
{
for (int i = 0; i < lengths.Length; i++)
{
Cell cell = r.Descendants <Cell>().ElementAt(i);
int length;
//Take the cell length value from the shared string table if the cell has its value in the shared string table
if (cell.DataType.Equals(CellValues.SharedString) && cell.CellValue.Text.IsNumber())
{
int index = Convert.ToInt32(cell.CellValue.Text);
length = StringPart.SharedStringTable.ElementAt(index).InnerText.Length;
}
else
{
//Take the length from the cell value directly
length = cell.CellValue.Text.Length;
}
//Set the current length for the cell at row if the length at the current position is less than the current
if (lengths[i] < length)
{
lengths[i] = length;
}
}
}
//Get or create the custom columns reference
Columns columns = ws.GetFirstChild <Columns>() ?? new Columns();
//Adjust the columns width
for (uint i = 1; i <= lengths.Length; i++)
{
double width = ((lengths[i - 1] * max + 5) / max * 256) / 256;
columns.Append(new Column()
{
Min = i,
Max = i,
Width = width + 1,
CustomWidth = true
});
}
//Add the columns if they do not exists
if (ws.GetFirstChild <Columns>() == null)
{
ws.InsertBefore(columns, sd);
}
//Save all changes made
ws.Save();
});
document.Save();
}
private static List<ColumnData> GetColumnsEquivalences(this SpreadsheetDocument document, SheetData sheetData, ResultTable results)
{
var resultsCols = results.Columns.ToDictionary(c => c.Column.DisplayName);
var headerCells = sheetData.Descendants<Row>().FirstEx().Descendants<Cell>().ToList();
var templateCols = headerCells.ToDictionary(c => document.GetCellValue(c));
var rowDataCellTemplates = sheetData.Descendants<Row>()
.FirstEx(r => IsValidRowDataTemplate(r, headerCells))
.Descendants<Cell>().ToList();
var dic = templateCols.OuterJoinDictionaryCC(resultsCols, (name, cell, resultCol) =>
{
if (resultCol == null)
throw new ApplicationException(ExcelMessage.TheExcelTemplateHasAColumn0NotPresentInTheFindWindow.NiceToString().FormatWith(name));
if (cell != null)
{
return new ColumnData
{
IsNew = false,
StyleIndex = rowDataCellTemplates[headerCells.IndexOf(cell)].StyleIndex,
Column = resultCol,
};
}
else
{
CellBuilder cb = PlainExcelGenerator.CellBuilder;
return new ColumnData
{
IsNew = true,
StyleIndex = 0,
Column = resultCol,
};
}
});
return dic.Values.ToList();
}
public void openWorkBook(string fileName, DataSet studentData)
{
using (SpreadsheetDocument spreadsheetDocument = SpreadsheetDocument.Open(fileName, true))
{
WorkbookPart workbookPart = spreadsheetDocument.WorkbookPart;
IEnumerable <Sheet> sheets = spreadsheetDocument.WorkbookPart.Workbook.GetFirstChild <Sheets>().Elements <Sheet>();
string relationshipId = sheets.First().Id.Value;
WorksheetPart worksheetPart = InsertErrorWorksheet(workbookPart);
Worksheet workSheet = worksheetPart.Worksheet;
SheetData sheetData = workSheet.GetFirstChild <SheetData>();
IEnumerable <Row> rows = sheetData.Descendants <Row>();
List <string> columnNames = new List <string>();
//Row headerRow = rows.ElementAt(0);
// int errorColIndex = headerRow.Count();
WorkbookStylesPart styles = workbookPart.WorkbookStylesPart;
//Write Dataset
var dataTable = studentData.Tables[0];
//var errorStyle = SetErrorStyle(styles.Stylesheet);
//// Set columns
//foreach (Cell cell in headerRow)
//{
// columnNames.Add(getCellValue(workbookPart, cell));
// //Console.Write($"{} ");
//}
if (dataTable.Columns.Contains("Error"))
{
var columnList = new List <string> {
"A", "B", "C", "D", "E", "F", "G", "H", "I", "J", "K", "L"
};
//add header column
var headerRow = AddRowToErrorSheet(sheetData);
foreach (DataColumn column in dataTable.Columns)
{
var cellReference = columnList[column.Ordinal] + headerRow.RowIndex;
// If there is not a cell with the specified column name, insert one.
// Cells must be in sequential order according to CellReference. Determine where to insert the new cell.
Cell refCell = null;
foreach (Cell cell in headerRow.Elements <Cell>())
{
if (cell.CellReference.Value.Length == cellReference.Length)
{
if (string.Compare(cell.CellReference.Value, cellReference, true) > 0)
{
refCell = cell;
break;
}
}
}
var newRowCell = AddRowText(workbookPart, headerRow, column.ColumnName);
newRowCell.CellReference = cellReference;
headerRow.InsertBefore(newRowCell, refCell);
}
foreach (DataRow errorRow in dataTable.Rows)
{
var row = AddRowToErrorSheet(sheetData);
foreach (DataColumn column in dataTable.Columns)
{
var cellReference = columnList[column.Ordinal] + row.RowIndex;
// If there is not a cell with the specified column name, insert one.
// 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 (cell.CellReference.Value.Length == cellReference.Length)
{
if (string.Compare(cell.CellReference.Value, cellReference, true) > 0)
{
refCell = cell;
break;
}
}
}
var newRowCell = AddRowText(workbookPart, row, errorRow[column.ColumnName].ToString());
newRowCell.CellReference = cellReference;
row.InsertBefore(newRowCell, refCell);
}
}
}
Console.WriteLine();
//Write data to datatable
//var indexDiff = 2;
//var dataTable = studentData.Tables[0];
//foreach (Row row in rows.Skip(1))
//{
// var rowIndex = Convert.ToInt32(row.RowIndex.ToString()) - indexDiff;
// var errorText= dataTable.Rows[rowIndex].Field<string>("Error");
// var newRowCell = AddRowText(workbookPart, row, errorText);
// // newRowCell.StyleIndex = errorStyle;
// if (row.Count() < errorColIndex)
// {
// Console.WriteLine($"Add cells between { row.Count() } to {errorColIndex}");
// for(int x = row.Count(); x <= errorColIndex; x++)
// {
// Cell emptyCell = new Cell() { CellReference = row.RowIndex.ToString(), CellValue = new CellValue()};
// row.InsertAt(emptyCell, x);
// }
// }
// if (row.ElementAt(errorColIndex) != null)
// {
// var existingCell = row.Descendants<Cell>().ElementAt(errorColIndex);
// existingCell.CellValue.Text = newRowCell.CellValue.InnerText;
// existingCell.DataType = newRowCell.DataType;
// // existingCell.StyleIndex = errorStyle;
// }
// else
// {
// row.InsertAt(newRowCell, errorColIndex);
// }
// Console.WriteLine(row.Count());
//}
workSheet.Save();
}
}
static void InsertIntoDataTable(ClientContext clientContext, String fileName) //this method is used to read the
{ //data from excel then stored on datatble
string strErrorMsg = string.Empty;
try
{
DataTable dataTable = new DataTable("ExcelInformation");//Use temporary datatble
List list = clientContext.Web.Lists.GetByTitle(Constant.RootFolder);
clientContext.Load(list.RootFolder);
clientContext.ExecuteQuery();
string fileServerRelativeUrl = list.RootFolder.ServerRelativeUrl + "/" + fileName;
File file = clientContext.Web.GetFileByServerRelativeUrl(fileServerRelativeUrl);//Extracting the file
ClientResult <System.IO.Stream> data = file.OpenBinaryStream();
clientContext.Load(file);
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)) //reading the file colums
{
string str = GetCellValue(document, cell); //getting cell data using the cell value(column name)
dataTable.Columns.Add(str); //after adding to the datatable
}
foreach (Row row in rows) //rows contains the all information now take each one from roes then added to the datatable
{
if (row != null) //if row does't contains the data then no need to insert the data
{
DataRow dataRow = dataTable.NewRow();
for (int i = 0; i < row.Descendants <Cell>().Count(); i++)
{
dataRow[i] = GetCellValue(document, row.Descendants <Cell>().ElementAt(i)); //here reading the entire row from excel sheet
}
dataTable.Rows.Add(dataRow); //finally addin the each row to the datatble
}
}
dataTable.Rows.RemoveAt(0); //deleting the column heading
}
}
}
OriginaldataTable = dataTable.Copy(); //copied to temporary datatble to original datatable
// display();
UploadFile(clientContext, dataTable, fileName); //Here Upload the all files to sharepoint
string FileNameAfterChange = ExportToExcelSheet();
UploadExcelFileToSharepoint(clientContext, FileNameAfterChange + ".xlsx"); //Upload the updated sharepoint
}
catch (Exception e)
{
LogClass.RecordException(e);
strErrorMsg = e.Message;
}
}
public static Cell FindCell(this SheetData sheetData, string addressName)
{
return(sheetData.Descendants <Cell>().
Where(c => c.CellReference == addressName).FirstOrDefault());
}
private IEnumerable<Cell> GetColumnCells(SheetData sheetData, int colIndex)
{
var cells = sheetData.Descendants<Row>()
.Select(x => x.Descendants<Cell>().ElementAt(colIndex));
return cells;
}
/// <summary>
/// Read Data from selected excel file into DataTable
/// </summary>
/// <param name="filename">Excel File Path</param>
/// <returns></returns>
private DataTable ReadExcelFile(string filename)
{
// Initialize an instance of DataTable
DataTable dt = new DataTable();
try
{
// Use SpreadSheetDocument class of Open XML SDK to open excel file
using (SpreadsheetDocument spreadsheetDocument = SpreadsheetDocument.Open(filename, false))
{
// Get Workbook Part of Spread Sheet Document
WorkbookPart workbookPart = spreadsheetDocument.WorkbookPart;
// Get all sheets in spread sheet document
IEnumerable <Sheet> sheetcollection = spreadsheetDocument.WorkbookPart.Workbook.GetFirstChild <Sheets>().Elements <Sheet>();
// Get relationship Id
string relationshipId = sheetcollection.First().Id.Value;
// Get sheet1 Part of Spread Sheet Document
WorksheetPart worksheetPart = (WorksheetPart)spreadsheetDocument.WorkbookPart.GetPartById(relationshipId);
// Get Data in Excel file
SheetData sheetData = worksheetPart.Worksheet.Elements <SheetData>().First();
IEnumerable <Row> rowcollection = sheetData.Descendants <Row>();
if (rowcollection.Count() == 0)
{
return(dt);
}
// Add columns
foreach (Cell cell in rowcollection.ElementAt(0))
{
dt.Columns.Add(GetValueOfCell(spreadsheetDocument, cell));
}
// Add rows into DataTable
foreach (Row row in rowcollection)
{
DataRow temprow = dt.NewRow();
int columnIndex = 0;
foreach (Cell cell in row.Descendants <Cell>())
{
// Get Cell Column Index
int cellColumnIndex = GetColumnIndex(GetColumnName(cell.CellReference));
if (columnIndex < cellColumnIndex)
{
do
{
temprow[columnIndex] = string.Empty;
columnIndex++;
}while (columnIndex < cellColumnIndex);
}
temprow[columnIndex] = GetValueOfCell(spreadsheetDocument, cell);
columnIndex++;
}
// Add the row to DataTable
// the rows include header row
dt.Rows.Add(temprow);
}
}
// Here remove header row
dt.Rows.RemoveAt(0);
return(dt);
}
catch (IOException ex)
{
throw new IOException(ex.Message);
}
}
private static bool LoadRowsFromExcel(this DataTable dt, SpreadsheetDocument sd, LoadRowsFromSpreadsheetSettings settings)
{
DataTableHelpers.RequiresZeroRows(dt, nameof(dt));
Requires.NonNull(sd, nameof(sd));
Requires.NonNull(settings, nameof(settings));
var rows = new List <IList <object> >();
var sharedStringDictionary = ConvertSharedStringTableToDictionary(sd);
var sheets = sd.WorkbookPart.Workbook.GetFirstChild <Sheets>().Elements <Sheet>();
int sheetNumber = 0;
foreach (var sheet in sheets)
{
if (sheetNumber == settings.SheetNumber || 0 == string.Compare(settings.SheetName, sheet.Name, true))
{
if (settings.UseSheetNameForTableName)
{
dt.TableName = sheet.Name;
}
string relationshipId = sheet.Id.Value;
var worksheetPart = (WorksheetPart)sd.WorkbookPart.GetPartById(relationshipId);
SheetData sheetData = worksheetPart.Worksheet.GetFirstChild <SheetData>();
IEnumerable <Row> eRows = sheetData.Descendants <Row>();
foreach (Row erow in eRows)
{
CreateRow:
var row = new List <object>();
rows.Add(row);
foreach (var cell in erow.Descendants <Cell>())
{
var cr = GetColRowFromCellReference(cell.CellReference);
if (rows.Count <= cr.Item2)
{
goto CreateRow;
}
while (row.Count < cr.Item1)
{
row.Add(null);
}
Debug.Assert(row.Count == cr.Item1);
var val = GetCellValue(sd, cell, settings.TreatAllValuesAsText, sharedStringDictionary);
row.Add(val);
}
}
GC.Collect();
IEnumerable <IList <object> > positionnedRows;
if (settings.SkipRawRows.HasValue)
{
positionnedRows = rows.Skip(settings.SkipRawRows.Value);
}
else if (settings.SkipWhileTester != null)
{
positionnedRows = rows.SkipWhile(settings.SkipWhileTester);
}
else
{
positionnedRows = rows;
}
dt.LoadRows(positionnedRows, settings);
return(true);
}
++sheetNumber;
}
if (settings.ThrowOnMissingSheet)
{
throw new Exception(string.Format(
"Sheet [{0}] was not found",
(object)settings.SheetNumber ?? (object)settings.SheetName));
}
return(false);
}
public static Cell GetCell(SheetData sheetData, string cellAddress)
{
uint rowIndex = uint.Parse(Regex.Match(cellAddress, @"[0-9]+").Value);
return(sheetData.Descendants <Row>().FirstOrDefault(p => p.RowIndex == rowIndex).Descendants <Cell>().FirstOrDefault(p => p.CellReference == cellAddress));
}
public async Task <FileInfo> GetExcelFile()
{
Dictionary <string, Int32> sharedStringDic = new Dictionary <string, int>();
const string excelTemplate = "Tools_CalDue_Template.xlsx";
const string sheetname = "Sheet1";
const int styleString = 0; // for this template
const int styleDate = 3; // for this template
string providername = Startup.AppSettings["StorageProvider"];
string folder = Startup.AppSettings["PdfFoldername"];
FileInfo fileinfo;
Int32 sharedStringId = 0;
UInt32 lineIndex;
SysIO.MemoryStream ms = new SysIO.MemoryStream();;
if (providername == "localfile")
{
string filepath = SysIO.Path.Combine(folder, excelTemplate);
//fileinfo.FileByteStream = SysIO.File.Open(filepath, SysIO.FileMode.Open);
SysIO.FileStream fs;
fs = SysIO.File.Open(filepath, System.IO.FileMode.Open);
fs.CopyTo(ms);
long bytesInStream = ms.Length;
fs.Close();
}
else if (providername == "AzureBlob")
{
string connectionstring = Startup.AppSettings["AzureBlob"];
CloudStorageAccount storageAccount = CloudStorageAccount.Parse(connectionstring);
CloudBlobClient blobClient = storageAccount.CreateCloudBlobClient();
CloudBlobContainer container = blobClient.GetContainerReference("templates");
CloudBlockBlob blockBlob = container.GetBlockBlobReference(excelTemplate);
SysIO.Stream frs = await blockBlob.OpenReadAsync();
frs.CopyTo(ms);
long bytesInStream = ms.Length;
bool canread = ms.CanRead;
bool canwrite = ms.CanWrite;
frs.Close();
}
SpreadsheetDocument document = SpreadsheetDocument.Open(ms, true);
WorkbookPart wbPart = document.WorkbookPart;
Sheet theSheet = wbPart.Workbook.Descendants <Sheet>().Where(s => s.Name == sheetname).FirstOrDefault();
if (theSheet == null)
{
throw new ArgumentException("sheetName");
}
WorksheetPart wsPart = (WorksheetPart)(wbPart.GetPartById(theSheet.Id));
Worksheet ws = wsPart.Worksheet;
Columns columns = ws.Descendants <Columns>().FirstOrDefault();
SheetData sheetData = ws.Descendants <SheetData>().FirstOrDefault();
Row firstRow = sheetData.Descendants <Row>().ElementAt(0); // get first row , line 1
firstRow.DyDescent = 0.3D;
Row secondRow = sheetData.Descendants <Row>().ElementAt(1); // get second row , line 2
secondRow.Remove();
SharedStringTablePart sharedStringPart = wbPart.GetPartsOfType <SharedStringTablePart>().First();
SharedStringTable sharedStringTable = sharedStringPart.SharedStringTable;
foreach (SharedStringItem item in sharedStringTable.Elements <SharedStringItem>()) // read shared string and add to Dictionary
{
if (item.InnerText != null)
{
sharedStringDic.Add(item.InnerText, sharedStringId);
++sharedStringId;
}
}
lineIndex = 2;
string id;
// StoreLocation が column "B" と "M" の 2箇所にある
foreach (var tool in filteredData)
{
Row newRow = new Row()
{
RowIndex = lineIndex, Spans = new ListValue <StringValue>()
{
InnerText = "1:20"
}, Height = 16.5D, CustomHeight = true, DyDescent = 0.3D
};
Cell cell1 = new Cell()
{
CellReference = "A" + lineIndex.ToString(), DataType = CellValues.SharedString, StyleIndex = styleString
};
id = sharedStringDic.AddToSharedString(tool.Plant).ToString();
cell1.CellValue = new CellValue(id);
Cell cell2 = new Cell()
{
CellReference = "B" + lineIndex.ToString(), DataType = CellValues.SharedString, StyleIndex = styleString
};
id = sharedStringDic.AddToSharedString(tool.StoreLocation).ToString();
cell2.CellValue = new CellValue(id);
Cell cell3 = new Cell()
{
CellReference = "C" + lineIndex.ToString(), DataType = CellValues.SharedString, StyleIndex = styleString
};
id = sharedStringDic.AddToSharedString(tool.ToolkitSloc).ToString();
cell3.CellValue = new CellValue(id);
Cell cell4 = new Cell()
{
CellReference = "D" + lineIndex.ToString(), DataType = CellValues.SharedString, StyleIndex = styleString
};
id = sharedStringDic.AddToSharedString(tool.SerialNumber).ToString();
cell4.CellValue = new CellValue(id);
Cell cell5 = new Cell()
{
CellReference = "E" + lineIndex.ToString(), DataType = CellValues.SharedString, StyleIndex = styleString
};
id = sharedStringDic.AddToSharedString(tool.Material).ToString();
cell5.CellValue = new CellValue(id);
Cell cell6 = new Cell()
{
CellReference = "F" + lineIndex.ToString(), DataType = CellValues.SharedString, StyleIndex = styleString
};
id = sharedStringDic.AddToSharedString(tool.Description).ToString();
cell6.CellValue = new CellValue(id);
Cell cell7 = new Cell()
{
CellReference = "G" + lineIndex.ToString(), StyleIndex = styleDate
};
if (tool.LatestCalDate != null)
{
cell7.CellValue = new CellValue(((DateTime)tool.LatestCalDate).ToOADate().ToString());
}
else
{
cell7.CellValue = new CellValue();
}
Cell cell8 = new Cell()
{
CellReference = "H" + lineIndex.ToString(), DataType = CellValues.SharedString, StyleIndex = styleString
};
id = sharedStringDic.AddToSharedString(tool.CalStatus).ToString();
cell8.CellValue = new CellValue(id);
Cell cell9 = new Cell()
{
CellReference = "I" + lineIndex.ToString(), DataType = CellValues.SharedString, StyleIndex = styleString
};
id = sharedStringDic.AddToSharedString(tool.Comment).ToString();
cell9.CellValue = new CellValue(id);
Cell cell10 = new Cell()
{
CellReference = "J" + lineIndex.ToString(), StyleIndex = styleDate
};
if (tool.CalDue != null)
{
cell10.CellValue = new CellValue(((DateTime)tool.CalDue).ToOADate().ToString());
}
else
{
cell10.CellValue = new CellValue();
}
Cell cell11 = new Cell()
{
CellReference = "K" + lineIndex.ToString(), DataType = CellValues.SharedString, StyleIndex = styleString
};
id = sharedStringDic.AddToSharedString(tool.CalPlace).ToString();
cell11.CellValue = new CellValue(id);
Cell cell12 = new Cell()
{
CellReference = "L" + lineIndex.ToString(), DataType = CellValues.Number, StyleIndex = styleString
};
cell12.CellValue = new CellValue(tool.CalInterval.ToString());
Cell cell13 = new Cell()
{
CellReference = "M" + lineIndex.ToString(), DataType = CellValues.SharedString, StyleIndex = styleString
};
id = sharedStringDic.AddToSharedString(tool.StoreLocation).ToString();
cell13.CellValue = new CellValue(id);
Cell cell14 = new Cell()
{
CellReference = "N" + lineIndex.ToString(), DataType = CellValues.SharedString, StyleIndex = styleString
};
id = sharedStringDic.AddToSharedString(tool.SystemStatus).ToString();
cell14.CellValue = new CellValue(id);
// todo tool.System_status is null, replace field to represent value
Cell cell15 = new Cell()
{
CellReference = "O" + lineIndex.ToString(), DataType = CellValues.SharedString, StyleIndex = styleString
};
id = sharedStringDic.AddToSharedString(tool.UserStatus).ToString();
cell15.CellValue = new CellValue(id);
Cell cell16 = new Cell()
{
CellReference = "P" + lineIndex.ToString(), DataType = CellValues.SharedString, StyleIndex = styleString
};
id = sharedStringDic.AddToSharedString(tool.Room).ToString();
cell16.CellValue = new CellValue(id);
Cell cell17 = new Cell()
{
CellReference = "Q" + lineIndex.ToString(), DataType = CellValues.SharedString, StyleIndex = styleString
};
id = sharedStringDic.AddToSharedString(tool.SuperordEquip).ToString();
cell17.CellValue = new CellValue(id);
Cell cell18 = new Cell()
{
CellReference = "R" + lineIndex.ToString(), DataType = CellValues.SharedString, StyleIndex = styleString
};
id = sharedStringDic.AddToSharedString(tool.SortField).ToString();
cell18.CellValue = new CellValue(id);
Cell cell19 = new Cell()
{
CellReference = "S" + lineIndex.ToString(), DataType = CellValues.SharedString, StyleIndex = styleString
};
id = sharedStringDic.AddToSharedString(tool.Machine).ToString();
cell19.CellValue = new CellValue(id);
Cell cell20 = new Cell()
{
CellReference = "T" + lineIndex.ToString(), DataType = CellValues.SharedString, StyleIndex = styleString
};
id = sharedStringDic.AddToSharedString(tool.ToolkitMachine).ToString();
cell20.CellValue = new CellValue(id);
newRow.Append(cell1);
newRow.Append(cell2);
newRow.Append(cell3);
newRow.Append(cell4);
newRow.Append(cell5);
newRow.Append(cell6);
newRow.Append(cell7);
newRow.Append(cell8);
newRow.Append(cell9);
newRow.Append(cell10);
newRow.Append(cell11);
newRow.Append(cell12);
newRow.Append(cell13);
newRow.Append(cell14);
newRow.Append(cell15);
newRow.Append(cell16);
newRow.Append(cell17);
newRow.Append(cell18);
newRow.Append(cell19);
newRow.Append(cell20);
sheetData.AppendChild <Row>(newRow);
++lineIndex;
}
Int32 count = 0;
foreach (string key in sharedStringDic.Keys)
{
if (count >= sharedStringId)
{
sharedStringTable.AppendChild(new SharedStringItem(new DocumentFormat.OpenXml.Spreadsheet.Text(key)));
}
++count;
}
sharedStringTable.Save();
ws.Save();
wbPart.Workbook.Save();
document.Close();
fileinfo = new FileInfo();
fileinfo.FileName = ""; // file name is added at client (Silverlight)
fileinfo.Length = ms.Length;
fileinfo.byteArray = new byte[fileinfo.Length + 10];
Array.Copy(ms.GetBuffer(), fileinfo.byteArray, fileinfo.Length);
//Array.Resize(ref fileinfo.FileByteStream, (int)ms.Length) ;
//fileinfo.Length = ms.Length;
return(fileinfo);
}
static string XLGetValue(string fileName, string sheetName)
{
string value = null;
var error = string.Empty;
try
{
using (SpreadsheetDocument document = SpreadsheetDocument.Open(fileName, false))
{
WorkbookPart wbPart = document.WorkbookPart;
Sheet theSheet = wbPart.Workbook.Descendants <Sheet>().
FirstOrDefault(s => s.Name == sheetName);
if (theSheet == null)
{
throw new ArgumentException("sheetName");
}
WorksheetPart wsPart = (WorksheetPart)(wbPart.GetPartById(theSheet.Id));
Worksheet workSheet = wsPart.Worksheet;
SheetData sheetData = workSheet.GetFirstChild <SheetData>();
IEnumerable <Row> rows = sheetData.Descendants <Row>();
var rowss = sheetData.Elements <Row>();
//skip first heading row and check for non-empty rows
foreach (var row in rows.Skip(1).Where(d => d.InnerText != ""))
{
var theRow = rows.FirstOrDefault(r => r.RowIndex.Value == row.RowIndex.Value);
//get only 2 (A and B) columns, if require others then take accordingly
foreach (Cell cell in theRow.Take(2))
{
if (cell.DataType != null)
{
switch (cell.DataType.Value)
{
case CellValues.SharedString:
var stringTable = wbPart.SharedStringTablePart;
if (stringTable != null)
{
var textItem = stringTable.SharedStringTable.
ElementAtOrDefault(int.Parse(cell.InnerText));
if (textItem != null)
{
if (cell.CellReference.ToString().Contains("A"))
{
value += "Cell Value = " + textItem.InnerText + " of cell = " + cell.CellReference.ToString();
}
}
}
break;
case CellValues.Boolean:
switch (value)
{
case "0":
value = "FALSE";
break;
default:
value = "TRUE";
break;
}
break;
}
}
}
}
}
}
catch (Exception ex)
{
error += "Error occured<br/>";
}
return(value);
}
public async Task <FileInfo> GetExcelFile()
{
Dictionary <string, Int32> sharedStringDic = new Dictionary <string, int>();
const string excelTemplate = "CalInProcessTemplateVer4.xlsx";
const string sheetname = "Sheet1";
uint styleString = 0;
uint styleDate = 0;
string providername = Startup.AppSettings["StorageProvider"];
FileInfo fileinfo;
Int32 sharedStringId = 0;
UInt32 lineIndex;
SysIO.MemoryStream ms = new SysIO.MemoryStream();;
if (providername == "localfile")
{
// Response.WriteFile(AppResources.GetCalCertPath(pdffilename)); TemplateFolder
string folder = Startup.AppSettings["PdfFoldername"];
string filepath = SysIO.Path.Combine(folder, excelTemplate);
//fileinfo.FileByteStream = SysIO.File.Open(filepath, SysIO.FileMode.Open);
SysIO.FileStream fs;
fs = SysIO.File.Open(filepath, System.IO.FileMode.Open);
fs.CopyTo(ms);
long bytesInStream = ms.Length;
fs.Close();
}
else if (providername == "AzureBlob")
{
string connectionstring = Startup.AppSettings["AzureBlob"];
CloudStorageAccount storageAccount = CloudStorageAccount.Parse(connectionstring);
CloudBlobClient blobClient = storageAccount.CreateCloudBlobClient();
CloudBlobContainer container = blobClient.GetContainerReference("templates");
CloudBlockBlob blockBlob = container.GetBlockBlobReference(excelTemplate);
//SysIO.Stream templateStream = blockBlob.OpenRead();
//fileinfo.FileByteStream = blockBlob.OpenRead();
SysIO.Stream frs = await blockBlob.OpenReadAsync();
frs.CopyTo(ms);
long bytesInStream = ms.Length;
bool canread = ms.CanRead;
bool canwrite = ms.CanWrite;
frs.Close();
}
SpreadsheetDocument document = SpreadsheetDocument.Open(ms, true);
WorkbookPart wbPart = document.WorkbookPart;
Sheet theSheet = wbPart.Workbook.Descendants <Sheet>().Where(s => s.Name == sheetname).FirstOrDefault();
if (theSheet == null)
{
throw new ArgumentException(string.Format("sheetName{0} not found", sheetname));
}
WorksheetPart wsPart = (WorksheetPart)(wbPart.GetPartById(theSheet.Id));
Worksheet ws = wsPart.Worksheet;
Columns columns = ws.Descendants <Columns>().FirstOrDefault();
SheetData sheetData = ws.Descendants <SheetData>().FirstOrDefault();
Row firstRow = sheetData.Descendants <Row>().ElementAt(0); // get first row , line 1
firstRow.DyDescent = 0.3D;
Row secondRow = sheetData.Descendants <Row>().ElementAt(1); // get second row , line 2
foreach (Cell cel2nd in secondRow)
{
if (cel2nd != null)
{
var cellAdd = cel2nd.CellReference;
if (cellAdd == "A2")
{
styleString = cel2nd.StyleIndex.Value;
}
if (cellAdd == "H2")
{
styleDate = cel2nd.StyleIndex.Value;
}
}
}
secondRow.Remove();
SharedStringTablePart sharedStringPart = wbPart.GetPartsOfType <SharedStringTablePart>().First();
SharedStringTable sharedStringTable = sharedStringPart.SharedStringTable;
foreach (SharedStringItem item in sharedStringTable.Elements <SharedStringItem>()) // read shared string and add to Dictionary
{
if (item.InnerText != null)
{
sharedStringDic.Add(item.InnerText, sharedStringId);
++sharedStringId;
}
}
lineIndex = 2;
string id;
foreach (var entry in filteredData)
{
Row newRow = new Row()
{
RowIndex = lineIndex, Spans = new ListValue <StringValue>()
{
InnerText = "1:22"
}, Height = 16.5D, CustomHeight = true, DyDescent = 0.3D
};
Cell cell1 = new Cell()
{
CellReference = "A" + lineIndex.ToString(), DataType = CellValues.SharedString, StyleIndex = styleString
};
id = sharedStringDic.AddToSharedString(entry.Plant).ToString();
cell1.CellValue = new CellValue(id);
Cell cell2 = new Cell()
{
CellReference = "B" + lineIndex.ToString(), DataType = CellValues.SharedString, StyleIndex = styleString
};
id = sharedStringDic.AddToSharedString(entry.Location).ToString();
cell2.CellValue = new CellValue(id);
Cell cell3 = new Cell()
{
CellReference = "C" + lineIndex.ToString(), DataType = CellValues.SharedString, StyleIndex = styleString
};
id = sharedStringDic.AddToSharedString(entry.SerialNumber).ToString();
cell3.CellValue = new CellValue(id);
Cell cell4 = new Cell()
{
CellReference = "D" + lineIndex.ToString(), DataType = CellValues.SharedString, StyleIndex = styleString
};
id = sharedStringDic.AddToSharedString(entry.Material).ToString();
cell4.CellValue = new CellValue(id);
Cell cell5 = new Cell()
{
CellReference = "E" + lineIndex.ToString(), DataType = CellValues.SharedString, StyleIndex = styleString
};
id = sharedStringDic.AddToSharedString(entry.Description).ToString();
cell5.CellValue = new CellValue(id);
Cell cell6 = new Cell()
{
CellReference = "F" + lineIndex.ToString(), DataType = CellValues.SharedString, StyleIndex = styleString
};
id = sharedStringDic.AddToSharedString(entry.CalPlace).ToString();
cell6.CellValue = new CellValue(id);
Cell cell7 = new Cell()
{
CellReference = "G" + lineIndex.ToString(), DataType = CellValues.Number, StyleIndex = styleString
};
cell7.CellValue = new CellValue(entry.CalInterval.ToString());
Cell cell8 = new Cell()
{
CellReference = "H" + lineIndex.ToString(), StyleIndex = styleDate
};
cell8.CellValue = ConvertDateToCellValue(entry.RegisteredDate);
Cell cell9 = new Cell()
{
CellReference = "I" + lineIndex.ToString(), StyleIndex = styleDate
};
cell9.CellValue = ConvertDateToCellValue(entry.UserShipDate);
Cell cell10 = new Cell()
{
CellReference = "J" + lineIndex.ToString(), StyleIndex = styleDate
};
cell10.CellValue = ConvertDateToCellValue(entry.VenReceiveDate);
Cell cell11 = new Cell()
{
CellReference = "K" + lineIndex.ToString(), StyleIndex = styleDate
};
cell11.CellValue = ConvertDateToCellValue(entry.CalDate);
Cell cell12 = new Cell()
{
CellReference = "L" + lineIndex.ToString(), DataType = CellValues.SharedString, StyleIndex = styleString
};
string result = "";
if (entry.CalResult == true)
{
result = "GD";
}
if (entry.CalResult == false)
{
result = "NG";
}
id = sharedStringDic.AddToSharedString(result).ToString();
cell12.CellValue = new CellValue(id);
Cell cell13 = new Cell()
{
CellReference = "M" + lineIndex.ToString(), DataType = CellValues.SharedString, StyleIndex = styleString
};
id = sharedStringDic.AddToSharedString(entry.VenComment).ToString();
cell13.CellValue = new CellValue(id);
Cell cell14 = new Cell()
{
CellReference = "N" + lineIndex.ToString(), StyleIndex = styleDate
};
cell14.CellValue = ConvertDateToCellValue(entry.PlanedShipDate);
Cell cell15 = new Cell()
{
CellReference = "O" + lineIndex.ToString(), StyleIndex = styleDate
};
cell15.CellValue = ConvertDateToCellValue(entry.VenShipDate);
// todo tool.System_status is null, replace field to represent value
Cell cell16 = new Cell()
{
CellReference = "P" + lineIndex.ToString(), StyleIndex = styleDate
};
cell16.CellValue = ConvertDateToCellValue(entry.UserReceiveDate);
Cell cell17 = new Cell()
{
CellReference = "Q" + lineIndex.ToString(), StyleIndex = styleDate
};
cell17.CellValue = ConvertDateToCellValue(entry.CcReceiveDate);
Cell cell18 = new Cell()
{
CellReference = "R" + lineIndex.ToString(), StyleIndex = styleDate
};
cell18.CellValue = ConvertDateToCellValue(entry.CcUploadDate);
Cell cell19 = new Cell()
{
CellReference = "S" + lineIndex.ToString(), DataType = CellValues.Number, StyleIndex = styleString
};
cell19.CellValue = new CellValue(entry.StdTat.ToString());
Cell cell20 = new Cell()
{
CellReference = "T" + lineIndex.ToString(), DataType = CellValues.Number, StyleIndex = styleString
};
cell20.CellValue = new CellValue(entry.Tat.ToString());
Cell cell21 = new Cell()
{
CellReference = "U" + lineIndex.ToString(), DataType = CellValues.SharedString, StyleIndex = styleString
};
id = sharedStringDic.AddToSharedString(entry.TatStatus).ToString();
cell21.CellValue = new CellValue(id);
Cell cell22 = new Cell()
{
CellReference = "V" + lineIndex.ToString(), DataType = CellValues.SharedString, StyleIndex = styleString
};
string finished = "";
if (entry.Finished == true)
{
finished = "Done";
}
id = sharedStringDic.AddToSharedString(finished).ToString();
cell22.CellValue = new CellValue(id);
Cell cell23 = new Cell()
{
CellReference = "W" + lineIndex.ToString(), DataType = CellValues.SharedString, StyleIndex = styleString
};
String yearmonth;
if (entry.UserShipDate == null)
{
yearmonth = "";
}
else
{
yearmonth = String.Format("{0:yyyy-MM}", (DateTime)entry.UserShipDate);
}
id = sharedStringDic.AddToSharedString(yearmonth).ToString();
cell23.CellValue = new CellValue(id);
Cell cell24 = new Cell()
{
CellReference = "X" + lineIndex.ToString(), DataType = CellValues.SharedString, StyleIndex = styleString
};
id = sharedStringDic.AddToSharedString(entry.PMaker).ToString();
cell24.CellValue = new CellValue(id);
Cell cell25 = new Cell()
{
CellReference = "Y" + lineIndex.ToString(), DataType = CellValues.SharedString, StyleIndex = styleString
};
id = sharedStringDic.AddToSharedString(entry.PModel).ToString();
cell25.CellValue = new CellValue(id);
Cell cell26 = new Cell()
{
CellReference = "Z" + lineIndex.ToString(), DataType = CellValues.SharedString, StyleIndex = styleString
};
id = sharedStringDic.AddToSharedString(entry.PName).ToString();
cell26.CellValue = new CellValue(id);
Cell cell27 = new Cell()
{
CellReference = "AA" + lineIndex.ToString(), DataType = CellValues.SharedString, StyleIndex = styleString
};
id = sharedStringDic.AddToSharedString(entry.PSN).ToString();
cell27.CellValue = new CellValue(id);
newRow.Append(cell1);
newRow.Append(cell2);
newRow.Append(cell3);
newRow.Append(cell4);
newRow.Append(cell5);
newRow.Append(cell6);
newRow.Append(cell7);
newRow.Append(cell8);
newRow.Append(cell9);
newRow.Append(cell10);
newRow.Append(cell11);
newRow.Append(cell12);
newRow.Append(cell13);
newRow.Append(cell14);
newRow.Append(cell15);
newRow.Append(cell16);
newRow.Append(cell17);
newRow.Append(cell18);
newRow.Append(cell19);
newRow.Append(cell20);
newRow.Append(cell21);
newRow.Append(cell22);
newRow.Append(cell23);
newRow.Append(cell24);
newRow.Append(cell25);
newRow.Append(cell26);
newRow.Append(cell27);
sheetData.AppendChild <Row>(newRow);
++lineIndex;
}
Int32 count = 0;
foreach (string key in sharedStringDic.Keys)
{
if (count >= sharedStringId)
{
sharedStringTable.AppendChild(new SharedStringItem(new DocumentFormat.OpenXml.Spreadsheet.Text(key)));
}
++count;
}
sharedStringTable.Save();
ws.Save();
wbPart.Workbook.Save();
document.Close();
fileinfo = new FileInfo();
fileinfo.FileName = ""; // file name is added at client (Silverlight)
fileinfo.Length = ms.Length;
fileinfo.byteArray = new byte[fileinfo.Length + 10];
Array.Copy(ms.GetBuffer(), fileinfo.byteArray, fileinfo.Length);
//Array.Resize(ref fileinfo.FileByteStream, (int)ms.Length) ;
//fileinfo.Length = ms.Length;
return(fileinfo);
}
