Important Notes

The samples and information below is provided without a warranty of any kind. This post is for informational purposes and coresystems ag assumes no responsibility for errors or omissions in the information provided.

Purpose

The Export Excel plugin allows you to create automatically Excel Files with Data exported from SAP Business One.  With the help of coresuite customize you can automate the data export from SAP Business One for further analysis or transfer the data to a different system.

This sample rule attached here fills an Excel with the results of sql queries stored in the SAP Business One Query Manager under a certain category. With the help of coresuite customize this export can be automated according to the customers Business Rewuirements. For example whenever a certain report is viewed or whenever a certain user logs in to the system or whenever certain documents or data is entered in the system.

Requirements

The sample requires coresuite Version 3.70 or higher, and SAP Business One 8.82 or higher. Excel installed on the System.

Procedure to use this small solution

1. Download the attached file FAQ_10188_Export_data_to_Excel.zip
2. Unpack the .zip files, this file contains two files: ExcelExport_Plugin.sip and FAQ_10188_Export_data_to_Excel.cocu
3. Install the .sip file via the coresuite administration menu.
4. Import the .cocu file in SAP Business One via
   > Administration > Add-Ons > coresuite customize > Import / Export > Import rules.
   In the message box, select “All Active”. Click on “Import”.
5. Restart the coresuite Add-On.
6. Click on the new menu entry
   > Administration > Data Import / Export > Data Export > Export Data to Excel
   
7. Wait until an Excel File opens with the exported data. 

Notes

The sample rule does not support queries with parameters. The sample rule automatically selects the queries which are saved in the first Category in the SAP Business One Query Manager.

It is advised to adjust the sample rule with the data required for your use case.

Procedure to adjust this small solution

1. Open the "New Menu" rule under
   > Administration > Add-Ons > coresuite customize > New Menu 
   > Rule FAQ_10188: Export data to Excel
2. Adjust the parameters after "TODO" at the beginning of the rule. See the sample code below for details.

Sample Code

/*
***** General Information *****
Creator: coresystems ag, muf@coresystems.ch
Create Date: 2013-02-04
 
***** StartConfDesc *****
Create Excel File with data exported via queries saved in SAP Business One query manager
***** EndConfDesc *****
 
***** Updates *****
YYYY-MM-DD, name: Update Comment
*/

string ruleName = pVal.RuleInfo.RuleName.ToString();
string errorMsg = "Error in New Menu Rule " + ruleName;

StatusBar.WriteSucess(ruleName + ": Excel Export started.");

try
{
 /* Parameters to be adjusted */
 // TODO: Enter query category to be used here. Notice that this rule does not work for queries that contain parameters
 string queryCategory = SwissAddonFramework.Utils.SQL.ExecuteQueryScalar("SELECT TOP 1 CatName FROM OQCN").ToString();
 //StatusBar.WriteWarning("DEBUG: queryCategory " + queryCategory);
 // TODO: Enter Filepath where to store the excel here
 string filePath = System.IO.Path.GetTempPath();
 //StatusBar.WriteWarning("DEBUG: filePath " + filePath);
 // TODO: Add file name here
 string fileName = queryCategory + @"_" + System.DateTime.Now.Year.ToString() + @"_" + System.DateTime.Now.Month.ToString() + @"_" + System.DateTime.Now.Day.ToString() + @".xlsx";
 //StatusBar.WriteWarning("DEBUG: fileName " + fileName);
 // TODO: Set the document properties
 string excelTitle = "Export Data for " + queryCategory;
 string excelAuthor = "Friederike Mundt";
 string excelCompany = "coresystems ag";
 // TODO: Set if you want to open the excel file automatically after create or not
 bool openAfterSave = true;
 // TODO: Adjust the start row: Which should be the first row in the excel to enter the data exported
 int startRow = 1;
 
 /* Internal parameters */
 int row, col, i;
 string sql;
 string regex;
 OfficeOpenXml.ExcelWorksheet worksheet;
 string worksheetName;
 System.Data.SqlClient.SqlDataReader sdrData;
 /* Set the whole filepath */ 
 filePath = filePath + fileName;
 //StatusBar.WriteWarning("DEBUG: filePath " + filePath);
 System.IO.FileInfo newFile = new System.IO.FileInfo(filePath);
 //StatusBar.WriteWarning("DEBUG: newFile " + newFile.Name);
 // Delete the file if it already exists
 if (newFile.Exists)
 {
 newFile.Delete();
 newFile = new System.IO.FileInfo(filePath);
 }
 // Create the new file
 using (OfficeOpenXml.ExcelPackage package = new OfficeOpenXml.ExcelPackage(newFile))
 {
 /* Get SQL Queries from category defined above */
 sql = @"SELECT T0.QName, T0.QString FROM OUQR T0
 INNER JOIN OQCN T1 ON T0.QCategory = T1.CategoryId
 WHERE T1.CatName = '" + queryCategory + @"'";
 //StatusBar.WriteWarning("DEBUG: sql " + sql);
 using (System.Data.SqlClient.SqlDataReader sdrDatasources = SwissAddonFramework.Utils.SQL.ExecuteQuery(sql))
 while (sdrDatasources.Read())
 {
 // The worksheet name will be the name of the query stored in the SAP B1 Query Manager (OUQR.QName)
 worksheetName = sdrDatasources.GetValue(0).ToString();
 //StatusBar.WriteWarning("DEBUG: worksheetName " + worksheetName);
 // Get the sql query (OUQR.QString)
 sql = sdrDatasources.GetValue(1).ToString();
 // StatusBar.WriteWarning("DEBUG: sql " + sql);
 // Remove the parameters in the where clause as they do not work in this general sample.
 // Instead all data will be exported
 if (sql.Contains("[%"))
 {
 StatusBar.WriteWarning("Queries with parameters are not supported here. Query " + worksheetName + " will be skipped.");
 }
 else
 { 
 // Add a new worksheet to the empty workbook
 worksheet = package.Workbook.Worksheets.Add(worksheetName);
 
 // iterate through the rows
 row = startRow; 
 using (sdrData = SwissAddonFramework.Utils.SQL.ExecuteQuery(sql))
 while (sdrData.Read())
 {
 col = 1; 
 // iterate through the columns
 for (i = 0; i < sdrData.FieldCount; i++)
 {
 // do not bother filling cell with blank data (also useful if there is a formula in a cell)
 if (sdrData.GetValue(i) != null)
 worksheet.Cells[row, col].Value = sdrData.GetValue(i);
 col++;
 
 }
 row++;
 } 
 //Format the Date Columns if you know the format of the columns
 //worksheet.Cells[startRow, 3, row - 1, 4].Style.Numberformat.Format = "yyyy-mm-dd";
 
 //Set column width if required
 //worksheet.Column(1).Width = 30;
 }
 }
 // set some document properties
 package.Workbook.Properties.Title = excelTitle;
 package.Workbook.Properties.Author = excelAuthor;
 package.Workbook.Properties.Company = excelCompany;

// save the new workbook
 package.Save(); 
 StatusBar.WriteSucess(ruleName + @": Excel Export completed. File saved under " + filePath);
 // Open the Excel file if required
 if (openAfterSave)
 System.Diagnostics.Process.Start(filePath);
 }
}
catch (Exception ex)
{
 StatusBar.WriteError(errorMsg + ": " + ex.ToString());
 Debug.WriteMessage(errorMsg + ": " + ex.ToString(), SwissAddonFramework.Messaging.Debug.DebugLevel.Always);
}