Hi,
I am exporting the list details to excel as shown below using SaveFileDialog option. But from SaveFileDialog, i am not able to get the full path of the file. So while saving the excel sheet , i am not able to give the user selected folder name. By Default , it is saving in the documents folder.
How to get the user selected folder path. Below is my code.
public void ExportButtonHandler()
{
int ll_ExcelRow = 0;
int ll_PhraseRows;
string as_TabPage_Tag = "Phrase Dictionary";
SaveFileDialog dialog = new SaveFileDialog();
dialog.DefaultExt = "*.xls";
dialog.Filter = "Grid Data (*.xls)|*.xls";
dialog.DefaultFileName = DateTime.Now.ToString("yyyyMMddHHmmss").ToString() + "_" + SelectedLanguage.LANGUAGE_CODE.ToString();
if (dialog.ShowDialog() == true)
{
int activeSheet;
ll_PhraseRows = SearchResults.Count;
List<string> sheetNames = new List<string>();
sheetNames.Add("Standard Phrases");
sheetNames.Add("Phrase Dictionary");
List<string> sheetColumnNames = new List<string>();
sheetColumnNames.Add("PHR_ID");
sheetColumnNames.Add("PHR_TEXT");
sheetColumnNames.Add("LANGUAGE_CODE");
sheetColumnNames.Add("LANGUAGE_DESC");
sheetColumnNames.Add("TRANSLATION");
sheetColumnNames.Add("USER_LAST_UPDT");
sheetColumnNames.Add("TMSP_LAST_UPDT");
dynamic excel = AutomationFactory.CreateObject("Excel.Application");
excel.Visible = false;
dynamic iole_Workbook = null;
if (!File.Exists(dialog.DefaultFileName))
{
double li_SheetsInNewWorkbook = excel.SheetsInNewWorkbook; //add a new workbook with 2 sheets
excel.SheetsInNewWorkbook = 2;
// add a workbook to the instance
iole_Workbook = excel.workbooks.Add();
excel.SheetsInNewWorkbook = li_SheetsInNewWorkbook;
}
else
{
iole_Workbook = excel.Workbooks.Open(dialog.SafeFileName);
}
dynamic iole_Sheet = null;
int li_Sheets;
int liNames = sheetNames.Count;
for (int liName = 0; liName < liNames; liName++)// For each of the possible tabpage tags
{
li_Sheets = iole_Workbook.Sheets.Count;// get count of sheets in workbook
for (int li_Sheet1 = 1; li_Sheet1 <= li_Sheets; li_Sheet1++)// go through each of the workbook sheets
{
iole_Sheet = iole_Workbook.WorkSheets(li_Sheet1);
if (iole_Sheet.Name == sheetNames[liName]) // If the current sheet name matches the tag, then exit
{
break;
}
if (li_Sheet1 == li_Sheets) // Otherwise, if on the final sheet with no match,
{
for (int li_Sheet2 = 1; li_Sheet2 <= li_Sheets; li_Sheet2++) // go through the sheets again looking for one
{
iole_Sheet = iole_Workbook.WorkSheets(li_Sheet2);
if (iole_Sheet.Name == "Sheet1" || iole_Sheet.Name == "Sheet2" || iole_Sheet.Name == "Sheet3" || iole_Sheet.Name == "Sheet4" || iole_Sheet.Name == "Sheet5")
{
// If a 'Sheet?' sheet is found, and has no data, then exit
//if String(iole_Sheet.Cells.SpecialCells (EXCEL_CELL_TYPE_LAST_CELL, &
//EXCEL_CELL_TYPE_BLANKS).Address) = "$A$1" then Exit
break;
}
if (li_Sheet2 == li_Sheets)
{
iole_Workbook.Worksheets.Add();// Otherwise, if on the final sheet with no blank 'Sheet?' found,
// add a new sheet (will be named 'Sheet1')
}
}
}
}
if (iole_Sheet.Name == as_TabPage_Tag || iole_Sheet.Name == "Sheet1" || iole_Sheet.Name == "Sheet2" || iole_Sheet.Name == "Sheet3" || iole_Sheet.Name == "Sheet4")
{
iole_Sheet.Select();
iole_Sheet.Name = sheetNames[liName];
iole_Sheet.Cells.Select();
excel.Selection.ClearContents();
for (int li_Token = 0; li_Token < sheetColumnNames.Count; li_Token++)
{
// and set the column names into row 1
iole_Sheet.Cells[1, li_Token + 1].Value = sheetColumnNames[li_Token];
}
string ls_TextOnlyRange = "A:" + (Convert.ToChar(64 + sheetColumnNames.Count)).ToString();
excel.Selection.NumberFormat = "@";
iole_Sheet.Rows["1:1"].Select();
excel.Selection.Font.Bold = true;
excel.Selection.Columns.AutoFit();
iole_Sheet.Range["A2"].Select();
if (sheetNames[liName] == as_TabPage_Tag)
{
activeSheet = liName;
for (int row = 0; row < ll_PhraseRows; row++)
{
ll_ExcelRow = row + 1;
string ls_User_Last_Updt = SearchResults[row].USER_LAST_UPDT;
if (ls_User_Last_Updt == "(NONE)")
{
ls_User_Last_Updt = "";
}
if (as_TabPage_Tag == "Phrase Dictionary")
{
iole_Sheet.Cells[ll_ExcelRow, 1].Value = SearchResults[row].PHR_ID;
iole_Sheet.Cells[ll_ExcelRow, 2].Value = SearchResults[row].PHR_TEXT;
iole_Sheet.Cells[ll_ExcelRow, 5].Value = SearchResults[row].PHR_TEXT_TRANS;
iole_Sheet.Cells[ll_ExcelRow, 6].Value = ls_User_Last_Updt;
iole_Sheet.Cells[ll_ExcelRow, 7].Value = SearchResults[row].TMSP_LAST_UPDT;
}
iole_Sheet.Cells[ll_ExcelRow, 3].Value = SelectedLanguage.LANGUAGE_CODE; // Language code and description
iole_Sheet.Cells[ll_ExcelRow, 4].Value = SelectedLanguage.LANGUAGE_DESC; // are always in the same columns
}
iole_Sheet.Range["A2"].Select();
}
}
}
iole_Sheet = iole_Workbook.WorkSheets(as_TabPage_Tag);
iole_Sheet.Select();
iole_Sheet.Range["A2"].Select();
iole_Workbook.SaveAs(dialog.DefaultFileName);
excel.Quit();
}
Please suggest me the way to get the user selected folder path.
Thanks
Gopinath
I am exporting the list details to excel as shown below using SaveFileDialog option. But from SaveFileDialog, i am not able to get the full path of the file. So while saving the excel sheet , i am not able to give the user selected folder name. By Default , it is saving in the documents folder.
How to get the user selected folder path. Below is my code.
public void ExportButtonHandler()
{
int ll_ExcelRow = 0;
int ll_PhraseRows;
string as_TabPage_Tag = "Phrase Dictionary";
SaveFileDialog dialog = new SaveFileDialog();
dialog.DefaultExt = "*.xls";
dialog.Filter = "Grid Data (*.xls)|*.xls";
dialog.DefaultFileName = DateTime.Now.ToString("yyyyMMddHHmmss").ToString() + "_" + SelectedLanguage.LANGUAGE_CODE.ToString();
if (dialog.ShowDialog() == true)
{
int activeSheet;
ll_PhraseRows = SearchResults.Count;
List<string> sheetNames = new List<string>();
sheetNames.Add("Standard Phrases");
sheetNames.Add("Phrase Dictionary");
List<string> sheetColumnNames = new List<string>();
sheetColumnNames.Add("PHR_ID");
sheetColumnNames.Add("PHR_TEXT");
sheetColumnNames.Add("LANGUAGE_CODE");
sheetColumnNames.Add("LANGUAGE_DESC");
sheetColumnNames.Add("TRANSLATION");
sheetColumnNames.Add("USER_LAST_UPDT");
sheetColumnNames.Add("TMSP_LAST_UPDT");
dynamic excel = AutomationFactory.CreateObject("Excel.Application");
excel.Visible = false;
dynamic iole_Workbook = null;
if (!File.Exists(dialog.DefaultFileName))
{
double li_SheetsInNewWorkbook = excel.SheetsInNewWorkbook; //add a new workbook with 2 sheets
excel.SheetsInNewWorkbook = 2;
// add a workbook to the instance
iole_Workbook = excel.workbooks.Add();
excel.SheetsInNewWorkbook = li_SheetsInNewWorkbook;
}
else
{
iole_Workbook = excel.Workbooks.Open(dialog.SafeFileName);
}
dynamic iole_Sheet = null;
int li_Sheets;
int liNames = sheetNames.Count;
for (int liName = 0; liName < liNames; liName++)// For each of the possible tabpage tags
{
li_Sheets = iole_Workbook.Sheets.Count;// get count of sheets in workbook
for (int li_Sheet1 = 1; li_Sheet1 <= li_Sheets; li_Sheet1++)// go through each of the workbook sheets
{
iole_Sheet = iole_Workbook.WorkSheets(li_Sheet1);
if (iole_Sheet.Name == sheetNames[liName]) // If the current sheet name matches the tag, then exit
{
break;
}
if (li_Sheet1 == li_Sheets) // Otherwise, if on the final sheet with no match,
{
for (int li_Sheet2 = 1; li_Sheet2 <= li_Sheets; li_Sheet2++) // go through the sheets again looking for one
{
iole_Sheet = iole_Workbook.WorkSheets(li_Sheet2);
if (iole_Sheet.Name == "Sheet1" || iole_Sheet.Name == "Sheet2" || iole_Sheet.Name == "Sheet3" || iole_Sheet.Name == "Sheet4" || iole_Sheet.Name == "Sheet5")
{
// If a 'Sheet?' sheet is found, and has no data, then exit
//if String(iole_Sheet.Cells.SpecialCells (EXCEL_CELL_TYPE_LAST_CELL, &
//EXCEL_CELL_TYPE_BLANKS).Address) = "$A$1" then Exit
break;
}
if (li_Sheet2 == li_Sheets)
{
iole_Workbook.Worksheets.Add();// Otherwise, if on the final sheet with no blank 'Sheet?' found,
// add a new sheet (will be named 'Sheet1')
}
}
}
}
if (iole_Sheet.Name == as_TabPage_Tag || iole_Sheet.Name == "Sheet1" || iole_Sheet.Name == "Sheet2" || iole_Sheet.Name == "Sheet3" || iole_Sheet.Name == "Sheet4")
{
iole_Sheet.Select();
iole_Sheet.Name = sheetNames[liName];
iole_Sheet.Cells.Select();
excel.Selection.ClearContents();
for (int li_Token = 0; li_Token < sheetColumnNames.Count; li_Token++)
{
// and set the column names into row 1
iole_Sheet.Cells[1, li_Token + 1].Value = sheetColumnNames[li_Token];
}
string ls_TextOnlyRange = "A:" + (Convert.ToChar(64 + sheetColumnNames.Count)).ToString();
excel.Selection.NumberFormat = "@";
iole_Sheet.Rows["1:1"].Select();
excel.Selection.Font.Bold = true;
excel.Selection.Columns.AutoFit();
iole_Sheet.Range["A2"].Select();
if (sheetNames[liName] == as_TabPage_Tag)
{
activeSheet = liName;
for (int row = 0; row < ll_PhraseRows; row++)
{
ll_ExcelRow = row + 1;
string ls_User_Last_Updt = SearchResults[row].USER_LAST_UPDT;
if (ls_User_Last_Updt == "(NONE)")
{
ls_User_Last_Updt = "";
}
if (as_TabPage_Tag == "Phrase Dictionary")
{
iole_Sheet.Cells[ll_ExcelRow, 1].Value = SearchResults[row].PHR_ID;
iole_Sheet.Cells[ll_ExcelRow, 2].Value = SearchResults[row].PHR_TEXT;
iole_Sheet.Cells[ll_ExcelRow, 5].Value = SearchResults[row].PHR_TEXT_TRANS;
iole_Sheet.Cells[ll_ExcelRow, 6].Value = ls_User_Last_Updt;
iole_Sheet.Cells[ll_ExcelRow, 7].Value = SearchResults[row].TMSP_LAST_UPDT;
}
iole_Sheet.Cells[ll_ExcelRow, 3].Value = SelectedLanguage.LANGUAGE_CODE; // Language code and description
iole_Sheet.Cells[ll_ExcelRow, 4].Value = SelectedLanguage.LANGUAGE_DESC; // are always in the same columns
}
iole_Sheet.Range["A2"].Select();
}
}
}
iole_Sheet = iole_Workbook.WorkSheets(as_TabPage_Tag);
iole_Sheet.Select();
iole_Sheet.Range["A2"].Select();
iole_Workbook.SaveAs(dialog.DefaultFileName);
excel.Quit();
}
Please suggest me the way to get the user selected folder path.
Thanks
Gopinath