//Exports a DataView to Excel. The following steps are carried out
//in order to export the DataView to Excel
//Create Excel Objects
//Create Column & Row Workbook Cell Rendering Styles
//Fill Worksheet With DataView
//Add Auto Shapes To Excel Worksheet
//Select All Used Cells
//Create Headers/Footers
//Set Status Finished
//Save workbook & Tidy up all objects
//@param dv : DataView to use
//@param path : The path to save/open the EXCEL file to/from
//@param sheetName : The target sheet within the EXCEL file
public void Export1(DataView dv, string path, string sheetName, ArrayList columnsProperties)
{
try
{
//Assign Instance Fields
this.dv = dv;
this._columnsProperties = columnsProperties;
#region NEW EXCEL DOCUMENT : Create Excel Objects
//create new EXCEL application
EXL = new Excel.ApplicationClass();
//index to hold location of the requested sheetName in the workbook sheets
//collection
int indexOfsheetName;
#region FILE EXISTS
//Does the file exist for the given path
if (File.Exists(path))
{
//Yes file exists, so open the file
workbook = EXL.Workbooks.Open(path,
0, false, 5, "", "", false, Excel.XlPlatform.xlWindows, "",
true, false, 0, true, false, false);
//get the workbook sheets collection
sheets = workbook.Sheets;
//set the location of the requested sheetName to -1, need to find where
//it is. It may not actually exist
indexOfsheetName = -1;
//loop through the sheets collection
for (int i = 1; i <= sheets.Count; i++)
{
//get the current worksheet at index (i)
worksheet = (Worksheet)sheets.get_Item(i);
//is the current worksheet the sheetName that was requested
if (worksheet.Name.ToString().Equals(sheetName))
{
//yes it is, so store its index
indexOfsheetName = i;
//Select all cells, and clear the contents
Excel.Range myAllRange = worksheet.Cells;
myAllRange.Select();
myAllRange.CurrentRegion.Select();
myAllRange.ClearContents();
}
}
//At this point it is known that the sheetName that was requested
//does not exist within the found file, so create a new sheet within the
//sheets collection
if (indexOfsheetName == -1)
{
//Create a new sheet for the requested sheet
Worksheet sh = (Worksheet)workbook.Sheets.Add(
Type.Missing, (Worksheet)sheets.get_Item(sheets.Count),
Type.Missing, Type.Missing);
//Change its name to that requested
sh.Name = sheetName;
}
}
#endregion
#region FILE DOESNT EXIST
//No the file DOES NOT exist, so create a new file
else
{
//Add a new workbook to the file
workbook = EXL.Workbooks.Add(XlWBATemplate.xlWBATWorksheet);
//get the workbook sheets collection
sheets = workbook.Sheets;
//get the new sheet
worksheet = (Worksheet)sheets.get_Item(1);
//Change its name to that requested
worksheet.Name = sheetName;
}
#endregion
#region get correct worksheet index for requested sheetName
//get the workbook sheets collection
sheets = workbook.Sheets;
//set the location of the requested sheetName to -1, need to find where
//it is. It will definately exist now as it has just been added
indexOfsheetName = -1;
//loop through the sheets collection
for (int i = 1; i <= sheets.Count; i++)
{
//get the current worksheet at index (i)
worksheet = (Worksheet)sheets.get_Item(i);
//is the current worksheet the sheetName that was requested
if (worksheet.Name.ToString().Equals(sheetName))
{
//yes it is, so store its index
indexOfsheetName = i;
}
}
//set the worksheet that the DataView should write to, to the known index of the
//requested sheet
worksheet = (Worksheet)sheets.get_Item(indexOfsheetName);
#endregion
#endregion
// Set styles 1st
SetUpStyles();
//Fill EXCEL worksheet with DataView values
fillWorksheet_WithDataView();
//Add the autoshapes to EXCEL
AddAutoShapesToExcel();
//Select all used cells within current worksheet
SelectAllUsedCells();
/*
* try
* {
* workbook.Close(true, path, Type.Missing);
* EXL.UserControl = false;
* EXL.Quit();
* EXL = null;
* //kill the EXCEL process as a safety measure
* killExcel();
* // Show that processing is finished
* ProgressEventArgs pe = new ProgressEventArgs(100);
* OnProgressChange(pe);
* MessageBox.Show("Finished adding dataview to Excel", "Info", MessageBoxButtons.OK, MessageBoxIcon.Information);
* }
* catch (COMException cex)
* {
* MessageBox.Show("User closed Excel manually, so we don't have to do that");
* }
* catch (Exception ex)
* {
* MessageBox.Show("Error " + ex.Message);
* }
*/
EXL.Visible = true;
worksheet = (Worksheet)EXL.ActiveSheet;
worksheet.Activate();
}
catch (Exception ex)
{
MessageBox.Show("Error " + ex.Message);
}
}
