public void Run()
{
// this example shows you another dispose method: DisposeChildInstances
// this means all child proxies from an instance
// start application
Excel.Application application = new Excel.Application();
application.DisplayAlerts = false;
Excel.Workbook book = application.Workbooks.Add();
Excel.Worksheet sheet = (Excel.Worksheet)book.Worksheets.Add();
/*
* we have 5 created proxies now in proxy table as follows
*
* Application
* + Workbooks
* + Workbook
* + Worksheets
* + Worksheet
*/
// we dispose the child instances from book
book.DisposeChildInstances();
/*
* we have 3 created proxies now, the childs from book are disposed
*
* Application
* + Workbooks
* + Workbook
*/
application.Quit();
application.Dispose();
// the Dispose() call for application release the instance and created childs Workbooks and Workbook
HostApplication.ShowFinishDialog();
}
public ImportBuildingDataFileResponse ImportBuildingDataFile(ImportBuildingDataFileRequest request) {
ImportBuildingDataFileResponse response = null;
DataSet ds = null;
try {
response = new ImportBuildingDataFileResponse();
//read file from excel, using NetOffice
Excel.Application excelApplication = null;
using (excelApplication = new Excel.Application()) {
Excel.Workbook workBook = excelApplication.Workbooks.Open(request.SourceFilePath,
0, true, 5, "", "", true, null, "\t", false, false, 0, true, 1, 0);
Excel.Worksheet workSheet = (Excel.Worksheet)workBook.Sheets[1];
Excel.Range excelRange = workSheet.UsedRange;
int rowCount = excelRange.Rows.Count;
int colCount = excelRange.Columns.Count;
//System.Data.SQLite.SQLiteConnection cn = new SQLiteConnection(@"Data Source=C:\sqlite\test.db;Version=3;New=True;Compress=True;");
//SQLiteCommand sqlite_cmd;
//SQLiteDataReader sqlite_datareader;
//// open the connection:
//cn.Open();
//// create a new SQL command:
//sqlite_cmd = cn.CreateCommand();
//// Let the SQLiteCommand object know our SQL-Query:
//sqlite_cmd.CommandText = "CREATE TABLE hello (id integer primary key, text varchar(100));";
//// Now lets execute the SQL ;D
//sqlite_cmd.ExecuteNonQuery();
ds = new DataSet();
ds.Tables.Add("Source");
ds.AcceptChanges();
BuildingDataAssessor record = null;
for (int i = 1; i <= rowCount; i++) {
record = new BuildingDataAssessor();
for (int j = 1; j <= colCount; j++) {
//MessageBox.Show(workSheet.Cells[i, j].Value.ToString());
switch (j) {
case 1:
record.Owner = workSheet.Cells[i, j].Value.ToString();
break;
case 2:
record.BuildingLocation = workSheet.Cells[i, j].Value.ToString();
break;
case 3:
record.LandArea = Convert.ToDouble(workSheet.Cells[i, j].Value.ToString());
break;
case 4:
record.BuildingType = workSheet.Cells[i, j].Value.ToString();
break;
case 5:
record.DateContructed = Convert.ToDateTime(workSheet.Cells[i, j].Value.ToString());
break;
case 6:
record.BuildingCost = Convert.ToDouble(workSheet.Cells[i, j].Value.ToString());
break;
}
}
//DataRow newRow = ds.Tables[0].NewRow();
ds.Tables[0].Rows.Add(new object[] {
record.Owner,
record.BuildingLocation,
record.LandArea,
record.BuildingType,
record.DateContructed,
record.BuildingCost
});
//newRow[0] = record.Owner;
ds.AcceptChanges();
// // Lets insert something into our new table:
// sqlite_cmd.CommandText = "INSERT INTO test (id, text) VALUES (1, 'Test Text 1');";
// // And execute this again ;D
// sqlite_cmd.ExecuteNonQuery();
}
//// We are ready, now lets cleanup and close our connection:
//cn.Close();
//ds.
ds.WriteXml(request.SourceDataPath);
workBook.DisposeChildInstances();
workBook = null;
excelApplication.Quit();
//excelApplication.Dispose();
//excelApplication = null;
}
//save to database
return response;
}
finally {
response = null;
ds = null;
}
}