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();
}
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();
}
/// <summary>
/// Write excel file of a list of object as T
/// Assume that maximum of 24 columns
/// </summary>
/// <typeparam name="T">Object type to pass in</typeparam>
/// <param name="fileName">Full path of the file name of excel spreadsheet</param>
/// <param name="objects">list of the object type</param>
/// <param name="sheetName">Sheet names of Excel File</param>
/// <param name="headerNames">Header names of the object</param>
public void Create <T>(
string fileName,
List <T> objects,
string sheetName,
List <string> headerNames)
{
//Open the copied template workbook.
using (SpreadsheetDocument myWorkbook = SpreadsheetDocument.Create(fileName, SpreadsheetDocumentType.Workbook))
{
WorkbookPart workbookPart = myWorkbook.AddWorkbookPart();
WorksheetPart worksheetPart = workbookPart.AddNewPart <WorksheetPart>();
// Create Styles and Insert into Workbook
WorkbookStylesPart stylesPart = myWorkbook.WorkbookPart.AddNewPart <WorkbookStylesPart>();
Stylesheet styles = new CustomStylesheet();
styles.Save(stylesPart);
string relId = workbookPart.GetIdOfPart(worksheetPart);
Workbook workbook = new Workbook();
FileVersion fileVersion = new FileVersion {
ApplicationName = "Microsoft Office Excel"
};
SheetData sheetData = CreateSheetData <T>(objects, headerNames, stylesPart);
Worksheet worksheet = new Worksheet();
//PageMargins pageM = worksheet.GetFirstChild<PageMargins>();
//SheetProtection sheetProtection = new SheetProtection();
//sheetProtection.Sheet = true;
//sheetProtection.Objects = true;
//sheetProtection.Scenarios = true;
////add column C:Z to allow edit range, which means column A,B and after Z are locked
//ProtectedRanges pRanges = new ProtectedRanges();
//ProtectedRange pRange = new ProtectedRange();
//ListValue<StringValue> lValue = new ListValue<StringValue>();
//lValue.InnerText = "D1:Z1048576";
//pRange.SequenceOfReferences = lValue;
//pRange.Name = "AllowEditRange1";
//pRanges.Append(pRange);
//worksheet.InsertBefore(sheetProtection, pageM);
//worksheet.InsertBefore(pRanges, pageM);
int numCols = headerNames.Count;
int width = headerNames.Max(h => h.Length) + 5;
Columns columns = new Columns();
for (int col = 0; col < numCols; col++)
{
Column c = CreateColumnData((UInt32)col + 1, (UInt32)numCols + 1, width);
if (col == 0)
{
c.Hidden = BooleanValue.FromBoolean(true);
}
columns.Append(c);
}
worksheet.Append(columns);
Sheets sheets = new Sheets();
Sheet sheet = new Sheet {
Name = sheetName, SheetId = 1, Id = relId
};
sheets.Append(sheet);
workbook.Append(fileVersion);
workbook.Append(sheets);
worksheet.Append(sheetData);
worksheetPart.Worksheet = worksheet;
worksheetPart.Worksheet.Save();
myWorkbook.WorkbookPart.Workbook = workbook;
myWorkbook.WorkbookPart.Workbook.Save();
myWorkbook.Close();
}
}
