public DataTable[] GetDataTablesFromExcelStream(Stream ExcelStream)
{
Aspose.Excel.License license = new Aspose.Excel.License();
license.SetLicense("Aspose.Excel.lic");
try
{
Excel excel = new Excel();
excel.Open(ExcelStream);
Worksheets sheets = excel.Worksheets;
DataTable[] tables = new DataTable[sheets.Count];
int index = 0;
foreach (Worksheet sheet in sheets)
{
Cells cells = sheet.Cells;
tables[index] = cells.ExportDataTableAsString(0, 0, cells.MaxDataRow + 1, cells.MaxDataColumn + 1);
tables[index++].TableName = sheet.Name;
}
return tables;
}
catch
{
return null;
}
}
protected void DownloadTemplate_Click(object sender, EventArgs e)
{
//Instantiate an instance of license and set the license file through its path
Aspose.Excel.License license = new Aspose.Excel.License();
license.SetLicense("Aspose.Excel.lic");
Excel excel = new Excel();
string path = MapPath("../..") + @"\templates\";
string designerFile = path + "UserCredentialsTemplate.xls";
excel.Open(designerFile);
excel.Save("User_Credentials.xls", SaveType.OpenInExcel, FileFormatType.Default, this.Response);
}
protected void Page_Load(object sender, EventArgs e)
{
Util util = new Util();
Hashtable State = (Hashtable)HttpRuntime.Cache[Session.SessionID];
if (util.CheckSessionTimeout(State, Response, "../../Default.aspx")) return;
try
{
//Instantiate an instance of license and set the license file through its path
Aspose.Excel.License license = new Aspose.Excel.License();
license.SetLicense("Aspose.Excel.lic");
Excel excel = new Excel();
string path = MapPath("../../") + @"\templates\";
string query = "";
string designerFile = path + "ViewTemplate.XLS";
excel.Open(designerFile);
query = "SELECT * FROM customers ";
DB db = new DB();
DataTable table = db.ViziAppsQuery(State,query);
Worksheet sheet = excel.Worksheets[0];
string[] column_names = new string[table.Columns.Count];
int col = 0;
foreach (DataColumn column in table.Columns)
{
column_names[col++] = column.ColumnName.Replace("_"," ");
}
sheet.Cells.ImportArray(column_names, 0, 0, false);
sheet.Cells.ImportDataTable(table, false, 1, 0);
col = 0;
foreach (DataColumn column in table.Columns)
{
sheet.AutoFitColumn(col++);
}
int index = excel.Styles.Add();
Aspose.Excel.Style style = excel.Styles[index];
style.Number = 22;
//set date time columns
col = 0;
foreach (DataColumn column in table.Columns)
{
string name = column.ColumnName.ToLower();
if (name.IndexOf("date") >= 0 || name.IndexOf("time") >= 0)
{
Range range = sheet.Cells.CreateRange(1,col,table.Rows.Count,1);
range.Style = style;
}
col++;
}
sheet.Name = "Customer List";
excel.Save("Customer_List.xls", SaveType.OpenInExcel, FileFormatType.Default, this.Response);
db.CloseViziAppsDatabase(State);
}
catch (Exception ex)
{
util.ProcessMainExceptions(State, Response, ex);
}
}
