/// <summary> /// export_data /// </summary> public static string export_data(data_table_export_format format, DataSet ds, string temp_directory) { string destination; string file_name; Random rnd = new Random(DateTime.Now.Millisecond); file_name = rnd.Next(10000, 1000000000) + "." + format.ToString(); destination = temp_directory + "\\" + file_name; if (format == data_table_export_format.xls) { #region Check Table Name For Excel Length Limit of 31 Characters foreach (DataTable objTable in ds.Tables) { if (objTable.TableName.Length > 31) { objTable.TableName = StringFunctions.Right(objTable.TableName, 31); } } #endregion #region Remove Secure Fields From Export try { foreach (DataTable objTable in ds.Tables) { lookagain : foreach (DataColumn col in objTable.Columns) { string strColName = col.ColumnName.ToLower(); if (strColName == "password" || strColName == "pass" || strColName == "exhibitorpassword" || strColName == "administratorpassword" || strColName == "customerpassword") { objTable.Columns.Remove(col.ColumnName); goto lookagain; } if (strColName == "username" || strColName == "user" || strColName == "exhibitorusername" || strColName == "administratorusername" || strColName == "customerusername") { objTable.Columns.Remove(col.ColumnName); goto lookagain; } } } } catch (Exception ex) { General.Debugging.Report.SendError("Error removing secured fields in Excel Spreadsheet for export", ex); } #endregion /* THE OLD WAY * string xml = WorkbookEngine.CreateWorkbook(ds); * System.IO.StreamWriter sw = new System.IO.StreamWriter(destination,false); * sw.Write(xml); * sw.Close(); */ xpOutputFormat outputFormat; outputFormat = xpOutputFormat.Excel8; _xportToolsDemo = new DS2XL(); //Check if we need to optimize export output for smaller export size. If not, then //export will be optimized for speed (default). This option works only for export //into XLS file format. _xportToolsDemo.OptimizeForSize = false; try { //Export loaded data ExportDataSetIntoResponse(ds, null, outputFormat, true, false, _xportToolsDemo, destination); } finally { ReleaseGlobalResources(); } } else if (format == data_table_export_format.xml) { //Remove Spaces From Column Names DataTable objTable = ds.Tables[0]; foreach (DataColumn objColumn in objTable.Columns) { if (objColumn.ColumnName.Contains(" ")) { objColumn.ColumnName = objColumn.ColumnName.Replace(" ", ""); } } ds.WriteXml(destination); } else if (format == data_table_export_format.mdb) { access_tools.create_mdb_file(ds, destination, temp_directory + "\\Templates\\template.mdb"); } else if (format == data_table_export_format.csv) { text_tools.create_txt_file(ds, destination); } else if (format == data_table_export_format.txt) { text_tools.create_txt_file(ds, destination, txt_data_type.tab, "", true); } return(file_name); }
/// <summary> /// Closes and releases all resources (variables) declared on page level /// </summary> /// <remarks></remarks> private static void ReleaseGlobalResources() { //_dataHelper = null; _xportToolsDemo.Dispose(); _xportToolsDemo = null; }
private static DS2XL _xportToolsDemo = null; //Excel xPorter needs this /// <summary> /// Export data from source DataSet to browser's response. /// </summary> /// <param name="datasetToExport">Source DataSet to export data from.</param> /// <param name="tablesToExport">List of DataTables inside of DataSet to export data from.</param> /// <param name="outputFormat">Output export format.</param> /// <param name="exportHeaders">Specifies if we need to export column names.</param> /// <param name="exportIntoStream">Specifies if we want to export data into some stream first.</param> /// <param name="xportToolsDemo">Exporter class instance.</param> /// <remarks></remarks> private static void ExportDataSetIntoResponse(DataSet datasetToExport, ExportTable[] tablesToExport, xpOutputFormat outputFormat, bool exportHeaders, bool exportIntoStream, DS2XL xportToolsDemo, string strFileName) { //string tempExportFileName; //System.IO.FileInfo outputFile = null; //MemoryStream exportStream = null; /* * //Generate temporary file name. * tempExportFileName = xPortDemoHelper.GenerateTempFileName(outputFormat); * * if (outputFormat == xpOutputFormat.HTML) * { * Response.ContentType = @"text/html"; * } * else * { * Response.ContentType = @"application/vnd.ms-excel"; * } * * Response.AddHeader("Content-Disposition", "attachment; filename=" + tempExportFileName); */ //If you need to export data from all DataTables then use overloaded Export/ExportToStream //method that does not require tablesToExport parameter. For example, //xportTools.Export(products, "Products", outputFormat, True, chkHeaders.Checked) /* * if (exportIntoStream) * { * * //For the demo purposes we export into memory stream, but it could be any type of stream. * //When Export method executed, make sure that DataTable's TableName property is not blank * //and set to any valid DataTable name. If DataTable does not have name, 'Incorrect table name' * //exception will be thrown * exportStream = (MemoryStream)xportToolsDemo.ExportToStream(datasetToExport, * tablesToExport, outputFormat, exportHeaders); * * //Do something with stream here. For the demo purposes we store stream content into the response output * Response.OutputStream.Write(exportStream.ToArray(), 0, (int)exportStream.Length); * exportStream.Close(); * exportStream = null; * } * else * { */ //Export data into temporary file xportToolsDemo.Export(datasetToExport, tablesToExport, strFileName, outputFormat, true, exportHeaders); //Output file into client's response //outputFile = new System.IO.FileInfo(strFileName); /* * if (outputFile.Exists && Response.IsClientConnected) * { * Response.WriteFile(tempExportFileName); * } */ //outputFile = null; //} //Response.Flush(); //We need to close response to make sure that generated HTML markup from our //aspx page does not attach to output Excel file. //Response.Close(); }
/// <summary> /// export_data /// </summary> public static string export_data(data_table_export_format format, DataSet ds, string temp_directory) { string destination; string file_name; Random rnd = new Random(DateTime.Now.Millisecond); file_name = rnd.Next(10000,1000000000) + "." + format.ToString(); destination = temp_directory + "\\" + file_name; if(format == data_table_export_format.xls) { #region Check Table Name For Excel Length Limit of 31 Characters foreach (DataTable objTable in ds.Tables) if (objTable.TableName.Length > 31) objTable.TableName = StringFunctions.Right(objTable.TableName, 31); #endregion #region Remove Secure Fields From Export try { foreach (DataTable objTable in ds.Tables) { lookagain: foreach (DataColumn col in objTable.Columns) { string strColName = col.ColumnName.ToLower(); if (strColName == "password" || strColName == "pass" || strColName == "exhibitorpassword" || strColName == "administratorpassword" || strColName == "customerpassword") { objTable.Columns.Remove(col.ColumnName); goto lookagain; } if (strColName == "username" || strColName == "user" || strColName == "exhibitorusername" || strColName == "administratorusername" || strColName == "customerusername") { objTable.Columns.Remove(col.ColumnName); goto lookagain; } } } } catch (Exception ex) { General.Debugging.Report.SendError("Error removing secured fields in Excel Spreadsheet for export", ex); } #endregion /* THE OLD WAY string xml = WorkbookEngine.CreateWorkbook(ds); System.IO.StreamWriter sw = new System.IO.StreamWriter(destination,false); sw.Write(xml); sw.Close(); */ xpOutputFormat outputFormat; outputFormat = xpOutputFormat.Excel8; _xportToolsDemo = new DS2XL(); //Check if we need to optimize export output for smaller export size. If not, then //export will be optimized for speed (default). This option works only for export //into XLS file format. _xportToolsDemo.OptimizeForSize = false; try { //Export loaded data ExportDataSetIntoResponse(ds, null, outputFormat, true, false, _xportToolsDemo, destination); } finally { ReleaseGlobalResources(); } } else if (format == data_table_export_format.xml) { //Remove Spaces From Column Names DataTable objTable = ds.Tables[0]; foreach (DataColumn objColumn in objTable.Columns) { if (objColumn.ColumnName.Contains(" ")) objColumn.ColumnName = objColumn.ColumnName.Replace(" ", ""); } ds.WriteXml(destination); } else if(format == data_table_export_format.mdb) { access_tools.create_mdb_file(ds,destination,temp_directory + "\\Templates\\template.mdb"); } else if(format == data_table_export_format.csv) { text_tools.create_txt_file(ds,destination); } else if(format == data_table_export_format.txt) { text_tools.create_txt_file(ds,destination,txt_data_type.tab,"",true); } return(file_name); }
/// <summary> /// Export data from source DataSet to browser's response. /// </summary> /// <param name="datasetToExport">Source DataSet to export data from.</param> /// <param name="tablesToExport">List of DataTables inside of DataSet to export data from.</param> /// <param name="outputFormat">Output export format.</param> /// <param name="exportHeaders">Specifies if we need to export column names.</param> /// <param name="exportIntoStream">Specifies if we want to export data into some stream first.</param> /// <param name="xportToolsDemo">Exporter class instance.</param> /// <remarks></remarks> private static void ExportDataSetIntoResponse(DataSet datasetToExport, ExportTable[] tablesToExport, xpOutputFormat outputFormat, bool exportHeaders, bool exportIntoStream, DS2XL xportToolsDemo, string strFileName) { //string tempExportFileName; //System.IO.FileInfo outputFile = null; //MemoryStream exportStream = null; /* //Generate temporary file name. tempExportFileName = xPortDemoHelper.GenerateTempFileName(outputFormat); if (outputFormat == xpOutputFormat.HTML) { Response.ContentType = @"text/html"; } else { Response.ContentType = @"application/vnd.ms-excel"; } Response.AddHeader("Content-Disposition", "attachment; filename=" + tempExportFileName); */ //If you need to export data from all DataTables then use overloaded Export/ExportToStream //method that does not require tablesToExport parameter. For example, //xportTools.Export(products, "Products", outputFormat, True, chkHeaders.Checked) /* if (exportIntoStream) { //For the demo purposes we export into memory stream, but it could be any type of stream. //When Export method executed, make sure that DataTable's TableName property is not blank //and set to any valid DataTable name. If DataTable does not have name, 'Incorrect table name' //exception will be thrown exportStream = (MemoryStream)xportToolsDemo.ExportToStream(datasetToExport, tablesToExport, outputFormat, exportHeaders); //Do something with stream here. For the demo purposes we store stream content into the response output Response.OutputStream.Write(exportStream.ToArray(), 0, (int)exportStream.Length); exportStream.Close(); exportStream = null; } else { */ //Export data into temporary file xportToolsDemo.Export(datasetToExport, tablesToExport, strFileName, outputFormat, true, exportHeaders); //Output file into client's response //outputFile = new System.IO.FileInfo(strFileName); /* if (outputFile.Exists && Response.IsClientConnected) { Response.WriteFile(tempExportFileName); } */ //outputFile = null; //} //Response.Flush(); //We need to close response to make sure that generated HTML markup from our //aspx page does not attach to output Excel file. //Response.Close(); }