public static void CreateSpreadsheetWorkbook(string filepath, DataTable dt, List<SQLBuilder.Clauses.Column> SelectedColumns)
{
FileInfo f = new FileInfo(filepath);
if (f.Exists)
f.Delete();
SpreadsheetDocument spreadsheetDocument = SpreadsheetDocument.Create(filepath, SpreadsheetDocumentType.Workbook);
WorkbookPart workbookpart = spreadsheetDocument.AddWorkbookPart();
var stylesPart =
spreadsheetDocument.WorkbookPart.AddNewPart<WorkbookStylesPart>();
Stylesheet styles = new CustomStylesheet();
styles.Save(stylesPart);
workbookpart.Workbook = new Workbook();
WorksheetPart worksheetPart = workbookpart.AddNewPart<WorksheetPart>();
worksheetPart.Worksheet = new Worksheet(new SheetData());
Sheets sheets = spreadsheetDocument.WorkbookPart.Workbook.AppendChild<Sheets>(new Sheets());
Sheet sheet = new Sheet() { Id = spreadsheetDocument.WorkbookPart.GetIdOfPart(worksheetPart), SheetId = 1, Name = "mySheet" };
sheets.Append(sheet);
string cl = "";
uint row = 2;
int index;
Cell cell;
foreach (DataRow dr in dt.Rows)
{
for (int idx = 0; idx < dt.Columns.Count; idx++)
{
if (idx >= 26)
{
cl = Convert.ToString(Convert.ToChar(65 + ((idx / 26) - 1))) + Convert.ToString(Convert.ToChar(65 + idx % 26));
}
else
{
cl = Convert.ToString(Convert.ToChar(65 + idx));
}
SharedStringTablePart shareStringPart;
if (spreadsheetDocument.WorkbookPart.GetPartsOfType<SharedStringTablePart>().Count() > 0)
{
shareStringPart = spreadsheetDocument.WorkbookPart.GetPartsOfType<SharedStringTablePart>().First();
}
else
{
shareStringPart = spreadsheetDocument.WorkbookPart.AddNewPart<SharedStringTablePart>();
}
if (row == 2)
{
index = InsertSharedStringItem(dt.Columns[idx].ColumnName, shareStringPart);
cell = InsertCellInWorksheet(cl, row - 1, worksheetPart);
cell.CellValue = new CellValue(index.ToString());
cell.DataType = new EnumValue<CellValues>(CellValues.SharedString);
cell.StyleIndex = 7;
}
cell = InsertCellInWorksheet(cl, row, worksheetPart);
cell.CellValue = new CellValue(Convert.ToString(dr[idx]));
if (dr[idx] != null)
{
if (SelectedColumns.Count != 0)
{
string colFormat = SelectedColumns[idx].Format;
if (colFormat != null)
{
cell.StyleIndex = (UInt32)SQLBuilder.Common.ColumnFormat.Instance.getExcelColumnFormat(colFormat);
if (dr[idx].GetType() == typeof(string))
{
cell.DataType = CellValues.String;
}
else if (dr[idx].GetType() == typeof(bool))
{
cell.DataType = CellValues.Boolean;
}
else if (dr[idx].GetType() == typeof(DateTime))
{
cell.DataType = CellValues.Date;
}
else if (dr[idx].GetType() == typeof(decimal) ||
dr[idx].GetType() == typeof(double))
{
cell.DataType = CellValues.Number;
}
else if (dr[idx].GetType() == typeof(Int16) || dr[idx].GetType() == typeof(Int32) || dr[idx].GetType() == typeof(Int64))
{
cell.DataType = CellValues.Number;
}
else
{
cell.DataType = CellValues.String;
}
}
else
{
cell.StyleIndex = 8;
cell.DataType = CellValues.String;
}
}
}
}
row++;
}
worksheetPart.Worksheet.Save();
workbookpart.Workbook.Save();
// Close the document.
spreadsheetDocument.Close();
}
public static void CreateCrossTabulation(string filePath, DataTable dt, List<SQLBuilder.Clauses.Column> SelectedColumns, ResultViewModel rvm)
{
FileInfo f = new FileInfo(filePath);
if (f.Exists)
f.Delete();
SpreadsheetDocument spreadsheetDocument = SpreadsheetDocument.Create(filePath, SpreadsheetDocumentType.Workbook);
WorkbookPart workbookpart = spreadsheetDocument.AddWorkbookPart();
var stylesPart = spreadsheetDocument.WorkbookPart.AddNewPart<WorkbookStylesPart>();
Stylesheet styles = new CustomStylesheet();
styles.Save(stylesPart);
workbookpart.Workbook = new DocumentFormat.OpenXml.Spreadsheet.Workbook();
WorksheetPart worksheetPart = workbookpart.AddNewPart<WorksheetPart>();
worksheetPart.Worksheet = new DocumentFormat.OpenXml.Spreadsheet.Worksheet(new DocumentFormat.OpenXml.Spreadsheet.SheetData());
DocumentFormat.OpenXml.Spreadsheet.Sheets sheets = (spreadsheetDocument.WorkbookPart.Workbook.AppendChild<Sheets>(new Sheets()));
Sheet sheet = new Sheet() { Id = spreadsheetDocument.WorkbookPart.GetIdOfPart(worksheetPart), SheetId = 1, Name = "mySheet" };
sheets.Append(sheet);
string cl = "A";
uint row = 1;
int index;
Cell cell = new Cell();
System.Windows.Controls.Label colRow = new System.Windows.Controls.Label();
int lineNumber = 0;
colRow.Content = "Cross Tabulation by " + rvm.QueryBulder.CrossTabulationResults.CrossTabColumn.AliasName;
cell = InsertCellInWorksheet(cl, row, worksheetPart);
cell.CellValue = new CellValue(Convert.ToString(colRow.Content));
cell.DataType = new EnumValue<CellValues>(CellValues.SharedString);
cell.StyleIndex = 7;
row++;
List<List<string>> groupByColumnValues = rvm.QueryBulder.CrossTabulationResults.GroupByColumnValueList;
for (int groupByColIndex = 0; groupByColIndex < rvm.QueryBulder.CrossTabulationResults.GroupByColumns.Count; groupByColIndex++)
{
SQLBuilder.Clauses.Column groupByCol = rvm.QueryBulder.CrossTabulationResults.GroupByColumns.ElementAt<SQLBuilder.Clauses.Column>(groupByColIndex);
string colFormat = SQLBuilder.Common.ColumnFormat.Instance.getColumnFormat(groupByCol.Format);
CrossTabulationViewGroupByControl ctvgCntrl = new CrossTabulationViewGroupByControl();
colRow.Content = Common.getColumnNameOrAlias(groupByCol);
for (int i = 0; i <= groupByColumnValues.Count + 1; i++)
{
if (groupByColIndex >= 26)
{
cl = Convert.ToString(Convert.ToChar(65 + ((groupByColIndex / 26) - 1))) + Convert.ToString(Convert.ToChar(65 + groupByColIndex % 26));
}
else
{
cl = Convert.ToString(Convert.ToChar(65 + groupByColIndex));
}
SharedStringTablePart shareStringPart;
if (spreadsheetDocument.WorkbookPart.GetPartsOfType<SharedStringTablePart>().Count() > 0)
{
shareStringPart = spreadsheetDocument.WorkbookPart.GetPartsOfType<SharedStringTablePart>().First();
}
else
{
shareStringPart = spreadsheetDocument.WorkbookPart.AddNewPart<SharedStringTablePart>();
}
if (row == 2)
{
index = InsertSharedStringItem(dt.Columns[groupByColIndex].ColumnName, shareStringPart);
cell = InsertCellInWorksheet(cl, row, worksheetPart);
cell.CellValue = new CellValue(Convert.ToString(dt.Columns[groupByColIndex]));
cell.DataType = new EnumValue<CellValues>(CellValues.SharedString);
cell.StyleIndex = 7;
}
else if (row > 3)
{
cell = InsertCellInWorksheet(cl, row, worksheetPart);
cell.CellValue = new CellValue(Convert.ToString(groupByColumnValues.ElementAt<List<string>>(i - 2).ElementAt<string>(groupByColIndex)));
}
lineNumber = lineNumber + 1;
row++;
}
row = 2;
}
int groupByColCount = rvm.QueryBulder.CrossTabulationResults.GroupByColumns.Count;
int summarrizeValueIndex = groupByColCount + 1;
Dictionary<string, Object> dataMap = rvm.QueryBulder.CrossTabulationResults.DataMap;
row = 1;
foreach (string summaryMainValue in rvm.QueryBulder.CrossTabulationResults.CrossTabColumnVaues)
{
CrossTabulationViewSummaryMainControl summaryMain = new CrossTabulationViewSummaryMainControl();
summaryMain.lblSummaryHeader.Content = summaryMainValue;
int totalcolumn = rvm.QueryBulder.CrossTabulationResults.SummarizeColumns.Count + groupByColCount;
for (int summaryColIndex = 0; summaryColIndex < rvm.QueryBulder.CrossTabulationResults.SummarizeColumns.Count; summaryColIndex++)
{
SQLBuilder.Clauses.Column summaryCol = rvm.QueryBulder.CrossTabulationResults.SummarizeColumns.ElementAt<SQLBuilder.Clauses.Column>(summaryColIndex);
string summaryColName = summaryCol.AliasName;
string summarycolFormat = SQLBuilder.Common.ColumnFormat.Instance.getColumnFormat(summaryCol.Format);
CrossTabulationViewSummaryControl ctvsCtrl = new CrossTabulationViewSummaryControl();
for (int keyIndex = 0; keyIndex <= rvm.QueryBulder.CrossTabulationResults.KeyPrefixes.Count + 2; keyIndex++)
{
if (summaryColIndex >= 26)
{
cl = Convert.ToString(Convert.ToChar(65 + ((summarrizeValueIndex / 26) - 1))) + Convert.ToString(Convert.ToChar(65 + summarrizeValueIndex % 26));
}
else
{
cl = Convert.ToString(Convert.ToChar(65 + summarrizeValueIndex));
}
SharedStringTablePart shareStringPart;
if (spreadsheetDocument.WorkbookPart.GetPartsOfType<SharedStringTablePart>().Count() > 0)
{
shareStringPart = spreadsheetDocument.WorkbookPart.GetPartsOfType<SharedStringTablePart>().First();
}
else
{
shareStringPart = spreadsheetDocument.WorkbookPart.AddNewPart<SharedStringTablePart>();
}
if (row == 1)
{
index = InsertSharedStringItem(summaryMainValue, shareStringPart);
cell = InsertCellInWorksheet(cl, row, worksheetPart);
cell.CellValue = new CellValue(Convert.ToString(summaryMainValue));
cell.DataType = new EnumValue<CellValues>(CellValues.SharedString);
cell.StyleIndex = 7;
}
else if (row == 2)
{
index = InsertSharedStringItem(summaryColName, shareStringPart);
cell = InsertCellInWorksheet(cl, row, worksheetPart);
cell.CellValue = new CellValue(Convert.ToString(summaryColName));
cell.DataType = new EnumValue<CellValues>(CellValues.SharedString);
cell.StyleIndex = 7;
}
else if (row > 3)
{
string key = rvm.QueryBulder.CrossTabulationResults.KeyPrefixes.ElementAt<string>(keyIndex - 3);
string keyValue = key + summaryMainValue + summaryColIndex;
cell = InsertCellInWorksheet(cl, row, worksheetPart);
if (dataMap.ContainsKey(keyValue))
{
if (summarycolFormat != null)
{
cell.CellValue = new CellValue(String.Format(summarycolFormat, dataMap[keyValue]));
}
else
{
cell.CellValue = new CellValue(Convert.ToString(dataMap[keyValue]));
}
}
else
{
cell.CellValue = new CellValue(Convert.ToString("00"));
}
}
lineNumber = lineNumber + 1;
if (row > rvm.QueryBulder.CrossTabulationResults.KeyPrefixes.Count + 2)
{
row = 1;
summarrizeValueIndex = summarrizeValueIndex + 1;
}
else
{
row++;
}
}
lineNumber = 0;
}
row = 1;
groupByColCount = groupByColCount + 1;
}
worksheetPart.Worksheet.Save();
workbookpart.Workbook.Save();
spreadsheetDocument.Close();
}
