// 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(); } }