.NET Upload Excel or CSV file to Server as DataSet or DataTable
This class will upload an xls/xlsx/csv file to the server as a datatable (user may select a sheet) or dataset (if multiple sheets).
There are also some extended options if you want to get at the raw xlsx content (such as to pull out images), and whether to hide the prompt user to select a sheet if there is more than one.
Usage: This extends FileUpload so you can call UploadFile() once you detect a file has been uploaded.
If there are multiple sheets, you will get back SheetNames, and must set LoadSheetName and then call UploadFile() again.
To cancel the upload, call CancelUpload()
On success, your content will be in ExcelContent and in MultiSheetExcelContent (use whichever you prefer)
Encoding: this assumes default file encoding of 1252 (windows office). You can change that to something else or pass it in if you prefer.
There is code in here to correct leading zeros in a CSV so they can be read as strings.
If your column header contains '.', it gets changed by the reader to "#". To get around this, have your user use {decimal} in the sheet name if this is absolutely necessary to keep that in your final column name.
***** CODE: ********
public class ExcelFileUploader : FileUpload
{
/// <summary>
/// Default code file to use when reading a csv. We're using 1252 since that is the excel default. We have also updated the portal to export CSV files as code 1252.
/// Anything else will need to be opened and saved in excel.
/// </summary>
const int DEFAULT_CSV_CODEFILE = 1252;
DataSet excelContent = null;
/// <summary>
/// Data returned from the sheet specified in 'LoadSheetName', or the active sheet if there is 1 sheet.
/// </summary>
public DataTable ExcelContent
{
get { return excelContent.Tables[0]; }
}
/// <summary>
/// Data returned from the sheet specified in 'LoadSheetName', or the active sheet if there is 1 sheet.
/// </summary>
public DataSet MultiSheetExcelContent
{
get { return excelContent; }
}
private List<string> sheetNames;
/// <summary>
/// Table names found in the file
/// </summary>
public List<string> SheetNames
{
get { return sheetNames; }
}
/// <summary>
/// Location of uploaded file during processing.
/// If a file has multiple sheets, it has to sit in the folder until the user selects a sheet.
/// </summary>
public string SavePath
{
get { return (string)this.ViewState["SaveFileName"]; }
internal set { this.ViewState["SaveFileName"] = value; }
}
/// <summary>
/// If true, all sheets are processed. Otherwise user must select which sheet to process.
/// </summary>
public bool UploadAllSheets
{
get
{
if (this.ViewState["UploadAllSheets"] == null)
return false;
return (bool)this.ViewState["UploadAllSheets"];
}
set { this.ViewState["UploadAllSheets"] = value; }
}
/// <summary>
/// If true, file is kept in temp folder for advanced reading of raw XLSX content
/// </summary>
public bool AllowXLSXDirectRead
{
get
{
if (this.ViewState["AllowXLSXDirectRead"] == null)
return false;
return (bool)this.ViewState["AllowXLSXDirectRead"];
}
set { this.ViewState["AllowXLSXDirectRead"] = value; }
}
/// <summary>
/// If there is more than 1 sheet in the file, you need to pass this or the import will fail.
/// Available tables will be loaded into the SheetNames propery if the import fails.
/// </summary>
public string LoadSheetName = null;
/// <summary>
/// Control for uploading excel files
/// </summary>
public ExcelFileUploader()
: base()
{
}
/// <summary>
/// Cancels any pending upload
/// </summary>
public void CancelUpload()
{
if (File.Exists(SavePath) && !AllowXLSXDirectRead)
{
File.Delete(SavePath);
SavePath = null;
}
}
/// <summary>
/// Uploads the selected file content into the ExcelContent datatable object.
/// </summary>
/// <returns>True if load succeeded</returns>
public bool UploadFile()
{
sheetNames = null; // reset
if (!String.IsNullOrEmpty(SavePath))
{ // finish a pending upload
return UploadSavedFile();
}
if (this.HasFile)
{
string serverPath = YourTempUploadDirectory; // a directory that won't get wiped out during build process, since it needs special permissions.
if (!Directory.Exists(serverPath))
Directory.CreateDirectory(serverPath);
// a unique name
string fn = "Upload" + DateTime.Now.Ticks + "_" + YourCurrentLoginId; // ENSURES 2 USERS DONT OVERRIDE ONE ANOTHER
string ext = Path.GetExtension(this.FileName).ToLower().Trim();
SavePath = string.Format("{0}/{1}{2}", serverPath, fn, ext);
if (File.Exists(SavePath) && !!AllowXLSXDirectRead)
{
File.Delete(SavePath);
}
this.SaveAs(SavePath);
if (Path.GetExtension(SavePath).ToLower() == ".csv")
{
PreprocessCSV();
}
return UploadSavedFile();
}
return false;
} // Upload File
/// <summary>
/// ADO.NET CSV import cannot handle data in the format ="01". But Excel requires that format. Solution, find leading equal signs and delete them.
/// </summary>
void PreprocessCSV()
{
// SP14.5 assuming all files are encoding 1252 for now as that's what most of our computers are using. This is a mess. Vendor files could be ANYTHING!!!
Encoding dfltCsvEncoding = Encoding.GetEncoding(DEFAULT_CSV_CODEFILE);
var tmpFile = SavePath + ".tmp";
if (File.Exists(tmpFile))
File.Delete(tmpFile);
StringBuilder sbOutput = new StringBuilder();
using (FileStream fs = new FileStream(tmpFile, FileMode.CreateNew))
{
using (var sw = new StreamWriter(fs, dfltCsvEncoding))
{
using (var sr = new StreamReader(SavePath, dfltCsvEncoding)) // System.IO.File.OpenText(SavePath))
{
int charsRead;
bool inQuotes = false;
bool isLeading = true;
char[] c = new char[1];
while (!sr.EndOfStream)
{
charsRead = sr.Read(c, 0, 1);
if (charsRead > 0)
{
if (c[0] == '"')
{ // start or end of data
inQuotes = !inQuotes;
sw.Write(c);
isLeading = false;
}
else if (c[0] == '=' && !inQuotes && isLeading)
{ // ignore any leading = sign (first char following comma delimiter) that's not in quotes. Used by Excel ONLY.
// Note that if your data isn't quoted and it looks like =hi, the = sign is lost, but that's really unlikely and in Excel, the = sign would be thrown out anyway.
}
else
{
sw.Write(c);
// if
isLeading = (!inQuotes && c[0] == ','); // if there is a comma outside of quotes, the very next char would be a leading = sign, so use this flag to not remove any other = signs.
}
}
}
sr.Close();
}
sw.Close();
}
fs.Close();
}
// string sb = File.ReadAllText(tmpFile, dfltCsvEncoding);
File.Delete(SavePath);
File.Copy(tmpFile, SavePath);
File.Delete(tmpFile);
}
private bool UploadSavedFile()
{
if (!String.IsNullOrEmpty(SavePath))
{
string ext = Path.GetExtension(SavePath).ToLower().Trim();
string excelVersion;
string providerVersion;
string connString;
if (ext.Equals(".xls"))
{
excelVersion = "8.0";
providerVersion = "Microsoft.Jet.OLEDB.4.0";
connString = string.Format(@"Provider={0};Data Source={1};Extended Properties=""Excel {2};HDR=YES;IMEX=1""", // SP14.1; switched to IMEX=1 as mixed format cells were not importing properly
providerVersion, SavePath, excelVersion);
}
else if (ext.Equals(".xlsx"))
{
excelVersion = "12.0";
providerVersion = "Microsoft.ACE.OLEDB.12.0";
connString = string.Format(@"Provider={0};Data Source={1};Extended Properties=""Excel {2};HDR=YES;IMEX=1""", // SP14.1; switched to IMEX=1 as mixed format cells were not importing properly
providerVersion, SavePath, excelVersion);
}
else if (ext.Equals(".csv"))
{
// az: using code 1252 since that is excel windows default which we output to above
connString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=\"" + Path.GetDirectoryName(SavePath) + "\\\";Extended Properties=\"text;HDR=YES;IMEX=1;MaxScanrows=16000;FMT=Delimited;CharacterSet=" + DEFAULT_CSV_CODEFILE + ";\"";
// csv only has one sheet - the file name
LoadSheetName = Path.GetFileName(SavePath);
}
else
{
throw new InvalidBusinessOperationException("Uploaded file is not a supported type. File must be .xls or .xlsx file type.");
}
DataSet ds = new DataSet();
using (OleDbCommand cmd = new OleDbCommand())
{
using (OleDbConnection conn = new OleDbConnection(connString))
{
cmd.Connection = conn;
// get list of worksheets in file
if (LoadSheetName == null)
{ // need to get a table name
cmd.Connection.Open();
DataTable dt = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
cmd.Connection.Close();
if (dt.Rows.Count > 1)
{ // too many sheets; fail! But populate list of available sheets.
sheetNames = new List<string>();
foreach (DataRow dr in dt.Rows)
{
string tableName = FieldReader.ReadString(dr, "TABLE_NAME");
if(tableName.Replace("'","").Replace("\"","").EndsWith("$")) // valid sheets should always end with $, $', or $"
sheetNames.Add(tableName);
}
if (sheetNames.Count == 0)
{
SavePath = null; // bad file
}
if(sheetNames.Count == 0 || (!UploadAllSheets && sheetNames.Count > 1))
{
return false; // no tables, or too many tables!
}
LoadSheetName = sheetNames[0]; // if only one sheet, select it
}
else
{
LoadSheetName = dt.Rows[0]["TABLE_NAME"].ToString();
}
}
if (UploadAllSheets)
{
if (sheetNames == null && LoadSheetName != null)
{
sheetNames = new List<string>();
sheetNames.Add(LoadSheetName);
} // only 1 sheet in file
foreach (string sheetName in sheetNames)
{
cmd.CommandText = String.Format("SELECT * FROM [{0}]", sheetName);
using (OleDbDataAdapter da = new OleDbDataAdapter(cmd))
{
try
{ // add each table to sheet
DataTable dtTemp = new DataTable(sheetName);
da.Fill(dtTemp);
ds.Tables.Add(dtTemp);
}
catch
{
string msg = string.Format("Excel sheet {0} could not be opened. Please ensure the sheet name does not include punctuation or special characters.", sheetName);
LoadSheetName = null;
throw new InvalidBusinessOperationException(msg);
}
}
}
}
else
{
cmd.CommandText = String.Format("SELECT * FROM [{0}]", LoadSheetName);
using (OleDbDataAdapter da = new OleDbDataAdapter(cmd))
{
try
{
da.Fill(ds);
}
catch
{
string msg = string.Format("Excel sheet {0} could not be opened. Please ensure the sheet name does not include punctuation or special characters.", LoadSheetName);
LoadSheetName = null;
throw new InvalidBusinessOperationException(msg);
}
}
}
}
}
excelContent = ds;
if (ds.Tables.Count == 0)
excelContent.Tables.Add(new DataTable());
if (!this.UploadAllSheets)
{ // single sheet: clean up data. If multiple, the uploader is responsible for reading content...
// correct excel column headers, which change . to # (assume col header never contains #) - so user is instructed to use {0}
foreach (DataColumn dc in ExcelContent.Columns)
dc.ColumnName = dc.ColumnName.Replace("{decimal}", ".");
}
CancelUpload(); // clean up since we're done.
return true; // upload ok
}
return false;
} // UploadSavedFile
} // class
Created By: amos 10/16/2014 5:38:59 PM
Updated: 10/16/2014 5:44:23 PM
|
|