private void btnAutoFilter_Click(object sender, EventArgs e)
{
int colCount = 32;
int dataCount = 1648;
int lastRow = 1651;
using (var excel = new Excel.Application())
{
excel.Visible = true;
using (var workbook = excel.Workbooks.Open(Path.GetDirectoryName(Assembly.GetExecutingAssembly().GetModules()[0].FullyQualifiedName) + "\\Templates\\AutoFilter.xls"))
{
using (var worksheet = workbook.ActiveSheet)
{
worksheet.AutoFilterMode = false;
int start = 0;
using (var r = worksheet.Range("__Table__"))
start = r.Row;
using (var r = worksheet.Range(STC.Automation.Office.Excel.Utilities.Ranges.Format(start - 1, 1)))
r.AutoFilter();
for (int c = 1; c <= colCount; c++)
{
using (var r = worksheet.Range(STC.Automation.Office.Excel.Utilities.Ranges.Format(start, c, start + dataCount, c)))
{
// set formats before calculating column autofit
if (c >= 13 && c <= 20)
{
r.NumberFormat = Ranges.ConvertFormat("C0");
}
if (c >= 21 && c <= 24)
{
r.NumberFormat = Ranges.ConvertFormat("dd/MM/yyyy");
}
r.Columns.AutoFit();
if (r.ColumnWidth > 80)
{
r.ColumnWidth = 80;
}
}
}
//// sort the AutoFilter by data in column B
//using (var filter = worksheet.AutoFilter)
//{
// filter.Sort.SortFields.Clear();
// using (var range = worksheet.Range("B4:B" + lastRow))
// filter.Sort.SortFields.Add(range, Excel.Enums.SortOn.Values, Excel.Enums.SortOrder.Ascending, Excel.Enums.SortDataOption.Normal);
// filter.Sort.Header = Excel.Enums.YesNoGuess.Yes;
// filter.Sort.MatchCase = false;
// filter.Sort.Orientation = Excel.Enums.SortOrientation.Columns;
// filter.Sort.Apply();
//}
}
excel.DisplayAlerts = false;
workbook.Save();
excel.DisplayAlerts = true;
//workbook.Close();
}
//excel.Quit();
}
}