public override void addDataRows(SheetData sheetData, DataTable dt)
{
// бежим по строкам
foreach (DataRow dataRow in dt.Rows)
{
var row = new Row();
for (int i = 0; i < dataRow.ItemArray.Length; i++)
{
var cell = new Cell();
var cellValue = new CellValue();
ApplyStyle(dataRow[i], RestMetadata.GetType(dt.Columns[i].ColumnName), ref cell, ref cellValue);
cell.Append(cellValue);
row.AppendChild(cell);
}
sheetData.AppendChild(row);
}
}
public void ExportDataTable(DataTable table, string exportFile)
{
//create the empty spreadsheet template and save the file //using the class generated by the Productivity tool
ExcelDocument excelDocument = new ExcelDocument();
excelDocument.CreatePackage(exportFile);
//string filename = "";
//File.Copy(filename, filename, true);
//populate the data into the spreadsheet
using (SpreadsheetDocument spreadsheet = SpreadsheetDocument.Open(exportFile, true))
{
WorkbookPart workbook = spreadsheet.WorkbookPart;
//create a reference to Sheet1
WorksheetPart worksheet = workbook.WorksheetParts.Last();
SheetData data = worksheet.Worksheet.GetFirstChild<SheetData>();
//add column names to the first row
Row header = new Row();
header.RowIndex = (UInt32)1;
foreach (DataColumn column in table.Columns)
{
Cell headerCell = createTextCell(
table.Columns.IndexOf(column) + 1,
1,
column.ColumnName);
header.AppendChild(headerCell);
}
data.AppendChild(header);
//loop through each data row
DataRow contentRow;
for (int i = 0; i < table.Rows.Count; i++)
{
contentRow = table.Rows[i];
data.AppendChild(createContentRow(contentRow, i + 2));
}
}
}
public bool ImportExcel(string strPropbandid)
{
if (System.IO.File.Exists(System.IO.Path.Combine(HttpContext.Current.Server.MapPath("~/Upload"), "OwnerTemplate.xlsx")))
{
System.IO.File.Delete(System.IO.Path.Combine(HttpContext.Current.Server.MapPath("~/Upload"), "OwnerTemplate.xlsx"));
}
string[] ListOfExportFieldName = new string[] { "ClientId", "Client", "NetworkId", "Network"
, "PropertyId","Property Address","First Name","Last Name","Start Date","Bank Name","Bank Sort Code","Correspondence Address",
"City","County","Post Code","E-mail Address","Mail Service","Telephone Number","Mobile Number","Managing Agent",
"Managing Telephone Number","Managing Email","Notes","Client Reference Number"};
using (SpreadsheetDocument spreadsheetDocument = SpreadsheetDocument.Create(System.IO.Path.Combine(HttpContext.Current.Server.MapPath("~/Upload"), "OwnerTemplate.xlsx"), SpreadsheetDocumentType.Workbook))
{
SheetData sheetData = new SheetData();
Row row = new Row();
Row titleRow = new Row { RowIndex = (UInt32)1 };
//-------------------------Field Type----------------------------------
titleRow.AppendChild(CreateTextCell("A", ListOfExportFieldName[0], 1));
titleRow.AppendChild(CreateTextCell("B", ListOfExportFieldName[1], 1));
titleRow.AppendChild(CreateTextCell("C", ListOfExportFieldName[2], 1));
titleRow.AppendChild(CreateTextCell("D", ListOfExportFieldName[3], 1));
titleRow.AppendChild(CreateTextCell("E", ListOfExportFieldName[4], 1));
titleRow.AppendChild(CreateTextCell("F", ListOfExportFieldName[5], 1));
titleRow.AppendChild(CreateTextCell("G", ListOfExportFieldName[6], 1));
titleRow.AppendChild(CreateTextCell("H", ListOfExportFieldName[7], 1));
titleRow.AppendChild(CreateTextCell("I", ListOfExportFieldName[8], 1));
titleRow.AppendChild(CreateTextCell("J", ListOfExportFieldName[9], 1));
titleRow.AppendChild(CreateTextCell("K", ListOfExportFieldName[10], 1));
titleRow.AppendChild(CreateTextCell("L", ListOfExportFieldName[11], 1));
titleRow.AppendChild(CreateTextCell("M", ListOfExportFieldName[12], 1));
titleRow.AppendChild(CreateTextCell("N", ListOfExportFieldName[13], 1));
titleRow.AppendChild(CreateTextCell("O", ListOfExportFieldName[14], 1));
titleRow.AppendChild(CreateTextCell("P", ListOfExportFieldName[15], 1));
titleRow.AppendChild(CreateTextCell("Q", ListOfExportFieldName[16], 1));
titleRow.AppendChild(CreateTextCell("R", ListOfExportFieldName[17], 1));
titleRow.AppendChild(CreateTextCell("S", ListOfExportFieldName[18], 1));
titleRow.AppendChild(CreateTextCell("T", ListOfExportFieldName[19], 1));
titleRow.AppendChild(CreateTextCell("U", ListOfExportFieldName[20], 1));
titleRow.AppendChild(CreateTextCell("V", ListOfExportFieldName[21], 1));
titleRow.AppendChild(CreateTextCell("W", ListOfExportFieldName[22], 1));
titleRow.AppendChild(CreateTextCell("X", ListOfExportFieldName[23], 1));
// Append Row to SheetData
sheetData.AppendChild(titleRow);
DataTable dt = GetRecordFromSiteMaster(strPropbandid);
if (dt != null)
{
for (int i = 0; i < dt.Rows.Count; i++)
{
Row Subrow = new Row();
Subrow.AppendChild(CreateTextCell("A", Convert.ToString(dt.Rows[i]["ClientId"]), i + 2));
Subrow.AppendChild(CreateTextCell("B", Convert.ToString(dt.Rows[i]["Client"]), i + 2));
Subrow.AppendChild(CreateTextCell("C", Convert.ToString(dt.Rows[i]["NetworkId"]), i + 2));
Subrow.AppendChild(CreateTextCell("D", Convert.ToString(dt.Rows[i]["Network"]), i + 2));
Subrow.AppendChild(CreateTextCell("E", Convert.ToString(dt.Rows[i]["PropertyId"]), i + 2));
Subrow.AppendChild(CreateTextCell("F", Convert.ToString(dt.Rows[i]["Property Address"]), i + 2));
// Append Row to SheetData
sheetData.AppendChild(Subrow);
}
}
// Add a WorkbookPart to the document.
WorkbookPart workbookpart = spreadsheetDocument.AddWorkbookPart();
workbookpart.Workbook = new Workbook();
Worksheet sheet1 = new Worksheet();
//-------------------Column Size---------------------
Columns columns = new Columns();
uint ival = 1;
foreach (string value in ListOfExportFieldName)
{
columns.Append(CreateColumnSize(1, ival, 28));
ival++;
}
sheet1.Append(columns);
//-------------------Column Size---------------------
sheet1.Append(sheetData);
// Add a WorksheetPart to the WorkbookPart.
WorksheetPart worksheetPart = workbookpart.AddNewPart<WorksheetPart>();
worksheetPart.Worksheet = sheet1;
// Add Sheets to the Workbook.
Sheets sheets = spreadsheetDocument.WorkbookPart.Workbook.AppendChild<Sheets>(new Sheets());
// Append a new worksheet and associate it with the workbook.
Sheet sheet = new Sheet()
{
Id = spreadsheetDocument.WorkbookPart.GetIdOfPart(worksheetPart),
SheetId = 1,
Name = "PropertyTemplate"
};
sheets.Append(sheet);
// Close the document.
spreadsheetDocument.Close();
}
return true;
}
private Row createContentRow(DataRow dataRow, int rowIndex)
{
Row row = new Row
{
RowIndex = (UInt32)rowIndex
};
for (int i = 0; i < dataRow.Table.Columns.Count; i++)
{
Cell dataCell = createTextCell(i + 1, rowIndex, dataRow[i]);
row.AppendChild(dataCell);
}
return row;
}
public static void ExportToOxml(bool firstTime, string filePath, DataTable resultsData)
{
//Delete the file if it exists.
if (firstTime && File.Exists(filePath))
{
File.Delete(filePath);
}
uint sheetId = 1; //Start at the first sheet in the Excel workbook.
if (firstTime)
{
//This is the first time of creating the excel file and the first sheet.
// Create a spreadsheet document by supplying the filepath.
// By default, AutoSave = true, Editable = true, and Type = xlsx.
SpreadsheetDocument spreadsheetDocument = SpreadsheetDocument.
Create(filePath, SpreadsheetDocumentType.Workbook);
// Add a WorkbookPart to the document.
WorkbookPart workbookpart = spreadsheetDocument.AddWorkbookPart();
workbookpart.Workbook = new Workbook();
// Add a WorksheetPart to the WorkbookPart.
var worksheetPart = workbookpart.AddNewPart<WorksheetPart>();
var sheetData = new SheetData();
worksheetPart.Worksheet = new Worksheet(sheetData);
var bold1 = new Bold();
CellFormat cf = new CellFormat();
// Add Sheets to the Workbook.
Sheets sheets;
sheets = spreadsheetDocument.WorkbookPart.Workbook.
AppendChild<Sheets>(new Sheets());
// Append a new worksheet and associate it with the workbook.
var sheet = new Sheet()
{
Id = spreadsheetDocument.WorkbookPart.
GetIdOfPart(worksheetPart),
SheetId = sheetId,
Name = "Sheet" + sheetId
};
sheets.Append(sheet);
//Add Header Row.
var headerRow = new Row();
foreach (DataColumn column in resultsData.Columns)
{
var cell = new Cell
{
DataType = CellValues.String,
CellValue = new CellValue(column.ColumnName)
};
headerRow.AppendChild(cell);
}
sheetData.AppendChild(headerRow);
foreach (DataRow row in resultsData.Rows)
{
var newRow = new Row();
foreach (DataColumn col in resultsData.Columns)
{
var cell = new Cell
{
DataType = CellValues.String,
CellValue = new CellValue(row[col].ToString())
};
newRow.AppendChild(cell);
}
sheetData.AppendChild(newRow);
}
workbookpart.Workbook.Save();
spreadsheetDocument.Close();
}
else
{
// Open the Excel file that we created before, and start to add sheets to it.
var spreadsheetDocument = SpreadsheetDocument.Open(filePath, true);
var workbookpart = spreadsheetDocument.WorkbookPart;
if (workbookpart.Workbook == null)
workbookpart.Workbook = new Workbook();
var worksheetPart = workbookpart.AddNewPart<WorksheetPart>();
var sheetData = new SheetData();
worksheetPart.Worksheet = new Worksheet(sheetData);
var sheets = spreadsheetDocument.WorkbookPart.Workbook.Sheets;
if (sheets.Elements<Sheet>().Any())
{
//Set the new sheet id
sheetId = sheets.Elements<Sheet>().Max(s => s.SheetId.Value) + 1;
}
else
{
sheetId = 1;
}
// Append a new worksheet and associate it with the workbook.
var sheet = new Sheet()
{
Id = spreadsheetDocument.WorkbookPart.
GetIdOfPart(worksheetPart),
SheetId = sheetId,
Name = "Sheet" + sheetId
};
sheets.Append(sheet);
//Add the header row here.
var headerRow = new Row();
foreach (DataColumn column in resultsData.Columns)
{
var cell = new Cell
{
DataType = CellValues.String,
CellValue = new CellValue(column.ColumnName)
};
headerRow.AppendChild(cell);
}
sheetData.AppendChild(headerRow);
foreach (DataRow row in resultsData.Rows)
{
var newRow = new Row();
foreach (DataColumn col in resultsData.Columns)
{
var cell = new Cell
{
DataType = CellValues.String,
CellValue = new CellValue(row[col].ToString())
};
newRow.AppendChild(cell);
}
sheetData.AppendChild(newRow);
}
workbookpart.Workbook.Save();
// Close the document.
spreadsheetDocument.Close();
}
}
public bool ImportExcel(string strSiteid, string strSupplierID, string strSupplyType, string strNotificationId, string strNotification, string CategoryID, string Category, string strReadingInterval)
{
if (System.IO.File.Exists(System.IO.Path.Combine(HttpContext.Current.Server.MapPath("~/Upload"), "MeterTemplate.xlsx")))
{
System.IO.File.Delete(System.IO.Path.Combine(HttpContext.Current.Server.MapPath("~/Upload"), "MeterTemplate.xlsx"));
}
string[] ListOfExportFieldName = new string[] { "ClientId", "Client", "NetworkId", "Network"
, "PropertyId","Property Address","SupplyTypeId","Supply Type","ReadFrequencyId","Read Frequency","MeterCategoryId","Meter Category","Reading Interval",
"Start Date","End Date","Meter Serial","Device ID","Meter Start Date","Meter End Date","Opening Read","Offset Value",
"Warrenty Date"};
using (SpreadsheetDocument spreadsheetDocument = SpreadsheetDocument.Create(System.IO.Path.Combine(HttpContext.Current.Server.MapPath("~/Upload"), "MeterTemplate.xlsx"), SpreadsheetDocumentType.Workbook))
{
SheetData sheetData = new SheetData();
Row row = new Row();
Row titleRow = new Row { RowIndex = (UInt32)1 };
//-------------------------Field Type----------------------------------
titleRow.AppendChild(CreateTextCell("A", ListOfExportFieldName[0], 1));
titleRow.AppendChild(CreateTextCell("B", ListOfExportFieldName[1], 1));
titleRow.AppendChild(CreateTextCell("C", ListOfExportFieldName[2], 1));
titleRow.AppendChild(CreateTextCell("D", ListOfExportFieldName[3], 1));
titleRow.AppendChild(CreateTextCell("E", ListOfExportFieldName[4], 1));
titleRow.AppendChild(CreateTextCell("F", ListOfExportFieldName[5], 1));
titleRow.AppendChild(CreateTextCell("G", ListOfExportFieldName[6], 1));
titleRow.AppendChild(CreateTextCell("H", ListOfExportFieldName[7], 1));
titleRow.AppendChild(CreateTextCell("I", ListOfExportFieldName[8], 1));
titleRow.AppendChild(CreateTextCell("J", ListOfExportFieldName[9], 1));
titleRow.AppendChild(CreateTextCell("K", ListOfExportFieldName[10], 1));
titleRow.AppendChild(CreateTextCell("L", ListOfExportFieldName[11], 1));
titleRow.AppendChild(CreateTextCell("M", ListOfExportFieldName[12], 1));
titleRow.AppendChild(CreateTextCell("N", ListOfExportFieldName[13], 1));
titleRow.AppendChild(CreateTextCell("O", ListOfExportFieldName[14], 1));
titleRow.AppendChild(CreateTextCell("P", ListOfExportFieldName[15], 1));
titleRow.AppendChild(CreateTextCell("Q", ListOfExportFieldName[16], 1));
titleRow.AppendChild(CreateTextCell("R", ListOfExportFieldName[17], 1));
titleRow.AppendChild(CreateTextCell("S", ListOfExportFieldName[18], 1));
titleRow.AppendChild(CreateTextCell("T", ListOfExportFieldName[19], 1));
titleRow.AppendChild(CreateTextCell("U", ListOfExportFieldName[20], 1));
titleRow.AppendChild(CreateTextCell("V", ListOfExportFieldName[21], 1));
// Append Row to SheetData
sheetData.AppendChild(titleRow);
DataTable dt = GetRecordFromSiteMaster(strSiteid, strSupplierID, strSupplyType, strNotificationId, strNotification, CategoryID, Category, strReadingInterval);
if (dt != null)
{
for (int i = 0; i < dt.Rows.Count; i++)
{
Row Subrow = new Row();
Subrow.AppendChild(CreateTextCell("A", Convert.ToString(dt.Rows[i]["ClientId"]), i + 2));
Subrow.AppendChild(CreateTextCell("B", Convert.ToString(dt.Rows[i]["Client"]), i + 2));
Subrow.AppendChild(CreateTextCell("C", Convert.ToString(dt.Rows[i]["NetworkId"]), i + 2));
Subrow.AppendChild(CreateTextCell("D", Convert.ToString(dt.Rows[i]["Network"]), i + 2));
Subrow.AppendChild(CreateTextCell("E", Convert.ToString(dt.Rows[i]["PropertyId"]), i + 2));
Subrow.AppendChild(CreateTextCell("F", Convert.ToString(dt.Rows[i]["Property Address"]), i + 2));
Subrow.AppendChild(CreateTextCell("G", Convert.ToString(dt.Rows[i]["SupplyTypeId"]), i + 2));
Subrow.AppendChild(CreateTextCell("H", Convert.ToString(dt.Rows[i]["Supply Type"]), i + 2));
Subrow.AppendChild(CreateTextCell("I", Convert.ToString(dt.Rows[i]["ReadFrequencyId"]), i + 2));
Subrow.AppendChild(CreateTextCell("J", Convert.ToString(dt.Rows[i]["Read Frequency"]), i + 2));
Subrow.AppendChild(CreateTextCell("K", Convert.ToString(dt.Rows[i]["MeterCategoryId"]), i + 2));
Subrow.AppendChild(CreateTextCell("L", Convert.ToString(dt.Rows[i]["Meter Category"]), i + 2));
Subrow.AppendChild(CreateTextCell("M", Convert.ToString(dt.Rows[i]["Reading Interval"]), i + 2));
// Append Row to SheetData
sheetData.AppendChild(Subrow);
}
}
// Add a WorkbookPart to the document.
WorkbookPart workbookpart = spreadsheetDocument.AddWorkbookPart();
workbookpart.Workbook = new Workbook();
Worksheet sheet1 = new Worksheet();
//-------------------Column Size---------------------
Columns columns = new Columns();
uint ival = 1;
foreach (string value in ListOfExportFieldName)
{
columns.Append(CreateColumnSize(1, ival, 28));
ival++;
}
sheet1.Append(columns);
//-------------------Column Size---------------------
sheet1.Append(sheetData);
// Add a WorksheetPart to the WorkbookPart.
WorksheetPart worksheetPart = workbookpart.AddNewPart<WorksheetPart>();
worksheetPart.Worksheet = sheet1;
// Add Sheets to the Workbook.
Sheets sheets = spreadsheetDocument.WorkbookPart.Workbook.AppendChild<Sheets>(new Sheets());
// Append a new worksheet and associate it with the workbook.
Sheet sheet = new Sheet()
{
Id = spreadsheetDocument.WorkbookPart.GetIdOfPart(worksheetPart),
SheetId = 1,
Name = "MeterTemplate"
};
sheets.Append(sheet);
// Close the document.
spreadsheetDocument.Close();
}
return true;
}
public bool ImportExcel(string strNotificationFrequencyId, string strNotificationFrequency, string NotificationTime, string BillingCurrencyId, string strBillingCurrency, string ContractTypeId, string ContractType, string DepartmentId, string Department)
{
if (System.IO.File.Exists(System.IO.Path.Combine(HttpContext.Current.Server.MapPath("~/Upload"), "ClientTemplate.xlsx")))
{
System.IO.File.Delete(System.IO.Path.Combine(HttpContext.Current.Server.MapPath("~/Upload"), "ClientTemplate.xlsx"));
}
string[] ListOfExportFieldName = new string[] { "NotificationFrequencyId", "Notification Frequency", "Notification Time", "BillingCurrencyId"
, "Billing Currency","ContractTypeId","Contract Type","ClientContactDepartmentId","Client Contact Department","Client Name","1st Line of the Address",
"2nd Line of the Address","City","County","Postcode","Company Number", "VAT Number","Client Bank","Sort Code","Client Bank A/c No","End Date",
"Client Contact Start Date","Client Contact First Name","Client Contact Last Name","Client Contact 1st Line of the Address","Client Contact 2nd Line of the Address",
"Client Contact City","Client Contact County","Client Contact Postcode","Client Contact Telephone Number","Client Contact Mobile Number","Client Contact Email Address"};
using (SpreadsheetDocument spreadsheetDocument = SpreadsheetDocument.Create(System.IO.Path.Combine(HttpContext.Current.Server.MapPath("~/Upload"), "ClientTemplate.xlsx"), SpreadsheetDocumentType.Workbook))
{
SheetData sheetData = new SheetData();
Row row = new Row();
Row titleRow = new Row { RowIndex = (UInt32)1 };
//-------------------------Field Type----------------------------------
titleRow.AppendChild(CreateTextCell("A", ListOfExportFieldName[0], 1));
titleRow.AppendChild(CreateTextCell("B", ListOfExportFieldName[1], 1));
titleRow.AppendChild(CreateTextCell("C", ListOfExportFieldName[2], 1));
titleRow.AppendChild(CreateTextCell("D", ListOfExportFieldName[3], 1));
titleRow.AppendChild(CreateTextCell("E", ListOfExportFieldName[4], 1));
titleRow.AppendChild(CreateTextCell("F", ListOfExportFieldName[5], 1));
titleRow.AppendChild(CreateTextCell("G", ListOfExportFieldName[6], 1));
titleRow.AppendChild(CreateTextCell("H", ListOfExportFieldName[7], 1));
titleRow.AppendChild(CreateTextCell("I", ListOfExportFieldName[8], 1));
titleRow.AppendChild(CreateTextCell("J", ListOfExportFieldName[9], 1));
titleRow.AppendChild(CreateTextCell("K", ListOfExportFieldName[10], 1));
titleRow.AppendChild(CreateTextCell("L", ListOfExportFieldName[11], 1));
titleRow.AppendChild(CreateTextCell("M", ListOfExportFieldName[12], 1));
titleRow.AppendChild(CreateTextCell("N", ListOfExportFieldName[13], 1));
titleRow.AppendChild(CreateTextCell("O", ListOfExportFieldName[14], 1));
titleRow.AppendChild(CreateTextCell("P", ListOfExportFieldName[15], 1));
titleRow.AppendChild(CreateTextCell("Q", ListOfExportFieldName[16], 1));
titleRow.AppendChild(CreateTextCell("R", ListOfExportFieldName[17], 1));
titleRow.AppendChild(CreateTextCell("S", ListOfExportFieldName[18], 1));
titleRow.AppendChild(CreateTextCell("T", ListOfExportFieldName[19], 1));
titleRow.AppendChild(CreateTextCell("U", ListOfExportFieldName[20], 1));
titleRow.AppendChild(CreateTextCell("V", ListOfExportFieldName[21], 1));
titleRow.AppendChild(CreateTextCell("W", ListOfExportFieldName[22], 1));
titleRow.AppendChild(CreateTextCell("X", ListOfExportFieldName[23], 1));
titleRow.AppendChild(CreateTextCell("Y", ListOfExportFieldName[24], 1));
titleRow.AppendChild(CreateTextCell("Z", ListOfExportFieldName[25], 1));
titleRow.AppendChild(CreateTextCell("AA",ListOfExportFieldName[26], 1));
titleRow.AppendChild(CreateTextCell("AB",ListOfExportFieldName[27], 1));
titleRow.AppendChild(CreateTextCell("AC",ListOfExportFieldName[28], 1));
titleRow.AppendChild(CreateTextCell("AD",ListOfExportFieldName[29], 1));
titleRow.AppendChild(CreateTextCell("AE",ListOfExportFieldName[30], 1));
titleRow.AppendChild(CreateTextCell("AF",ListOfExportFieldName[31], 1));
// Append Row to SheetData
sheetData.AppendChild(titleRow);
DataTable dt = GetRecordFromSiteMaster(strNotificationFrequencyId, strNotificationFrequency, NotificationTime, BillingCurrencyId, strBillingCurrency, ContractTypeId, ContractType, DepartmentId, Department);
if (dt != null)
{
for (int i = 0; i < dt.Rows.Count; i++)
{
Row Subrow = new Row();
Subrow.AppendChild(CreateTextCell("A", Convert.ToString(dt.Rows[i]["NotificationFrequencyId"]), i + 2));
Subrow.AppendChild(CreateTextCell("B", Convert.ToString(dt.Rows[i]["Notification Frequency"]), i + 2));
Subrow.AppendChild(CreateTextCell("C", Convert.ToString(dt.Rows[i]["Notification Time"]), i + 2));
Subrow.AppendChild(CreateTextCell("D", Convert.ToString(dt.Rows[i]["BillingCurrencyId"]), i + 2));
Subrow.AppendChild(CreateTextCell("E", Convert.ToString(dt.Rows[i]["Billing Currency"]), i + 2));
Subrow.AppendChild(CreateTextCell("F", Convert.ToString(dt.Rows[i]["ContractTypeId"]), i + 2));
Subrow.AppendChild(CreateTextCell("G", Convert.ToString(dt.Rows[i]["Contract Type"]), i + 2));
Subrow.AppendChild(CreateTextCell("H", Convert.ToString(dt.Rows[i]["ClientContactDepartmentId"]), i + 2));
Subrow.AppendChild(CreateTextCell("I", Convert.ToString(dt.Rows[i]["Client Contact Department"]), i + 2));
// Append Row to SheetData
sheetData.AppendChild(Subrow);
}
}
// Add a WorkbookPart to the document.
WorkbookPart workbookpart = spreadsheetDocument.AddWorkbookPart();
workbookpart.Workbook = new Workbook();
Worksheet sheet1 = new Worksheet();
//-------------------Column Size---------------------
Columns columns = new Columns();
uint ival = 1;
foreach (string value in ListOfExportFieldName)
{
columns.Append(CreateColumnSize(1, ival, 28));
ival++;
}
sheet1.Append(columns);
//-------------------Column Size---------------------
sheet1.Append(sheetData);
// Add a WorksheetPart to the WorkbookPart.
WorksheetPart worksheetPart = workbookpart.AddNewPart<WorksheetPart>();
worksheetPart.Worksheet = sheet1;
// Add Sheets to the Workbook.
Sheets sheets = spreadsheetDocument.WorkbookPart.Workbook.AppendChild<Sheets>(new Sheets());
// Append a new worksheet and associate it with the workbook.
Sheet sheet = new Sheet()
{
Id = spreadsheetDocument.WorkbookPart.GetIdOfPart(worksheetPart),
SheetId = 1,
Name = "ClientTemplate"
};
sheets.Append(sheet);
// Close the document.
spreadsheetDocument.Close();
}
return true;
}
public bool ImportExcel(string strSiteid, string strNotificationId, string DepartmentId)
{
if (System.IO.File.Exists(System.IO.Path.Combine(HttpContext.Current.Server.MapPath("~/Upload"), "SiteTemplate.xlsx")))
{
System.IO.File.Delete(System.IO.Path.Combine(HttpContext.Current.Server.MapPath("~/Upload"), "SiteTemplate.xlsx"));
}
string[] ListOfExportFieldType = new string[] { "Auto Generated from system", "Auto Generated from system", "Auto Generated from system", "Auto Generated from system", "Auto Generated from system"
, "Auto Generated from system","Manual Entry Field","Manual Entry Field","Manual Entry Field","Manual Entry Field","Manual Entry Field","Manual Entry Field"
,"Manual Entry Field","Manual Entry Field","Manual Entry Field","Manual Entry Field","Manual Entry Field","Manual Entry Field","Manual Entry Field"
,"Manual Entry Field","Manual Entry Field","Manual Entry Field","Manual Entry Field","Manual Entry Field"};
string[] ListOfExportFieldName = new string[] { "ClientId", "Client", "BillingFrequencyId", "Billing Frequency"
, "ConsumerDepartmentId","Consumer Department","Network Name","Billing Upto","Billing Date","Fixed Bill Notice","Bank Name","Bank Sort Code",
"Bank Account Number","OIN / SUN Number","Manual Review of Bill","Contact Name","Telephone Number","Mobile Number","Email Address","Number of Domestic Properties",
"Number of Commercial Properties","Number of Bulk Supplies","End Date","Client Reference Number"};
using (SpreadsheetDocument spreadsheetDocument = SpreadsheetDocument.Create(System.IO.Path.Combine(HttpContext.Current.Server.MapPath("~/Upload"), "SiteTemplate.xlsx"), SpreadsheetDocumentType.Workbook))
{
SheetData sheetData = new SheetData();
Row row = new Row();
Row titleRow = new Row { RowIndex = (UInt32)1 };
//-------------------------Field Type----------------------------------
titleRow.AppendChild(CreateTextCell("A", ListOfExportFieldType[0], 1));
titleRow.AppendChild(CreateTextCell("B", ListOfExportFieldType[1], 1));
titleRow.AppendChild(CreateTextCell("C", ListOfExportFieldType[2], 1));
titleRow.AppendChild(CreateTextCell("D", ListOfExportFieldType[3], 1));
titleRow.AppendChild(CreateTextCell("E", ListOfExportFieldType[4], 1));
titleRow.AppendChild(CreateTextCell("F", ListOfExportFieldType[5], 1));
titleRow.AppendChild(CreateTextCell("G", ListOfExportFieldType[6], 1));
titleRow.AppendChild(CreateTextCell("H", ListOfExportFieldType[7], 1));
titleRow.AppendChild(CreateTextCell("I", ListOfExportFieldType[8], 1));
titleRow.AppendChild(CreateTextCell("J", ListOfExportFieldType[9], 1));
titleRow.AppendChild(CreateTextCell("K", ListOfExportFieldType[10], 1));
titleRow.AppendChild(CreateTextCell("L", ListOfExportFieldType[11], 1));
titleRow.AppendChild(CreateTextCell("M", ListOfExportFieldType[12], 1));
titleRow.AppendChild(CreateTextCell("N", ListOfExportFieldType[13], 1));
titleRow.AppendChild(CreateTextCell("O", ListOfExportFieldType[14], 1));
titleRow.AppendChild(CreateTextCell("P", ListOfExportFieldType[15], 1));
titleRow.AppendChild(CreateTextCell("Q", ListOfExportFieldType[16], 1));
titleRow.AppendChild(CreateTextCell("R", ListOfExportFieldType[17], 1));
titleRow.AppendChild(CreateTextCell("S", ListOfExportFieldType[18], 1));
titleRow.AppendChild(CreateTextCell("T", ListOfExportFieldType[19], 1));
titleRow.AppendChild(CreateTextCell("U", ListOfExportFieldType[20], 1));
titleRow.AppendChild(CreateTextCell("V", ListOfExportFieldType[21], 1));
titleRow.AppendChild(CreateTextCell("W", ListOfExportFieldType[22], 1));
titleRow.AppendChild(CreateTextCell("X", ListOfExportFieldType[23], 1));
// Append Row to SheetData
sheetData.AppendChild(titleRow);
//-------------------------Field Name----------------------------------
row.AppendChild(CreateTextCell("A", ListOfExportFieldName[0], 2));
row.AppendChild(CreateTextCell("B", ListOfExportFieldName[1], 2));
row.AppendChild(CreateTextCell("C", ListOfExportFieldName[2], 2));
row.AppendChild(CreateTextCell("D", ListOfExportFieldName[3], 2));
row.AppendChild(CreateTextCell("E", ListOfExportFieldName[4], 2));
row.AppendChild(CreateTextCell("F", ListOfExportFieldName[5], 2));
row.AppendChild(CreateTextCell("G", ListOfExportFieldName[6], 2));
row.AppendChild(CreateTextCell("H", ListOfExportFieldName[7], 2));
row.AppendChild(CreateTextCell("I", ListOfExportFieldName[8], 2));
row.AppendChild(CreateTextCell("J", ListOfExportFieldName[9], 2));
row.AppendChild(CreateTextCell("K", ListOfExportFieldName[10], 2));
row.AppendChild(CreateTextCell("L", ListOfExportFieldName[11], 2));
row.AppendChild(CreateTextCell("M", ListOfExportFieldName[12], 2));
row.AppendChild(CreateTextCell("N", ListOfExportFieldName[13], 2));
row.AppendChild(CreateTextCell("O", ListOfExportFieldName[14], 2));
row.AppendChild(CreateTextCell("P", ListOfExportFieldName[15], 2));
row.AppendChild(CreateTextCell("Q", ListOfExportFieldName[16], 2));
row.AppendChild(CreateTextCell("R", ListOfExportFieldName[17], 2));
row.AppendChild(CreateTextCell("S", ListOfExportFieldName[18], 2));
row.AppendChild(CreateTextCell("T", ListOfExportFieldName[19], 2));
row.AppendChild(CreateTextCell("U", ListOfExportFieldName[20], 2));
row.AppendChild(CreateTextCell("V", ListOfExportFieldName[21], 2));
row.AppendChild(CreateTextCell("W", ListOfExportFieldName[22], 2));
row.AppendChild(CreateTextCell("X", ListOfExportFieldName[23], 2));
// Append Row to SheetData
sheetData.AppendChild(row);
DataTable dt = GetRecordFromSiteMaster(strSiteid, strNotificationId, DepartmentId);
if (dt != null)
{
for (int i = 0; i < dt.Rows.Count; i++)
{
Row Subrow = new Row();
Subrow.AppendChild(CreateTextCell("A", Convert.ToString(dt.Rows[i]["ClientId"]), i + 3));
Subrow.AppendChild(CreateTextCell("B", Convert.ToString(dt.Rows[i]["Client"]), i + 3));
Subrow.AppendChild(CreateTextCell("C", Convert.ToString(dt.Rows[i]["BillingFrequencyId"]), i + 3));
Subrow.AppendChild(CreateTextCell("D", Convert.ToString(dt.Rows[i]["Billing Frequency"]), i + 3));
Subrow.AppendChild(CreateTextCell("E", Convert.ToString(dt.Rows[i]["ConsumerDepartmentId"]), i + 3));
Subrow.AppendChild(CreateTextCell("F", Convert.ToString(dt.Rows[i]["Consumer Department"]), i + 3));
// Append Row to SheetData
sheetData.AppendChild(Subrow);
}
}
// Add a WorkbookPart to the document.
WorkbookPart workbookpart = spreadsheetDocument.AddWorkbookPart();
workbookpart.Workbook = new Workbook();
Worksheet sheet1 = new Worksheet();
//-------------------Column Size---------------------
Columns columns = new Columns();
uint ival = 1;
foreach (string value in ListOfExportFieldType)
{
columns.Append(CreateColumnSize(1, ival, 28));
ival++;
}
sheet1.Append(columns);
//-------------------Column Size---------------------
sheet1.Append(sheetData);
// Add a WorksheetPart to the WorkbookPart.
WorksheetPart worksheetPart = workbookpart.AddNewPart<WorksheetPart>();
worksheetPart.Worksheet = sheet1;
// Add Sheets to the Workbook.
Sheets sheets = spreadsheetDocument.WorkbookPart.Workbook.AppendChild<Sheets>(new Sheets());
// Append a new worksheet and associate it with the workbook.
Sheet sheet = new Sheet()
{
Id = spreadsheetDocument.WorkbookPart.GetIdOfPart(worksheetPart),
SheetId = 1,
Name = "SiteTemplate"
};
sheets.Append(sheet);
// Close the document.
spreadsheetDocument.Close();
}
return true;
}