//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 ExportToExcel(List<DataView> dvList,string path, string sheetName)
{
try
{
//Assign Instance Fields
this.dvList = dvList;
#region NEW EXCEL DOCUMENT : Create Excel Objects
//create new EXCEL application
EXL = new Microsoft.Office.Interop.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, Microsoft.Office.Interop.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
Microsoft.Office.Interop.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();
#region Finish and Release
try
{
NAR(sheets);
NAR(worksheet);
workbook.Close(true, path, Type.Missing);
NAR(workbook);
EXL.UserControl = false;
EXL.Quit();
NAR(EXL);
//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);
}
#endregion
}
catch (Exception ex)
{
MessageBox.Show("Error " + ex.Message);
}
}
//Fills an Excel worksheet with the values contained in the DataView
//parameter
private void FillWorksheet_WithDataView()
{
position = 0;
//Add DataView Columns To Worksheet
int row = 1;
int col = 1;
int rowIndex = 1;//��¼д��Excel��Row�����
int allRowsCount = 0;
foreach (DataView dv in dvList)
{
allRowsCount += dv.Count + 1;//��ͷ����һ��
}
foreach (DataView dv in dvList)
{
// Loop thought the columns
for (int i = 0; i < dv.Table.Columns.Count; i++)
{
FillExcelCell(worksheet, row, col++, dv.Table.Columns[i].ToString(), styleColumnHeadings.Name);
}
//Add DataView Rows To Worksheet
row++;
col = 1;
rowIndex++;
for (int i = 0; i < dv.Table.Rows.Count; i++)
{
for (int j = 0; j < dv.Table.Columns.Count; j++)
{
FillExcelCell(worksheet, row, col++, dv[i][j].ToString(), styleRows.Name);
}
position = (100 * rowIndex) / (allRowsCount);
ProgressEventArgs pe = new ProgressEventArgs(position);
OnProgressChange(pe);
col = 1;
row++;
rowIndex++;
}
row = row + 2;//�ڶ�����ʼ������
}
}
//Exports a DataView to Excel. The following steps are carried out
//in order to export the DataView to Excel
//Create Excel Objects And Open Template File
//Select All Used Cells
//Create Headers/Footers
//Set Status Finished
//Save workbook & Tidy up all objects
//@param path : The path to save/open the EXCEL file to/from
public void UseTemplate(string path, string templatePath, string[,] myTemplateValues)
{
try
{
this.myTemplateValues = myTemplateValues;
//create new EXCEL application
EXL = new Microsoft.Office.Interop.Excel.ApplicationClass();
//Yes file exists, so open the file
workbook = EXL.Workbooks.Open(templatePath,
0, false, 5, "", "", false, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "",
true, false, 0, true, false, false);
//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 = "ATemplate";
//Fills the Excel Template File Selected With A 2D Test Array
FillTemplate_WithTestValues();
//Select all used cells within current worksheet
SelectAllUsedCells();
#region Finish and Release
try
{
NAR(sheets);
NAR(worksheet);
workbook.Close(true, path, Type.Missing);
NAR(workbook);
EXL.UserControl = false;
EXL.Quit();
NAR(EXL);
//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 test values to Template", "Info", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
catch (COMException)
{
Console.WriteLine("User closed Excel manually, so we don't have to do that");
}
#endregion
}
catch (Exception ex)
{
MessageBox.Show("Error " + ex.Message);
}
}
//Fills the Excel Template File Selected With A 2D Test Array parameter
private void FillTemplate_WithTestValues()
{
//Initilaise the correct Start Row/Column to match the Template
int StartRow = 3;
int StartCol = 2;
position=0;
// Display the array elements within the Output window, make sure its correct before
for (int i=0; i <= myTemplateValues.GetUpperBound(0); i++)
{
//loop through array and put into EXCEL template
for (int j = 0 ; j <= myTemplateValues.GetUpperBound(1) ; j++)
{
//update position in progress bar
position = (100 / myTemplateValues.Length) * i;
ProgressEventArgs pe = new ProgressEventArgs(position);
OnProgressChange(pe);
//put into EXCEL template
Range rng = (Range)worksheet.Cells[StartRow,StartCol++];
rng.Select();
rng.Value2 = myTemplateValues[i,j].ToString();
rng.Rows.EntireRow.AutoFit();
}
//New row, so column needs to be reset
StartCol=2;
StartRow++;
}
}
/// Raises the OnProgressChange event for the parent form.
public virtual void OnProgressChange(ProgressEventArgs e)
{
if (OnProgressHandler != null)
{
// Invokes the delegates.
OnProgressHandler(this, e);
}
}