// establish a connection to MS Access Data Base and getting all user tables from the Data Base
public static void LoadMSAccessFile(this IWorkbook workbook, string fileName)
{
try {
string connectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + fileName;
// Create the dataset and add the Categories table to it:
OleDbConnection myAccessConn = null;
try {
myAccessConn = new OleDbConnection(connectionString);
}
catch (Exception ex) {
MessageBox.Show("Error: Failed to create a database connection. \r\n" + ex.Message, "Error");
return;
}
try {
myAccessConn.Open();
workbook.BeginUpdate();
workbook.CreateNewDocument();
// We only want user tables, not system tables
string[] restrictions = new string[4];
restrictions[3] = "Table";
// Get list of user tables
DataTable userTables = myAccessConn.GetSchema("Tables", restrictions);
// Getting the data for every user tables
foreach (DataRow userTablesRow in userTables.Rows)
{
string currentTableName = userTablesRow["TABLE_NAME"].ToString();
using (OleDbCommand cmd = new OleDbCommand(string.Format("SELECT * FROM [{0}]", currentTableName), myAccessConn)) {
using (OleDbDataAdapter adapter = new OleDbDataAdapter(cmd)) {
DataTable currentDataTable = new DataTable(currentTableName);
adapter.Fill(currentDataTable);
LoadDataTableContentIntoSpreadsheetControl(workbook, currentDataTable, userTables.Rows.IndexOf(userTablesRow));
}
}
}
workbook.EndUpdate();
}
catch (Exception ex) {
MessageBox.Show("Error: Failed to retrieve the required data from the DataBase.\r\n" + ex.Message, "Error");
return;
}
finally {
myAccessConn.Close();
}
}
catch (Exception ex) {
MessageBox.Show("Error: Could not read file from disk. Original error: " + ex.Message);
}
}
protected void btnClick(object sender, EventArgs e)
{
IWorkbook book = Spreadsheet.Document;
book.CreateNewDocument();
if (book.Worksheets.Count == 1)
{
book.Worksheets.Add();
}
book.Worksheets.ActiveWorksheet = book.Worksheets[0];
book.Unit = DevExpress.Office.DocumentUnit.Point;
book.Styles.DefaultStyle.Font.Name = DefaultFontName;
book.Styles.DefaultStyle.Font.Size = DefaultFontSize;
book.Styles.DefaultStyle.Alignment.Vertical = SpreadsheetVerticalAlignment.Center;
Worksheet sheet = book.Worksheets[1];
sheet.ActiveView.ShowGridlines = false;
sheet.Name = "Invoice";
PrepareColumns(sheet);
PrepareWatermarkStyleCell(sheet.Cells[1, 1]);
FillInvoice(sheet);
//using (FileStream stream = new FileStream("C:\\Users\\LeHuyHoang\\Documents\\SavedDocumentHoangSX.xlsx",
// FileMode.Create, FileAccess.ReadWrite))
using (MemoryStream stream = new MemoryStream())
{
book.SaveDocument(stream, DocumentFormat.Xlsb);
byte[] bytesInStream = stream.ToArray(); // simpler way of converting to array
stream.Close();
Response.Clear();
Response.ContentType = "application/force-download";
Response.AddHeader("content-disposition", "attachment; filename=name_you_fileSB.xlsb");
Response.BinaryWrite(bytesInStream);
Response.End();
}
}