/// <summary>
/// Convert Pivot Table
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
protected void Convert_to_PDF_Click(object sender, EventArgs e)
{
//Step 1 : Instantiate the spreadsheet creation engine.
ExcelEngine excelEngine = new ExcelEngine();
//Step 2 : Instantiate the excel application object.
IApplication application = excelEngine.Excel;
application.DefaultVersion = ExcelVersion.Excel2016;
// Accessing workbook
IWorkbook workbook = application.Workbooks.Open(XlsIOHelper.ResolveApplicationDataPath("PivotLayout.xlsx", Request));
CreatePivotTable(workbook);
//Intialize the ExcelToPdfConverter class
ExcelToPdfConverter converter = new ExcelToPdfConverter(workbook);
//Intialize the ExcelToPdfConverterSettings class
ExcelToPdfConverterSettings settings = new ExcelToPdfConverterSettings();
//Set the Layout Options for the output Pdf page.
settings.LayoutOptions = LayoutOptions.FitSheetOnOnePage;
//Convert the Excel document to PDf
PdfDocument pdfDoc = converter.Convert(settings);
//Save the document as PDf
pdfDoc.Save("PivotLayout.pdf", Response, HttpReadType.Save);
pdfDoc.Close();
converter.Dispose();
workbook.Close();
excelEngine.Dispose();
}
private void button4_Click(object sender, EventArgs e)
{
string dBPath = Path.GetFullPath(@"../../Data/EmployeeData.mdb");
string query = "SELECT EmployeeID,FirstName,LastName,Title,HireDate,Extension,ReportsTo FROM [Employees]";
XlsIOHelper.MDBToExcel(dBPath, query, @"../../Data/MDBToExcel.xlsx");
}
protected void Button2_Click(object sender, EventArgs e)
{
#region Workbook Initialize
ExcelEngine excelEngine = new ExcelEngine();
//Get the path of the input file
string inputPath = XlsIOHelper.ResolveApplicationDataPath("ReplaceOptions.xlsx", Request);
IWorkbook workbook = excelEngine.Excel.Workbooks.Open(inputPath, ExcelOpenType.Automatic);
IWorksheet sheet = workbook.Worksheets[0];
ExcelFindOptions options = ExcelFindOptions.None;
if (Check1.Checked == true)
{
options |= ExcelFindOptions.MatchCase;
}
if (Check2.Checked == true)
{
options |= ExcelFindOptions.MatchEntireCellContent;
}
sheet.Replace(FindList.SelectedItem.ToString(), textbox1.Text, options);
//Saving the workbook to disk.
workbook.SaveAs("ReplaceOptions.xlsx", Response, ExcelDownloadType.PromptDialog, ExcelHttpContentType.Excel2016);
workbook.Close();
excelEngine.Dispose();
#endregion
}
/// <summary>
/// Create Pivot Table
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
protected void Create_Pivot_Table_Click(object sender, EventArgs e)
{
//Step 1 : Instantiate the spreadsheet creation engine.
ExcelEngine excelEngine = new ExcelEngine();
//Step 2 : Instantiate the excel application object.
IApplication application = excelEngine.Excel;
application.DefaultVersion = ExcelVersion.Excel2016;
// Accessing workbook
IWorkbook workbook = application.Workbooks.Open(XlsIOHelper.ResolveApplicationDataPath("PivotLayout.xlsx", Request));
CreatePivotTable(workbook);
IPivotTable pivotTable = workbook.Worksheets[1].PivotTables[0];
pivotTable.Layout();
//To view the pivot table inline formatting in MS Excel, we have to set the IsRefreshOnLoad property as true.
(workbook.PivotCaches[pivotTable.CacheIndex] as PivotCacheImpl).IsRefreshOnLoad = true;
//Save the document as Excel
workbook.SaveAs("PivotLayout.xlsx", Response, ExcelDownloadType.PromptDialog, ExcelHttpContentType.Excel2016);
workbook.Close();
excelEngine.Dispose();
}
private void button2_Click(object sender, EventArgs e)
{
List <Customers> customers = new List <Customers>()
{
new Customers()
{
SalesPerson = "Jim Halpert",
SalesJanJune = 34001,
SalesJulyDec = 65001,
Change = 91
},
new Customers()
{
SalesPerson = "Karen Fillippelli",
SalesJanJune = 34002,
SalesJulyDec = 65002,
Change = 92
},
new Customers()
{
SalesPerson = "Phyllis Lapin",
SalesJanJune = 34003,
SalesJulyDec = 65003,
Change = 93
},
};
XlsIOHelper.CollectionObjToExcel(customers, Path.GetFullPath(@"../../Data/CollectionObjToExcel.xlsx"));
}
private void button1_Click(object sender, EventArgs e)
{
object[] expenseArray = new object[14] {
"Paul Pogba", 469.00d, 263.00d, 131.00d, 139.00d, 474.00d, 253.00d, 467.00d, 142.00d, 417.00d, 324.00d, 328.00d, 497.00d, "=SUM(B11:M11)"
};
XlsIOHelper.ImportArrayToExcel(Path.GetFullPath(@"../../Data/ArrayToExcel.xlsx"), expenseArray);
}
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
CheckBoxList1.Items[0].Selected = true;
CheckBoxList1.Items[2].Selected = true;
CheckBoxList1.Items[4].Selected = true;
}
// Create the DataSet
ds = new DataSet();
string path = XlsIOHelper.ResolveApplicationDataPath("Database.mdb", Request);
dataDirectory = new DirectoryInfo(path);
conString = @"Provider=Microsoft.JET.OLEDB.4.0;" + @"data source=" + dataDirectory.FullName;
// Create an open the connection
OleDbConnection conn = new OleDbConnection(conString);
conn.Open();
// Create the adapter and fill the DataSet
OleDbCommand command = new OleDbCommand(@"SELECT Min(Date) as MinDate, Max(Date) as MaxDate FROM StockData", conn);
OleDbDataAdapter adapter = new OleDbDataAdapter(command);
adapter.Fill(ds);
DateTime minDate = DateTime.Parse(ds.Tables[0].Rows[0]["MinDate"].ToString().Trim(), CultureInfo.InvariantCulture);
DateTime maxDate = DateTime.Parse(ds.Tables[0].Rows[0]["MaxDate"].ToString().Trim(), CultureInfo.InvariantCulture);
// Close the connection
conn.Close();
}
public void GenerateXls(object selctedcountry)
{
//New instance of XlsIO is created.[Equivalent to launching Microsoft Excel with no workbooks open].
//Step 1 : Instantiate the spreadsheet creation engine.
ExcelEngine excelEngine = new ExcelEngine();
//Step 2 : Instantiate the excel application object.
IApplication application = excelEngine.Excel;
//Set the Workbook version
application.DefaultVersion = ExcelVersion.Excel2016;
//Create the workbook with default sheet
IWorkbook workbook = application.Workbooks.Create();
//Get the 1st sheet from the workbook
IWorksheet sheet = workbook.Worksheets[0];
//Get the DataBase Path.
string dbPath = XlsIOHelper.ResolveApplicationDataBasePath("Northwind.mdb", Request);
//connection string for DataSource
string ConnectionString = "OLEDB;Provider=Microsoft.JET.OLEDB.4.0;Password=\"\";User ID=Admin;Data Source=" + dbPath;
//query for the datasource
string query = null;
if (selctedcountry != null)
{
query = "select * from Customers where country='" + selctedcountry + "'";
}
else
{
query = "select * from Customers";
}
//Add the connection to workbook
IConnection Connection = workbook.Connections.Add("Connection1", "Sample connection with MsAccess", ConnectionString, query, ExcelCommandType.Sql);
//Add the QueryTable to sheet object
sheet.ListObjects.AddEx(ExcelListObjectSourceType.SrcQuery, Connection, sheet.Range["C3"]);
//Refresh the Connection for include the data
if (this.refresh.Checked)
{
try
{
sheet.ListObjects[0].Refresh();
sheet.UsedRange.AutofitColumns();
}
catch (Exception ex)
{
Response.Write(ex.Message);
}
}
//Save the workbook to disk.
workbook.SaveAs("Sample.xlsx", Response, ExcelDownloadType.PromptDialog, ExcelHttpContentType.Excel2016);
//Close the workbook.
workbook.Close();
excelEngine.Dispose();
}
private IList <Brands> GetVehicleDetails()
{
XmlSerializer deserializer = new XmlSerializer(typeof(BrandObjects));
string resourcePath = XlsIOHelper.ResolveApplicationDataPath("ExportData.xml", Request);
TextReader textReader = new StreamReader(resourcePath);
BrandObjects brands = (BrandObjects)deserializer.Deserialize(textReader);
List <Brands> list = new List <Brands>();
string brandName = brands.BrandObject[0].BrandName;
string vehicleType = brands.BrandObject[0].VahicleType;
string modelName = brands.BrandObject[0].ModelName;
Brands brand = new Brands(brandName);
brand.VehicleTypes = new List <VehicleTypes>();
VehicleTypes vehicle = new VehicleTypes(vehicleType);
vehicle.Models = new List <Model>();
Model model = new Model(modelName);
brand.VehicleTypes.Add(vehicle);
list.Add(brand);
foreach (BrandObject brandObj in brands.BrandObject)
{
if (brandName == brandObj.BrandName)
{
if (vehicleType == brandObj.VahicleType)
{
vehicle.Models.Add(new Model(brandObj.ModelName));
continue;
}
else
{
vehicle = new VehicleTypes(brandObj.VahicleType);
vehicle.Models = new List <Model>();
vehicle.Models.Add(new Model(brandObj.ModelName));
brand.VehicleTypes.Add(vehicle);
vehicleType = brandObj.VahicleType;
}
continue;
}
else
{
brand = new Brands(brandObj.BrandName);
vehicle = new VehicleTypes(brandObj.VahicleType);
vehicle.Models = new List <Model>();
vehicle.Models.Add(new Model(brandObj.ModelName));
brand.VehicleTypes = new List <VehicleTypes>();
brand.VehicleTypes.Add(vehicle);
vehicleType = brandObj.VahicleType;
list.Add(brand);
brandName = brandObj.BrandName;
}
}
textReader.Close();
return(list);
}
private DataTable GetDataTable()
{
DataSet customersDataSet = new DataSet();
//Get the path of the input file
string inputXmlPath = XlsIOHelper.ResolveApplicationDataPath("Customers.xml", Request);
customersDataSet.ReadXml(inputXmlPath);
DataTable dataTable = new DataTable();
dataTable = customersDataSet.Tables[0];
dataTable.Columns.RemoveAt(4);
return dataTable;
}
private void button7_Click(object sender, EventArgs e)
{
DataTable dt = XlsIOHelper.ExcelToDataTable(@"../../Data/CSVToExcel.xlsx");
this.dataGridView1.DataSource = dt;
dataGridView1.AlternatingRowsDefaultCellStyle.BackColor = Color.White;
dataGridView1.RowsDefaultCellStyle.BackColor = Color.LightBlue;
dataGridView1.ColumnHeadersDefaultCellStyle.Font = new System.Drawing.Font("Tahoma", 9F, ((System.Drawing.FontStyle)(System.Drawing.FontStyle.Bold)));
dataGridView1.ForeColor = Color.Black;
dataGridView1.BorderStyle = BorderStyle.None;
}
protected void Button2_Click(object sender, EventArgs e)
{
//Step 1 : Instantiate the spreadsheet creation engine.
ExcelEngine excelEngine = new ExcelEngine();
//Step 2 : Instantiate the excel application object.
IApplication application = excelEngine.Excel;
//A new workbook is created.[Equivalent to creating a new workbook in Microsoft Excel]
//The new workbook will have 2 worksheets
IWorkbook workbook = application.Workbooks.Open(XlsIOHelper.ResolveApplicationDataPath("Template.xls", Request));
workbook.Version = ExcelVersion.Excel97to2003;
workbook.SaveAs("Template.xls", Response, ExcelDownloadType.PromptDialog);
}
private void button6_Click(object sender, EventArgs e)
{
//Create a dataset from XML file
DataSet customersDataSet = new DataSet();
customersDataSet.ReadXml(Path.GetFullPath(@"../../Data/Employees.xml"));
//Create datatable from the dataset
DataTable dataTable = new DataTable();
dataTable = customersDataSet.Tables[1];
XlsIOHelper.DataTableToExcel(dataTable, @"../../Data/DataTableToExcel.xlsx");
}
protected void Button1_Click(object sender, EventArgs e)
{
//Step 1 : Instantiate the spreadsheet creation engine.
ExcelEngine excelEngine = new ExcelEngine();
//Step 2 : Instantiate the excel application object.
IApplication application = excelEngine.Excel;
//Open an existing spreadsheet which will be used as a template for generating the new spreadsheet.
//After opening, the workbook object represents the complete in-memory object model of the template spreadsheet.
IWorkbook workbook = excelEngine.Excel.Workbooks.Open(XlsIOHelper.ResolveApplicationDataPath(@"ReplaceOptions.xlsx", Request), ExcelOpenType.Automatic);
workbook.SaveAs("InputTemplate.xlsx", Response, ExcelDownloadType.PromptDialog);
workbook.Close();
}
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
try
{
AppDomain.CurrentDomain.SetData("SQLServerCompactEditionUnderWebHosting", true);
using (SqlCeConnection sqlCeConnection = new SqlCeConnection())
{
if (sqlCeConnection.ServerVersion.StartsWith("3.5"))
{
sqlCeConnection.ConnectionString = "Data Source = " + XlsIOHelper.ResolveApplicationDataBasePath("NorthwindIO_3.5.sdf", Request);
}
else
{
sqlCeConnection.ConnectionString = "Data Source = " + XlsIOHelper.ResolveApplicationDataBasePath("NorthwindIO.sdf", Request);
}
connString = sqlCeConnection.ConnectionString;
using (SqlCeDataAdapter sqlCeAdapter = new SqlCeDataAdapter("select OrderID from SyncOrders Order By OrderID", sqlCeConnection))
{
DataSet ds = new DataSet();
sqlCeAdapter.Fill(ds);
if (ds.Tables.Count > 0)
{
this.DropDownList1.DataSource = ds.Tables[0];
this.DropDownList1.DataTextField = "OrderID";
this.DropDownList1.DataBind();
}
}
}
}
catch (Exception Ex)
{
// Shows the Message box with Exception message, if an exception is thrown.
this.Response.Write(Ex.Message);
}
}
}
protected void Button1_Click(object sender, EventArgs e)
{
DateTime MaxDate = new DateTime(2008, 1, 29);
DateTime MinDate = new DateTime(2008, 1, 1);
if (Calendar2.SelectedDate > MaxDate || Calendar2.SelectedDate < MinDate || Calendar1.SelectedDate < MinDate || Calendar1.SelectedDate > MaxDate)
{
ClientScript.RegisterClientScriptBlock(this.GetType(), "alert", "alert('Selected Date is not valid.Please select the date between 1st Jan 2008 and 29th Jan 2008!');", true);
}
else
{
ExcelEngine excelEngine = new ExcelEngine();
IApplication application = excelEngine.Excel;
// A new workbook is created.[Equivalent to creating a new workbook in Microsoft Excel]
// The number of default worksheets is the application setting in Microsoft Excel.
myWorkbook = excelEngine.Excel.Workbooks.Add(XlsIOHelper.ResolveApplicationDataPath("Template.xls", Request));
ListItem[] StockList;// = new ListItem();
// ArrayList[] StockList;
int index = 0;
foreach (ListItem lItem in CheckBoxList1.Items)
{
if (lItem.Selected)
{
index++;
}
}
StockList = new ListItem[index];
int stockItem = 0;
foreach (ListItem lItem in CheckBoxList1.Items)
{
if (lItem.Selected)
{
StockList[stockItem] = lItem;
stockItem++;
}
}
IChart chart = myWorkbook.Worksheets[1].Charts[0];
chart.PrimaryCategoryAxis.NumberFormat = "m/d/yyyy";
chart.PrimaryValueAxis.NumberFormat = "\"$\"#,##0.00";
chart.SecondaryValueAxis.NumberFormat = "\"$\"#,##0.00";
chart.SecondaryValueAxis.TickLabelPosition = ExcelTickLabelPosition.TickLabelPosition_High;
// Adding new worksheets in workbook's sheets collection
for (int count = 1; count < StockList.Length; count++)
{
myWorkbook.Worksheets.AddCopyAfter(myWorkbook.Worksheets[1], myWorkbook.Worksheets[0]);
}
// Adding hyperlinks to menu sheet
IWorksheet menu_sheet = myWorkbook.Worksheets[0];
int InsertIndex = DEF_FST_ROW_NUM_SC - 3;
menu_sheet.HyperLinks.RemoveAt(0);
menu_sheet.Range["G21"].Text = "";
for (int count = 0; count < StockList.Length; count++)
{
menu_sheet.InsertRow(InsertIndex, 2, ExcelInsertOptions.FormatAsBefore);
IHyperLink report_hyperlink = menu_sheet.HyperLinks.Add(menu_sheet.Range["G" + InsertIndex + ":I" + InsertIndex]);
report_hyperlink.Type = ExcelHyperLinkType.Workbook;
report_hyperlink.Address = StockList[count].Text + "!A1";
report_hyperlink.TextToDisplay = StockList[count].Text;
InsertIndex += 2;
}
// Creating Stock report
int itemIndex = 1;
foreach (Object StockListItem in StockList)
{
CreateStockReport(StockListItem.ToString(), itemIndex);
FillAnalysisPortfolioSheet(StockListItem.ToString());
itemIndex += 1;
}
myWorkbook.Worksheets[0].Activate();
//Saving the workbook to disk.
if (rBtnXls.Checked == true)
{
myWorkbook.Version = ExcelVersion.Excel97to2003;
myWorkbook.SaveAs(reportDirectory + "\\Sample.xls", ExcelSaveType.SaveAsXLS, Response, ExcelDownloadType.PromptDialog);
}
else
{
myWorkbook.Version = ExcelVersion.Excel2016;
myWorkbook.SaveAs(reportDirectory + "\\Sample.xlsx", ExcelSaveType.SaveAsXLS, Response, ExcelDownloadType.PromptDialog, ExcelHttpContentType.Excel2016);
}
//No exception will be thrown if there are unsaved workbooks.
excelEngine.ThrowNotSavedOnDestroy = false;
excelEngine.Dispose();
}
}
private void button3_Click(object sender, EventArgs e)
{
XlsIOHelper.CSVToExcel(@"../../Data/TemplateSales.csv", @"../../Data/CSVToExcel.xlsx");
}
private void button5_Click(object sender, EventArgs e)
{
XlsIOHelper.DataGridViewToExcel(dataGridView1, @"../../Data/DataGridViewToExcel.xlsx");
}
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
try
{
OleDbConnection Oledb = new OleDbConnection();
Oledb.ConnectionString = "Provider=Microsoft.JET.OLEDB.4.0;Password=\"\";User ID=Admin;Data Source=" + XlsIOHelper.ResolveApplicationDataBasePath("Northwind.mdb", Request);
OleDbCommand Command = new OleDbCommand("select distinct country from Customers order by country", Oledb);
OleDbDataAdapter Adapter = new OleDbDataAdapter(Command);
DataSet Dataset = new DataSet();
Adapter.Fill(Dataset);
// Add Customer ID to the list box.
if (Dataset.Tables.Count > 0)
{
this.DropDownList1.DataSource = Dataset.Tables[0];
this.DropDownList1.DataTextField = "Country";
this.DropDownList1.DataBind();
}
}
catch (Exception ex)
{
this.Response.Write(ex.Message);
}
}
}
