/// <summary>
/// Write the excel rows for the spreadsheet.
/// </summary>
/// <param name="rowData">Excel row values.</param>
/// <param name="rowDataKeys">Excel row-key values.</param>
/// <param name="rowNum">Row number.</param>
/// <param name="maxWidth">Max width.</param>
/// <param name="spreadSheet">Spreadsheet to write to. </param>
/// <param name="workSheet">Worksheet to write to. </param>
//private static void WriteRowsFromKeys(IQueryable rowData, string[] rowDataKeys, int rowNum, out int maxWidth, SpreadsheetDocument spreadSheet, WorksheetPart workSheet)
//{
// maxWidth = 0;
// foreach (object row in rowData)
// {
// int colNum = 0;
// foreach (string rowKey in rowDataKeys)
// {
// var getKey = row.GetType().GetProperty(rowKey);
// var getValue = getKey != null ? getKey.GetValue(row, null) : null;
// string strValue = getValue != null ? getValue.ToString() : "";
// strValue = ReplaceSpecialCharacters(strValue);
// maxWidth = strValue.Length > maxWidth ? strValue.Length : maxWidth;
// string cellLocation = string.Format("{0}{1}", GetColumnLetter(colNum.ToString()), rowNum);
// ExcelDocument.WriteValues(cellLocation, strValue, spreadSheet, workSheet);
// colNum++;
// }
// rowNum++;
// }
//}
private static void WriteRowsFromKeys(System.Data.DataTable rowData, string[] rowDataKeys, int rowNum, out int maxWidth, SpreadsheetDocument spreadSheet, WorksheetPart workSheet)
{
maxWidth = 0;
for (int i = 0; i < rowData.Rows.Count; i++)
{
int colNum = 0;
for (int j = 0; j < rowDataKeys.Count(); j++)
{
var getValue = rowData.Rows[i][rowDataKeys[j]];
//var getValue = getKey != null ? getKey.GetValue(row, null) : null;
string strValue = getValue != null?getValue.ToString() : "";
strValue = ReplaceSpecialCharacters(strValue);
maxWidth = strValue.Length > maxWidth ? strValue.Length : maxWidth;
string cellLocation = string.Format("{0}{1}", GetColumnLetter(colNum.ToString()), rowNum);
ExcelDocument.WriteValues(cellLocation, strValue, spreadSheet, workSheet);
colNum++;
}
rowNum++;
}
//foreach (object row in rowData)
//{
// int colNum = 0;
// foreach (string rowKey in rowDataKeys)
// {
// var getKey = row.GetType().GetProperty(rowKey);
// var getValue = getKey != null ? getKey.GetValue(row, null) : null;
// string strValue = getValue != null ? getValue.ToString() : "";
// strValue = ReplaceSpecialCharacters(strValue);
// maxWidth = strValue.Length > maxWidth ? strValue.Length : maxWidth;
// string cellLocation = string.Format("{0}{1}", GetColumnLetter(colNum.ToString()), rowNum);
// ExcelDocument.WriteValues(cellLocation, strValue, spreadSheet, workSheet);
// colNum++;
// }
// rowNum++;
//}
}
/// <summary>
/// Write the excel headers for the spreadsheet.
/// </summary>
/// <param name="headerData">Excel header values.</param>
/// <param name="rowNum">Row number.</param>
/// <param name="colNum">Column Number.</param>
/// <param name="maxWidth">Max column width</param>
/// <param name="spreadSheet">Maximum Column Width to write to. </param>
/// <param name="workSheet">Worksheet to write to. </param>
private static void WriteHeaders(string[] headerData, out int rowNum, out int colNum, out int maxWidth, SpreadsheetDocument spreadSheet, WorksheetPart workSheet)
{
rowNum = 1;
colNum = 0;
maxWidth = 15;
foreach (string header in headerData)
{
string strValue = ReplaceSpecialCharacters(header);
string cellLocation = string.Format("{0}{1}", GetColumnLetter(colNum.ToString()), rowNum);
maxWidth = strValue.Length > maxWidth ? strValue.Length : maxWidth;
ExcelDocument.WriteValues(cellLocation, strValue, spreadSheet, workSheet);
SeatHeaderStyle(cellLocation, spreadSheet, workSheet);
colNum++;
}
rowNum++;
}
private static void WriteRowsFromHeaders(System.Data.DataTable rowData, string[] headerData, int rowNum, out int maxWidth, SpreadsheetDocument spreadSheet, WorksheetPart workSheet)
{
WorksheetWriter workSheetWriter = new WorksheetWriter(spreadSheet, workSheet);
maxWidth = 0;
for (int i = 0; i < rowData.Rows.Count; i++)
{
for (int j = 0; j < headerData.Count(); j++)
{
int colNum = 0;
string getValue = rowData.Rows[i][headerData[j]] != null ? rowData.Rows[i][headerData[j]].ToString() : "";
string strValue = getValue;
strValue = ReplaceSpecialCharacters(strValue);
maxWidth = strValue.Length > maxWidth ? strValue.Length : maxWidth;
string cellLocation = string.Format("{0}{1}", GetColumnLetter(colNum.ToString()), rowNum);
ExcelDocument.WriteValues(cellLocation, strValue, spreadSheet, workSheet);
colNum++;
}
rowNum++;
}
//foreach (object row in rowData)
//{
// int colNum = 0;
// foreach (string header in headerData)
// {
// string strValue = row.GetType().GetProperty(header).GetValue(row, null).ToString();
// strValue = ReplaceSpecialCharacters(strValue);
// maxWidth = strValue.Length > maxWidth ? strValue.Length : maxWidth;
// string cellLocation = string.Format("{0}{1}", GetColumnLetter(colNum.ToString()), rowNum);
// ExcelDocument.WriteValues(cellLocation, strValue, spreadSheet, workSheet);
// colNum++;
// }
// rowNum++;
//}
}
/// <summary>
/// Write the values for the rows from headers.
/// </summary>
/// <param name="rowData">Excel row values.</param>
/// <param name="headerData">Excel header values.</param>
/// <param name="rowNum">Row number.</param>
/// <param name="maxWidth">Max width.</param>
/// <param name="spreadSheet">Spreadsheet to write to. </param>
/// <param name="workSheet">Worksheet to write to. </param>
private static void WriteRowsFromHeaders(IQueryable rowData, string[] headerData, int rowNum, out int maxWidth, SpreadsheetDocument spreadSheet, WorksheetPart workSheet)
{
WorksheetWriter workSheetWriter = new WorksheetWriter(spreadSheet, workSheet);
maxWidth = 0;
foreach (object row in rowData)
{
int colNum = 0;
foreach (string header in headerData)
{
string strValue = row.GetType().GetProperty(header).GetValue(row, null).ToString();
strValue = ReplaceSpecialCharacters(strValue);
maxWidth = strValue.Length > maxWidth ? strValue.Length : maxWidth;
string cellLocation = string.Format("{0}{1}", GetColumnLetter(colNum.ToString()), rowNum);
ExcelDocument.WriteValues(cellLocation, strValue, spreadSheet, workSheet);
colNum++;
}
rowNum++;
}
}