Upload Excel Spreadsheet

[Morris Neuman]

Hi,

I have a web page that allows a user to select an excel file using the
FileUpload Control (BrowseFileToUpload), then using a button's onclick
command (ButtonUploadFile_Click), uploads the selected file and saves it
using a preset name ("ExcelImportODS-" + u.UserName + ".xls") in a folder on
the webserver.

Is there anyway to tell how many rows were in the uploaded file?

My upload is as follows:

protected void ButtonUploadFile_Click(object sender, EventArgs e)
{
//Uploads the file selected by the user using the Browse button of
the fileupload control(CALLMaster\Data\TestImport.xls)
//and uploads to web server using new name
ExcelImportODS-{webaccountname].xls.
if (BrowseFileToUpload.HasFile)
{
try
{
String strFileExt =
System.IO.Path.GetExtension(BrowseFileToUpload.PostedFile.FileName);
if (strFileExt == ".xls")
{
try
{
String strSettingSelected = "";
if (RadioButtonListImportSetting.SelectedIndex == 1)
{
strSettingSelected = "Custom Settings";
}
else
{
strSettingSelected = "Fixed Settings";
}

MembershipUser u =
Membership.GetUser(User.Identity.Name);
//You can use the u.xxxxx method to access various
user info. ex. u.email or u.lastactivitydate
//use intelisense for options.

BrowseFileToUpload.SaveAs(Server.MapPath("~/ImportData/ExcelImportODS-" +
u.UserName + ".xls"));
//LabelUploadStatus.Text = "<br>Uploaded File Name:
" + BrowseFileToUpload.PostedFile.FileName + "<br>" + "Type: " +
BrowseFileToUpload.PostedFile.ContentType + " File Size: " +
BrowseFileToUpload.PostedFile.ContentLength + "kb FileExtension: " +
strFileExt + "<br>";
LabelUploadStatus0.Text = "<br>Uploaded File Name: "
+ BrowseFileToUpload.PostedFile.FileName + "<br>" + " with " +
strSettingSelected + "<br>";
PanelBrowseUpload.Visible = false;
PanelViewImport.Visible = true;
}
catch (Exception ex)
{
LabelUploadStatus.Text = "Upload Error: " +
ex.Message.ToString();
}
}
else
{
LabelUploadStatus.ForeColor = System.Drawing.Color.Red;
LabelUploadStatus.Text = "<br> * File Type Error: File
must be .xls. File not uploaded.";
}
}
catch (Exception ex)
{
LabelUploadStatus.Text = "Error: " + ex.Message.ToString();
}
}
else
{
LabelUploadStatus.Text = "Please select a file to upload";
}
}

 

[Allen Chen [MSFT]]

Hi Morris,

From your description the requirement is to know how many rows are there in
the uploaded xls file, right?
If so I think you can try Excel interop. Here's the code that demonstrates
how to do so. (Please add reference Microsoft.Office.Interop.Excel first)

Aspx:

<asp:FileUpload ID="FileUpload1" runat="server" />
<asp:Button ID="Button1" runat="server" Text="Upload"
onclick="Button1_Click" />

Aspx.cs:

using Excel = Microsoft.Office.Interop.Excel;

static object mylock = new object();

protected void Button1_Click(object sender, EventArgs e)
{
string extension=
System.IO.Path.GetExtension(this.FileUpload1.FileName);
if (extension.ToLower() == ".xls")
{
lock (mylock)
{
string path = Server.MapPath("test.xls");
this.FileUpload1.SaveAs(path);
//get the total row number
Excel.Application app = new Excel.Application();
object missing = Type.Missing;
Excel.Workbook wb =
app.Workbooks.Open(Server.MapPath("test.xls"), missing, missing, missing,
missing, missing, missing, missing, missing, missing, missing, missing,
missing, missing, missing);
try
{

Excel.Worksheet ws = app.ActiveSheet as
Excel.Worksheet;
//Please notice the code demonstrates how to get the used row of the active
sheet.
//You can get the reference of the inactive sheets via app.Sheets collection
int total_rows= ws.UsedRange.Rows.Count;
}
finally
{
app.Quit();
}

}
}
}

Please test the above code to see if it works.

Regards,
Allen Chen
Microsoft Online Support

Delighting our customers is our #1 priority. We welcome your comments and
suggestions about how we can improve the support we provide to you. Please
feel free to let my manager know what you think of the level of service
provided. You can send feedback directly to my manager at:
(e-mail address removed).

==================================================
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/en-us/subscriptions/aa948868.aspx#notifications.

Note: MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 2 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions. Issues of this
nature are best handled working with a dedicated Microsoft Support Engineer
by contacting Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/en-us/subscriptions/aa948874.aspx
==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.

 

[Allen Chen [MSFT]]

Hi Morris,

Have you tried my code? Can it work?

Regards,
Allen Chen
Microsoft Online Community Support