public static Excel.Worksheet SheetReset(Excel.Workbook Wb, string name, bool QuietMode = false)
{
Log.set(@"SheetReset(" + Wb.Name + "/" + name + ")");
try
{
if (isSheetExist(Wb, name))
{
Excel.Worksheet oldSh = Wb.Worksheets[name];
Wb.Worksheets.Add(Before: oldSh);
_sh = Wb.ActiveSheet;
Wb.Application.DisplayAlerts = false;
oldSh.Delete();
Wb.Application.DisplayAlerts = true;
}
else
{
if (!QuietMode)
{
Log.Warning("Лист(" + Wb.Name + "/" + name + ") не существовал. Создал новый.");
}
Wb.Worksheets.Add();
_sh = Wb.ActiveSheet;
}
_sh.Name = name;
}
catch (Exception e) { Log.FATAL("ошибка \"" + e.Message + "\""); }
Log.exit();
return(_sh);
}
protected override void Execute(CodeActivityContext context)
{
Console.WriteLine("Delete");
var EngineInstance = (Program)instance.Get(context);
string InstanceName = instanceName.Get(context);
string sheetName = SheetName.Get(context);
object excel;
//Console.WriteLine(sendingInstance.GetType());
if (EngineInstance.appInstance.TryGetValue(InstanceName, out excel))
{
Console.WriteLine("Success");
//Excel.Workbook eWB = (Excel.Workbook)excel;
Excel.Application eXL = (Excel.Application)excel;
Excel.Workbook eWB = eXL.ActiveWorkbook;
Excel.Worksheet eWS = eWB.Worksheets.Item[sheetName];
eWS.Delete();
//ReleaseExcelObject(eWB);
}
else
{
Console.WriteLine("Fail");
}
}
public void testAddNewSheet(string distFileName)
{
excelApp = new Excel.Application();
excelWBook = excelApp.Workbooks.Open(distFileName, null, false, 5, "", "", true, Excel.XlPlatform.xlWindows, "\t", true, false, null, true, 1, 0);
// Заполняем главный лист
excelWSheet = (Excel.Worksheet)excelWBook.Worksheets.Item[1];
excelWSheet.Name = "Лист " + currentSheetNumber;
int i = 10;
while (i > 0)
{
addNewSheetForm4A();
i--;
}
excelApp.DisplayAlerts = false;
i = 5;
while (i > 0)
{
Excel.Worksheet deletedSheet = excelWBook.Worksheets.Item[i];
deletedSheet.Delete();
i--;
}
excelApp.DisplayAlerts = true;
excelWBook.Close(true, misValue, misValue);
excelApp.Quit();
releaseOdject(excelWSheet);
releaseOdject(excelWBook);
releaseOdject(excelApp);
}
static void MergeExcels(Excel._Application app, IEnumerable <string> sourcePaths, string destinationPath)
{
var dstWb = app.Workbooks.Add("");
var srcWbs = sourcePaths.Select(sourcePath => app.Workbooks.Add(sourcePath));
// Don't start with i = 1 because trying to delete the last sheet makes an error.
for (var i = dstWb.Worksheets.Count; 2 <= i; i--)
{
dstWb.Worksheets[i].Delete();
}
// Keep the last sheet to be deleted when it is no longer the last sheet.
Excel.Worksheet ws1 = dstWb.Worksheets[1];
// app.Workbooks[1] is a destination so needs to be skipped.
for (var i = app.Workbooks.Count; 2 <= i; i--)
{
var srcWb = app.Workbooks[i];
for (var j = srcWb.Worksheets.Count; 1 <= j; j--)
{
srcWb.Worksheets[j].Copy(dstWb.Worksheets[1]);
}
}
ws1.Delete();
dstWb.SaveAs(destinationPath);
foreach (var srcWb in srcWbs)
{
srcWb.Close();
}
}
public void ExcelTricksRun(string[] strFiles, string strDeleteSheet)
{
Excel.Application excel = null;
Excel.Workbook wkb = null;
Excel.Worksheet wks = null;
excel = new Excel.Application();
excel.Visible = false;
foreach (string strFile in strFiles)
{
wkb = General.OpenExcelFile(excel, strFile);
Thread.Sleep(1000);
for (int i = wkb.Worksheets.Count; i > 0; i--)
{
wks = wkb.Worksheets[i];
if (wks.Name == strDeleteSheet)
{
DeletedFilesInfo = wkb.Name + " => " + wks.Name;
wks.Delete();
}
}
wkb.Close(true);
}
excel.Quit();
}
static void copypaste(Excel.Application excelapp, Excel.Workbook source, Excel.Workbook destination, string worksheetname, string filter, int filtercolumn, string lastcolumn)
{
Excel.Worksheet sourceworksheet = source.Worksheets[worksheetname];
sourceworksheet.Copy(destination.Worksheets[1]);
Excel.Worksheet destinationworksheet = destination.Worksheets[worksheetname];
long rows = sourceworksheet.UsedRange.Rows.Count;
destinationworksheet.Rows["2:" + rows + 1].Delete(Excel.XlDeleteShiftDirection.xlShiftUp);
string filterrange = "A1:" + lastcolumn + rows;
string copyrange = "A2:" + lastcolumn + rows;
try
{
sourceworksheet.Range[filterrange].AutoFilter(filtercolumn, "=*" + filter + "*", Excel.XlAutoFilterOperator.xlAnd);
sourceworksheet.Range[copyrange].SpecialCells(Excel.XlCellType.xlCellTypeVisible).Copy();
destinationworksheet.Range[copyrange].PasteSpecial();
//destinationworksheet.Rows["2"].Delete(Excel.XlDeleteShiftDirection.xlShiftUp);
}
catch (Exception)
{
destinationworksheet.Delete();
Console.WriteLine("Cannot filter " + worksheetname + ", sheet removed for " + destination.Path);
}
}
public void RemoveWorsheet(SheetTab sheetTab)
{
Excel.Worksheet worksheet = (Excel.Worksheet)Application.ActiveWorkbook.Worksheets[sheetTab.Worksheet.Index];
worksheet.Delete();
NumTabs--;
}
public static void TestToPDF()
{
string strFileName = @"D:/BaiduNetdiskDownload/#137959/20180125_1つの具体例を作る/03_XXX_ABC株式会社（レポート）.xls";
Action <Excel.Application, Excel.Workbook> action = (Excel.Application app, Excel.Workbook wb) =>
{
//wb.Worksheets.Select();//导出全部sheet
//wb.ExportAsFixedFormat(Excel.XlFixedFormatType.xlTypePDF,
// @"D:/BaiduNetdiskDownload/#137959/20180125_1つの具体例を作る/20180227_01.pdf",
// Excel.XlFixedFormatQuality.xlQualityStandard);
Excel.Workbook tmpWb = app.Workbooks.Add();
for (int i = 3; i <= 25; i++)
{
Excel.Worksheet copysheet = (Excel.Worksheet)wb.Sheets[i];
copysheet.Copy(tmpWb.Worksheets[tmpWb.Worksheets.Count]);
}
Excel.Worksheet lastsheet = (Excel.Worksheet)tmpWb.Worksheets[tmpWb.Worksheets.Count];
Log.Println("最后一个sheet名： " + lastsheet.Name);
if (lastsheet.Name.Equals("Sheet1"))
{
lastsheet.Delete(); //删除最后一个名字是Sheet1的sheet
}
tmpWb.Worksheets.Select();
tmpWb.ExportAsFixedFormat(Excel.XlFixedFormatType.xlTypePDF,
@"D:/BaiduNetdiskDownload/#137959/20180125_1つの具体例を作る/20180227_01.pdf",
Excel.XlFixedFormatQuality.xlQualityStandard);
Log.Println("PDF文件导出成功。");
//tmpWb.SaveAs(@"D:/BaiduNetdiskDownload/#137959/20180125_1つの具体例を作る/tmp20180227_01.xls");
//tmpWb.Close();
tmpWb.Close(false, Type.Missing, Type.Missing);
};
ExcelUtil.GetExcelWorkbook(strFileName, action);
}
// This will remove the sheet named if it exists
internal void DeleteSheetByName(string sheetName)
{
if (WorksheetExists(sheetName))
{
Excel.Worksheet worksheet = (Excel.Worksheet)Application.ActiveWorkbook.Worksheets[sheetName];
worksheet.Delete();
}
return;
}
private void DeleteSheet(Excel.Workbook WB, ArrayList toDelete)
{
foreach (string i in toDelete)
{
Excel.Worksheet WS = ((Excel.Worksheet)WB.Worksheets.get_Item(i)); // Activate specific sheet
WS.Delete();
}
//WB.Save();
}
public bool RemoveWorksheet(string name)
{
Excel.Worksheet targetSheet = GetWorksheet(name);
if (targetSheet != null)
{
targetSheet.Delete();
return(true);
}
return(false);
}
public void Dispose()
{
_ws3.Delete();
_wb.Save();
//_wb.Close(0);
//_excelApp.Quit();
//Marshal.ReleaseComObject(_wb);
//Marshal.ReleaseComObject(_workbooks);
//Marshal.ReleaseComObject(_excelApp);
}
public bool MergeFiles(Dictionary <string, ReportContext> MergedFiles, string mergedFile, out string outputFile)
{
try
{
Excel.Workbook bookDest = excel.Workbooks.Add(Missing.Value);
//create a new work sheet
Excel.Worksheet sheetDest = bookDest.Worksheets[1] as Excel.Worksheet;
foreach (var item in MergedFiles.Keys)
{
if (!File.Exists(MergedFiles[item].OutputFullName))
{
continue;
}
this.Logger.Message("Merge " + item);
sourceBook = excel.Workbooks.Open(MergedFiles[item].OutputFullName);
Excel.Worksheet sheet = sourceBook.Worksheets[1];
sheet.Name = MergedFiles[item].Description;
sheet.Copy(Missing.Value, sheetDest);
Excel.Worksheet copysheet = bookDest.Worksheets[MergedFiles[item].Description];
FunnelReportHelper.SaveTempWorkbook(sourceBook);
}
foreach (var item in bookDest.Worksheets)
{
Excel.Worksheet sheet = item as Excel.Worksheet;
if (!MergedFiles.Values.Select(x => x.Description).Contains(sheet.Name))
{
sheet.Delete();
}
}
outputFile = mergedFile.Replace(".xls", "_" + DateTime.Now.ToString("yyyMMdd_HHmmss") + ".xls");
bookDest.SaveAs(outputFile);
bookDest.Close();
ZipHelper.ZipFile(outputFile, outputFile.Replace(".xls", ".zip"), 1);
outputFile = outputFile.Replace(".xls", ".zip");
}
catch (Exception ex)
{
this.Logger.Error(ex.Message + ex.StackTrace);
throw;
}
return(true);
}
public void DelWorkSheet(int index)
{
ewsh = (Excel.Worksheet)ewb.Sheets[index];
try
{
ewsh.Delete();
}
catch (Exception e)
{
WebLog.LogClass.WriteToLog("Excel.DelWorkSheet: " + e.Message);
}
}
internal void ExportNW(int projektId)
{
log.Info("ExportNW");
var pi = GetProjektInfo(projektId);
if (pi == null)
{
throw new InvalidOperationException(string.Format(CultureInfo.InvariantCulture, "ProjektInfo with id {0} doesn't exist in Database!", projektId));
}
log.Debug("Getting Excel.Application");
_MyApp = new Excel.Application();
log.Debug("Got Excel.Application");
try
{
_WorkBook = _MyApp.Workbooks.Open(_TemplateFile, Missing.Value, ReadOnly: false); //, 5, "", "", true, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "\t", false, false, 0, true, 1, 0);
//_MyApp.Visible = true;
//_MyApp.ScreenUpdating = true;
_SheetNW = GetWorksheet("Template_Nutzwertfestlegung");
_SheetPari = GetWorksheet("Template_Parifizierung");
_WohnungInfos.Clear();
var wohnungen = _Database.GetWohnungen(projektId);
foreach (var w in wohnungen)
{
_WohnungInfos[w.Top] = w;
}
WriteNW(projektId, pi);
WritePari(projektId, pi); // Nutzflächenanteile
log.Debug("Deleting Template-Sheets.");
_MyApp.DisplayAlerts = false;
_SheetNW.Delete();
_SheetPari.Delete();
_MyApp.DisplayAlerts = true;
if (_TargetFile != null)
{
log.Debug(string.Format(CultureInfo.InvariantCulture, "Saving to '{0}'", _TargetFile));
_WorkBook.SaveAs(_TargetFile);
}
}
finally
{
if (_TargetFile == null)
{
log.Debug("_MyApp.Visible = true;");
_MyApp.Visible = true;
log.Debug("_MyApp.ScreenUpdating = true;");
_MyApp.ScreenUpdating = true;
}
}
}
// ************************************************************************
// ************************************************************************
void IDisposable.Dispose()
{
if (book.Sheets.Count > 1)
{
Excel.Worksheet sheet = (Excel.Worksheet)book.Sheets["TempNameToDelete"];
sheet.Delete();
}
book.SaveAs(fileName);
book.Close();
app.Quit();
}
public bool MergeFiles(Dictionary <string, ReportContext> MergedFiles, string mergedFile, out string outputFile)
{
Excel.Workbook bookDest = null;
try
{
bookDest = excel.Workbooks.Add(Missing.Value);
//create a new work sheet
Excel.Worksheet sheetDest = bookDest.Worksheets[1] as Excel.Worksheet;
if (MergedFiles.Count > 0)
{
this.Logger.Message("Merge " + MergedFiles.First().Key);
sourceBook = excel.Workbooks.Open(MergedFiles.First().Value.OutputFullName);
Excel.Worksheet sheet = sourceBook.Worksheets[1];
sheet.Name = "Summary";
sheet.Copy(Missing.Value, sheetDest);
Excel.Worksheet copysheet = bookDest.Worksheets["Summary"];
this.FormatSummary(copysheet);
FunnelReportHelper.CloseWorkingWorkbook(sourceBook);
}
foreach (var item in bookDest.Worksheets)
{
Excel.Worksheet sheet = item as Excel.Worksheet;
if (sheet.Name != "Summary")
{
sheet.Delete();
}
}
//outputFile = "ChinaDash_" + DateTime.Now.ToString("yyyMMdd") + ".xls";
outputFile = mergedFile.Replace(".xls", "_" + DateTime.Now.ToString("yyyMMdd") + ".xls");
if (File.Exists(outputFile))
{
File.Delete(outputFile);
}
bookDest.SaveAs(outputFile);
bookDest.Close();
}
catch (Exception ex)
{
throw;
}
return(true);
}
/// <summary>
/// Deletes the specified worksheet from the spreadsheet. If no sheet is specified the currently selected sheet is deleted.
/// </summary>
/// <param name="sheet">The sheet to delete.</param>
public void deleteSheet(string sheet = "default")
{
if (sheet.Equals("default"))
{
worksheet = (Excel.Worksheet)workbook.ActiveSheet;
}
else
{
worksheet = (Excel.Worksheet)worksheets.get_Item(sheet);
}
worksheet.Delete();
}
private void Office_SheetChange(object sender, EventArgs e)
{
if (this.Office.GetCurrentProgID() == EXCEL_ID)
{
Excel.Application xlApp = this.Office.GetApplication() as Excel.Application;
xlApp.DisplayAlerts = false;
if (xlApp.ActiveWorkbook.Sheets.Count > 1)
{
Excel.Worksheet workSheet = (Excel.Worksheet)xlApp.ActiveWorkbook.ActiveSheet;
workSheet.Delete();
}
}
}
public static string SaveAllDataToExcel(string PathToOutput, DataGridView dataGridViewInvoicing, DataGridView dataGridViewTRM, DataGridView dataGridViewEDFBUManager, DataGridView dataGridViewED)
{
Excel.Application excelapp = new Excel.Application();
Excel.Workbook workbook = excelapp.Workbooks.Add();
Excel.Worksheet worksheet = workbook.ActiveSheet;
foreach (Excel.Worksheet ws in workbook.Worksheets)
{
if (worksheet != ws)
{
ws.Delete();
}
}
if (dataGridViewInvoicing.RowCount > 0)
{
Excel.Worksheet worksheet4 = workbook.Worksheets.Add();
worksheet4.Name = "Invoicing";
FillWorkSheet(dataGridViewInvoicing, worksheet4);
}
if (dataGridViewTRM.RowCount > 0)
{
Excel.Worksheet worksheet3 = workbook.Worksheets.Add();
worksheet3.Name = "TRM";
FillWorkSheet(dataGridViewTRM, worksheet3);
}
if (dataGridViewEDFBUManager.RowCount > 0)
{
Excel.Worksheet worksheet2 = workbook.Worksheets.Add();
worksheet2.Name = "ED_Managers";
FillWorkSheet(dataGridViewEDFBUManager, worksheet2);
}
if (dataGridViewED.RowCount > 0)
{
Excel.Worksheet worksheet1 = workbook.Worksheets.Add();
worksheet1.Name = "ED_Permissions";
FillWorkSheet(dataGridViewED, worksheet1);
}
if (workbook.Worksheets.Count > 1)
{
worksheet.Delete();
}
string localDateStr = DateTime.Now.ToString().Replace(":", "").Replace(".", "");
excelapp.AlertBeforeOverwriting = false;
workbook.SaveAs(PathToOutput + "TerminatedEmployees_" + localDateStr + ".xlsx");
excelapp.Quit();
return("Save completed. " + PathToOutput + "\\TerminatedEmployees_" + localDateStr + ".xlsx");
}
public Boolean DeleteDstExcelSheet(int iSheetNo)
{
Excel.Worksheet xlsSheet = null;
try
{
xlsSheet = m_xlsDstWBook.Sheets[iSheetNo];
xlsSheet.Delete();
}
catch (Exception ex)
{
return(false);
}
return(true);
}
/// <summary>
/// Delete temporary export objects: mem. clean
/// </summary>
/// <param name="Wb"></param>
/// <param name="Success"></param>
private void WorkbookAfterSave(Excel.Workbook Wb, bool Success)
{
if (Success)
{
_ = MessageBox.Show(
"Export success!",
"Info",
MessageBoxButtons.OK,
MessageBoxIcon.Information,
MessageBoxDefaultButton.Button1, 0);
app.Quit();
worksheet.Delete();
Wb.Close();
}
}
public static void ShowImportTemplete(string TempleteName)
{
try
{
string filename = clsLxms.GetParameterValue("EXCEL_TEMPLETE_FILE");
if (!File.Exists(filename))
{
MessageBox.Show(clsTranslate.TranslateString("Can not find the templete file") + ": \r\n" + filename, clsTranslate.TranslateString("Information"), MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
return;
}
string destfile = @Path.GetTempPath() + TempleteName + ".xls";
Util.retValue1 = destfile;
File.Copy(filename, destfile, true);
Excel.Application m_objExcel = new Excel.Application();
m_objExcel.DisplayAlerts = false;
Excel.Workbooks m_objBooks = m_objExcel.Workbooks;
m_objBooks.Open(destfile, Type.Missing, Type.Missing, Type.Missing, Type.Missing,
Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing,
Type.Missing, Type.Missing, Type.Missing, Type.Missing);
Excel.Workbook m_objBook = (Excel.Workbook)m_objBooks.get_Item(1);
Excel.Sheets sm_objSheets = (Excel.Sheets)m_objBook.Worksheets;
Excel.Worksheet m_objSheet = (Excel.Worksheet)sm_objSheets.get_Item(TempleteName);
for (int i = sm_objSheets.Count; i >= 1; i--)
{
Excel.Worksheet m_Sheet = sm_objSheets.get_Item(i);
if (m_Sheet.Name.ToLower().CompareTo(TempleteName.ToLower()) != 0)
{
m_Sheet.Delete();
}
}
m_objSheet.Name = "Sheet1";
//m_objSheet.Activate();
System.Windows.Forms.Application.DoEvents();
m_objExcel.DisplayAlerts = false;
m_objExcel.AlertBeforeOverwriting = false;
//保存工作簿
m_objExcel.Visible = true;
return;
}
catch (System.Exception ex)
{
MessageBox.Show(ex.Message, clsTranslate.TranslateString("Information"), MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
return;
}
}
public void ExcelSave()
{
try
{
wb.Save();
ws.Delete();
wb.Close();
excelApp.Quit();
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
wb.Close();
excelApp.Quit();
}
}
protected void Page_Load(object sender, EventArgs e)
{
int i = 3, max = 5, index = 1;
string filePath = @"C:\Users\anandra\Desktop\Book1.xlsx";
Excel.Application excelApp = new Excel.Application();
excelApp.Interactive = false;
Excel.Workbook workBook = excelApp.Workbooks.Open(filePath);
for (i = 1; i <= max; i++)
{
//Adding an extra check here to skip your error
if (i != index + 1 && workBook.Sheets.Count > i)
{
Excel.Worksheet worksheets = (Excel.Worksheet)workBook.Sheets[i];
worksheets.Delete();
}
}
}
public string DeleteSheet(string FilePath, string SheetName)
{
string result = "";
try
{
result = OpenExcel(FilePath, SheetName);
if (result == "")
{
Ws.Delete();
result = "";
}
}
catch (Exception e)
{
return("Exception caught - " + e.Message);
}
return(result);
}
/// <summary>
/// Deletes the specified worksheet from the spreadsheet. If no sheet is specified the currently selected sheet is deleted.
/// </summary>
/// <param name="sheet">The sheet to delete.</param>
public void DeleteSheet(string sheet = null)
{
if (sheet == null)
{
worksheet = (Excel.Worksheet)workbook.ActiveSheet;
}
else
{
try
{
worksheet = (Excel.Worksheet)worksheets.get_Item(sheet);
}
catch (System.Runtime.InteropServices.COMException)
{
throw new ArgumentException("The specified worksheet: " + sheet + ", was not found.");
}
}
worksheet.Delete();
}
private void SaveFiles()
{
FolderBrowserDialog ChoisirPath = new FolderBrowserDialog();
object misValue = System.Reflection.Missing.Value;
if (!executee) // La première fois on l'execute
{
xlWorkBook = xlApp.Workbooks.Add(misValue);
}
if (ChoisirPath.ShowDialog() == DialogResult.OK)
{
for (int x = 1; x <= Convert.ToInt32(TB_NbEchantillons.Text); x++)
{
xlWorkBook = xlApp.Workbooks.Add(misValue);
if (VerifReady()) // Vérification des champs
{
if (RB_AleatoireSimple.Checked == true) // On agit selon le choix
{
ModeAleatoireSimple();
}
else if (RB_Systematique.Checked == true)
{
ModeSystematique();
}
// Cette ligne change le nom du WorkSheet dans le fichier Excel
xlWorkSheetFinal.Name = "Échantillon #" + x;
// Ces 2 prochaines lignes suppriment la WorkSheet vide de départ du fichier Excel
Excel.Worksheet ws = (Excel.Worksheet)xlWorkBook.Worksheets[2];
ws.Delete();
xlWorkSheetFinal.SaveAs(ChoisirPath.SelectedPath + "\\" + TB_NomsFichiers.Text + x);
}
progressBar.Value = x * 100 / Convert.ToInt32(TB_NbEchantillons.Text); // La barre de progression qui est super jolie!
}
}
executee = true;
}
protected void Page_Load(object sender, EventArgs e)
{
int i = 3, max = 5, index = 1;
string filePath = @"C:\Users\anandra\Desktop\Book1.xlsx";
Excel.Application excelApp = new Excel.Application();
Excel.Workbook workBook = excelApp.Workbooks.Open(filePath);
for (i = 1; i <= max; i++)
{
//Adding an extra check here to skip your error
if (i != index + 1 && workBook.Sheets.Count > i)
{
Excel.Worksheet worksheets = (Excel.Worksheet)workBook.Sheets[i];
excelApp.DisplayAlerts = false;
worksheets.Delete();
excelApp.DisplayAlerts = true;
//Decreasing the value of index and i as after deleting the sheet the index will start agarin from 1.
i--;
index--;
}
}
}
public Boolean DeleteDstExcelSheetToName(string name)
{
int max, i;
Excel.Worksheet xlsSheet = null;
try
{
max = m_xlsDstWBook.Sheets.Count;
for (i = max; i >= 1; i--)
{
xlsSheet = m_xlsDstWBook.Sheets[i];
if (xlsSheet.Name == name)
{
xlsSheet.Delete();
}
}
}
catch (Exception ex)
{
return(false);
}
return(true);
}
public void ExportSelectedRecordsToExcel()
{
ESRI.ArcGIS.Framework.IProgressDialogFactory progressDialogFactory = null;
ESRI.ArcGIS.esriSystem.IStepProgressor stepProgressor = null;
ESRI.ArcGIS.Framework.IProgressDialog2 progressDialog = null;
IMxDocument mxdoc = null;
IMap map = null;
IStandaloneTableCollection standTabColl = null;
IStandaloneTable standTable = null;
ITableSelection tableSel = null;
IEnumFeature enumFeat = null;
IFeature feat = null;
IFeatureSelection featSel = null;
ITable openTable = null;
object missing = null;
//object fileName = null;
object newTemplate = null;
object docType = null;
object isVisible = null;
UID geoFeatureLayerID = null;
IEnumLayer enumLayer = null;
IFeatureLayer featlayer = null;
try
{
mxdoc = (IMxDocument)_app.Document;
map = mxdoc.FocusMap;
long standTableCnt;
int i = 0;
bool selectionInTable = false;
missing = System.Reflection.Missing.Value;
// fileName = "normal.dot";
newTemplate = false;
docType = 0;
isVisible = true;
//Get enumeration of feature layers
geoFeatureLayerID = new UIDClass();
geoFeatureLayerID.Value = "{E156D7E5-22AF-11D3-9F99-00C04F6BC78E}";
enumLayer = map.get_Layers(geoFeatureLayerID, true);
enumLayer.Reset();
standTabColl = (IStandaloneTableCollection)map;
standTableCnt = standTabColl.StandaloneTableCount;
enumFeat = (IEnumFeature)map.FeatureSelection;
enumFeat.Reset();
feat = enumFeat.Next();
if (standTableCnt > 0)
{
for (int j = 0; j < standTableCnt; j++)
{
standTable = standTabColl.get_StandaloneTable(j);
if (standTable.Valid)
{
openTable = (ITable)standTable;
tableSel = (ITableSelection)openTable;
if (tableSel.SelectionSet.Count > 0)
{
selectionInTable = true;
break;
}
}
}
}
if ((selectionInTable == false) && (feat == null))
{
MessageBox.Show(A4LGSharedFunctions.Localizer.GetString("DataToolsMess_1") + Environment.NewLine + A4LGSharedFunctions.Localizer.GetString("DataToolsMess_2"), messageBoxHeader);
return;
}
// Create a CancelTracker
ESRI.ArcGIS.esriSystem.ITrackCancel trackCancel = new ESRI.ArcGIS.Display.CancelTrackerClass();
// Set the properties of the Step Progressor
System.Int32 int32_hWnd = _app.hWnd;
progressDialogFactory = new ESRI.ArcGIS.Framework.ProgressDialogFactoryClass();
stepProgressor = progressDialogFactory.Create(trackCancel, int32_hWnd);
stepProgressor.MinRange = 0;
stepProgressor.MaxRange = mxdoc.FocusMap.SelectionCount;
stepProgressor.StepValue = 1;
stepProgressor.Message = A4LGSharedFunctions.Localizer.GetString("ExportXL");
// Create the ProgressDialog. This automatically displays the dialog
progressDialog = (ESRI.ArcGIS.Framework.IProgressDialog2)stepProgressor; // Explict Cast
// Set the properties of the ProgressDialog
progressDialog.CancelEnabled = true;
progressDialog.Description = A4LGSharedFunctions.Localizer.GetString("ExportAsset") + "1" + A4LGSharedFunctions.Localizer.GetString("Of") + mxdoc.FocusMap.SelectionCount.ToString() + ".";
progressDialog.Title = A4LGSharedFunctions.Localizer.GetString("ExportXL");
progressDialog.Animation = ESRI.ArcGIS.Framework.esriProgressAnimationTypes.esriProgressGlobe;
progressDialog.ShowDialog();
if ((feat != null) && (selectionInTable == true))
{
ExcelApp = new Excel.ApplicationClass();
//Delete default worksheets
ExcelApp.DisplayAlerts = false;
objBook = ExcelApp.Workbooks.Add(missing);
//objBook = ExcelApp.Workbooks.get_Item(1);
for (int m = objBook.Sheets.Count; m > 1; m--)
{
objSheet = (Excel.Worksheet)objBook.Sheets.get_Item(m);
objSheet.Delete();
}
featlayer = (IFeatureLayer)enumLayer.Next();
while (featlayer != null)
{
if ((featlayer.Valid) && (featlayer.Selectable))
{
featSel = (IFeatureSelection)featlayer;
if (featSel.SelectionSet.Count > 0)
{
ExportLayer(objBook, mxdoc, featlayer, ref progressDialog, ref stepProgressor);
}
}
featlayer = (IFeatureLayer)enumLayer.Next();
}
for (i = 0; i < standTableCnt; i++)
{
standTable = standTabColl.get_StandaloneTable(i);
if (standTable.Valid)
{
openTable = (ITable)standTable;
tableSel = (ITableSelection)openTable;
if (tableSel.SelectionSet.Count > 0)
{
ExportTable(objBook, mxdoc, standTable, ref progressDialog, ref stepProgressor);
}
}
}
}
else if ((feat != null) && (selectionInTable == false))
{
ExcelApp = new Excel.ApplicationClass();
//Delete default worksheets
ExcelApp.DisplayAlerts = false;
objBook = ExcelApp.Workbooks.Add(missing);
//objBook = ExcelApp.Workbooks.get_Item(1);
for (int m = objBook.Sheets.Count; m > 1; m--)
{
objSheet = (Excel.Worksheet)objBook.Sheets.get_Item(m);
objSheet.Delete();
}
ILayer pTLay = enumLayer.Next();
while (pTLay != null)
{
if (pTLay is IFeatureLayer)
{
featlayer = (IFeatureLayer)pTLay;
if ((featlayer.Valid) )
{
featSel = (IFeatureSelection)featlayer;
if (featSel.SelectionSet.Count > 0)
{
ExportLayer(objBook, mxdoc, featlayer, ref progressDialog, ref stepProgressor);
}
}
}
pTLay = enumLayer.Next();
}
pTLay = null;
}
else if ((feat == null) && (selectionInTable == true))
{
ExcelApp = new Excel.ApplicationClass();
//Delete default worksheets
ExcelApp.DisplayAlerts = false;
objBook = ExcelApp.Workbooks.Add(missing);
//objBook = ExcelApp.Workbooks.get_Item(1);
for (int m = objBook.Sheets.Count; m > 1; m--)
{
objSheet = (Excel.Worksheet)objBook.Sheets.get_Item(m);
objSheet.Delete();
}
for (i = 0; i < standTableCnt; i++)
{
standTable = standTabColl.get_StandaloneTable(i);
if (standTable.Valid)
{
openTable = (ITable)standTable;
tableSel = (ITableSelection)openTable;
if (tableSel.SelectionSet.Count > 0)
{
ExportTable(objBook, mxdoc, standTable, ref progressDialog, ref stepProgressor);
}
}
}
}
if (objBook.Sheets.Count > 1)
{
objSheet = (Excel.Worksheet)objBook.Sheets.get_Item(1);
objSheet.Delete();
}
//objSheet = (Excel.Worksheet)objBook.Sheets["Sheet2"];
//objSheet.Delete();
//objSheet = (Excel.Worksheet)objBook.Sheets["Sheet3"];
//objSheet.Delete();
ExcelApp.DisplayAlerts = true;
//Make the first sheet active
objSheet = (Excel.Worksheet)objBook.Sheets.get_Item(1) as Excel.Worksheet;
(objSheet as Microsoft.Office.Interop.Excel._Worksheet).Activate();
//Make Excel visible
ExcelApp.Visible = true;
return;
}
catch (Exception ex)
{
if (ExcelApp != null)
ExcelApp = null;
//ExcelApp.Visible = true;
MessageBox.Show("ExportSelectedRecordsToExcel\n" + ex.Message, ex.Source);
return;
}
finally
{
if (progressDialog != null)
progressDialog.HideDialog();
progressDialogFactory = null;
stepProgressor = null;
progressDialog = null;
mxdoc = null;
map = null;
standTabColl = null;
standTable = null;
tableSel = null;
enumFeat = null;
feat = null;
featSel = null;
openTable = null;
missing = null;
//fileName = null;
newTemplate = null;
docType = null;
isVisible = null;
geoFeatureLayerID = null;
enumLayer = null;
featlayer = null;
}
}
