private static Cell GetCell(Worksheet worksheet, string columnName, uint rowIndex)
{
SheetData sheetData = worksheet.GetFirstChild <SheetData>();
return(sheetData?.Elements <Row>().Where(r => r.RowIndex == rowIndex).FirstOrDefault()
?.Elements <Cell>().Where(c => c.CellReference.Value == columnName + rowIndex).FirstOrDefault());
}
public static Row GetRow(SheetData sheetData, int rowIndex)
{
try
{
return sheetData.Elements<Row>().Where(r => r.RowIndex == rowIndex).First();
}
catch
{
return null;
}
}
private static Dictionary<int, string> GetHeaders(SheetData sheetData, WorkbookPart workbookPart)
{
var headers = new Dictionary<int, string>();
var headerCells = sheetData.Elements<Row>().First().Elements<Cell>().ToList();
for (var i = 0; i < headerCells.Count; i++)
{
var cellValue = ExcelFileInceptor.GetCellValueAsString(workbookPart, headerCells[i]);
headers.Add(i, cellValue);
}
return headers;
}
/// <summary>
/// 移除冗余的行
/// </summary>
/// <param name="sheetData"><see cref="SheetData"/>对象</param>
private void RemoveRedundancyRows(SheetData sheetData)
{
uint ri = TemplateRowIndexContent;
if (UseTemplate && ri > 0)
{
Row[] rows = sheetData.Elements <Row>().ToArray();
int count = rows.Length;
int i = (int)ri;
for (; i < count; i++)
{
rows[i].Remove();
}
}
}
public Row GetRow(string sheetName, uint RowNo)
{
Row res = null;
if (!string.IsNullOrEmpty(sheetName))
{
SheetData sheetdata = GetSheetData(sheetName);
var rows = sheetdata.Elements <Row>().Where(x => x.RowIndex == RowNo);
if (rows.Count() > 0)
{
res = rows.First();
}
}
return(res);
}
// Return the row at the specified rowIndex located within
// the sheet data passed in via wsData. If the row does not
// exist, create it.
private Row GetRow(SheetData wsData, UInt32 rowIndex)
{
var row = wsData.Elements <Row>().
Where(r => r.RowIndex.Value == rowIndex).FirstOrDefault();
if (row == null)
{
row = new Row
{
RowIndex = rowIndex
};
wsData.Append(row);
}
return(row);
}
private static string GetValueCell(this SheetData sheetData, WorkbookPart workBookPart, int rowIndex, int colIndex)
{
Row row = sheetData.Elements <Row>().Where(r => r.RowIndex == rowIndex).First();
string rCell = colIndex.GetColumnName() + rowIndex;
Cell cell = row.Elements <Cell>().Where(c => string.Compare(c.CellReference.Value, rCell, true) == 0).First();
if (cell.DataType != null && cell.DataType.Value.Equals(CellValues.SharedString))
{
SharedStringItem item = GetSharedStringItemById(workBookPart, Convert.ToInt32(cell.CellValue.Text));
return(item.InnerText);
}
return(cell.CellValue == null ? null : cell.CellValue.Text);
}
public Cell GetCellByContent(string sheet, string content, int rowindex)
{
WorksheetPart currentSheet = excelData.GetWorksheetPartByName(sheet);
SheetData sd = currentSheet.Worksheet.Elements <SheetData>().FirstOrDefault();
Row row = sd.Elements <Row>().ElementAt(rowindex);
Cell result = row.Elements <Cell>().Where(
c => content.Equals(GetCellText(c))
).FirstOrDefault();
if (result == null)
{
throw new CellNotFoundException("Impossibile trovare la cella " + content);
}
return(result);
}
private Row GetRow(UInt32 index) // zero-based
{
Row row = null;
SheetData sheetData = worksheet.GetFirstChild <SheetData>();
foreach (Row r in sheetData.Elements <Row>())
{
if (r.RowIndex == (index + 1))
{
row = r;
break;
}
}
return(row);
}
/// <summary>
/// This function reads Excel file and returns dictionary
/// </summary>
/// <param name="filePath">Full path to Excel file</param>
/// <param name="sheetName">Sheet name containing configurations</param>
/// <returns>Dictionary of configurations</returns>
public static Dictionary <string, string> ReadFromExcel(string filePath, string sheetName)
{
if (!(string.IsNullOrWhiteSpace(filePath) || string.IsNullOrWhiteSpace(sheetName)))
{
Dictionary <string, string> myList = new Dictionary <string, string>();
try
{
using (SpreadsheetDocument myDoc = SpreadsheetDocument.Open(filePath, false))
{
WorkbookPart workbookPart = myDoc.WorkbookPart;
IEnumerable <Sheet> sheets = myDoc.WorkbookPart.Workbook.GetFirstChild <Sheets>().Elements <Sheet>().Where(s => s.Name == sheetName);
if (sheets.Count() == 0)
{
throw new ArgumentException("Message: Provided Sheet name is incorrect");
}
string relationshipId = sheets.First().Id.Value;
WorksheetPart worksheetPart = (WorksheetPart)myDoc.WorkbookPart.GetPartById(relationshipId);
SheetData sheetData = worksheetPart.Worksheet.GetFirstChild <SheetData>();
foreach (Row row in sheetData.Elements <Row>())
{
List <string> rowValue = new List <string>();
rowValue = ReadRow(row, workbookPart).ToList();
if (null != rowValue && 0 < rowValue.Count()) // Check count to avoid adding of blank row entry which having meta data
{
myList.Add(rowValue[0].Trim(), rowValue[1].Trim());
}
}
}
}
catch (FileNotFoundException exception)
{
ErrorLogger.LogErrorToTextFile(errorFilePath, "Invalid file path, file not found" + exception.Message);
}
catch (Exception exception)
{
if (exception.Message.IndexOf("Invalid Hyperlink", 0, StringComparison.OrdinalIgnoreCase) != -1)
{
ErrorLogger.DisplayErrorMessage("Unable to read Excel. Please remove all hyperlinks from configuration Excel");
}
ErrorLogger.LogErrorToTextFile(errorFilePath, "Exception Details: " + exception.Message);
}
return(myList);
}
else
{
throw new ArgumentException("Message: FilePath or Sheet name missing");
}
}
/// <summary>
/// Gets the row.
/// </summary>
/// <param name="sheetData">The sheet data.</param>
/// <param name="rowIndex">Index of the row.</param>
/// <param name="create">if set to <c>true</c> [create].</param>
/// <returns></returns>
public static Row GetRow(this SheetData sheetData, int rowIndex, bool create = false)
{
var row = sheetData.Elements <Row>().FirstOrDefault(r => r.RowIndex == rowIndex);
if (row == null)
{
if (!create)
{
return(null);
}
row = new Row();
sheetData.AppendChild(row);
}
return(row);
}
/// <summary>
/// Given a worksheet's data it returns the largest column index for any rows.
/// </summary>
/// <param name="sheetData">Worksheet's data</param>
/// <returns>The largest column index found</returns>
private static int GetLargestColumnIndex(SheetData sheetData)
{
var result = 1;
foreach (var row in sheetData.Elements <Row>())
{
var lastCell = row.LastChild as Cell;
var current = GetColumnIndex(lastCell.CellReference);
if (current > result)
{
result = current;
}
}
return(result);
}
/// <summary>
/// Gets the row.
/// </summary>
/// <param name="sheetData">The sheet data.</param>
/// <param name="cellReference">The cell reference.</param>
/// <param name="create">if set to <c>true</c> [create].</param>
/// <returns></returns>
public static Row GetRow(this SheetData sheetData, string cellReference, bool create = false)
{
var row = sheetData.Elements <Row>().FirstOrDefault(r => cellReference.EndsWith(r.RowIndex?.ToString()));
if (row == null)
{
if (!create)
{
return(null);
}
row = new Row();
sheetData.AppendChild(row);
}
return(row);
}
// Given a worksheet and a row index, return the row.
// See: http://msdn.microsoft.com/en-us/library/bb508943(v=office.12).aspx#Y2142
private static Row getRow(SheetData worksheet, uint rowIndex, bool autoCreate)
{
if (rowIndex < 1)
{
throw new Exception("The row # can't be less then 1.");
}
Row foundRow = worksheet.Elements <Row>().Where(r => r.RowIndex == rowIndex).FirstOrDefault();
if (foundRow == null && autoCreate)
{
foundRow = new Row();
foundRow.RowIndex = rowIndex;
worksheet.AppendChild(foundRow);
}
return(foundRow);
}
public static List <ItemArquivo> Ler(string arquivoXlsx)
{
var arquivoByte = Convert.FromBase64String(arquivoXlsx);
MemoryStream documento = new MemoryStream(arquivoByte);
List <ItemArquivo> itensArquivo = new List <ItemArquivo>();
using (SpreadsheetDocument spreadsheetDocument = SpreadsheetDocument.Open(documento, false))
{
WorkbookPart workbookPart = spreadsheetDocument.WorkbookPart;
WorksheetPart worksheetPart = workbookPart.WorksheetParts.First();
SheetData sheetData = worksheetPart.Worksheet.Elements <SheetData>().First();
var stringTable = workbookPart.GetPartsOfType <SharedStringTablePart>().FirstOrDefault();
foreach (Row r in sheetData.Elements <Row>())
{
if (Convert.ToInt32(r.RowIndex.InnerText) == 1)
{
continue;
}
var listaElementos = r.Elements <Cell>().ToList();
var dataEntrega = DateTime.FromOADate(Convert.ToDouble(listaElementos[0].CellValue.InnerText));
var nomeProduto = listaElementos[1].CellValue.InnerText;
if (stringTable != null)
{
nomeProduto =
stringTable.SharedStringTable
.ElementAt(int.Parse(nomeProduto)).InnerText;
}
var quntidade = Convert.ToInt32(listaElementos[2].CellValue.InnerText);
var valorUnitario = Convert.ToDecimal(listaElementos[3].CellValue.InnerText);
itensArquivo.Add(new ItemArquivo
{
DataEntrega = dataEntrega,
NomeProduto = nomeProduto,
Quantidade = quntidade,
ValorUnitario = valorUnitario,
LinhaImpportada = Convert.ToInt32(r.RowIndex.InnerText)
});
}
}
return(itensArquivo);
}
public static Row GetRow(SheetData sheetData, uint line, bool createIfDoesntExists = false)
{
var row = sheetData?.Elements <Row>().FirstOrDefault(r => r.RowIndex == line);
// Se existir a linha retorna.
if (row != null)
{
row.Spans = null;
return(row);
}
else if (!createIfDoesntExists)
{
return(null);
}
// Senao cria uma nova linha.
row = new Row {
RowIndex = line
};
var rows = sheetData.Elements <Row>().ToList();
// Caso nao exista linhas pode simplesmente inserir
if (!rows.Any())
{
sheetData.AppendChild(row);
return(row);
}
// Insere a linha na order correta.
var before = rows.Where(w => w.RowIndex < line).OrderBy(o => o.RowIndex.Value).LastOrDefault();
if (before != null) // Existem linhas anteriores a que sera inserida.
{
sheetData.InsertAfter(row, before);
}
else // Nao existem nenhuma linha anterior a que sera inserida.
{
var after = rows.Where(w => w.RowIndex > line).OrderBy(o => o.RowIndex.Value).FirstOrDefault();
// Insere antes do primeiro.
sheetData.InsertBefore(row, after);
}
return(row);
}
private void MoveCurrentRow(int rowIndex)
{
if (currentRow == null)
{
var rows = currentSheetData.Elements <Row>();
if (rows.GetEnumerator().MoveNext())
{
//maybe the last row is still before the curretRow, so in this case no need to iterate trough the rows
Row lastRow = (Row)currentSheetData.LastChild;
if (lastRow.RowIndex > rowIndex)
{
//we search from the beginnig
foreach (Row item in rows)
{
if (item.RowIndex.Value > rowIndex)
{
currentRow = item;
break;
}
}
}
}
}
else
{
//trying to seach from the last position backward or forward
if (currentRow.RowIndex > rowIndex)
{
//going backward
Row previousRow;
while ((previousRow = currentRow.PreviousSibling <Row>()) != null &&
previousRow.RowIndex > rowIndex)
{
currentRow = previousRow;
}
}
else
{
//going forward
while (currentRow != null && currentRow.RowIndex <= rowIndex)
{
currentRow = currentRow.NextSibling <Row>();
}
}
}
}
private static void ParseViaDOM(string physicalPath)
{
using (SpreadsheetDocument spreadsheetDocument = SpreadsheetDocument.Open(physicalPath, false))
{
WorkbookPart workbookPart = spreadsheetDocument.WorkbookPart;
WorksheetPart worksheetPart = workbookPart.WorksheetParts.First();
string text = "";
foreach (WorksheetPart wsp in workbookPart.WorksheetParts)
{
SheetData sheetData = wsp.Worksheet.Elements <SheetData>().First();
foreach (Row r in sheetData.Elements <Row>())
{
foreach (Cell c in r.Elements <Cell>())
{
if (c != null && c.DataType != null && c.DataType == CellValues.SharedString)
{
int id = -1;
Int32.TryParse(c.InnerText, out id);
var item = GetSharedStringItemById(workbookPart, id);
if (item.Text != null)
{
text = item.Text.Text;
}
else if (item.InnerText != null)
{
text = item.InnerText;
}
else if (item.InnerXml != null)
{
text = item.InnerXml;
}
System.Diagnostics.Debug.WriteLine(text + "");
}
else
{
text = c.CellValue.Text;
Console.Write(text + " ");
System.Diagnostics.Debug.WriteLine(text + "");
}
}
}
}
}
}
public List <Cell> FindCellsWithText(WorkbookPart wb, SheetData sheetData, string textToFind)
{
var arr = new List <Cell>();
string values;
foreach (Row r in sheetData.Elements <Row>())
{
foreach (Cell c in r.Elements <Cell>())
{
if (GetCellValue(wb, c).Contains(textToFind))
{
arr.Add(c);
}
}
}
return(arr);
}
public MemoryStream ExportXls <T>(IEnumerable <T> listObjs)
{
if (listObjs != null && listObjs.Any())
{
var stream = OpenTemplate();
var templateSheet = GetTemplateSheet(stream);
stream.Seek(0, SeekOrigin.Begin);
using (SpreadsheetDocument workbook = SpreadsheetDocument.Open(stream, true))
{
var sheet = workbook.WorkbookPart.WorksheetParts.First().Worksheet;
#region Fill Range cells
var defRange = templateSheet.DefinedNames.FirstOrDefault(n => n.IsRange);
var startRow = Convert.ToUInt32(defRange.StartRow);
SheetData sheetData = sheet.GetFirstChild <SheetData>();
sheetData.Elements <Row>().FirstOrDefault(r => r.RowIndex == startRow).Remove();
//Row lastRow = sheetData.Elements<Row>().LastOrDefault();
foreach (var item in listObjs)
{
//startRow++;
Row newRow = new Row();
foreach (var col in defRange.Columns)
{
PropertyInfo propInfo = item.GetType().GetProperty(col.Parameter.Field);
Cell cell = new Cell();
cell.DataType = CellValues.String;
var value = propInfo.GetValue(item, null);
cell.CellValue = new CellValue((value != null) ? value.ToString() : string.Empty); //
newRow.AppendChild(cell);
}
sheetData.AppendChild(newRow);
}
#endregion
workbook.WorkbookPart.Workbook.Save();
}
stream.Seek(0, SeekOrigin.Begin);
return(stream);
}
else
{
return(null);
}
}
public static void OpenSpreadsheetDocument(string filepath)
{
// Open a SpreadsheetDocument based on a filepath.
using (SpreadsheetDocument spreadsheetDocument = SpreadsheetDocument.Open(filepath, false))
{
WorkbookPart workbookPart = spreadsheetDocument.WorkbookPart;
WorksheetPart worksheetPart = workbookPart.WorksheetParts.First();
SheetData sheetData = worksheetPart.Worksheet.Elements <SheetData>().First();
string text;
foreach (Row r in sheetData.Elements <Row>())
{
foreach (Cell c in r.Elements <Cell>())
{
text = c.CellValue.Text;
}
}
}
}
private IEnumerable <IGrouping <Row, Cell> > FindCellsByRange(CellRangeRef range)
{
Sheet sheet = FindSheetByName(range.SheetName);
WorksheetPart workSheetPart = (WorksheetPart)document.WorkbookPart.GetPartById(sheet.Id);
SheetData sheetData = workSheetPart.Worksheet.GetFirstChild <SheetData>();
var result = from row in sheetData.Elements <Row>()
where range.Start.Row <= row.RowIndex && row.RowIndex <= range.End.Row
let cells = row.Elements <Cell>()
from cell in cells
where range.Contains(new CellRef(cell.CellReference))
group cell by row into cellsInRange
select cellsInRange;
return(result);
}
private void LaunchOpenSDKExcel(string fullPath, string name)
{
using (SpreadsheetDocument spreadsheetDocument = SpreadsheetDocument.Open(fullPath, true))
{
WorkbookPart workbookPart = spreadsheetDocument.WorkbookPart;
//WorksheetPart worksheetPart = workbookPart.Workbook.First();
WorksheetPart worksheetPart = GetWorksheetPart(workbookPart, name);
SheetData sheetData = worksheetPart.Worksheet.Elements <SheetData>().First();
string text;
foreach (Cell c in sheetData.Elements <Row>().SelectMany(r => r.Elements <Cell>()))
{
text = c.CellValue.Text;
Console.Write(text + " ");
}
Console.WriteLine();
Console.ReadKey();
}
}
public bool ReadRow()
{
if (_rows == null)
{
_rows = _sheet_data.Elements <Row>().ToList();
_current_row = -1;
}
_current_row++;
if (_current_row >= _rows.Count)
{
return(false);
}
_cells = _rows[_current_row].Elements <Cell>().ToList();
return(true);
}
/// <summary>
/// Function is used to return all the values that are read from Sheet
/// </summary>
/// <param name="filePath">File path of the Excel</param>
/// <param name="sheetName">Sheet to be read from Excel</param>
/// <returns>Two-dimensional list with all values from Sheet</returns>
public static Collection <Collection <string> > ReadSheet(string filePath, string sheetName)
{
Collection <Collection <string> > myList = new Collection <Collection <string> >();
Collection <string> rowList = new Collection <string>();
try
{
using (SpreadsheetDocument myDoc = SpreadsheetDocument.Open(filePath, false))
{
WorkbookPart workbookPart = myDoc.WorkbookPart;
IEnumerable <Sheet> sheets = myDoc.WorkbookPart.Workbook.GetFirstChild <Sheets>().Elements <Sheet>().Where(s => s.Name == sheetName);
if (sheets.Count() == 0)
{
ErrorLogger.DisplayErrorMessage("Provided sheet name is invalid!");
return(myList);
}
string relationshipId = sheets.First().Id.Value;
WorksheetPart worksheetPart = (WorksheetPart)myDoc.WorkbookPart.GetPartById(relationshipId);
SheetData sheetData = worksheetPart.Worksheet.GetFirstChild <SheetData>();
foreach (Row row in sheetData.Elements <Row>())
{
rowList = ReadRow(row, workbookPart);
if (null != rowList && 0 < rowList.Count()) // Check count to avoid adding of blank row entry which having meta data
{
myList.Add(rowList);
}
}
}
}
catch (FileNotFoundException exception)
{
ErrorLogger.LogErrorToTextFile(errorFilePath, "Invalid file path, file not found" + exception.Message);
}
catch (Exception exception)
{
if (exception.Message.IndexOf("Invalid Hyperlink", 0, StringComparison.OrdinalIgnoreCase) != -1)
{
ErrorLogger.DisplayErrorMessage("Unable to read Excel. Please remove all hyperlinks from configuration Excel");
}
ErrorLogger.LogErrorToTextFile(errorFilePath, "Exception Details: " + exception.Message);
}
return(myList);
}
public static Row InsertRowInWorksheet(SheetData sheetData, int rowIndex)
{
if (sheetData is null)
{
throw new ArgumentNullException(nameof(sheetData));
}
Row row = sheetData.Elements <Row>().Where(r => r.RowIndex == rowIndex).FirstOrDefault();
if (row is null)
{
row = new Row()
{
RowIndex = (uint)rowIndex
};
sheetData.Append(row);
}
return(row);
}
public static void CreateBodyData(SheetData sheetdata, int rowIndex, List <string> values)
{
List <UInt32> bodyStyles = GetBodyStyles(sheetdata);
Row row = sheetdata.Elements <Row>().ElementAtOrDefault(rowIndex);
bool exist = row != null;
if (!exist)
{
row = new Row();
}
FillRow(row, values, bodyStyles);
if (!exist)
{
sheetdata.AppendChild(row);
}
}
/// <summary>
/// Gets the row specified at the row index if it exists
/// </summary>
public static Row GetRow(SheetData sheetData, uint rowIndex)
{
Row row = null;
uint index = rowIndex;
//Make sure the row exists
var match = sheetData.Elements <Row>().Where(r => r.RowIndex.Value == index);
if (match.Count() != 0)
{
row = match.First();
}
else
{
return(null);
}
return(row);
}
/// <summary>
/// Gets last rowindex.
/// </summary>
/// <returns></returns>
private uint GetLastRowIndex(SpreadsheetDocument spreadSheetDocument)
{
uint retval = 0;
WorkbookPart wbPart = spreadSheetDocument.WorkbookPart;
Sheet sheet = wbPart.Workbook.Descendants <Sheet>().Where(s => s.Name.Equals(_sheetName)).FirstOrDefault();
WorksheetPart worksheetPart = (WorksheetPart)(wbPart.GetPartById(sheet.Id));
SheetData sd = worksheetPart.Worksheet.Elements <SheetData>().FirstOrDefault();
if (sd == null)
{
return(retval);
}
// we are responsible for supplying our own, correct rowindex
// so we need to find out what the last one is
Row lastRow = sd.Elements <Row>().OrderBy(o => o.RowIndex).LastOrDefault();
retval = (lastRow == null) ? 0 : lastRow.RowIndex;
return(retval);
}
private bool editarArchivo(string archivo)
{
try
{
using (SpreadsheetDocument spreadsheetDocument = SpreadsheetDocument.Open(archivo, true))
{
IEnumerable <Sheet> sheets = spreadsheetDocument.WorkbookPart.Workbook.GetFirstChild <Sheets>().Elements <Sheet>();
Sheet sheets1 = sheets.First();
string relationshipId = sheets.First().Id.Value;
WorksheetPart worksheetPart = (WorksheetPart)spreadsheetDocument.WorkbookPart.GetPartById(relationshipId);
if (worksheetPart != null)
{
Worksheet worksheet = worksheetPart.Worksheet;
SheetData sheetData = worksheet.GetFirstChild <SheetData>();
string cellReference = "E14";
Row row = sheetData.Elements <Row>().Where(r => r.RowIndex == 14).First();
Cell cell = new Cell();
cell = row.Elements <Cell>().Where(c => c.CellReference.Value == cellReference).First();
//cell.CellReference = "E14";
cell.CellValue = new CellValue(dat.Nombres);
worksheetPart.Worksheet.Save();
}
spreadsheetDocument.Close();
}
return(true);
}
catch (Exception ex)
{
throw;
}
}
private static Dictionary <int, int> GetMaxCharacterWidth(SheetData sheetData, uint numberStyleIndex)
{
//iterate over all cells getting a max char value for each column
Dictionary <int, int> maxColWidth = new Dictionary <int, int>();
var rows = sheetData.Elements <Row>();
foreach (var r in rows)
{
var cells = r.Elements <Cell>().ToArray();
//using cell index as my column
for (int i = 0; i < cells.Length; i++)
{
var cell = cells[i];
var cellValue = cell.CellValue == null ? string.Empty : cell.CellValue.InnerText;
var cellTextLength = cellValue.Length;
if (cell.StyleIndex != null && cell.StyleIndex == numberStyleIndex)
{
int thousandCount = (int)Math.Truncate((double)cellTextLength / 4);
//add 3 for '.00'
cellTextLength += (3 + thousandCount);
}
if (maxColWidth.ContainsKey(i))
{
var current = maxColWidth[i];
if (cellTextLength > current)
{
maxColWidth[i] = cellTextLength;
}
}
else
{
maxColWidth.Add(i, cellTextLength);
}
}
}
return(maxColWidth);
}
private static DataTable ReadSpreadsheetTable(Table table, SheetData sheetData, WorkbookPart workbookPart, Lazy <IReadOnlyDictionary <uint, string> > lazyNumberingFormatCodeById)
{
var dataTable = new DataTable(table.DisplayName);
foreach (var tableColumn in table.TableColumns.OfType <TableColumn>())
{
dataTable.Columns.Add(tableColumn.Name.Value);
}
var tableReference = TableReference.Parse(table.Reference);
int startRowIndex = tableReference.StartCell.RowIndex + 1; // "+1": 열 헤더 제외
int endRowIndex = tableReference.EndCell.RowIndex;
int startColumnIndex = tableReference.StartCell.ColumnIndex;
int endColumnIndex = tableReference.EndCell.ColumnIndex;
foreach (var sheetRow in sheetData.Elements <Row>())
{
int rowIndex = int.Parse(sheetRow.RowIndex);
if (rowIndex < startRowIndex || rowIndex > endRowIndex)
{
continue;
}
var row = dataTable.NewRow();
foreach (var sheetCell in sheetRow.Elements <Cell>())
{
var cellReference = CellReference.Parse(sheetCell.CellReference);
if (cellReference.ColumnIndex < startColumnIndex || cellReference.ColumnIndex > endColumnIndex)
{
continue;
}
row[cellReference.ColumnIndex - 1] = GetCellValue(sheetCell, workbookPart, lazyNumberingFormatCodeById);
}
dataTable.Rows.Add(row);
}
return(dataTable);
}
private static Row GetRow(SheetData sheetData, int rowIndex)
{
return sheetData.Elements<Row>().Where(r => r.RowIndex == rowIndex).First();
}
private static Row GetRow(SheetData sheetData, uint excelRowIndex, bool autoAdd)
{
var row = sheetData.Elements<Row>().Where(r => r.RowIndex.Value == excelRowIndex).FirstOrDefault();
if (row == null && autoAdd)
{
row = new Row() { RowIndex = excelRowIndex };
sheetData.Append(row);
}
return row;
}
/// <summary>
/// Generate the report for catalogue categories
/// </summary>
/// <param name="templatePath">Path of the template</param>
/// <param name="itemSource">Item source</param>
/// <returns>MemoryStream</returns>
public static MemoryStream GenerateCatalogueValueReport(CatalogueValueSearchModel itemSource,string logoPath)
{
MemoryStream ms = new MemoryStream();
using (SpreadsheetDocument document = SpreadsheetDocument.Create(ms, SpreadsheetDocumentType.Workbook))
{
//create the new workbook
WorkbookPart workbookPart = document.AddWorkbookPart();
Workbook workbook = new Workbook();
workbookPart.Workbook = workbook;
// If we don't add a "WorkbookStylesPart", OLEDB will refuse to connect to this .xlsx file !
WorkbookStylesPart workbookStylesPart = workbookPart.AddNewPart<WorkbookStylesPart>("rIdStyles");
//get and save the stylesheet
Stylesheet stylesheet = VestalisStyleSheet();
workbookStylesPart.Stylesheet = stylesheet;
workbookStylesPart.Stylesheet.Save();
//add the new workseet
WorksheetPart worksheetPart = workbookPart.AddNewPart<WorksheetPart>();
Worksheet worksheet = new Worksheet();
SheetData sheetData1 = new SheetData();
Sheets sheets = new Sheets();
//get the number of columns in the report
Row rowTitle;
//get the string name of the columns
string[] excelColumnNamesTitle = new string[2];
for (int n = 0; n < 2; n++)
excelColumnNamesTitle[n] = GetExcelColumnName(n);
//build the title
for (int i = 1; i <= 6; i++)
{
rowTitle = new Row() { RowIndex = (UInt32Value)(uint)i };
for (int cellval = 0; cellval < 2; cellval++)
{
AppendTextCell(excelColumnNamesTitle[cellval] + i, string.Empty, rowTitle, 3);
}
sheetData1.Append(rowTitle);
}
MergeCells mergeCells = new MergeCells();
Row currentRowTitle = sheetData1.Elements<Row>().FirstOrDefault(row => row.RowIndex.Value == (uint)2);
//add the business application name
UpdateStringCellValue("B2", LanguageResource.CatalogueValuesReport, currentRowTitle, 5);
//merge all cells in the title
MergeCell mergeCell = new MergeCell();
mergeCell.Reference = "B2:B4";
mergeCells.Append(mergeCell);
Drawing drawing = AddLogo(logoPath, worksheetPart);
Columns columns = new Columns();
columns.Append(CreateColumnData((UInt32Value)(uint)1, (UInt32Value)(uint)1, 50));
columns.Append(CreateColumnData((UInt32Value)(uint)2, (UInt32Value)(uint)2, 71));
worksheet.Append(columns);
int rowIndex = 8;
Row rowData = new Row() { RowIndex = (UInt32Value)(uint)rowIndex };
AppendTextCell("A" + rowIndex, LanguageResource.BusinessApplicationName, rowData, 2);
AppendTextCell("B" + rowIndex, itemSource.BusinessApplicatioName, rowData, 1);
sheetData1.Append(rowData);
rowIndex = 9;
rowData = new Row() { RowIndex = (UInt32Value)(uint)rowIndex };
AppendTextCell("A" + rowIndex, LanguageResource.CatalogueName, rowData, 2);
AppendTextCell("B" + rowIndex, itemSource.CatalogueSelectedName, rowData, 1);
sheetData1.Append(rowData);
rowIndex = 11;
rowData = new Row() { RowIndex = (UInt32Value)(uint)rowIndex };
AppendTextCell("A" + rowIndex.ToString(), LanguageResource.Value, rowData, 2);
AppendTextCell("B" + rowIndex.ToString(), LanguageResource.Description, rowData, 2);
sheetData1.Append(rowData);
rowIndex = 12;
foreach (var item in itemSource.SearchResult.Collection)
{
rowData = new Row() { RowIndex = (UInt32Value)(uint)rowIndex };
AppendTextCell("A" + rowIndex.ToString(), item.CatalogueValueData, rowData, 1);
AppendTextCell("B" + rowIndex.ToString(), item.CatalogueValueDescription, rowData, 1);
sheetData1.Append(rowData);
rowIndex++;
}
//add the information of the current sheet
worksheet.Append(sheetData1);
//add merged cells
worksheet.InsertAfter(mergeCells, worksheet.Elements<SheetData>().First());
worksheet.Append(drawing);
worksheetPart.Worksheet = worksheet;
worksheetPart.Worksheet.Save();
//create the new sheet for this report
Sheet sheet = new Sheet() { Name = LanguageResource.Report, SheetId = (UInt32Value)1, Id = workbookPart.GetIdOfPart(worksheetPart) };
sheets.Append(sheet);
//add the new sheet to the report
workbook.Append(sheets);
//save all report
workbook.Save();
//close the stream.
document.Close();
}
return ms;
}
/// <summary>
/// Generate an excel file with the information of certificates
/// </summary>
/// <param name="dataSource">The list of certificates</param>
/// <returns>MemoryStream</returns>
public static MemoryStream GenerateCertificateReport(CertificateListModel model, string logoPath)
{
MemoryStream ms = new MemoryStream();
using (SpreadsheetDocument document = SpreadsheetDocument.Create(ms, SpreadsheetDocumentType.Workbook))
{
//create the new workbook
WorkbookPart workbookPart = document.AddWorkbookPart();
Workbook workbook = new Workbook();
workbookPart.Workbook = workbook;
// If we don't add a "WorkbookStylesPart", OLEDB will refuse to connect to this .xlsx file !
WorkbookStylesPart workbookStylesPart = workbookPart.AddNewPart<WorkbookStylesPart>("rIdStyles");
//get and save the stylesheet
Stylesheet stylesheet = VocStyleSheet();
workbookStylesPart.Stylesheet = stylesheet;
workbookStylesPart.Stylesheet.Save();
//add the new workseet
WorksheetPart worksheetPart = workbookPart.AddNewPart<WorksheetPart>();
Worksheet worksheet = new Worksheet();
SheetData sheetData1 = new SheetData();
Sheets sheets = new Sheets();
//get the number of columns in the report
Row rowTitle;
//get the string name of the columns
string[] excelColumnNamesTitle = new string[4];
for (int n = 0; n < 4; n++)
excelColumnNamesTitle[n] = GetExcelColumnName(n);
//build the title
for (int i = 1; i <= 6; i++)
{
rowTitle = new Row() { RowIndex = (UInt32Value)(uint)i };
for (int cellval = 0; cellval < 4; cellval++)
{
AppendTextCell(excelColumnNamesTitle[cellval] + i, string.Empty, rowTitle, 3);
}
sheetData1.Append(rowTitle);
}
List<CertificateDocument> dataSource = model.Certificates.Collection;
MergeCells mergeCells = new MergeCells();
Row currentRowTitle = sheetData1.Elements<Row>().FirstOrDefault(row => row.RowIndex.Value == (uint)2);
//add the title
UpdateStringCellValue("A2", Resources.Common.CertificateList, currentRowTitle, 5);
//set min date and max date in header
Row currentRowDateTitle = sheetData1.Elements<Row>().FirstOrDefault(row => row.RowIndex.Value == (uint)5);
string minDate, maxDate;
//get dates
if (string.IsNullOrEmpty(model.IssuanceDateFrom) || string.IsNullOrEmpty(model.IssuanceDateTo))
{
minDate = dataSource.Select(x => x.Certificate.IssuanceDate.GetValueOrDefault()).Min().ToString("dd/MM/yyyy");
maxDate = dataSource.Select(x => x.Certificate.IssuanceDate.GetValueOrDefault()).Max().ToString("dd/MM/yyyy");
}
else
{
minDate = model.IssuanceDateFrom;
maxDate = model.IssuanceDateTo;
}
//write both dates
UpdateStringCellValue("B5", Resources.Common.IssuanceDateFrom + ": "+minDate, currentRowDateTitle, 7);
UpdateStringCellValue("C5", Resources.Common.IssuanceDateTo + ": " + maxDate, currentRowDateTitle, 7);
//merge all cells in the title
MergeCell mergeCell = new MergeCell();
mergeCell.Reference = "A2:D4";
mergeCells.Append(mergeCell);
Drawing drawing = AddLogo(logoPath, worksheetPart);
Columns columns = new Columns();
columns.Append(CreateColumnData((UInt32Value)(uint)1, (UInt32Value)(uint)1, 32));
columns.Append(CreateColumnData((UInt32Value)(uint)2, (UInt32Value)(uint)2, 30));
columns.Append(CreateColumnData((UInt32Value)(uint)3, (UInt32Value)(uint)3, 33));
columns.Append(CreateColumnData((UInt32Value)(uint)4, (UInt32Value)(uint)4, 45));
worksheet.Append(columns);
int rowIndex = 8;
Row rowData = new Row() { RowIndex = (UInt32Value)(uint)rowIndex };
AppendTextCell("A" + rowIndex, Resources.Common.CertificateNumber, rowData, 2);
AppendTextCell("B" + rowIndex, Resources.Common.IssuanceDate, rowData, 2);
AppendTextCell("C" + rowIndex, Resources.Common.CertificateStatus, rowData, 2);
AppendTextCell("D" + rowIndex, Resources.Common.EntryPoint, rowData, 2);
sheetData1.Append(rowData);
rowIndex = 9;
//build the data
foreach (var item in dataSource)
{
rowData = new Row() { RowIndex = (UInt32Value)(uint)rowIndex };
AppendTextCell("A" + rowIndex.ToString(), item.Certificate.Sequential, rowData, 1);
AppendTextCell("B" + rowIndex.ToString(), item.Certificate.IssuanceDate.HasValue ? item.Certificate.IssuanceDate.Value.ToString("dd/MM/yyyy") : "", rowData, 1);
AppendTextCell("C" + rowIndex.ToString(), item.Certificate.CertificateStatusId.ToString(), rowData, 1);
AppendTextCell("D" + rowIndex.ToString(), item.Certificate.EntryPoint != null ? item.Certificate.EntryPoint.Name : "", rowData, 1);
sheetData1.Append(rowData);
rowIndex++;
}
//add the information of the current sheet
worksheet.Append(sheetData1);
//add merged cells
worksheet.InsertAfter(mergeCells, worksheet.Elements<SheetData>().First());
worksheet.Append(drawing);
worksheetPart.Worksheet = worksheet;
worksheetPart.Worksheet.Save();
//create the new sheet for this report
Sheet sheet = new Sheet() { Name = Resources.Common.CertificateList, SheetId = (UInt32Value)1, Id = workbookPart.GetIdOfPart(worksheetPart) };
sheets.Append(sheet);
//add the new sheet to the report
workbook.Append(sheets);
//save all report
workbook.Save();
//close the stream.
document.Close();
}
return ms;
}
/// <summary>
/// Generate an excel file with the list of users
/// </summary>
/// <param name="dataSource">The list of users</param>
/// <returns>MemoryStream</returns>
public static MemoryStream GenerateUserReport(List<UserModel> dataSource, string logoPath)
{
MemoryStream ms = new MemoryStream();
using (SpreadsheetDocument document = SpreadsheetDocument.Create(ms, SpreadsheetDocumentType.Workbook))
{
//create the new workbook
WorkbookPart workbookPart = document.AddWorkbookPart();
Workbook workbook = new Workbook();
workbookPart.Workbook = workbook;
// If we don't add a "WorkbookStylesPart", OLEDB will refuse to connect to this .xlsx file !
WorkbookStylesPart workbookStylesPart = workbookPart.AddNewPart<WorkbookStylesPart>("rIdStyles");
//get and save the stylesheet
Stylesheet stylesheet = VocStyleSheet();
workbookStylesPart.Stylesheet = stylesheet;
workbookStylesPart.Stylesheet.Save();
//add the new workseet
WorksheetPart worksheetPart = workbookPart.AddNewPart<WorksheetPart>();
Worksheet worksheet = new Worksheet();
SheetData sheetData1 = new SheetData();
Sheets sheets = new Sheets();
//get the number of columns in the report
Row rowTitle;
//get the string name of the columns
string[] excelColumnNamesTitle = new string[4];
for (int n = 0; n < 4; n++)
excelColumnNamesTitle[n] = GetExcelColumnName(n);
//build the title
for (int i = 1; i <= 6; i++)
{
rowTitle = new Row() { RowIndex = (UInt32Value)(uint)i };
for (int cellval = 0; cellval < 4; cellval++)
{
AppendTextCell(excelColumnNamesTitle[cellval] + i, string.Empty, rowTitle, 3);
}
sheetData1.Append(rowTitle);
}
MergeCells mergeCells = new MergeCells();
Row currentRowTitle = sheetData1.Elements<Row>().FirstOrDefault(row => row.RowIndex.Value == (uint)2);
//add the business application name
UpdateStringCellValue("A2", Resources.Common.UserList, currentRowTitle, 5);
//merge all cells in the title
MergeCell mergeCell = new MergeCell();
mergeCell.Reference = "A2:D4";
mergeCells.Append(mergeCell);
Drawing drawing = AddLogo(logoPath, worksheetPart);
Columns columns = new Columns();
columns.Append(CreateColumnData((UInt32Value)(uint)1, (UInt32Value)(uint)1, 45));
columns.Append(CreateColumnData((UInt32Value)(uint)2, (UInt32Value)(uint)2, 42));
columns.Append(CreateColumnData((UInt32Value)(uint)3, (UInt32Value)(uint)3, 10));
columns.Append(CreateColumnData((UInt32Value)(uint)4, (UInt32Value)(uint)4, 32));
worksheet.Append(columns);
int rowIndex = 8;
Row rowData = new Row() { RowIndex = (UInt32Value)(uint)rowIndex };
AppendTextCell("A" + rowIndex, Resources.Common.Email, rowData, 2);
AppendTextCell("B" + rowIndex, Resources.Common.FullName, rowData, 2);
AppendTextCell("C" + rowIndex, Resources.Common.Active, rowData, 2);
AppendTextCell("D" + rowIndex, Resources.Common.Role, rowData, 2);
sheetData1.Append(rowData);
rowIndex = 9;
//build the data
foreach (var item in dataSource)
{
rowData = new Row() { RowIndex = (UInt32Value)(uint)rowIndex };
AppendTextCell("A" + rowIndex.ToString(), item.Email, rowData, 1);
AppendTextCell("B" + rowIndex.ToString(), item.FullName, rowData, 1);
AppendTextCell("C" + rowIndex.ToString(), item.IsActive, rowData, 1);
AppendTextCell("D" + rowIndex.ToString(), item.Role, rowData, 1);
sheetData1.Append(rowData);
rowIndex++;
}
//add the information of the current sheet
worksheet.Append(sheetData1);
//add merged cells
worksheet.InsertAfter(mergeCells, worksheet.Elements<SheetData>().First());
worksheet.Append(drawing);
worksheetPart.Worksheet = worksheet;
worksheetPart.Worksheet.Save();
//create the new sheet for this report
Sheet sheet = new Sheet() { Name = Resources.Common.UserList, SheetId = (UInt32Value)1, Id = workbookPart.GetIdOfPart(worksheetPart) };
sheets.Append(sheet);
//add the new sheet to the report
workbook.Append(sheets);
//save all report
workbook.Save();
//close the stream.
document.Close();
}
return ms;
}
/// <summary>
/// Generate an excel report dinamically
/// </summary>
/// <param name="model">Data source</param>
public static MemoryStream GenerateReportDinamically(DynamicDataGrid model, string logoPath)
{
MemoryStream report = new MemoryStream();
using (SpreadsheetDocument document = SpreadsheetDocument.Create(report, SpreadsheetDocumentType.Workbook))
{
//create the new workbook
WorkbookPart workbookPart = document.AddWorkbookPart();
Workbook workbook = new Workbook();
workbookPart.Workbook = workbook;
// If we don't add a "WorkbookStylesPart", OLEDB will refuse to connect to this .xlsx file !
WorkbookStylesPart workbookStylesPart = workbookPart.AddNewPart<WorkbookStylesPart>("rIdStyles");
//get and save the stylesheet
Stylesheet stylesheet = VestalisStyleSheet();
workbookStylesPart.Stylesheet = stylesheet;
workbookStylesPart.Stylesheet.Save();
//add the new workseet
WorksheetPart worksheetPart = workbookPart.AddNewPart<WorksheetPart>();
Worksheet worksheet = new Worksheet();
SheetData sheetData1 = new SheetData();
Sheets sheets = new Sheets();
//get the number of columns in the report
Row rowTitle;
int numberOfColumnsCaption = model.Captions.Count;
//get the string name of the columns
string[] excelColumnNamesTitle = new string[numberOfColumnsCaption];
for (int n = 0; n < numberOfColumnsCaption; n++)
excelColumnNamesTitle[n] = GetExcelColumnName(n);
//build the title
for (int i = 1; i <= 6; i++)
{
rowTitle = new Row() { RowIndex = (UInt32Value)(uint)i };
for (int cellval = 0; cellval < numberOfColumnsCaption; cellval++)
{
AppendTextCell(excelColumnNamesTitle[cellval] + i, string.Empty, rowTitle, 3);
}
sheetData1.Append(rowTitle);
}
MergeCells mergeCells = new MergeCells();
Row currentRowTitle = sheetData1.Elements<Row>().FirstOrDefault(row => row.RowIndex.Value == (uint)2);
//add the business application name
UpdateStringCellValue("B2", model.BusinessApplicationName, currentRowTitle, 5);
string lastColumnName = excelColumnNamesTitle.Last() + "2";
//merge all cells in the title
MergeCell mergeCell = new MergeCell();
mergeCell.Reference = "B2:" + lastColumnName;
mergeCells.Append(mergeCell);
Drawing drawing = AddLogo(logoPath, worksheetPart);
currentRowTitle = sheetData1.Elements<Row>().FirstOrDefault(row => row.RowIndex.Value == (uint)4);
//add the form name
UpdateStringCellValue("B4", model.FormName, currentRowTitle,5);
lastColumnName = lastColumnName.Replace("2", "4");
//merge all cell in the form name
mergeCell = new MergeCell();
mergeCell.Reference = "B4:" + lastColumnName;
mergeCells.Append(mergeCell);
int rowIndex = 7;
//get the names of the columns
string[] excelColumnNamesCaptions = new string[numberOfColumnsCaption];
for (int n = 0; n < numberOfColumnsCaption; n++)
excelColumnNamesCaptions[n] = GetExcelColumnName(n);
Row rowCaption = new Row() { RowIndex = (UInt32Value)(uint)rowIndex };
//build column names of the report
Columns columns = new Columns();
for (int i = 0; i < model.Captions.Count; i++)
{
var caption = model.Captions[i];
AppendTextCell(excelColumnNamesCaptions[i] + rowIndex.ToString(), caption.Caption, rowCaption, 2);
columns.Append(CreateColumnData((UInt32Value)(uint)i + 1, (UInt32Value)(uint)i + 1, caption.ExcelColumnWidth));
}
sheetData1.Append(rowCaption);
//add the new row with the name of the columns
worksheet.Append(columns);
rowIndex = 8;
//write the data of the report
foreach (var item in model.DataRows)
{
int numberOfColumnsData = item.FieldValues.Count;
//get column names
string[] excelColumnNamesData = new string[numberOfColumnsData];
for (int n = 0; n < numberOfColumnsData; n++)
excelColumnNamesData[n] = GetExcelColumnName(n);
//build the data information
Row rowData = new Row() { RowIndex = (UInt32Value)(uint)rowIndex };
for (int colInx = 0; colInx < numberOfColumnsData; colInx++)
{
DynamicDataRowValue col = item.FieldValues[colInx];
switch (col.FieldType)
{
case (int)FieldType.Catalogue:
case (int)FieldType.RegularExpressionText:
case (int)FieldType.Time:
case (int)FieldType.SingleTextLine:
case (int)FieldType.MultipleTextLine:
case (int)FieldType.Datepicker:
case (int)FieldType.Boolean:
case (int)FieldType.AutoComplete:
case (int)FieldType.StatusField:
AppendTextCell(excelColumnNamesData[colInx] + rowIndex.ToString(), col.FieldValue, rowData, 1);
break;
case (int)FieldType.Integer:
case (int)FieldType.Decimal:
AppendNumberCell(excelColumnNamesData[colInx] + rowIndex.ToString(), col.FieldValue, rowData, 1);
break;
default:
break;
}
}
//add the new row to the report
sheetData1.Append(rowData);
rowIndex++;
}
//add the information of the current sheet
worksheet.Append(sheetData1);
//add merged cells
worksheet.InsertAfter(mergeCells, worksheet.Elements<SheetData>().First());
worksheet.Append(drawing);
worksheetPart.Worksheet = worksheet;
worksheetPart.Worksheet.Save();
//create the new sheet for this report
Sheet sheet = new Sheet() { Name = model.FormName, SheetId = (UInt32Value)1, Id = workbookPart.GetIdOfPart(worksheetPart) };
sheets.Append(sheet);
//add the new sheet to the report
workbook.Append(sheets);
//save all report
workbook.Save();
//close the stream.
document.Close();
}
return report;
}
/// <summary>
/// Generate service order report
/// </summary>
/// <param name="itemsource">Item source</param>
/// <param name="workbookPart">Worbook part</param>
private static void GenerateServiceOrder(ExportInspectionReportsModel itemsource, WorkbookPart workbookPart, Sheets sheets,int sheetId,string logoPath)
{
if (itemsource.IsSelectedServiceOrder)
{
// Remove the sheet reference from the workbook.
WorksheetPart worksheetPart = workbookPart.AddNewPart<WorksheetPart>();
// The SheetData object will contain all the data.
SheetData sheetData = new SheetData();
Worksheet worksheet = new Worksheet();
Form serviceOrder = itemsource.ServiceOrderData;
Row rowTitle;
//get the string name of the columns
string[] excelColumnNamesTitle = new string[9];
for (int n = 0; n < 9; n++)
excelColumnNamesTitle[n] = GetExcelColumnName(n);
//build the title
for (int i = 1; i <= 6; i++)
{
rowTitle = new Row() { RowIndex = (UInt32Value)(uint)i };
for (int cellval = 0; cellval < 9; cellval++)
{
AppendTextCell(excelColumnNamesTitle[cellval] + i, string.Empty, rowTitle, 3);
}
sheetData.Append(rowTitle);
}
MergeCells mergeCells = new MergeCells();
Row currentRowTitle = sheetData.Elements<Row>().FirstOrDefault(row => row.RowIndex.Value == (uint)2);
//add the business application name
UpdateStringCellValue("B2", itemsource.BusinessApplicationName, currentRowTitle, 5);
//merge all cells in the title
MergeCell mergeCell = new MergeCell();
mergeCell.Reference = "B2:E2";
mergeCells.Append(mergeCell);
currentRowTitle = sheetData.Elements<Row>().FirstOrDefault(row => row.RowIndex.Value == (uint)4);
//add the form name
UpdateStringCellValue("B4", itemsource.ServiceOrderSheetName, currentRowTitle, 5);
//merge all cell in the form name
mergeCell = new MergeCell();
mergeCell.Reference = "B4:E4";
mergeCells.Append(mergeCell);
Drawing drawing = AddLogo(logoPath, worksheetPart);
Columns columns = new Columns();
columns.Append(CreateColumnData((UInt32Value)(uint)1, (UInt32Value)(uint)1, 26));
columns.Append(CreateColumnData((UInt32Value)(uint)2, (UInt32Value)(uint)2, 73));
worksheet.Append(columns);
int rowIndex = 8;
Row sectionRow;
foreach (var section in serviceOrder.Sections)
{
sectionRow = new Row() { RowIndex = (UInt32Value)(uint)rowIndex };
mergeCell = new MergeCell();
mergeCell.Reference = "A" + rowIndex + ":B" + rowIndex;
mergeCells.Append(mergeCell);
AppendTextCell("A" + rowIndex, section.Caption, sectionRow, 6);
AppendTextCell("B" + rowIndex, string.Empty, sectionRow, 6);
sheetData.Append(sectionRow);
foreach (var element in section.FormElements)
{
rowIndex++;
//The current row is obtained for updating the value of the cell
Row rowData = new Row() { RowIndex = (UInt32Value)(uint)rowIndex };
switch (element.Field.FieldType)
{
case FieldType.Catalogue:
AppendTextCell("A" + rowIndex.ToString(), element.Field.Caption, rowData, 1);
if (!string.IsNullOrEmpty(element.Field.FieldValue))
{
string catalogueValue = CatalogueBusiness.GetCatalogueValue(new Guid(element.Field.FieldValue)).CatalogueValueData;
AppendTextCell("B" + rowIndex.ToString(), catalogueValue, rowData, 1);
}
else
{
AppendTextCell("B" + rowIndex.ToString(), string.Empty, rowData, 1);
}
break;
case FieldType.RegularExpressionText:
case FieldType.Time:
case FieldType.SingleTextLine:
case FieldType.MultipleTextLine:
case FieldType.Datepicker:
AppendTextCell("A" + rowIndex.ToString(), element.Field.Caption, rowData, 1);
AppendTextCell("B" + rowIndex.ToString(), element.Field.FieldValue, rowData, 1);
break;
case FieldType.Boolean:
string boolValue = element.Field.FieldValue == "True" ? LanguageResource.Yes : LanguageResource.No;
AppendTextCell("A" + rowIndex.ToString(), element.Field.Caption, rowData, 1);
AppendTextCell("B" + rowIndex.ToString(), boolValue, rowData, 1);
break;
case FieldType.Integer:
case FieldType.Decimal:
AppendTextCell("A" + rowIndex.ToString(), element.Field.Caption, rowData, 1);
AppendNumberCell("B" + rowIndex.ToString(), element.Field.FieldValue, rowData, 1);
break;
default:
break;
}
sheetData.Append(rowData);
}
rowIndex+=2;
}
worksheet.Append(sheetData);
//add merged cells
worksheet.InsertAfter(mergeCells, worksheet.Elements<SheetData>().First());
worksheet.Append(drawing);
worksheetPart.Worksheet = worksheet;
worksheetPart.Worksheet.Save();
//create the new sheet for this report
Sheet sheet = new Sheet() { Name = itemsource.ServiceOrderSheetName, SheetId = (UInt32Value)(uint)sheetId, Id = workbookPart.GetIdOfPart(worksheetPart) };
sheets.Append(sheet);
sheetId++;
}
}
// Return the row at the specified rowIndex located within
// the sheet data passed in via wsData. If the row does not
// exist, create it.
private static Row GetRow(SheetData wsData, UInt32 rowIndex)
{
var row = wsData.Elements<Row>().
Where(r => r.RowIndex.Value == rowIndex).FirstOrDefault();
if (row == null)
{
row = new Row();
row.RowIndex = rowIndex;
wsData.Append(row);
}
return row;
}
//
// Get the row of a given row index
//
private Row getRow(SheetData wsData, UInt32 rowIndex)
{
//
// Look for the rowIndex, if it exists return it otherwise create a new row and retourn it
//
var row = wsData.Elements<Row>().Where(r => r.RowIndex.Value == rowIndex).FirstOrDefault();
if (row == null)
{
row = new Row();
row.RowIndex = rowIndex;
wsData.Append(row);
}
return row;
}
/// <summary>
/// Generate inspection reports
/// </summary>
/// <param name="itemsource">Item source</param>
/// <param name="workbookPart">Worbook part</param>
private static void GenerateAllInspectionReports(ExportInspectionReportsModel itemsource, WorkbookPart workbookPart, Sheets sheets, int sheetId, string logoPath)
{
// Loop through each of the DataTables in our DataSet, and create a new Excel Worksheet for each.
foreach (var item in itemsource.InspectionReports)
{
WorksheetPart worksheetPart = workbookPart.AddNewPart<WorksheetPart>();
Worksheet worksheet = new Worksheet();
SheetData sheetData1 = new SheetData();
//get the number of columns in the report
Row rowTitle;
int numberOfColumnsCaption = item.Value.Captions.Count;
//get the string name of the columns
string[] excelColumnNamesTitle = new string[numberOfColumnsCaption];
for (int n = 0; n < numberOfColumnsCaption; n++)
excelColumnNamesTitle[n] = GetExcelColumnName(n);
//build the title
for (int i = 1; i <= 6; i++)
{
rowTitle = new Row() { RowIndex = (UInt32Value)(uint)i };
for (int cellval = 0; cellval < numberOfColumnsCaption; cellval++)
{
AppendTextCell(excelColumnNamesTitle[cellval] + i, string.Empty, rowTitle, 3);
}
sheetData1.Append(rowTitle);
}
MergeCells mergeCells = new MergeCells();
Row currentRowTitle = sheetData1.Elements<Row>().FirstOrDefault(row => row.RowIndex.Value == (uint)2);
//add the business application name
UpdateStringCellValue("B2", item.Value.BusinessApplicationName, currentRowTitle, 5);
string lastColumnName = excelColumnNamesTitle.Last() + "2";
//merge all cells in the title
MergeCell mergeCell = new MergeCell();
mergeCell.Reference = "B2:" + lastColumnName;
mergeCells.Append(mergeCell);
currentRowTitle = sheetData1.Elements<Row>().FirstOrDefault(row => row.RowIndex.Value == (uint)4);
//add the form name
UpdateStringCellValue("B4", item.Key, currentRowTitle, 5);
lastColumnName = lastColumnName.Replace("2", "4");
//merge all cell in the form name
mergeCell = new MergeCell();
mergeCell.Reference = "B4:" + lastColumnName;
mergeCells.Append(mergeCell);
Drawing drawing = AddLogo(logoPath, worksheetPart);
int rowIndex = 7;
//get the names of the columns
string[] excelColumnNamesCaptions = new string[numberOfColumnsCaption];
for (int n = 0; n < numberOfColumnsCaption; n++)
excelColumnNamesCaptions[n] = GetExcelColumnName(n);
Row rowCaption = new Row() { RowIndex = (UInt32Value)(uint)rowIndex };
//build column names of the report
Columns columns = new Columns();
for (int i = 0; i < item.Value.Captions.Count; i++)
{
var caption = item.Value.Captions[i];
AppendTextCell(excelColumnNamesCaptions[i] + rowIndex.ToString(), caption.Caption, rowCaption, 2);
columns.Append(CreateColumnData((UInt32Value)(uint)i + 1, (UInt32Value)(uint)i + 1, caption.ExcelColumnWidth));
}
sheetData1.Append(rowCaption);
//add the new row with the name of the columns
worksheet.Append(columns);
rowIndex = 8;
//write the data of the report
foreach (var row in item.Value.DataRows)
{
int numberOfColumnsData = row.FieldValues.Count;
//get column names
string[] excelColumnNamesData = new string[numberOfColumnsData];
for (int n = 0; n < numberOfColumnsData; n++)
excelColumnNamesData[n] = GetExcelColumnName(n);
//build the row
Row rowData = new Row() { RowIndex = (UInt32Value)(uint)rowIndex };
for (int colInx = 0; colInx < numberOfColumnsData; colInx++)
{
DynamicDataRowValue col = row.FieldValues[colInx];
switch (col.FieldType)
{
case (int)FieldType.Catalogue:
case (int)FieldType.RegularExpressionText:
case (int)FieldType.Time:
case (int)FieldType.SingleTextLine:
case (int)FieldType.MultipleTextLine:
case (int)FieldType.Datepicker:
case (int)FieldType.Boolean:
case (int)FieldType.AutoComplete:
case (int)FieldType.StatusField:
AppendTextCell(excelColumnNamesData[colInx] + rowIndex.ToString(), col.FieldValue, rowData, 1);
break;
case (int)FieldType.Integer:
case (int)FieldType.Decimal:
AppendNumberCell(excelColumnNamesData[colInx] + rowIndex.ToString(), col.FieldValue, rowData, 1);
break;
default:
break;
}
}
//add the new row to the report
sheetData1.Append(rowData);
rowIndex++;
}
//add the information of the current sheet
worksheet.Append(sheetData1);
//add merged cells
worksheet.InsertAfter(mergeCells, worksheet.Elements<SheetData>().First());
worksheet.Append(drawing);
worksheetPart.Worksheet = worksheet;
worksheetPart.Worksheet.Save();
//create the new sheet for this report
Sheet sheet = new Sheet() { Name = item.Key, SheetId = (UInt32Value)(uint)sheetId, Id = workbookPart.GetIdOfPart(worksheetPart) };
sheets.Append(sheet);
sheetId++;
}
}