public void UseTemplate(string path, string templatePath, string[,] myTemplateValues)
{
try
{
this.string_0 = myTemplateValues;
this.application_0 = new ApplicationClass();
this.workbook_0 = this.application_0.get_Workbooks().Open(templatePath, 0, false, 5, "", "", false, (XlPlatform) 2, "", true, false, 0, true, false, false);
this.sheets_0 = this.workbook_0.get_Sheets();
this.worksheet_0 = (Worksheet) this.sheets_0.get_Item(1);
this.worksheet_0.set_Name("ATemplate");
this.method_5();
this.method_4();
try
{
this.method_7(this.sheets_0);
this.method_7(this.worksheet_0);
this.workbook_0.Close(true, path, Type.Missing);
this.method_7(this.workbook_0);
this.application_0.set_UserControl(false);
this.application_0.Quit();
this.method_7(this.application_0);
this.method_6();
ProgressEventArgs e = new ProgressEventArgs(100);
this.OnProgressChange(e);
}
catch (COMException)
{
Console.WriteLine("用户手动关闭了Excel程序，导出操作不成功");
}
}
catch (Exception exception)
{
MessageBox.Show("错误 " + exception.Message);
}
}
public virtual void OnProgressChange(ProgressEventArgs e)
{
if (this.progressHandler_0 != null)
{
this.progressHandler_0(this, e);
}
}
public void ExportToExcel(List<DataView> dvList, string path, string sheetName)
{
Exception exception;
try
{
int num;
int num2;
this.list_0 = dvList;
this.application_0 = new ApplicationClass();
if (File.Exists(path))
{
this.workbook_0 = this.application_0.get_Workbooks().Open(path, 0, false, 5, "", "", false, (XlPlatform) 2, "", true, false, 0, true, false, false);
this.sheets_0 = this.workbook_0.get_Sheets();
num = -1;
for (num2 = 1; num2 <= this.sheets_0.get_Count(); num2++)
{
this.worksheet_0 = (Worksheet) this.sheets_0.get_Item(num2);
if (this.worksheet_0.get_Name().ToString().Equals(sheetName))
{
num = num2;
Range range = this.worksheet_0.get_Cells();
range.Select();
range.get_CurrentRegion().Select();
range.ClearContents();
}
}
if (num == -1)
{
((Worksheet) this.workbook_0.get_Sheets().Add(Type.Missing, (Worksheet) this.sheets_0.get_Item(this.sheets_0.get_Count()), Type.Missing, Type.Missing)).set_Name(sheetName);
}
}
else
{
this.workbook_0 = this.application_0.get_Workbooks().Add((XlWBATemplate) (-4167));
this.sheets_0 = this.workbook_0.get_Sheets();
this.worksheet_0 = (Worksheet) this.sheets_0.get_Item(1);
this.worksheet_0.set_Name(sheetName);
}
this.sheets_0 = this.workbook_0.get_Sheets();
num = -1;
for (num2 = 1; num2 <= this.sheets_0.get_Count(); num2++)
{
this.worksheet_0 = (Worksheet) this.sheets_0.get_Item(num2);
if (this.worksheet_0.get_Name().ToString().Equals(sheetName))
{
num = num2;
}
}
this.worksheet_0 = (Worksheet) this.sheets_0.get_Item(num);
this.method_0();
this.method_1();
this.method_4();
try
{
this.method_7(this.sheets_0);
this.method_7(this.worksheet_0);
this.workbook_0.Close(true, path, Type.Missing);
this.method_7(this.workbook_0);
this.application_0.set_UserControl(false);
this.application_0.Quit();
this.method_7(this.application_0);
this.method_6();
ProgressEventArgs e = new ProgressEventArgs(100);
this.OnProgressChange(e);
}
catch (COMException)
{
MessageBox.Show("用户手动关闭了Excel程序，导出操作不成功");
}
catch (Exception exception2)
{
exception = exception2;
MessageBox.Show("错误 " + exception.Message);
}
}
catch (Exception exception3)
{
exception = exception3;
MessageBox.Show("错误 " + exception.Message);
}
}
private void method_5()
{
int num = 3;
int num2 = 2;
this.int_0 = 0;
for (int i = 0; i <= this.string_0.GetUpperBound(0); i++)
{
for (int j = 0; j <= this.string_0.GetUpperBound(1); j++)
{
this.int_0 = (100 / this.string_0.Length) * i;
ProgressEventArgs e = new ProgressEventArgs(this.int_0);
this.OnProgressChange(e);
Range range = (Range) this.worksheet_0.get_Cells().get__Default(num, num2++);
range.Select();
range.set_NumberFormat("@");
range.set_Value2(this.string_0[i, j].ToString());
range.get_Rows().get_EntireRow().AutoFit();
}
num2 = 2;
num++;
}
}
private void method_1()
{
this.int_0 = 0;
int num = 1;
int num2 = 1;
int num3 = 1;
int num4 = 0;
foreach (DataView view in this.list_0)
{
num4 += view.Count + 1;
}
foreach (DataView view in this.list_0)
{
int num5 = 0;
while (num5 < view.Table.Columns.Count)
{
this.method_2(this.worksheet_0, num, num2++, view.Table.Columns[num5].ToString(), this.style_1.get_Name());
num5++;
}
num++;
num2 = 1;
num3++;
for (num5 = 0; num5 < view.Table.Rows.Count; num5++)
{
for (int i = 0; i < view.Table.Columns.Count; i++)
{
this.method_2(this.worksheet_0, num, num2++, view[num5][i].ToString(), this.style_0.get_Name());
}
this.int_0 = (100 * num3) / num4;
ProgressEventArgs e = new ProgressEventArgs(this.int_0);
this.OnProgressChange(e);
num2 = 1;
num++;
num3++;
}
num += 2;
}
}
//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("用户手动关闭了Excel程序，导出操作不成功");
}
catch (Exception ex)
{
MessageBox.Show("错误 " + ex.Message);
}
#endregion
}
catch (Exception ex)
{
MessageBox.Show("错误 " + ex.Message);
}
}
