Getting data from stored procedure

Aspose.Cells Product Family
1

Hi,

I was exploring Aspose Cells. I created a new prject and added aspose cells dll to it. I am trying to pull the data from database using a SQL query(planning to replace it with stored procedure call) and get the results into excel file called sample1.xls.

I wrote following code. Is there anything that I am missing? because following code is not giving any errors but also not writing any data into the excel sheet.

Waiting for your suggestions.

myConnection.ConnectionString = Con;

myConnection.Open();

SqlCommand myCommand = new SqlCommand();

myCommand.Connection = myConnection;

myCommand.CommandText = "Select * from Claims";

DataSet dt1 = new DataSet();

SqlDataAdapter da1 = new SqlDataAdapter(myCommand.CommandText,myConnection);

da1.Fill(dt1);

WorkbookDesigner design1 = new WorkbookDesigner();

design1.Open(Server.MapPath("~/sample1.xls"));

design1.SetDataSource(dt1);

design1.Process(0,true);

Thanks.

2

Hi,

I think you may change a line of code i.e..,

myConnection.ConnectionString = Con;

myConnection.Open();

SqlCommand myCommand = new SqlCommand();

myCommand.Connection = myConnection;

myCommand.CommandText = "Select * from Claims";

DataSet dt1 = new DataSet();

SqlDataAdapter da1 = new SqlDataAdapter(myCommand.CommandText,myConnection);

da1.Fill(dt1, "Claims");

WorkbookDesigner design1 = new WorkbookDesigner();

design1.Open(Server.MapPath("~/sample1.xls"));

design1.SetDataSource(dt1);

design1.Process(0,true);

Kindly let us know if it still does not work, we will check it soon.

Thank you.

3

I donno that WorkbookDesigner is not working for me. I tried something like below, I got it.

myConnection.ConnectionString = Con;

myConnection.Open();

SqlCommand myCommand = new SqlCommand();

myCommand.Connection = myConnection;

myCommand.CommandType = CommandType.StoredProcedure;

myCommand.CommandText = "Select * from Claims";

DataSet dt1 = new DataSet();

SqlDataAdapter da1 = new SqlDataAdapter(myCommand.CommandText,myConnection);

da1.Fill(dt1);

Workbook W1 = new Workbook();

string designerFile = MapPath("test1") + "\\designer.xls";

W1.Open(designerFile);

W1.Worksheets.Add();

Worksheet w2 = W1.Worksheets[0];

w2.Cells["A1"].PutValue("Claim Auto ID");

w2.Cells["B1"].PutValue("Claim ID");

w2.Cells["C1"].PutValue("Payor ID");

w2.Cells["D1"].PutValue("Provider ID");

int rowCounter = 0;

int colCounter = 2;

while (rowCounter < dt1.Tables[0].Rows.Count)

{

w2.Cells["A" + colCounter].PutValue(dt1.Tables[0].Rows[rowCounter].ItemArray[0].ToString());

w2.Cells["B" + colCounter].PutValue(dt1.Tables[0].Rows[rowCounter].ItemArray[1].ToString());

w2.Cells["C" + colCounter].PutValue(dt1.Tables[0].Rows[rowCounter].ItemArray[2].ToString());

w2.Cells["D" + colCounter].PutValue(dt1.Tables[0].Rows[rowCounter].ItemArray[3].ToString());

rowCounter++; colCounter++;

W1.Save(designerFile, FileFormatType.Default);

}

Do you have some functions to format the cells before we place the data in them. Like currency type, date type etc?

Thanks. I appriciate your quick response.

4

Hi,

1). Could you create a sample console application with the template designer file to show the issue. We have already tested your scenario and it works fine here.

2). Please see the following docs:

Setting Display Formats of Numbers and Dates
Formatting Rows and Columns

Thank you.