// Save data to excel file
protected void ProcessButton_Click(object sender, EventArgs e)
{
try
{
if (Session["AsposeDynamicFormsdataTable"] != null)
{
DataTable dt = (DataTable)Session["AsposeDynamicFormsdataTable"];
if (dt != null)
{
if (dt.Rows.Count > 0)
{
DataRow[] drs = dt.Select("[Field ID] = '" + txtFieldId.Text + "'");
if (drs != null)
{
if (drs.Length > 0)
{
drs[0]["Field Type"] = ddlFieldType.SelectedValue.Trim();
drs[0]["Field ID"] = txtFieldId.Text.Trim();
drs[0]["Field Label Text"] = txtFieldLableText.Text.Trim();
drs[0]["Field Values"] = txtFieldValues.Text.Trim();
if (chkIsDisplay.Checked)
{
drs[0]["Is Display"] = "TRUE";
}
else
{
drs[0]["Is Display"] = "FALSE";
}
drs[0]["Sort ID"] = txtSortId.Text.Trim();
drs[0]["Modified By"] = UserId.ToString();
drs[0]["Modified On"] = DateTime.Now.ToString("MM-dd-YYYY");
dt.AcceptChanges();
}
else
{
DataRow dr = dt.NewRow();
dr["Field Type"] = ddlFieldType.SelectedValue.Trim();
dr["Field ID"] = txtFieldId.Text.Trim();
dr["Field Label Text"] = txtFieldLableText.Text.Trim();
dr["Field Values"] = txtFieldValues.Text.Trim();
if (chkIsDisplay.Checked)
{
dr["Is Display"] = "TRUE";
}
else
{
dr["Is Display"] = "FALSE";
}
dr["Sort ID"] = txtSortId.Text.Trim();
dr["Modified By"] = UserId.ToString();
dr["Modified On"] = DateTime.Now.ToString("MM-dd-YYYY");
dt.Rows.Add(dr);
}
Session["AsposeDynamicFormsdataTable"] = dt;
// Check for license and apply if exists
string licenseFile = Server.MapPath("~/App_Data/Aspose.Total.lic");
if (File.Exists(licenseFile))
{
License license = new License();
license.SetLicense(licenseFile);
}
//Creating a file stream containing the Excel file to be opened
FileStream fstream = new FileStream(Server.MapPath("~/DesktopModules/Aspose.DNN.DynamicFormGenerator.Excel/Docs/AsposeDynamicFormsDataFile.xlsx"), FileMode.Open, FileAccess.Read);
//Instantiating a Workbook object
//Opening the Excel file through the file stream
Workbook workbook = new Workbook(fstream);
//Accessing a worksheet using its sheet name
Worksheet worksheet = workbook.Worksheets["Settings"];
//Closing the file stream to free all resources
fstream.Close();
workbook.Worksheets.RemoveAt("Settings");
worksheet = workbook.Worksheets.Add("Settings");
worksheet.Cells.ImportDataTable(dt, true, "A1");
// Apply Hearder Row/First Row text to Bold
Cells.Style objStyle = new Cells.Style();
objStyle.Font.IsBold = true;
StyleFlag objStyleFlag = new StyleFlag();
objStyleFlag.FontBold = true;
worksheet.Cells.ApplyRowStyle(0, objStyle, objStyleFlag);
//Auto-fit all the columns
workbook.Worksheets["Data"].AutoFitColumns();
workbook.Save(Server.MapPath("~/DesktopModules/Aspose.DNN.DynamicFormGenerator.Excel/Docs/AsposeDynamicFormsDataFile.xlsx"), SaveFormat.Xlsx);
ClearFormButton_Click(sender, e);
}
}
}
}
ProcessButton.Text = "Add New Field";
}
catch (Exception exc)
{
success_msg.Visible = false;
error_msg.Visible = true;
error_msg.InnerText = exc.Message;
}
}
// Save data to excel file
protected void ProcessButton_Click(object sender, EventArgs e)
{
try
{
// Check for license and apply if exists
string licenseFile = Server.MapPath("~/App_Data/Aspose.Total.lic");
if (File.Exists(licenseFile))
{
License license = new License();
license.SetLicense(licenseFile);
}
//Creating a file stream containing the Excel file to be opened
FileStream fstream = new FileStream(Server.MapPath("~/DesktopModules/Aspose.DNN.DynamicFormGenerator.Excel/Docs/AsposeDynamicFormsDataFile.xlsx"), FileMode.Open, FileAccess.Read);
//Instantiating a Workbook object
//Opening the Excel file through the file stream
Workbook workbook = new Workbook(fstream);
//Accessing a worksheet using its sheet name
Worksheet worksheet = workbook.Worksheets["Data"];
Worksheet worksheet1 = workbook.Worksheets["Settings"];
//Exporting the contents of 7 rows and 2 columns starting from 1st cell to DataTable
DataTable dataTable = null;
if (worksheet.Cells.Rows.Count <= 0)
{
dataTable = worksheet.Cells.ExportDataTableAsString(0, 0, 1, 1, true);
}
else
{
dataTable = worksheet.Cells.ExportDataTableAsString(0, 0, worksheet.Cells.Rows.Count, worksheet.Cells.Columns.Count, true);
}
//Exporting the contents of 7 rows and 2 columns starting from 1st cell to DataTable
DataTable dataTable1 = worksheet1.Cells.ExportDataTableAsString(0, 0, worksheet1.Cells.Rows.Count, 10, true);
if (dataTable1 != null)
{
if (dataTable != null)
{
if (dataTable.Columns.Count <= 1)
{
dataTable.Columns.RemoveAt(0);
}
foreach (DataRow row in dataTable1.Rows)
{
if (!row[1].ToString().Trim().Equals("Title") && !row[1].ToString().Trim().Equals("Success"))
{
foreach (string strItem in row[2].ToString().Trim().Split(';'))
{
if (!strItem.Trim().Equals(""))
{
if (dataTable.Columns[strItem.Trim()] == null)
{
dataTable.Columns.Add(strItem.Trim());
}
}
}
}
}
}
}
//Closing the file stream to free all resources
fstream.Close();
DataRow dr = dataTable.NewRow();
foreach (Control ctrl in myPlaceHolder.Controls)
{
if (ctrl != null)
{
if (ctrl is TextBox)
{
dr[ctrl.ID] = ((TextBox)ctrl).Text.Trim();
continue;
}
if (ctrl is RadioButton)
{
if (((RadioButton)ctrl).Checked)
{
dr[ctrl.ID] = ((RadioButton)ctrl).Text.Trim();
continue;
}
}
if (ctrl is CheckBox)
{
if (((CheckBox)ctrl).Checked)
{
dr[ctrl.ID] = ((CheckBox)ctrl).Text.Trim();
continue;
}
}
if (ctrl is DropDownList)
{
dr[ctrl.ID] = ((DropDownList)ctrl).SelectedItem.Text.Trim();
continue;
}
}
}
dataTable.Rows.Add(dr);
workbook.Worksheets.RemoveAt("Data");
worksheet = workbook.Worksheets.Add("Data");
worksheet.Cells.ImportDataTable(dataTable, true, "A1");
// Apply Hearder Row/First Row text to Bold
Cells.Style objStyle = new Cells.Style();
objStyle.Font.IsBold = true;
StyleFlag objStyleFlag = new StyleFlag();
objStyleFlag.FontBold = true;
worksheet.Cells.ApplyRowStyle(0, objStyle, objStyleFlag);
//Auto-fit all the columns
workbook.Worksheets["Data"].AutoFitColumns();
workbook.Save(Server.MapPath("~/DesktopModules/Aspose.DNN.DynamicFormGenerator.Excel/Docs/AsposeDynamicFormsDataFile.xlsx"), SaveFormat.Xlsx);
error_msg.Visible = false;
success_msg.Visible = true;
}
catch (Exception exc)
{
success_msg.Visible = false;
error_msg.Visible = true;
error_msg.InnerText = exc.Message;
}
}
//Export data file as per selected export type
protected void ProcessButton_Click(object sender, EventArgs e)
{
try
{
// Check for license and apply if exists
string licenseFile = Server.MapPath("~/App_Data/Aspose.Total.lic");
if (File.Exists(licenseFile))
{
License license = new License();
license.SetLicense(licenseFile);
}
String connString = txtConString.Text;
//**STRING UPDATED**
String Query = "SELECT * FROM " + ddlTables.SelectedValue + " ORDER BY 1";
switch (ddlSource.SelectedValue)
{
case "0":
if (ddlTables.Items.Count <= 0)
{
success_msg.Visible = false;
error_msg.Visible = true;
error_msg.InnerHtml = "There are no tables to select data.";
return;
}
Query = "SELECT * FROM " + ddlTables.SelectedValue + " ORDER BY 1";
break;
case "1":
if (ddlViews.Items.Count <= 0)
{
success_msg.Visible = false;
error_msg.Visible = true;
error_msg.InnerHtml = "There are no views to select data.";
return;
}
Query = "SELECT * FROM " + ddlViews.SelectedValue;
break;
case "2":
if (txtCustomQuery.Text.Trim().Equals(""))
{
success_msg.Visible = false;
error_msg.Visible = true;
error_msg.InnerHtml = "Please enter custom query.";
return;
}
Query = txtCustomQuery.Text.Trim();
break;
}
success_msg.Visible = false;
error_msg.Visible = false;
using (var cn = new SqlConnection(connString))
{
//opening database connection
cn.Open();
//creating datatable, sqlCommand and sqlReader objects
DataTable dt = new DataTable();
SqlCommand cmd = new SqlCommand(Query, cn);
SqlDataReader myReader = cmd.ExecuteReader();
dt.Load(myReader);
//creating leadoptions for excel file
LoadOptions loadOptions = new LoadOptions(LoadFormat.Xlsx);
loadOptions.CheckExcelRestriction = false;
//Instantiate a new Workbook with Load options to prevent max cell string length of 32k error
Workbook book = new Workbook(Server.MapPath("~/App_Data/Sample.xlsx"), loadOptions);
//Clear all the worksheets
book.Worksheets.Clear();
//Add a new Sheet "Data";
Worksheet sheet = book.Worksheets.Add("Exported Data");
//Data Table import to the worksheet, inserting from A1 cell of sheet
sheet.Cells.ImportDataTable(dt, true, "A1");
// Apply Hearder Row/First Row text to Bold
Cells.Style objStyle = new Cells.Style();
objStyle.Font.IsBold = true;
StyleFlag objStyleFlag = new StyleFlag();
objStyleFlag.FontBold = true;
sheet.Cells.ApplyRowStyle(0, objStyle, objStyleFlag);
// Fit columns width to contents
sheet.AutoFitColumns();
//Create unique file name
string fileName = System.Guid.NewGuid().ToString() + "." + ExportTypeDropDown.SelectedValue;
//Save workbook as per export type
book.Save(this.Response, fileName, ContentDisposition.Attachment, GetSaveFormat(ExportTypeDropDown.SelectedValue));
Response.End();
}
error_msg.Visible = false;
success_msg.Visible = true;
success_msg.InnerText = "Data exported successfully.";
}
catch (Exception exc)
{
success_msg.Visible = false;
error_msg.Visible = true;
error_msg.InnerText = exc.Message;
}
}
//Export data file as per selected export type
protected void ProcessButton_Click(object sender, EventArgs e)
{
try
{
// Check for license and apply if exists
string licenseFile = Server.MapPath("~/App_Data/Aspose.Total.lic");
if (File.Exists(licenseFile))
{
License license = new License();
license.SetLicense(licenseFile);
}
String connString = txtConString.Text;
//**STRING UPDATED**
String Query = "SELECT * FROM " + ddlTables.SelectedValue + " ORDER BY 1";
switch (ddlSource.SelectedValue)
{
case "0":
if (ddlTables.Items.Count <= 0)
{
lblMessage.Text = "There are no tables to select data.";
return;
}
Query = "SELECT * FROM " + ddlTables.SelectedValue + " ORDER BY 1";
break;
case "1":
if (ddlViews.Items.Count <= 0)
{
lblMessage.Text = "There are no views to select data.";
return;
}
Query = "SELECT * FROM " + ddlViews.SelectedValue;
break;
case "2":
if (txtCustomQuery.Text.Trim().Equals(""))
{
lblMessage.Text = "Please enter custom query.";
return;
}
Query = txtCustomQuery.Text.Trim();
break;
}
using (var cn = new SqlConnection(connString))
{
//opening database connection
cn.Open();
//creating datatable, sqlCommand and sqlReader objects
DataTable dt = new DataTable();
SqlCommand cmd = new SqlCommand(Query, cn);
SqlDataReader myReader = cmd.ExecuteReader();
dt.Load(myReader);
//creating leadoptions for excel file
LoadOptions loadOptions = new LoadOptions(LoadFormat.Xlsx);
loadOptions.CheckExcelRestriction = false;
//Instantiate a new Workbook with Load options to prevent max cell string length of 32k error
Workbook book = new Workbook(Server.MapPath("~/App_Data/Sample.xlsx"), loadOptions);
//Clear all the worksheets
book.Worksheets.Clear();
//Add a new Sheet "Data";
Worksheet sheet = book.Worksheets.Add("Exported Data");
//Data Table import to the worksheet, inserting from A1 cell of sheet
sheet.Cells.ImportDataTable(dt, true, "A1");
// Apply Hearder Row/First Row text to Bold
Cells.Style objStyle = new Cells.Style();
objStyle.Font.IsBold = true;
StyleFlag objStyleFlag = new StyleFlag();
objStyleFlag.FontBold = true;
sheet.Cells.ApplyRowStyle(0, objStyle, objStyleFlag);
// Fit columns width to contents
sheet.AutoFitColumns();
//Create unique file name
string fileName = System.Guid.NewGuid().ToString() + "." + ExportTypeDropDown.SelectedValue;
//Save workbook as per export type
book.Save(this.Response, fileName, ContentDisposition.Attachment, GetSaveFormat(ExportTypeDropDown.SelectedValue));
Response.End();
}
lblMessage.Text = "Data exported successfully.";
}
catch (Exception exc)
{
lblMessage.Text = exc.Message;
}
}
// Save data to excel file
protected void ProcessButton_Click(object sender, EventArgs e)
{
try
{
// Check for license and apply if exists
string licenseFile = Server.MapPath("~/App_Data/Aspose.Total.lic");
if (File.Exists(licenseFile))
{
License license = new License();
license.SetLicense(licenseFile);
}
//Creating a file stream containing the Excel file to be opened
FileStream fstream = new FileStream(Server.MapPath("~/DesktopModules/Aspose.DNN.DynamicFormGenerator.Excel/Docs/AsposeDynamicFormsDataFile.xlsx"), FileMode.Open, FileAccess.Read);
//Instantiating a Workbook object
//Opening the Excel file through the file stream
Workbook workbook = new Workbook(fstream);
//Accessing a worksheet using its sheet name
Worksheet worksheet = workbook.Worksheets["Data"];
DataTable dataTable = null;
if (worksheet.Cells.Rows.Count <= 0)
{
dataTable = worksheet.Cells.ExportDataTableAsString(0, 0, 1, 1, true);
}
else
{
dataTable = worksheet.Cells.ExportDataTableAsString(0, 0, worksheet.Cells.Rows.Count, worksheet.Cells.Columns.Count, true);
}
//Closing the file stream to free all resources
fstream.Close();
//Instantiate a new Workbook
Workbook book = new Workbook();
//Clear all the worksheets
book.Worksheets.Clear();
//Add a new Sheet "Data";
Worksheet sheet = book.Worksheets.Add("Data");
// import data in to sheet
sheet.Cells.ImportDataTable(dataTable, true, "A1");
// Apply Hearder Row/First Row text to Bold
Cells.Style objStyle = new Cells.Style();
objStyle.Font.IsBold = true;
StyleFlag objStyleFlag = new StyleFlag();
objStyleFlag.FontBold = true;
sheet.Cells.ApplyRowStyle(0, objStyle, objStyleFlag);
//Auto-fit all the columns
book.Worksheets[0].AutoFitColumns();
//Create unique file name
string fileName = System.Guid.NewGuid().ToString() + "." + ExportTypeDropDown.SelectedValue;
//Save workbook as per export type
book.Save(this.Response, fileName, ContentDisposition.Attachment, GetSaveFormat(ExportTypeDropDown.SelectedValue));
Response.End();
}
catch (Exception exc)
{
success_msg.Visible = false;
error_msg.Visible = true;
error_msg.InnerText = exc.Message;
}
}
// Save data to excel file
protected void ProcessButton_Click(object sender, EventArgs e)
{
try
{
// Check for license and apply if exists
string licenseFile = Server.MapPath("~/App_Data/Aspose.Total.lic");
if (File.Exists(licenseFile))
{
License license = new License();
license.SetLicense(licenseFile);
}
//Creating a file stream containing the Excel file to be opened
FileStream fstream = new FileStream(Server.MapPath("~/DesktopModules/Aspose.DNN.DynamicFormGenerator.Excel/Docs/AsposeDynamicFormsDataFile.xlsx"), FileMode.Open, FileAccess.Read);
//Instantiating a Workbook object
//Opening the Excel file through the file stream
Workbook workbook = new Workbook(fstream);
//Accessing a worksheet using its sheet name
Worksheet worksheet = workbook.Worksheets["Data"];
DataTable dataTable = null;
if (worksheet.Cells.Rows.Count <= 0)
{
dataTable = worksheet.Cells.ExportDataTableAsString(0, 0, 1, 1, true);
}
else
{
dataTable = worksheet.Cells.ExportDataTableAsString(0, 0, worksheet.Cells.Rows.Count, worksheet.Cells.Columns.Count, true);
}
//Closing the file stream to free all resources
fstream.Close();
//Instantiate a new Workbook
Workbook book = new Workbook();
//Clear all the worksheets
book.Worksheets.Clear();
//Add a new Sheet "Data";
Worksheet sheet = book.Worksheets.Add("Data");
// import data in to sheet
sheet.Cells.ImportDataTable(dataTable, true, "A1");
// Apply Hearder Row/First Row text to Bold
Cells.Style objStyle = new Cells.Style();
objStyle.Font.IsBold = true;
StyleFlag objStyleFlag = new StyleFlag();
objStyleFlag.FontBold = true;
sheet.Cells.ApplyRowStyle(0, objStyle, objStyleFlag);
//Auto-fit all the columns
book.Worksheets[0].AutoFitColumns();
//Create unique file name
string fileName = System.Guid.NewGuid().ToString() + "." + ExportTypeDropDown.SelectedValue;
//Save workbook as per export type
book.Save(this.Response, fileName, ContentDisposition.Attachment, GetSaveFormat(ExportTypeDropDown.SelectedValue));
Response.End();
}
catch (Exception exc)
{
success_msg.Visible = false;
error_msg.Visible = true;
error_msg.InnerText = exc.Message;
}
}
