private void Import()
{
OpenFileDialog openFileDialog1 = new OpenFileDialog();
openFileDialog1.Filter = FILE_FILTER;
openFileDialog1.Multiselect = false;
openFileDialog1.FileName = "";
DialogResult dr = openFileDialog1.ShowDialog();
if (dr == DialogResult.Cancel)
{
return;
}
string fileName = openFileDialog1.FileName;
if ((fileName == null) || (fileName == ""))
{
return;
}
FileInfo fi = new FileInfo(fileName);
string extension = fi.Extension;
string selectedWorksheet = "";
//Dealing with an Excel file
if ((String.Compare(extension, ".xls", true) == 0) || (String.Compare(extension, ".xlsx", true) == 0))
{
ExcelOleDb excelDb = new ExcelOleDb(fileName, true);
string[] names = excelDb.GetWorksheetNames();
//If there is only 1 worksheet in a workbook, lets not make the user select it. Just open it.
if (names.Length == 1)
{
selectedWorksheet = names[0];
}
else
{
frmChooseWorksheet chooseWorkSheet = new frmChooseWorksheet(fi.Name, names);
dr = chooseWorkSheet.ShowDialog();
if (dr == DialogResult.Cancel || chooseWorkSheet.SelectedWorksheetName == null ||
chooseWorkSheet.SelectedWorksheetName == "")
{
return;
}
selectedWorksheet = chooseWorkSheet.SelectedWorksheetName;
}
_dt = excelDb.Read(selectedWorksheet);
if ((_dt == null) || (_dt.Rows.Count < 1))
{
throw new Exception("The following Excel file and worksheet could not be imported: " + fileName + " : " + selectedWorksheet);
}
}
else if (String.Compare(extension, ".csv", true) == 0)
{
DelimitedFile delimFile = new DelimitedFile(fileName, ',', true);
_dt = delimFile.Read();
if ((_dt == null) || (_dt.Rows.Count < 1))
{
throw new Exception("The following CSV file could not be imported: " + fileName);
}
}
else
{
throw new Exception("Unknown file type: " + fileName);
}
_importFileName = fileName;
}
private bool Export()
{
//TODO: get overwrite existing file working, release locks on exported file when done
//and get this and IO.OLEDB in sync to work on general data types instead of VB2 data
Dictionary<string, string> tableInfo = new Dictionary<string, string>();
SaveFileDialog saveFileDialog1 = new SaveFileDialog();
saveFileDialog1.Filter = FILE_FILTER;
saveFileDialog1.FileName = "";
saveFileDialog1.OverwritePrompt = false;
DialogResult dr = saveFileDialog1.ShowDialog();
if (dr == DialogResult.Cancel)
return false;
string fileName = saveFileDialog1.FileName;
if ((fileName == null) || (fileName == ""))
return false;
FileInfo fi = new FileInfo(fileName);
//if files exists createTable will fail...
if (File.Exists(fileName))
{
MessageBox.Show("Cannot overwrite existing file; must provide unique name or save elsewhere.",
"Export failure", MessageBoxButtons.OK);
return false;
}
string extension = fi.Extension;
//if (extension != ".xls" && extension != ".xlsx" && extension != ".csv")
//{
// MessageBox.Show("Try again - only xls files working for sure.",
// "Save will fail if file exists.", MessageBoxButtons.OK);
// return;
//}
//Dealing with an Excel file
if ((String.Compare(".xls", extension, true) == 0) || (String.Compare(".xlsx", extension, true) == 0))
{
ExcelOleDb excelDb = new ExcelOleDb(fileName, true);
//excelDb.WriteTable("Sheet1", _rawdataDT);
if (_dt.TableName == "" || _dt.TableName == null) _dt.TableName = "VB2ExportedData";
bool writeSuccess = excelDb.WriteTable(_dt.TableName, getTableInfo(_dt));
//Console.WriteLine("export write success: " + writeSuccess);
if (writeSuccess)
{
for (int r = 0; r < _dt.Rows.Count; r++)
{
DataRow mydr = _dt.Rows[r];
bool insertSuccess = excelDb.AddNewRow(mydr);
//Console.WriteLine("export insert success: " + insertSuccess);
if (!insertSuccess)
{
MessageBox.Show("Issue with InsertRow at row = " + r.ToString() + ".", "OLEDB Export failure at insert row", MessageBoxButtons.OK);
return false; // break;
}
}
//something here to release lock on file... ???
}
//well this is stupid - REVISIT!!!!!!!!!!!!!!!!!!!!!!!!!
else
{
MessageBox.Show("Issue with CreatTable.", "OLEDB Export failure at createTable", MessageBoxButtons.OK);
return false;
}
}
else if (String.Compare(".csv", extension, true) == 0)
{
DelimitedFile delimFile = new DelimitedFile(fileName, ',', true);
delimFile.Write(_dt);
}
return true;
}
private bool Export()
{
//TODO: get overwrite existing file working, release locks on exported file when done
//and get this and IO.OLEDB in sync to work on general data types instead of VB2 data
Dictionary <string, string> tableInfo = new Dictionary <string, string>();
SaveFileDialog saveFileDialog1 = new SaveFileDialog();
saveFileDialog1.Filter = FILE_FILTER;
saveFileDialog1.FileName = "";
saveFileDialog1.OverwritePrompt = false;
DialogResult dr = saveFileDialog1.ShowDialog();
if (dr == DialogResult.Cancel)
{
return(false);
}
string fileName = saveFileDialog1.FileName;
if ((fileName == null) || (fileName == ""))
{
return(false);
}
FileInfo fi = new FileInfo(fileName);
//if files exists createTable will fail...
if (File.Exists(fileName))
{
MessageBox.Show("Cannot overwrite existing file; must provide unique name or save elsewhere.",
"Export failure", MessageBoxButtons.OK);
return(false);
}
string extension = fi.Extension;
//if (extension != ".xls" && extension != ".xlsx" && extension != ".csv")
//{
// MessageBox.Show("Try again - only xls files working for sure.",
// "Save will fail if file exists.", MessageBoxButtons.OK);
// return;
//}
//Dealing with an Excel file
if ((String.Compare(".xls", extension, true) == 0) || (String.Compare(".xlsx", extension, true) == 0))
{
ExcelOleDb excelDb = new ExcelOleDb(fileName, true);
//excelDb.WriteTable("Sheet1", _rawdataDT);
if (_dt.TableName == "" || _dt.TableName == null)
{
_dt.TableName = "VB2ExportedData";
}
bool writeSuccess = excelDb.WriteTable(_dt.TableName, getTableInfo(_dt));
//Console.WriteLine("export write success: " + writeSuccess);
if (writeSuccess)
{
for (int r = 0; r < _dt.Rows.Count; r++)
{
DataRow mydr = _dt.Rows[r];
bool insertSuccess = excelDb.AddNewRow(mydr);
//Console.WriteLine("export insert success: " + insertSuccess);
if (!insertSuccess)
{
MessageBox.Show("Issue with InsertRow at row = " + r.ToString() + ".", "OLEDB Export failure at insert row", MessageBoxButtons.OK);
return(false); // break;
}
}
//something here to release lock on file... ???
}
//well this is stupid - REVISIT!!!!!!!!!!!!!!!!!!!!!!!!!
else
{
MessageBox.Show("Issue with CreatTable.", "OLEDB Export failure at createTable", MessageBoxButtons.OK);
return(false);
}
}
else if (String.Compare(".csv", extension, true) == 0)
{
DelimitedFile delimFile = new DelimitedFile(fileName, ',', true);
delimFile.Write(_dt);
}
return(true);
}
private void Import()
{
OpenFileDialog openFileDialog1 = new OpenFileDialog();
openFileDialog1.Filter = FILE_FILTER;
openFileDialog1.Multiselect = false;
openFileDialog1.FileName = "";
DialogResult dr = openFileDialog1.ShowDialog();
if (dr == DialogResult.Cancel)
return;
string fileName = openFileDialog1.FileName;
if ((fileName == null) || (fileName == ""))
return;
FileInfo fi = new FileInfo(fileName);
string extension = fi.Extension;
string selectedWorksheet = "";
//Dealing with an Excel file
if ((String.Compare(extension, ".xls", true) == 0) || (String.Compare(extension, ".xlsx", true) == 0))
{
ExcelOleDb excelDb = new ExcelOleDb(fileName, true);
string[] names = excelDb.GetWorksheetNames();
//If there is only 1 worksheet in a workbook, lets not make the user select it. Just open it.
if (names.Length == 1)
selectedWorksheet = names[0];
else
{
frmChooseWorksheet chooseWorkSheet = new frmChooseWorksheet(fi.Name, names);
dr = chooseWorkSheet.ShowDialog();
if (dr == DialogResult.Cancel || chooseWorkSheet.SelectedWorksheetName == null ||
chooseWorkSheet.SelectedWorksheetName == "")
return;
selectedWorksheet = chooseWorkSheet.SelectedWorksheetName;
}
_dt = excelDb.Read(selectedWorksheet);
if ((_dt == null) || (_dt.Rows.Count < 1))
throw new Exception("The following Excel file and worksheet could not be imported: " + fileName + " : " + selectedWorksheet);
}
else if (String.Compare(extension, ".csv", true) == 0)
{
DelimitedFile delimFile = new DelimitedFile(fileName, ',', true);
_dt = delimFile.Read();
if ((_dt == null) || (_dt.Rows.Count < 1))
throw new Exception("The following CSV file could not be imported: " + fileName);
}
else
{
throw new Exception("Unknown file type: " + fileName);
}
_importFileName = fileName;
}
