How to copy my excel file to sql server database and render the data from the data base to browser

Aspose.Cells Product Family
1

Hi,
Can you please help me in uploading my excel files to store in sqlserver database and render the same into a browser using aspose.cells. I have used standard way of storing excel file to database using the following code
// Read the file and convert it to Byte Array
string filePath = Server.MapPath(“APP_DATA/Testxls.xlsx”);
string filename = Path.GetFileName(filePath);

FileStream fs = new FileStream(filePath, FileMode.Open, FileAccess.Read);
BinaryReader br = new BinaryReader(fs);
Byte[] bytes = br.ReadBytes((Int32)fs.Length);
br.Close();
fs.Close();

//insert the file into database
string strQuery = “insert into tblFiles(Name, ContentType, Data) values (@Name, @ContentType, @Data)”;
SqlCommand cmd = new SqlCommand(strQuery);
cmd.Parameters.Add("@Name", SqlDbType.VarChar).Value = filename;
cmd.Parameters.Add("@ContentType", SqlDbType.VarChar).Value = “application/vnd.ms-excel”;
cmd.Parameters.Add("@Data", SqlDbType.Binary).Value = bytes;
InsertUpdateData(cmd);

Now I should able to get the data from the database and display it as excel file which should be rendered in browser using aspose.cells.

Thanks,
Ramakrishna

2

Hi Ramakrishna,

You may follow the approach given in the code snippet below.

1st read the Excel file using OleDb object and then get the data into the dataset. Once the dataset is filled, you can use Worksheet.Cells.ImportDataTable() method import the data intot he Worksheet.

Code snippet:

string connstr = "Provider=Microsoft.Jet.Oledb.4.0;Data Source=C:\aaa.xls;Extended Properties=Excel 8.0";
OleDbConnection conn = new OleDbConnection(connstr);
string strSQL = "SELECT * FROM [MySheet$]";

OleDbCommand cmd = new OleDbCommand(strSQL, conn);
DataSet objDataSet = new DataSet();
OleDbDataAdapter da = new OleDbDataAdapter(cmd);
da.Fill(objDataSet);
//GridWeb1.DataSource = ds;
//GridWeb1.DataBind();

Workbook objWB = new Workbook();
Worksheet objWS = objWB.Worksheets[0];
objWS.Cells.ImportDataTable(objDataSet.Tables[0], true, "A1");


// Extended Properties='Excel 8.0;IMEX=1'; The single quotes should start after = and end after 1. If this does not work try the below
// "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\MyExcel.xls;Extended Properties=""Excel 8.0;HDR=Yes;IMEX=1"""
// "HDR=Yes;" indicates that the first row contains columnnames, not data.
// "IMEX=1;" tells the driver to always read "intermixed" data columns as text

Thanks,

3

Hi Salman,
I wanted the excel sheet to be displayed in IE. Is there any other way to display excel sheets as is, with out copying them to grid/gridview.
Thanks,
Ramakrishna

4

Hi Ramakrishna,

There are certain web scenarios that demand displaying of Excel sheet (Data & Presentation), a combination of parts or objects that can work together well. Aspose.Cells.GridWeb control provides you the real environment that allows you to apply rich and appealing format to data and calculate formula results like MS Excel.

Using Aspose.Cells.GridWeb.dll library; you can add GridWeb control on your web page. Following code snippet can be used.

string sFilePath = Server.MapPath("Report.xls");
FileStream fs = new FileStream(sFilePath, System.IO.FileMode.Open, System.IO.FileAccess.Read, System.IO.FileShare.None);
FileStream objFS = File.OpenRead(sFilePath);
GridWeb1.WebWorksheets.ImportExcelFile(objFS);
// OR you can use File name as well
// GridWeb1.WebWorksheets.ImportExcelFile(sFilePath);

For more details, please follow the link given below:

http://www.aspose.com/documentation/.net-components/aspose.cells-for-.net/aspose.cells.gridweb.html

Thanks,

5

Hi,

If you want to generate file dynamically and send the generated file directly to the client browser using Aspose.Cells API in ASP.NET. In order to do so, you may use a special overloaded version of Save method that accepts four parameters:

* ASP.NET HttpResponse Object
* File Name, represents the file name
* ContentDisposition, represents the content disposition type of the generated file
* SaveOptions, represents the file format type

e.g

string connstr = “Provider=Microsoft.Jet.Oledb.4.0;Data Source=C:\aaa.xls;Extended Properties=Excel 8.0”;
OleDbConnection conn = new OleDbConnection(connstr);
string strSQL = “SELECT * FROM [MySheet$]”;

OleDbCommand cmd = new OleDbCommand(strSQL, conn);
DataSet objDataSet = new DataSet();
OleDbDataAdapter da = new OleDbDataAdapter(cmd);
da.Fill(objDataSet);
//GridWeb1.DataSource = ds;
//GridWeb1.DataBind();

Workbook objWB = new Workbook();
Worksheet objWS = objWB.Worksheets[0];
objWS.Cells.ImportDataTable(objDataSet.Tables[0], true, "A1");

//Save in default format and send the file to user so that he may open the file in
//some application or save it to some location
objWB.Save(this.Response, "Report.xls", ContentDisposition.Inline, new XlsSaveOptions());


For further reference about Save method, see the document:
http://www.aspose.com/documentation/.net-components/aspose.cells-for-.net/saving-files.html

Thank you.