Last post Mar 18, 2011 11:39 AM by cubangt
Mar 16, 2011 05:20 PM|cubangt|LINK
I have a page within our site that allows admin users the ability to upload configuration settings into the system instead of submitting 1 at a time.
While testing yesterday, i found that one of the upload pages allowed me to upload a excel file that had alot more columns than expected and when i checked the database, the first few columns that were expected took the values from the file, and everything
else was ignored, but wanted to see if there is any way to check the number of columns in the file to make sure it matches the upload logic before accepting it.
Mar 16, 2011 06:51 PM|sansan|LINK
You can use GetSchemaTable method to get the columns information from excel sheet.. Check this example
public int GetNumberOfColumnsInSheet(string filename, string SheetName)
int columnsCount = 0; string FilePath = Server.MapPath(filename);
string excelConnectionString = "";
// xlsx file
if (Path.GetExtension(FilePath) == ".xlsx")
excelConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + FilePath +
";Mode=ReadWrite;Extended Properties=\"Excel 12.0 Xml;HDR=YES;IMEX=1\"";
// xls extension file
else if (Path.GetExtension(FilePath) == ".xls")
excelConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + FilePath +
";Mode=ReadWrite;Extended Properties=\"Excel 8.0;HDR=YES;IMEX=1\"";
//If a valid excel file
using (OleDbConnection conn = new OleDbConnection(excelConnectionString))
DataTable dt = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Columns, null);
if (dt.Rows.Count > 0)
columnsCount = dt.AsEnumerable().Where(a => a["TABLE_NAME"].ToString() == SheetName).Count();
protected void Page_Load(object sender, EventArgs e)
int columnsCountInSheet1 = GetNumberOfColumnsInSheet("data.xlsx", "Sheet1$");
int columnsCountInSheet2 = GetNumberOfColumnsInSheet("data.xlsx", "Sheet2$");
You will have to save the file to server and check for columnscount before saving to database. If the number of columns is more than what you have in database, display a message and skip database upload step.
Mar 16, 2011 07:02 PM|cubangt|LINK
Guess i forgot to add in the post, what would be the most optimal way to get the column count from the table ill be uploading to before hand so i can do the comparision?
because right now after i do my validation of the excel or csv i simply call my insert for each row like below:
siteID = valid(dReader, 0);servIpAddress = valid(dReader, 1);ddlCount = valid(dReader, 2);ddlEventsTrig = valid(dReader, 3);
dac.insertConfig(Convert.ToInt32(siteID), servIpAddress, Convert.ToInt32(ddlCount), Convert.ToInt32(ddlEventsTrig));
Mar 16, 2011 07:08 PM|sansan|LINK
If you want to get the column count from a table, you have to execute the below query from your code.
WHERE TABLE_NAME in ('---Your desired table name here---')
Mar 16, 2011 11:55 PM|cubangt|LINK
So i can do this before i open the excel file to have the number available, then open the excel file and return the count there, compare and if they match, then allow the upload.
sounds easy enough, will have to try it in the morning..
Mar 18, 2011 11:39 AM|cubangt|LINK
The above worked fine for one page, but on another page that i allow uploading, we have a table that has 30 columns and only 7 columns that you can upload to and on the form only 7 fields you can maintain, during the initial deign and setup this table was
going to require 30 columns of data, but as we move along, requirements were changed and for this initial roll out they only wanted 7 possible columns maintained.. BUT the issue is that because the other fields will eventually be used, they asked that they
be left in the table and just set to null until they need them.. since the application was built around displaying and using that information..
So would i be able to do the same sort of select statement and chose the columns i want or would it be better to just set that number as a variable in the code..