Demo using the SQL client?

#1

Hi, I’ve just downloaded the Aspose.Excel demo version, and have the demo project working in VS.Net 2003. However, I’m not familiar with the OLEDB commands used for database access and wondered if there was a version of the demo that used SQL server (or a demo that populates from an array without database access)? I was particularly interested in the CreateInvoice part of the demo that creates the .xls from the API.

Thanks

Nick

#2

Dear Nick,

Thanks for your consideration.

In VS.net, I think that OleDbClient is very similar with SqlClient.
Following is a simple sample.

private void Page_Load(object sender, System.EventArgs e)
{
DataTable dt = new DataTable();
dt.Columns.Add(“ProductId”, typeof(int));
dt.Columns.Add(“ProductName”, typeof(string));
dt.Columns.Add(“UnitPrice”, typeof(double));

DataRow row = dt.NewRow();
row[“ProductId”] = 1;
row[“ProductName”] = “Coffee”;
row[“UnitPrice”] = 10.5;
dt.Rows.Add(row);

row = dt.NewRow();
row[“ProductId”] = 2;
row[“ProductName”] = “Tea”;
row[“UnitPrice”] = 9.8;
dt.Rows.Add(row);

Excel excel = new Excel();
excel.Worksheets[0].Cells.ImportDataTable(dt, true, 0, 0);

excel.Save(“stock.xls”, SaveType.OpenInExcel, FileFormatType.Default, this.Response);
}

#3

Nick,

Here is some SQL Server code which uses Aspose.Excel:

public class ExtractUpdate
{
protected SqlConnection sqlConn;
protected SqlCommand sqlCmd;
protected SqlDataAdapter sqlDa;
protected DataTable dataTable1;

//Define constants and local variables

Excel excel = new Excel();
}
public string[] ExcelExtract()
{
//Init DB
DBInit();

//Export to Excel

string designerFile = “c:\CTC.WebApp\ExtractUpdate\XLSheets\MassExtract.xls”;
excel.Open(designerFile);

this.dataTable1.Reset();
this.sqlCmd.CommandText =
@“SELECT *
FROM auto_upload_temp_t”;

this.sqlDa.Fill(this.dataTable1);

Worksheet sheet = excel.Worksheets[0];
sheet.Cells.ImportDataTable(this.dataTable1, false, 2, 1);

FileFormatType XlFileFormat = new FileFormatType();
XlFileFormat = FileFormatType.Excel97;

excel.Save(“c:\CTC.WebApp\ExtractUpdate\XLSheets\Output.xls”, XlFileFormat);

Reply = new string[1];
Reply[0] = “Success”;
return Reply;

}

private void DBInit()
{
this.sqlConn = new SqlConnection();
this.sqlCmd = new SqlCommand();
this.sqlDa = new SqlDataAdapter();

sqlConn.ConnectionString =
“Data Source=NMCHQT27;” +
“Initial Catalog=ctcd;” +
“User ID=pgmctc;” +
“Password=pgmctc”;

this.sqlCmd.Connection = this.sqlConn;
this.sqlDa.SelectCommand = this.sqlCmd;
this.dataTable1 = new DataTable();
}

}


Best Regards,
Michael Minadeo