namespace Jitender.OfficeTools.Excel
{
using System;
using System.IO;
using Excel = Microsoft.Office.Interop.Excel;
class DemoExcelAutoFiltering {
[STAThread]
static void Main(string[] args) {
DirectoryInfo di = new DirectoryInfo(@"C:\Program Files\Microsoft Office\OFFICE11\1033");
ExcelFileReport efr = new ExcelFileReport(di);
efr.Generate();
}
}
public class ExcelFileReport {
private object _missing;
private Excel.Workbook _book;
Excel.Worksheet _sheet;
Excel.Range _rng;
int _row;
private DirectoryInfo _di;
ExcelHelper _eh = new ExcelHelper();
public ExcelFileReport(DirectoryInfo di) {
_di = di;
_missing = System.Reflection.Missing.Value;
_row = 4;
}
public void DocumentDirectory(DirectoryInfo di) {
foreach (DirectoryInfo d in di.GetDirectories()) {
DocumentDirectory(d);
}
foreach (FileInfo f in di.GetFiles()) {
_row++;
_rng = (Excel.Range)_sheet.Cells[_row, "A"];
_rng.Value2 = di.Name;
_rng = (Excel.Range)_sheet.Cells[_row, "B"];
_rng.Value2 = f.FullName;
_rng = (Excel.Range)_sheet.Cells[_row, "C"];
_rng.Value2 = f.Name;
_rng = (Excel.Range)_sheet.Cells[_row, "D"];
_rng.Value2 = f.Length;
_rng = (Excel.Range)_sheet.Cells[_row, "E"];
_rng.Value2 = f.Extension;
_rng = (Excel.Range)_sheet.Cells[_row, "F"];
_rng.Value2 = f.LastWriteTime.ToLongDateString();
}
}
public void Generate(){
string caption = "File Analysis Results";
string heading1 = "File Analysis Report for Folder " + _di.FullName;
_book = _eh.Create(caption, heading1);
_sheet = ((Excel.Worksheet)_book.ActiveSheet);
WriteTableHeader();
DocumentDirectory(_di);
SetAutoFilter();
_eh.Close();
}
private void SetAutoFilter(){
string lastrow = "F" + _row.ToString();
_rng = ((Excel.Worksheet)_book.ActiveSheet).get_Range("A4", lastrow);
_rng.AutoFilter(1, _missing, Excel.XlAutoFilterOperator.xlAnd, _missing, true);
_rng.Borders.LineStyle = Excel.XlLineStyle.xlContinuous;
}
public void WriteTableHeader(){
_rng = ((Excel.Worksheet)_book.ActiveSheet).get_Range("A4", "H3");
_rng.Font.Bold = true;
_rng.EntireRow.Font.Bold = true;
_rng = (Excel.Range)_sheet.Cells[_row, "A"];
_rng.Value2 = "Parent Directory";
_rng = (Excel.Range)_sheet.Cells[_row, "B"];
_rng.Value2 = "Full Path";
_rng = (Excel.Range)_sheet.Cells[_row, "C"];
_rng.Value2 = "File Name";
_rng = (Excel.Range)_sheet.Cells[_row, "D"];
_rng.Value2 = "Size";
_rng = (Excel.Range)_sheet.Cells[_row, "E"];
_rng.Value2 = "Type";
_rng = (Excel.Range)_sheet.Cells[_row, "F"];
_rng.Value2 = "Last Modified";
_sheet.Columns.ColumnWidth = 30;
}
}
class ExcelHelper {
private Excel.Application _excelApplication;
public ExcelHelper() {
_excelApplication = new Excel.Application();
}
public Excel.Workbook Create(string caption, string heading1) {
try {
_excelApplication.Caption = caption;
_excelApplication.ScreenUpdating = false;
_excelApplication.Visible = false;
Excel.Workbook book = _excelApplication.Workbooks.Add(Excel.XlSheetType.xlWorksheet);
Excel.Worksheet sheet = (Excel.Worksheet)book.ActiveSheet;
Excel.Range r = (Excel.Range)sheet.Cells[1, "A"];
r.Value2 = heading1;
r.EntireRow.Font.Bold = true;
return book;
}
catch (Exception ex) {
throw (ex);
}
}
public void Close() {
_excelApplication.ScreenUpdating = true;
_excelApplication.Visible = true;
_excelApplication.DisplayAlerts = true;
if (_excelApplication != null) {
_excelApplication.Quit();
_excelApplication = null;
GC.Collect();
GC.WaitForPendingFinalizers();
GC.Collect();
GC.WaitForPendingFinalizers();
}
}
}
}
!--removed tag-->
Enter your message below
Sign in or Join us (it's free).