static void Main(string[] args)
{
Excel.Application xlApp = null;
try {
xlApp = new Excel.Application();
xlApp.Visible = true;
Excel.Workbook xlWb = xlApp.Workbooks.Open(@"C:\Users\joe.bob\Desktop\tmp\Book1.xlsx"); //include the path to your real Excel file
Excel.Worksheet xlWSht = xlWb.Sheets["Sheet1"]; //include the name of your worksheet where you have the data
//here the data is on a Worksheet called Sheet1
int startRow = 9; //this is the row where the data starts
string startCol = "A"; //the start column
int endRow = 15;
string endCol = "F";
int filterColumn = 6; //this is an OFFSET
string[] filterList = new string[] { "DTP-3432", "DTP-343243" }; //this is the list of values you want to show
Excel.Range myData = xlWSht.get_Range(startCol + startRow, endCol + endRow);
myData.AutoFilter(filterColumn, filterList.Length > 0 ? filterList : Type.Missing, Excel.XlAutoFilterOperator.xlFilterValues, Type.Missing, true);
Console.WriteLine("Press a key to quit...");
Console.ReadKey();
}
finally {
if (xlApp != null)
{
xlApp.Quit();
}
}
} //end main
// Функция фильтрации таблицы по указанной колонке и критерию отбора
public static int Excel_FilterBy(Excel.Worksheet worksheet, int column, string criteria)
{
Excel.Range GetAllCells = worksheet.UsedRange;
GetAllCells.AutoFilter(column, criteria);
return(0);
}
public static void FormatApplyFilters(this ExcelSheet sheet, int filterRow = 1, int filterColumn = 1)
{
Range range = sheet.SetRange(filterRow, filterColumn, filterRow, filterColumn);
range.AutoFilter(2);
Cleanup.ReleaseObject(range);
}
/// <summary>
/// Method designs table
/// </summary>
/// <param name="worksheet"></param>
/// <param name="worksheetPivot"></param>
/// <param name="worksheetPeeChart"></param>
public static void DesignMyTableExcel(Excel.Application app, Excel.Workbook workbook, Excel.Worksheet worksheet)
{
worksheet.Activate();
worksheet.Application.ActiveWindow.SplitRow = 1;
worksheet.Application.ActiveWindow.FreezePanes = true;
// Now apply autofilter
Excel.Range firstRow = (Excel.Range)worksheet.Rows[1];
firstRow.AutoFilter(1,
Type.Missing,
Excel.XlAutoFilterOperator.xlAnd,
Type.Missing,
true);
BordersMaker.DrawFullTableBorders(app, workbook, worksheet);
Range rng = worksheet.UsedRange;
worksheet.get_Range("A1", "I1").Cells.Font.Bold = true;
worksheet.get_Range("A1", "I1").Cells.Font.Size = 14;
rng.Style.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
rng.Style.VerticalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
worksheet.get_Range("A1", "I1").Cells.Font.Name = "Arial Narrow";
worksheet.get_Range("A1", "I1").Cells.Font.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.FromArgb(0, 71, 179));
worksheet.get_Range("A1", "I1").Cells.Font.Bold = true;
worksheet.Columns.AutoFit();
Excel.Range thisRange = worksheet.get_Range("H:H");
thisRange.NumberFormat = "0.00";
Marshal.FinalReleaseComObject(thisRange);
}
public void AutoFilter(int rownum)
{
xlWorkSheet.EnableAutoFilter = true;
Excel.Range range = xlWorkSheet.get_Range("A6", "F" + rownum + "");
range.AutoFilter("1", "<>", Microsoft.Office.Interop.Excel.XlAutoFilterOperator.xlOr, "", true);
// xlWorkSheet.ListObjects.AddEx(Excel.XlListObjectSourceType.xlSrcRange, range, misValue, Microsoft.Office.Interop.Excel.XlYesNoGuess.xlNo, misValue).Name = "PriceList";
// xlWorkSheet.ListObjects.get_Item("PriceList").TableStyle = "TableStyleMedium4";
}
private void runExcel(string lcFile)
{
Excel.Application xlApp;
Excel.Workbook xlWorkBook;
Excel.Workbook xlWorkBook2;
Excel.Worksheet xlWorkSheet;
object misValue = System.Reflection.Missing.Value;
xlApp = new Excel.Application();
xlWorkBook = xlApp.Workbooks.Open(lcFile, 0, true, 5, "", "", true, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "\t", false, false, 0, true, 1, 0);
xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);
if (this.chkFreeze.Checked == true)
{
xlWorkSheet.Activate();
xlWorkSheet.Application.ActiveWindow.SplitRow = 1;
xlWorkSheet.Application.ActiveWindow.FreezePanes = true;
}
if (this.chkFilter.Checked == true)
{
Excel.Range firstRow = (Excel.Range)xlWorkSheet.Rows[1];
firstRow.AutoFilter(1,
Type.Missing,
Excel.XlAutoFilterOperator.xlAnd,
Type.Missing,
true);
}
if (this.chkResize.Checked == true)
{
xlWorkSheet.Application.Columns.AutoFit();
}
try
{
xlWorkBook.SaveAs(Filename: lcFile + "x", FileFormat: Excel.XlFileFormat.xlWorkbookDefault);
deleteOld(lcFile);
}
catch (Exception e)
{
MessageBox.Show("An error occurred: '{0}'" + e, "File Error", MessageBoxButtons.OK);
}
xlWorkBook.Close(true, misValue, misValue);
xlApp.Quit();
if (this.chkOpen.Checked == true && File.Exists(@lcFile + "x"))
{
xlApp.Visible = true;
xlWorkBook2 = xlApp.Workbooks.Open(@lcFile + "x");
releaseObject(xlWorkBook2);
}
releaseObject(xlWorkSheet);
releaseObject(xlWorkBook);
releaseObject(xlApp);
}
//オートフィルターの絞り込み解除
private void clear_mytext_auto_filter()
{
Excel.Range acl = Globals.ThisAddIn.Application.ActiveCell;
Excel.Worksheet ash = Globals.ThisAddIn.Application.ActiveSheet;
int cx = acl.Column;
Excel.Range ur = ash.UsedRange;
ur.AutoFilter(cx);
}
public void SetAutoFilterOnSubHeader()
{
SendProgressReportUpdate("Добавляем фильтр...");
if (xlWorksheet.AutoFilter != null)
{
xlWorksheet.AutoFilterMode = false;
}
Excel.Range xlFilterRow = xlWorksheet.Cells[subHeaderRow, 1].EntireRow;
xlFilterRow.AutoFilter(1);
}
public static void AddHeaderFilters(Worksheet workSheet)
{
workSheet.Activate();
workSheet.Application.ActiveWindow.SplitRow = 1;
workSheet.Application.ActiveWindow.FreezePanes = true;
// Now apply autofilter
Excel.Range firstRow = (Excel.Range)workSheet.Rows[1];
firstRow.AutoFilter(1,
Type.Missing,
Excel.XlAutoFilterOperator.xlAnd,
Type.Missing,
true);
}
/// <summary>
/// Adds a data filter to a range
/// </summary>
/// <param name="range">Target range, including the header row</param>
/// <param name="columnNumber">Column to apply filter to, index starts at 1 and relates to the first column on the left</param>
/// <param name="filterEnum">Excel enum how data will be filtered</param>
/// <param name="criteria">First criteria for filtering</param>
/// <param name="criteria2">Second criteria for filtering</param>
public static void AddAutoFilter(this Excel.Range range, int columnNumber = 1, Excel.XlAutoFilterOperator filterEnum = XlAutoFilterOperator.xlAnd, string[] criteria = null, string criteria2 = null)
{
int columnCount = range.Columns.Count;
if (columnNumber < 1 || columnNumber > columnCount)
{
throw new Exception($"The range selected, {range}, only has {columnCount} columns. Please enter a value greater than or equal to 1 and less than or equal to {columnCount}");
}
string singularCriteria = "";
if (criteria.Length == 1)
{
singularCriteria = criteria.First();
}
// Adds data filter to range without filtering data
if (columnNumber == 1 && filterEnum == XlAutoFilterOperator.xlAnd && criteria == null && criteria2 == null)
{
range.AutoFilter(1);
}
// If filter can be applied to a column without criteria
else if (criteria == null && criteria2 == null)
{
range.AutoFilter(columnNumber, Type.Missing, filterEnum);
}
// If there is one criteria
else if (criteria != null && criteria2 == null)
{
if (criteria.Length == 1)
{
range.AutoFilter(columnNumber, singularCriteria, filterEnum);
}
else
{
range.AutoFilter(columnNumber, criteria, filterEnum);
}
}
// If there are two criteria
else
{
if (criteria.Length == 1)
{
range.AutoFilter(columnNumber, singularCriteria, filterEnum, criteria2);
}
else
{
range.AutoFilter(columnNumber, criteria, filterEnum, criteria2);
}
}
}
/// <summary>
/// 필드 행 꾸미기
/// </summary>
private void DecorateFieldRow(Excel.Worksheet worksheet, Schema flatSchema)
{
if (flatSchema.FieldCount == 0)
{
return;
}
Excel.Range sheetCells = worksheet.Cells;
Excel.Range startCell = (Excel.Range)sheetCells[MarginRowCount + 1, MarginColumnCount + 1];
Excel.Range endCell = (Excel.Range)sheetCells[MarginRowCount + 1, MarginColumnCount + flatSchema.FieldCount];
Excel.Range range = sheetCells.Range[startCell, endCell];
// 이름 위치에 컬럼 검색 필터 적용
range.AutoFilter(1, VisibleDropDown: true);
// 이름 너비로 컬럼 크기 맞춤
range.Columns.AutoFit();
Excel.Borders borders = range.Borders;
borders.LineStyle = Excel.XlLineStyle.xlContinuous;
Schema schema = linker.GetSchema(flatSchema.Name);
if (!schema.IsFlat && ColorPool.Any())
{// 하나의 필드가 엑셀 상에선 여러 컬럼으로 이루어진다면 색칠한다.
int colorPoolIdx = 0;
int cellStartIdx = 1;
foreach (Field field in schema.Fields)
{
int count = GetFieldCellCount(field);
if (count > 1)
{
Excel.Range fieldStartCell = (Excel.Range)sheetCells[MarginRowCount + 1, MarginColumnCount + cellStartIdx];
Excel.Range fieldEndCell = (Excel.Range)sheetCells[MarginRowCount + 1, MarginColumnCount + cellStartIdx + count - 1];
Excel.Range fieldRange = sheetCells.Range[fieldStartCell, fieldEndCell];
Excel.XlRgbColor color = ColorPool[colorPoolIdx];
fieldRange.Interior.Color = color;
colorPoolIdx = (colorPoolIdx + 1) % ColorPool.Length;
}
cellStartIdx = cellStartIdx + count;
}
}
}
/// <summary>
/// 필드 행 꾸미기
/// </summary>
private void DecorateFieldRow(Excel.Worksheet worksheet, Schema flatSchema)
{
if (flatSchema.FieldCount == 0 || !ColorPool.Any())
{
return;
}
Excel.Range sheetCells = worksheet.Cells;
int colorPoolIdx = 0;
int nameRowIdx = MarginRowCount + 1;
int colStartIdx = MarginColumnCount + 1;
int colEndIdx = MarginColumnCount + flatSchema.FieldCount;
Excel.Range nameRange = sheetCells.Range[sheetCells[nameRowIdx, colStartIdx], sheetCells[nameRowIdx, colEndIdx]];
nameRange.Cells.Interior.ColorIndex = 0;
// 이름 위치에 컬럼 검색 필터 적용
nameRange.AutoFilter(1, VisibleDropDown: true);
// 이름 너비로 컬럼 크기 맞춤
nameRange.Columns.AutoFit();
Schema schema = linker.GetSchema(flatSchema.Name);
int fieldColStartIdx = colStartIdx;
// 색칠
foreach (Field field in schema.Fields)
{
int count = GetFieldCellCount(field);
int fieldColEndIdx = fieldColStartIdx + count - 1;
if (count > 1)
{
if (ColorPool.Length > 0)
{
Excel.Range range = sheetCells.Range[sheetCells[nameRowIdx, fieldColStartIdx], sheetCells[nameRowIdx, fieldColEndIdx]];
range.Interior.Color = ColorPool[colorPoolIdx];
colorPoolIdx = (colorPoolIdx + 1) % ColorPool.Length;
}
}
fieldColStartIdx = fieldColEndIdx + 1;
}
}
private void AddFinalTouches(Excel.Worksheet xlSheet)
{
Excel.Range firstCell = xlSheet.Cells[1, 1] as Excel.Range;
if (firstCell != null)
{
Excel.Range titleRow = firstCell.EntireRow as Excel.Range;
titleRow.RowHeight *= 2;
titleRow.AutoFilter(Field: 1, Operator: Excel.XlAutoFilterOperator.xlAnd);
titleRow.AutoFit();
Marshal.ReleaseComObject(titleRow);
Excel.Window xlWindow = this.xlApp.ActiveWindow;
xlWindow.SplitRow = 1;
xlWindow.FreezePanes = true;
Marshal.ReleaseComObject(xlWindow);
}
Marshal.ReleaseComObject(firstCell);
}
//オートフィルタの文字列絞り込み自動設定
private void set_mytext_auto_filter()
{
try
{
Excel.Range acl = Globals.ThisAddIn.Application.ActiveCell;
Excel.Worksheet ash = Globals.ThisAddIn.Application.ActiveSheet;
int cx = acl.Column;
Excel.Range ur = ash.UsedRange;
string src = criteriaStringText.Text;
if (src == "")
{
return;
}
if (criteriaFazzyCheck.Checked)
{
src = "*" + src + "*";
}
ur.AutoFilter(cx, src, Excel.XlAutoFilterOperator.xlFilterValues);
}
catch (Exception ex) { }
}
private void ThisAddIn_Startup(object sender, System.EventArgs e)
{
Excel.Application ExcelApp = Application;
//Add a Workbook.
Excel.Workbook objBook = ExcelApp.Workbooks.Add(System.Reflection.Missing.Value);
//Get the First sheet.
Excel.Worksheet sheet = (Excel.Worksheet)objBook.Sheets["Sheet1"];
//Add data into A1 and B1 Cells as headers.
sheet.Cells[1, 1] = "Product ID";
sheet.Cells[1, 2] = "Product Name";
//Add data into details cells.
sheet.Cells[2, 1] = 1;
sheet.Cells[3, 1] = 2;
sheet.Cells[4, 1] = 3;
sheet.Cells[5, 1] = 4;
sheet.Cells[2, 2] = "Apples";
sheet.Cells[3, 2] = "Bananas";
sheet.Cells[4, 2] = "Grapes";
sheet.Cells[5, 2] = "Oranges";
//Enable Auto-filter.
sheet.EnableAutoFilter = true;
//Create the range.
Excel.Range range = sheet.get_Range("A1", "B5");
//Auto-filter the range.
range.AutoFilter("1", "<>", Microsoft.Office.Interop.Excel.XlAutoFilterOperator.xlOr, "", true);
//Auto-fit the second column.
sheet.get_Range("B1", "B5").EntireColumn.AutoFit();
//Save the copy of workbook as .xlsx file.
objBook.SaveCopyAs("vsto_autofilter.xlsx");
}
static void Main(string[] args)
{
Excel.Application application = new Excel.Application();
Excel.Workbook workbook = application.Workbooks.Open(@"C:\test.xlsx");
Excel.Worksheet worksheet = workbook.Sheets[1];
worksheet.Activate();
worksheet.Application.ActiveWindow.SplitRow = 1;
worksheet.Application.ActiveWindow.FreezePanes = true;
Excel.Range firstRow = (Excel.Range)worksheet.Rows[1];
firstRow.AutoFilter(1,
Type.Missing,
Excel.XlAutoFilterOperator.xlAnd,
Type.Missing,
true);
workbook.Save();
workbook.Close(Type.Missing, Type.Missing, Type.Missing);
application.Quit();
Marshal.FinalReleaseComObject(application);
}
private void btnAccept_Click(object sender, EventArgs e)
{
svdReportStock.FileName = "Bitacora_" + DateTime.Now.ToString("ddMMyyyy_hhmmss") + ".xlsx";
if (svdReportStock.ShowDialog() == System.Windows.Forms.DialogResult.OK)
{
Cursor.Current = Cursors.WaitCursor;
string type = null;
var lBinnacle = new posb.Binnacle().List(1);
if (lBinnacle.Count < 1)
{
this.Alert("No se encontraron registros");
return;
}
int index = 3;
Microsoft.Office.Interop.Excel.Application xlApp = null;
Workbook xlWorkBook = null;
Worksheet xlWorkSheetItems = null;
var cc = new ColorConverter();
object misValue = System.Reflection.Missing.Value;
try
{
xlApp = new Microsoft.Office.Interop.Excel.Application();
xlApp.Visible = false;
xlApp.DisplayAlerts = false;
xlApp.EnableEvents = false;
xlWorkBook = xlApp.Workbooks.Open(this.GetPath() + "\\Templates\\" + this.AppSet <string>("BinnalceReport"), Type.Missing, Type.Missing, Type.Missing, Type.Missing,
Type.Missing, Type.Missing, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, Type.Missing,
Type.Missing, Type.Missing, Type.Missing, Type.Missing,
Type.Missing, Microsoft.Office.Interop.Excel.XlCorruptLoad.xlNormalLoad);
//Agrega la hoja de items
xlWorkSheetItems = (Microsoft.Office.Interop.Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);
foreach (var binnacle in lBinnacle)
{
(xlWorkSheetItems.Cells[index, 1] as Microsoft.Office.Interop.Excel.Range).NumberFormat = "@";
xlWorkSheetItems.Cells[index, 1] = binnacle.Id.ToString().PadLeft(10, '0');
xlWorkSheetItems.Cells[index, 2] = binnacle.Usuario;
xlWorkSheetItems.Cells[index, 3] = binnacle.Accion;
xlWorkSheetItems.Cells[index, 4] = binnacle.Tipo;
(xlWorkSheetItems.Cells[index, 5] as Microsoft.Office.Interop.Excel.Range).NumberFormat = "@";
xlWorkSheetItems.Cells[index, 5] = binnacle.Identificador.ToString().PadLeft(10, '0');
(xlWorkSheetItems.Cells[index, 6] as Microsoft.Office.Interop.Excel.Range).NumberFormat = "@";
xlWorkSheetItems.Cells[index, 6] = binnacle.ValorPrevio;
(xlWorkSheetItems.Cells[index, 7] as Microsoft.Office.Interop.Excel.Range).NumberFormat = "@";
xlWorkSheetItems.Cells[index, 7] = binnacle.ValorActual;
(xlWorkSheetItems.Cells[index, 8] as Microsoft.Office.Interop.Excel.Range).NumberFormat = "@";
xlWorkSheetItems.Cells[index, 8] = binnacle.Fecha;
index++;
}
//Mantiene el encabezado fijo
xlWorkSheetItems.Application.ActiveWindow.SplitRow = 2;
xlWorkSheetItems.Application.ActiveWindow.FreezePanes = true;
//Agrega autofiltros
Microsoft.Office.Interop.Excel.Range firstRow = (Microsoft.Office.Interop.Excel.Range)xlWorkSheetItems.Rows[2];
firstRow.Activate();
firstRow.Select();
firstRow.AutoFilter(2,
Type.Missing,
Microsoft.Office.Interop.Excel.XlAutoFilterOperator.xlAnd,
Type.Missing,
true);
xlWorkSheetItems.Cells[2, 1].Select();
//Ajusta el ancho de las columnas a su contenido
Microsoft.Office.Interop.Excel.Range aRange = xlWorkSheetItems.get_Range("A1", "ZZ1000000");
aRange.EntireColumn.AutoFit();
xlApp.EnableEvents = true;
xlWorkBook.SaveAs(svdReportStock.FileName, Microsoft.Office.Interop.Excel.XlFileFormat.xlOpenXMLWorkbook, Type.Missing, Type.Missing,
Type.Missing, Type.Missing,
Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange,
Microsoft.Office.Interop.Excel.XlSaveConflictResolution.xlLocalSessionChanges, Type.Missing, Type.Missing, Type.Missing, false);
xlWorkBook.Close(true, misValue, misValue);
xlApp.Application.Quit();
xlApp.Quit();
if (this.Confirm("¿Deseas abrir la bitácora?"))
{
Process.Start(svdReportStock.FileName);
}
}
catch (Exception ex)
{
this.Alert("Error: " + ex.Message);
}
finally
{
this.ReleasingObjects(xlWorkSheetItems, xlWorkBook, xlApp);
}
}
}
private void btnAccept_Click(object sender, EventArgs e)
{
if (this.ValidateForm())
{
this.svdReportStock.FileName = "PagosPorAño_" + DateTime.Now.ToString("ddMMyyyy_hhmmss") + ".xlsx";
if (svdReportStock.ShowDialog() == System.Windows.Forms.DialogResult.OK)
{
Cursor.Current = Cursors.WaitCursor;
string type = null;
var lConcentrated = new posb.PaymentPerYear().Report(int.Parse(this.cmbYear.Text));
if (lConcentrated.Count < 1)
{
this.Alert("No se encontraron registros en el año indicado");
return;
}
int index = 3;
Microsoft.Office.Interop.Excel.Application xlApp = null;
Workbook xlWorkBook = null;
Worksheet xlWorkSheetItems = null;
var cc = new ColorConverter();
object misValue = System.Reflection.Missing.Value;
try
{
xlApp = new Microsoft.Office.Interop.Excel.Application();
xlApp.Visible = false;
xlApp.DisplayAlerts = false;
xlApp.EnableEvents = false;
xlWorkBook = xlApp.Workbooks.Open(this.GetPath() + "\\Templates\\" + this.AppSet <string>("PaymentYearReport"), Type.Missing, Type.Missing, Type.Missing, Type.Missing,
Type.Missing, Type.Missing, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, Type.Missing,
Type.Missing, Type.Missing, Type.Missing, Type.Missing,
Type.Missing, Microsoft.Office.Interop.Excel.XlCorruptLoad.xlNormalLoad);
//Agrega la hoja de items
xlWorkSheetItems = (Microsoft.Office.Interop.Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);
foreach (var concentrated in lConcentrated)
{
(xlWorkSheetItems.Cells[index, 1] as Microsoft.Office.Interop.Excel.Range).NumberFormat = "@";
xlWorkSheetItems.Cells[index, 1] = concentrated.AP.ToString().PadLeft(10, '0');
(xlWorkSheetItems.Cells[index, 2] as Microsoft.Office.Interop.Excel.Range).NumberFormat = "@";
xlWorkSheetItems.Cells[index, 2] = concentrated.Name;
(xlWorkSheetItems.Cells[index, 3] as Microsoft.Office.Interop.Excel.Range).NumberFormat = "@";
xlWorkSheetItems.Cells[index, 3] = concentrated.IdWaterIntake.ToString().PadLeft(10, '0');
(xlWorkSheetItems.Cells[index, 4] as Microsoft.Office.Interop.Excel.Range).NumberFormat = "@";
xlWorkSheetItems.Cells[index, 4] = concentrated.NameWaterIntake;
WriteMonth(xlWorkSheetItems, index, 5, concentrated.Enero);
WriteMonth(xlWorkSheetItems, index, 6, concentrated.Febrero);
WriteMonth(xlWorkSheetItems, index, 7, concentrated.Marzo);
WriteMonth(xlWorkSheetItems, index, 8, concentrated.Abril);
WriteMonth(xlWorkSheetItems, index, 9, concentrated.Mayo);
WriteMonth(xlWorkSheetItems, index, 10, concentrated.Julio);
WriteMonth(xlWorkSheetItems, index, 11, concentrated.Julio);
WriteMonth(xlWorkSheetItems, index, 12, concentrated.Agosto);
WriteMonth(xlWorkSheetItems, index, 13, concentrated.Septiembre);
WriteMonth(xlWorkSheetItems, index, 14, concentrated.Octubre);
WriteMonth(xlWorkSheetItems, index, 15, concentrated.Noviembre);
WriteMonth(xlWorkSheetItems, index, 16, concentrated.Diciembre);
index++;
}
//Mantiene el encabezado fijo
xlWorkSheetItems.Application.ActiveWindow.SplitRow = 2;
xlWorkSheetItems.Application.ActiveWindow.FreezePanes = true;
//Agrega autofiltros
Microsoft.Office.Interop.Excel.Range firstRow = (Microsoft.Office.Interop.Excel.Range)xlWorkSheetItems.Rows[2];
firstRow.Activate();
firstRow.Select();
firstRow.AutoFilter(2,
Type.Missing,
Microsoft.Office.Interop.Excel.XlAutoFilterOperator.xlAnd,
Type.Missing,
true);
xlWorkSheetItems.Cells[2, 1].Select();
//Ajusta el ancho de las columnas a su contenido
Microsoft.Office.Interop.Excel.Range aRange = xlWorkSheetItems.get_Range("A1", "ZZ1000000");
aRange.EntireColumn.AutoFit();
xlApp.EnableEvents = true;
xlWorkBook.SaveAs(svdReportStock.FileName, Microsoft.Office.Interop.Excel.XlFileFormat.xlOpenXMLWorkbook, Type.Missing, Type.Missing,
Type.Missing, Type.Missing,
Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange,
Microsoft.Office.Interop.Excel.XlSaveConflictResolution.xlLocalSessionChanges, Type.Missing, Type.Missing, Type.Missing, false);
xlWorkBook.Close(true, misValue, misValue);
xlApp.Application.Quit();
xlApp.Quit();
if (this.Confirm("¿Deseas abrir el reporte?"))
{
Process.Start(svdReportStock.FileName);
}
}
catch (Exception ex)
{
this.Alert("Error: " + ex.Message);
}
finally
{
this.ReleasingObjects(xlWorkSheetItems, xlWorkBook, xlApp);
}
}
}
}
private void btnReport_Click(object sender, EventArgs e)
{
if (String.Compare(btnExport.Text, "Zatvori") == 0)
{
Application.Exit();
}
else
{
loadInfo.Text = "Učitavam podatke";
Cursor = Cursors.WaitCursor;
List <Siebel_export> dataSiebelExport = new List <Siebel_export>();
dataSiebelExport.Clear();
Excel.Application xlApp = new Excel.Application();
Excel.Workbook xlWorkbook = xlApp.Workbooks.Open(excelPath.ToString());
Excel._Worksheet xlWorksheet = xlWorkbook.Sheets[1];
Excel.Range xlRange = xlWorksheet.UsedRange;
int rowCount = xlRange.Rows.Count;
int colCount = xlRange.Columns.Count;
Excel.Range a1 = xlWorksheet.Cells[1, 4];
Excel.Range a2 = xlWorksheet.Cells[rowCount, 4];
xlWorksheet.get_Range(a1, a2).NumberFormat = "#";
object[,] _2dArray = new object[rowCount - 1, colCount - 1];
_2dArray = xlRange.Value2;
Siebel_export row_siebel_export = new Siebel_export();
List <Proizvodi> unique_proizvodi = new List <Proizvodi>();
List <string> unique_sims = new List <string>();
List <string> headers = new List <string>();
for (int k = 1; k <= colCount; k++)
{
headers.Add(_2dArray[1, k].ToString());
}
int kor_naplata = headers.IndexOf("Korisnik za naplatu") + 1;
int kor_usluga = headers.IndexOf("Korisnik za uslugu") + 1;
int broj_telefona = headers.IndexOf("Broj telefona") + 1;
int dat_akt = headers.IndexOf("Datum aktivacije") + 1;
int dat_deakt = headers.IndexOf("Datum deaktivacije") + 1;
int status = headers.IndexOf("Status") + 1;
int proizv = headers.IndexOf("Proizvod") + 1;
int prof_napl = headers.IndexOf("Profil naplate") + 1;
int sb_kor = headers.IndexOf("SB korisnik") + 1;
int prof_napl_sb = headers.IndexOf("Profil naplate SB korisnika") + 1;
int ser_sim = headers.IndexOf("Serijski broj SIM-a") + 1;
int dat_poc_uo = headers.IndexOf("Datum početka ugovorne obveze") + 1;
int dat_kraj_uo = headers.IndexOf("Datum isteka ugovorne obveze") + 1;
int pnp = headers.IndexOf("Skraćeni broj (PNP)") + 1;
int odl_prof = headers.IndexOf("VPN odlazni profil") + 1;
int dol_prof = headers.IndexOf("VPN dolazni profil") + 1;
int vpn_budget = headers.IndexOf("Iznos limita - VPN Budget") + 1;
int limit = headers.IndexOf("Iznos limita potrošnje") + 1;
int korp_apn = headers.IndexOf("Korporativni APN") + 1;
int multisim = headers.IndexOf("MultiSIM nominacija") + 1;
int vrsta_usluge = headers.IndexOf("Vrsta usluge") + 1;
int vrsta_proiz = headers.IndexOf("Vrsta proizvoda") + 1;
int klas_proiz = headers.IndexOf("Klasifikacija proizvoda") + 1;
int stat_uo = headers.IndexOf("Status ugovorne obveze") + 1;
int br_dana_uo = headers.IndexOf("Preostalo dana ugovorne obveze") + 1;
int multisimcount = 0;
int korporativniAPN = 0;
int limitPotrosnje = 0;
string temp_broj = _2dArray[2, broj_telefona].ToString();
for (var i = 2; i <= rowCount; i++)
{
if (String.Compare(_2dArray[i, status].ToString(), "Active") == 0 || String.Compare(_2dArray[i, status].ToString(), "Suspended") == 0)
{
if (String.Compare(temp_broj, _2dArray[i, broj_telefona].ToString()) == 0)
{
Proizvodi row_proizvod = new Proizvodi();
Proizvodi unique_proizvod = new Proizvodi();
if (_2dArray[i, kor_naplata] != null && String.Compare(_2dArray[i, kor_naplata].ToString(), "") != 0 && String.Compare(_2dArray[i, kor_naplata].ToString(), "--") != 0)
{
row_siebel_export.KorisnikZaNaplatu = _2dArray[i, kor_naplata].ToString();
}
if (_2dArray[i, kor_usluga] != null && String.Compare(_2dArray[i, kor_usluga].ToString(), "") != 0 && String.Compare(_2dArray[i, kor_usluga].ToString(), "--") != 0)
{
row_siebel_export.KorisnikZaUslugu = _2dArray[i, kor_usluga].ToString();
}
if (_2dArray[i, broj_telefona] != null && String.Compare(_2dArray[i, broj_telefona].ToString(), "") != 0 && String.Compare(_2dArray[i, broj_telefona].ToString(), "--") != 0)
{
row_siebel_export.BrojTelefona = _2dArray[i, broj_telefona].ToString();
}
if (_2dArray[i, status] != null && String.Compare(_2dArray[i, status].ToString(), "") != 0 && String.Compare(_2dArray[i, status].ToString(), "--") != 0)
{
row_siebel_export.Status = _2dArray[i, status].ToString();
}
if (_2dArray[i, prof_napl] != null && String.Compare(_2dArray[i, prof_napl].ToString(), "") != 0 && String.Compare(_2dArray[i, prof_napl].ToString(), "--") != 0)
{
row_siebel_export.ProfilNaplate = _2dArray[i, prof_napl].ToString();
}
if (_2dArray[i, dat_poc_uo] != null && String.Compare(_2dArray[i, dat_poc_uo].ToString(), "") != 0 && String.Compare(_2dArray[i, dat_poc_uo].ToString(), "--") != 0)
{
try
{
if (Convert.ToDateTime(row_siebel_export.PocetakUO) < Convert.ToDateTime(DateTime.FromOADate(Convert.ToDouble(_2dArray[i, dat_poc_uo].ToString())).ToString("dd.MM.yyyy")))
{
row_siebel_export.PocetakUO = DateTime.FromOADate(Convert.ToDouble(_2dArray[i, dat_poc_uo].ToString())).ToString("dd.MM.yyyy");
}
}
catch
{
if (Convert.ToDateTime(row_siebel_export.PocetakUO) < Convert.ToDateTime(_2dArray[i, dat_poc_uo].ToString().Substring(2, _2dArray[i, dat_poc_uo].ToString().Length - 2)))
{
row_siebel_export.PocetakUO = DateTime.ParseExact(_2dArray[i, dat_poc_uo].ToString().Substring(2, _2dArray[i, dat_poc_uo].ToString().Length - 2), "dd-MM-yyyy", CultureInfo.InvariantCulture).ToString("dd.MM.yyyy");
}
}
}
if (_2dArray[i, dat_kraj_uo] != null && String.Compare(_2dArray[i, dat_kraj_uo].ToString(), "") != 0 && String.Compare(_2dArray[i, dat_kraj_uo].ToString(), "--") != 0)
{
try
{
if (Convert.ToDateTime(row_siebel_export.IstekUO) < Convert.ToDateTime(DateTime.FromOADate(Convert.ToDouble(_2dArray[i, dat_kraj_uo].ToString())).ToString("dd.MM.yyyy")))
{
row_siebel_export.IstekUO = DateTime.FromOADate(Convert.ToDouble(_2dArray[i, dat_kraj_uo].ToString())).ToString("dd.MM.yyyy");
}
}
catch
{
if (Convert.ToDateTime(row_siebel_export.IstekUO) < Convert.ToDateTime(_2dArray[i, dat_kraj_uo].ToString().Substring(2, _2dArray[i, dat_kraj_uo].ToString().Length - 2)))
{
row_siebel_export.IstekUO = DateTime.ParseExact(_2dArray[i, dat_kraj_uo].ToString().Substring(2, _2dArray[i, dat_kraj_uo].ToString().Length - 2), "dd-MM-yyyy", CultureInfo.InvariantCulture).ToString("dd.MM.yyyy");
}
}
}
if (_2dArray[i, pnp] != null && String.Compare(_2dArray[i, pnp].ToString(), "") != 0 && String.Compare(_2dArray[i, pnp].ToString(), "--") != 0)
{
row_siebel_export.PNP = _2dArray[i, pnp].ToString();
}
if (_2dArray[i, odl_prof] != null && String.Compare(_2dArray[i, odl_prof].ToString(), "") != 0 && String.Compare(_2dArray[i, odl_prof].ToString(), "--") != 0)
{
row_siebel_export.OdlazniProfil = _2dArray[i, odl_prof].ToString();
}
if (_2dArray[i, dol_prof] != null && String.Compare(_2dArray[i, dol_prof].ToString(), "") != 0 && String.Compare(_2dArray[i, dol_prof].ToString(), "--") != 0)
{
row_siebel_export.DolazniProfil = _2dArray[i, dol_prof].ToString();
}
if (_2dArray[i, stat_uo] != null && String.Compare(_2dArray[i, stat_uo].ToString(), "") != 0 && String.Compare(_2dArray[i, stat_uo].ToString(), "--") != 0)
{
row_siebel_export.StatusUgovorneObveze = _2dArray[i, stat_uo].ToString();
}
if (_2dArray[i, br_dana_uo] != null && String.Compare(_2dArray[i, br_dana_uo].ToString(), "") != 0 && String.Compare(_2dArray[i, br_dana_uo].ToString(), "--") != 0)
{
row_siebel_export.PreostaloDana = _2dArray[i, br_dana_uo].ToString();
}
if (_2dArray[i, multisim] != null && String.Compare(_2dArray[i, multisim].ToString(), "") != 0 && String.Compare(_2dArray[i, multisim].ToString(), "--") != 0)
{
row_siebel_export.MultiSIM_nominacija = _2dArray[i, multisim].ToString();
multisimcount = 1;
}
if (_2dArray[i, korp_apn] != null && String.Compare(_2dArray[i, korp_apn].ToString(), "") != 0 && String.Compare(_2dArray[i, korp_apn].ToString(), "--") != 0)
{
row_siebel_export.KorporativniAPN = _2dArray[i, korp_apn].ToString();
korporativniAPN = 1;
}
if (_2dArray[i, limit] != null && String.Compare(_2dArray[i, limit].ToString(), "") != 0 && String.Compare(_2dArray[i, limit].ToString(), "--") != 0)
{
row_siebel_export.LimitPotrosnje = _2dArray[i, limit].ToString();
limitPotrosnje = 1;
}
if (_2dArray[i, klas_proiz] == null)
{
continue;
}
else if (String.Compare(_2dArray[i, klas_proiz].ToString(), "Root Service") == 0)
{
if (_2dArray[i, dat_akt] != null && String.Compare(_2dArray[i, dat_akt].ToString(), "") != 0 && String.Compare(_2dArray[i, dat_akt].ToString(), "--") != 0)
{
try
{
row_siebel_export.DatumAktivacijeUsluge = DateTime.FromOADate(Convert.ToDouble(_2dArray[i, dat_akt].ToString())).ToString("dd.MM.yyyy");
}
catch
{
row_siebel_export.DatumAktivacijeUsluge = DateTime.ParseExact(_2dArray[i, dat_akt].ToString().Substring(2, _2dArray[i, dat_akt].ToString().Length - 2), "dd-MM-yyyy", CultureInfo.InvariantCulture).ToString("dd.MM.yyyy");
}
}
if (_2dArray[i, proizv] != null && String.Compare(_2dArray[i, proizv].ToString(), "") != 0 && String.Compare(_2dArray[i, proizv].ToString(), "--") != 0)
{
row_siebel_export.Usluga = _2dArray[i, proizv].ToString();
}
}
else if (String.Compare(_2dArray[i, klas_proiz].ToString(), "Tariff") == 0)
{
if (_2dArray[i, dat_akt] != null && String.Compare(_2dArray[i, dat_akt].ToString(), "") != 0 && String.Compare(_2dArray[i, dat_akt].ToString(), "--") != 0)
{
try
{
row_siebel_export.DatumAktivacijeTarife = DateTime.FromOADate(Convert.ToDouble(_2dArray[i, dat_akt].ToString())).ToString("dd.MM.yyyy");
}
catch
{
row_siebel_export.DatumAktivacijeTarife = DateTime.ParseExact(_2dArray[i, dat_akt].ToString().Substring(2, _2dArray[i, dat_akt].ToString().Length - 2), "dd-MM-yyyy", CultureInfo.InvariantCulture).ToString("dd.MM.yyyy");
}
}
if (_2dArray[i, proizv] != null && String.Compare(_2dArray[i, proizv].ToString(), "") != 0 && String.Compare(_2dArray[i, proizv].ToString(), "--") != 0)
{
row_siebel_export.Tarifa = _2dArray[i, proizv].ToString();
}
}
else if (String.Compare(_2dArray[i, klas_proiz].ToString(), "Hardware") == 0)
{
/* simovi lista */
SIM_S sim = new SIM_S();
if (_2dArray[i, proizv] != null && String.Compare(_2dArray[i, proizv].ToString(), "") != 0 && String.Compare(_2dArray[i, proizv].ToString(), "--") != 0)
{
sim.Naziv = _2dArray[i, proizv].ToString();
}
if (_2dArray[i, ser_sim] != null && String.Compare(_2dArray[i, ser_sim].ToString(), "") != 0 && String.Compare(_2dArray[i, ser_sim].ToString(), "--") != 0)
{
sim.Serial = _2dArray[i, ser_sim].ToString();
}
row_siebel_export.Simovi.Add(sim);
if (unique_sims.Find(x => String.Compare(x, sim.Naziv) == 0) == null)
{
unique_sims.Add(sim.Naziv);
}
}
else if (String.Compare(_2dArray[i, klas_proiz].ToString(), "Split Biller") == 0)
{
if (_2dArray[i, sb_kor] != null && String.Compare(_2dArray[i, sb_kor].ToString(), "") != 0 && String.Compare(_2dArray[i, sb_kor].ToString(), "--") != 0)
{
row_siebel_export.SplitBiller = _2dArray[i, sb_kor].ToString();
}
if (_2dArray[i, vpn_budget] != null && String.Compare(_2dArray[i, vpn_budget].ToString(), "") != 0 && String.Compare(_2dArray[i, vpn_budget].ToString(), "--") != 0)
{
row_siebel_export.Vpn_budget = _2dArray[i, vpn_budget].ToString();
}
if (_2dArray[i, prof_napl_sb] != null && String.Compare(_2dArray[i, prof_napl_sb].ToString(), "") != 0 && String.Compare(_2dArray[i, prof_napl_sb].ToString(), "--") != 0)
{
row_siebel_export.ProfilNaplateSB = _2dArray[i, prof_napl_sb].ToString();
}
splitBillerActive = true;
}
else
{
//SVE OSTALO
if (_2dArray[i, dat_akt] != null && String.Compare(_2dArray[i, dat_akt].ToString(), "") != 0 && String.Compare(_2dArray[i, dat_akt].ToString(), "--") != 0)
{
try
{
if (Convert.ToDateTime(row_proizvod.DatumAktivacije) < Convert.ToDateTime(DateTime.FromOADate(Convert.ToDouble(_2dArray[i, dat_akt].ToString())).ToString("dd.MM.yyyy")))
{
row_proizvod.DatumAktivacije = DateTime.FromOADate(Convert.ToDouble(_2dArray[i, dat_akt].ToString())).ToString("dd.MM.yyyy");
}
}
catch
{
if (Convert.ToDateTime(row_proizvod.DatumAktivacije) < Convert.ToDateTime(_2dArray[i, dat_akt].ToString().Substring(2, _2dArray[i, dat_akt].ToString().Length - 2)))
{
row_proizvod.DatumAktivacije = DateTime.ParseExact(_2dArray[i, dat_akt].ToString().Substring(2, _2dArray[i, dat_akt].ToString().Length - 2), "dd-MM-yyyy", CultureInfo.InvariantCulture).ToString("dd.MM.yyyy");
}
}
}
if (_2dArray[i, proizv] != null && String.Compare(_2dArray[i, proizv].ToString(), "") != 0 && String.Compare(_2dArray[i, proizv].ToString(), "--") != 0)
{
row_proizvod.Proizvod = _2dArray[i, proizv].ToString();
unique_proizvod.Proizvod = _2dArray[i, proizv].ToString();
}
if (_2dArray[i, klas_proiz] != null && String.Compare(_2dArray[i, klas_proiz].ToString(), "") != 0 && String.Compare(_2dArray[i, klas_proiz].ToString(), "--") != 0)
{
row_proizvod.KlasifikacijaProizvoda = _2dArray[i, klas_proiz].ToString();
unique_proizvod.KlasifikacijaProizvoda = _2dArray[i, klas_proiz].ToString();
}
row_siebel_export.Proizvodi.Add(row_proizvod);
if (unique_proizvodi.Find(x => String.Compare(x.Proizvod, unique_proizvod.Proizvod) == 0) == null)
{
unique_proizvodi.Add(unique_proizvod);
}
}
if (i != rowCount && String.Compare(temp_broj, _2dArray[i + 1, broj_telefona].ToString()) != 0)
{
row_siebel_export.Simovi = row_siebel_export.Simovi.OrderBy(x => x.Naziv).ToList();
dataSiebelExport.Add(row_siebel_export);
if (i + 1 < rowCount)
{
temp_broj = _2dArray[i + 1, broj_telefona].ToString();
row_siebel_export = new Siebel_export();
}
}
}
}
else if (i + 1 <= rowCount && String.Compare(temp_broj, _2dArray[i + 1, broj_telefona].ToString()) != 0)
{
if (row_siebel_export.Status != null)
{
row_siebel_export.Simovi = row_siebel_export.Simovi.OrderBy(x => x.Naziv).ToList();
dataSiebelExport.Add(row_siebel_export);
}
if (i + 1 < rowCount)
{
temp_broj = _2dArray[i + 1, broj_telefona].ToString();
row_siebel_export = new Siebel_export();
}
}
if (i == rowCount && row_siebel_export.BrojTelefona != null)
{
row_siebel_export.Simovi = row_siebel_export.Simovi.OrderBy(x => x.Naziv).ToList();
dataSiebelExport.Add(row_siebel_export);
}
}
unique_proizvodi = unique_proizvodi.OrderBy(x => x.KlasifikacijaProizvoda).ThenBy(x => x.Proizvod).ToList();
unique_sims = unique_sims.OrderBy(x => x).ToList();
dataSiebelExport = dataSiebelExport.OrderBy(x => x.BrojTelefona).ToList();
xlWorkbook.Close(false, Type.Missing, Type.Missing);
xlApp.Quit();
loadInfo.Text = "Podatci su učitani";
int rowCountExport = dataSiebelExport.Count + 1;
Excel.Application excelApp = new Excel.Application();
Excel.Workbook workBook = excelApp.Workbooks.Add(Type.Missing);
Excel._Worksheet workSheet = workBook.Worksheets[1];
workSheet.PageSetup.Orientation = Excel.XlPageOrientation.xlLandscape;
object[,] _2dData = new object[rowCountExport, 16 + multisimcount + korporativniAPN + limitPotrosnje + 3 + unique_sims.Count + unique_proizvodi.Count];
workSheet.Name = "Export";
_2dData[0, 0] = "Korisnik za naplatu";
_2dData[0, 1] = "Korisnik za uslugu";
_2dData[0, 2] = "Datum aktivacije usluge";
_2dData[0, 3] = "Usluga";
_2dData[0, 4] = "Broj telefona";
_2dData[0, 5] = "Status";
_2dData[0, 6] = "Profil naplate";
if (splitBillerActive)
{
_2dData[0, 7] = "Split Biller";
_2dData[0, 8] = "Iznos limita - VPN Budget";
_2dData[0, 9] = "Profil Naplate SB";
sbBroj = 3;
}
_2dData[0, 7 + sbBroj] = "Datum aktivacije tarife";
_2dData[0, 8 + sbBroj] = "Tarifa";
_2dData[0, 9 + sbBroj] = "Početak ugovorne obveze";
_2dData[0, 10 + sbBroj] = "Istek ugovorne obveze";
_2dData[0, 11 + sbBroj] = "PNP";
_2dData[0, 12 + sbBroj] = "Odlazni profil";
_2dData[0, 13 + sbBroj] = "Dolazni profil";
_2dData[0, 14 + sbBroj] = "Status ugovorne obveze";
_2dData[0, 15 + sbBroj] = "Preostali broj dana ugovorne obveze";
if (limitPotrosnje == 1)
{
_2dData[0, 16 + sbBroj] = "Iznos limita potrošnje";
}
if (korporativniAPN == 1)
{
_2dData[0, 16 + limitPotrosnje + sbBroj] = "Korporativni APN";
}
if (multisimcount == 1)
{
_2dData[0, 16 + limitPotrosnje + korporativniAPN + sbBroj] = "MultiSIM nominacija";
}
string stupac = GetExcelColumnName(16 + multisimcount + limitPotrosnje + korporativniAPN + sbBroj + unique_proizvodi.Count + unique_sims.Count);
workSheet.get_Range("a1", stupac + "1").Cells.Interior.Color = System.Drawing.Color.Orange;
workSheet.get_Range("a1", stupac + "1").Cells.Font.Color = System.Drawing.Color.Black;
workSheet.get_Range("a1", stupac + "1").Cells.Font.Bold = true;
workSheet.get_Range("a1", stupac + "1").Cells.Borders[Excel.XlBordersIndex.xlEdgeBottom].Weight = 3d;
Excel.Range c1 = workSheet.Cells[1, 17 + multisimcount + limitPotrosnje + korporativniAPN + sbBroj];
Excel.Range c2 = workSheet.Cells[rowCountExport, 16 + multisimcount + limitPotrosnje + korporativniAPN + sbBroj + unique_sims.Count];
Excel.Range rangeNum = workSheet.get_Range(c1, c2);
rangeNum.NumberFormat = "@";
rangeNum.Cells.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
for (int k = 1; k <= unique_sims.Count; k++)
{
_2dData[0, 15 + multisimcount + limitPotrosnje + korporativniAPN + sbBroj + k] = unique_sims[k - 1];
}
for (int k = 1; k <= unique_proizvodi.Count; k++)
{
_2dData[0, 15 + multisimcount + limitPotrosnje + korporativniAPN + sbBroj + unique_sims.Count + k] = unique_proizvodi[k - 1].Proizvod;
}
for (int i = 0; i < dataSiebelExport.Count; i++)
{
_2dData[i + 1, 0] = dataSiebelExport[i].KorisnikZaNaplatu;
_2dData[i + 1, 1] = dataSiebelExport[i].KorisnikZaUslugu;
_2dData[i + 1, 2] = Convert.ToDateTime(dataSiebelExport[i].DatumAktivacijeUsluge);
_2dData[i + 1, 3] = dataSiebelExport[i].Usluga;
_2dData[i + 1, 4] = dataSiebelExport[i].BrojTelefona;
_2dData[i + 1, 5] = dataSiebelExport[i].Status;
_2dData[i + 1, 6] = dataSiebelExport[i].ProfilNaplate;
if (splitBillerActive)
{
_2dData[i + 1, 7] = dataSiebelExport[i].SplitBiller;
_2dData[i + 1, 8] = dataSiebelExport[i].Vpn_budget;
_2dData[i + 1, 9] = dataSiebelExport[i].ProfilNaplateSB;
}
_2dData[i + 1, 7 + sbBroj] = Convert.ToDateTime(dataSiebelExport[i].DatumAktivacijeTarife);
_2dData[i + 1, 8 + sbBroj] = dataSiebelExport[i].Tarifa;
_2dData[i + 1, 9 + sbBroj] = Convert.ToDateTime(dataSiebelExport[i].PocetakUO);
_2dData[i + 1, 10 + sbBroj] = Convert.ToDateTime(dataSiebelExport[i].IstekUO);
_2dData[i + 1, 11 + sbBroj] = dataSiebelExport[i].PNP;
_2dData[i + 1, 12 + sbBroj] = dataSiebelExport[i].OdlazniProfil;
_2dData[i + 1, 13 + sbBroj] = dataSiebelExport[i].DolazniProfil;
_2dData[i + 1, 14 + sbBroj] = dataSiebelExport[i].StatusUgovorneObveze;
_2dData[i + 1, 15 + sbBroj] = dataSiebelExport[i].PreostaloDana;
if (limitPotrosnje == 1)
{
_2dData[i + 1, 16 + sbBroj] = dataSiebelExport[i].LimitPotrosnje;
}
if (korporativniAPN == 1)
{
_2dData[i + 1, 16 + limitPotrosnje + sbBroj] = dataSiebelExport[i].KorporativniAPN;
}
if (multisimcount == 1)
{
_2dData[i + 1, 16 + limitPotrosnje + korporativniAPN + sbBroj] = dataSiebelExport[i].MultiSIM_nominacija;
}
foreach (SIM_S temp in dataSiebelExport[i].Simovi)
{
_2dData[i + 1, 16 + multisimcount + limitPotrosnje + korporativniAPN + sbBroj + unique_sims.IndexOf(temp.Naziv)] = temp.Serial;
}
foreach (Proizvodi temp in dataSiebelExport[i].Proizvodi)
{
int index = unique_proizvodi.FindIndex(x => String.Compare(x.Proizvod, temp.Proizvod) == 0);
_2dData[i + 1, 16 + multisimcount + limitPotrosnje + korporativniAPN + sbBroj + unique_sims.Count + index] = "X";
}
}
c1 = workSheet.Cells[1, 1];
c2 = workSheet.Cells[rowCountExport, 16 + multisimcount + limitPotrosnje + korporativniAPN + sbBroj + unique_sims.Count + unique_proizvodi.Count];
Excel.Range range = workSheet.get_Range(c1, c2);
if (chkDatumi.Checked)
{
for (int i = 0; i < dataSiebelExport.Count; i++)
{
foreach (Proizvodi temp in dataSiebelExport[i].Proizvodi)
{
int index = unique_proizvodi.FindIndex(x => String.Compare(x.Proizvod, temp.Proizvod) == 0);
c1 = workSheet.Cells[i + 2, 17 + multisimcount + limitPotrosnje + korporativniAPN + sbBroj + unique_sims.Count + index];
c1.AddComment(temp.DatumAktivacije);
}
}
}
c1 = workSheet.Cells[1, 5];
c2 = workSheet.Cells[rowCountExport, 7];
rangeNum = workSheet.get_Range(c1, c2);
rangeNum.NumberFormat = "#";
rangeNum.Cells.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
c1 = workSheet.Cells[1, 12];
c2 = workSheet.Cells[rowCountExport, 12];
workSheet.get_Range(c1, c2).Cells.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
c1 = workSheet.Cells[1, 16];
c2 = workSheet.Cells[rowCountExport, 16 + sbBroj + unique_proizvodi.Count + unique_sims.Count];
workSheet.get_Range(c1, c2).Cells.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
range.Value = _2dData;
workSheet.Application.ActiveWindow.SplitRow = 1;
workSheet.Application.ActiveWindow.FreezePanes = true;
Excel.Range firstRow = (Excel.Range)workSheet.Rows[1];
firstRow.Activate();
firstRow.Select();
firstRow.AutoFilter(1,
Type.Missing,
Excel.XlAutoFilterOperator.xlAnd,
Type.Missing,
true);
workSheet.get_Range("A:" + stupac, Type.Missing).Columns.AutoFit();
excelApp.DisplayAlerts = true;
excelPath = Path.GetDirectoryName(Environment.GetCommandLineArgs()[0]);
bool tempSave = false;
int saveCounter = 0;
while (!tempSave)
{
try
{
tempSave = true;
if (saveCounter == 0)
{
workSheet.SaveAs(excelPath + "\\" + "export " + dataSiebelExport[0].KorisnikZaNaplatu + " " + DateTime.Now.ToShortDateString() + ".xlsx");
}
else
{
workSheet.SaveAs(excelPath + "\\" + "export " + dataSiebelExport[0].KorisnikZaNaplatu + " " + DateTime.Now.ToShortDateString() + ".xlsx");
}
}
catch
{
tempSave = false;
saveCounter++;
}
}
workBook.Close(true, Type.Missing, Type.Missing);
excelApp.Quit();
Cursor = DefaultCursor;
loadInfo.Text = "Export završen";
loadInfo.ForeColor = Color.Green;
chkDatumi.Enabled = false;
btnExport.Text = "Zatvori";
btnOpenExport.Enabled = false;
}
}
private void btnConvertion_Click(object sender, RoutedEventArgs e)
{
const int DATA_LABEL_POSITION = 3;
const int DATA_TYPE_POSITION = 1;
//ProgressIndicator.IsBusy = true;
string multiple_choice_str = null;
/*
* Task.Factory.StartNew(() =>
* {*/
//- start of task
//https://coderwall.com/p/app3ya/read-excel-file-in-c
//Create COM Objects. Create a COM object for everything that is referenced
Excel.Application xlApp = new Excel.Application();
//kobo workbook
Excel.Workbook xlWorkbook_Results = xlApp.Workbooks.Open(txtKoboFile.Text);
Excel._Worksheet xlWorksheet_Dataset = xlWorkbook_Results.Sheets[1];
Excel.Range xlRange_Dataset = xlWorksheet_Dataset.UsedRange;
int rowCount_Dataset = xlRange_Dataset.Rows.Count;
int colCount_Dataset = xlRange_Dataset.Columns.Count;
//Environment.GetFolderPath(Environment.SpecialFolder.Desktop);
var converted_file = string.Format("{0}\\Converted__{1}.xlsx", Environment.GetFolderPath(Environment.SpecialFolder.Desktop), DateTime.Now.ToFileTime());
//var xlWorkbook_Converted = xlApp.Workbooks.Open(converted_file);
//xlsform workbook
Excel.Workbook xlWorkbook_XLSForm = xlApp.Workbooks.Open(txtXLSFormFile.Text);
//survey sheet
Excel._Worksheet xlWorksheet_Survey = xlWorkbook_XLSForm.Sheets[1];
Excel.Range xlRange_Survey = xlWorksheet_Survey.get_Range("B:B", Type.Missing);
//choices sheet
Excel._Worksheet xlWorksheet_Choices = xlWorkbook_XLSForm.Sheets[2];
Excel.Range xlRange_Choices = xlWorksheet_Choices.get_Range("A:B", Type.Missing);
// int rowCount_Survey = xlRange_Survey.Rows.Count;
// int rowCount_Choices = xlRange_Choices.Rows.Count;
// int tmp = findCodeRowIndex(xlRange_Survey, "cur_ward");
//start decoding labels
//through the dataset columns
int i = 1;
for (var j = 1; j < colCount_Dataset; j++)
{
string toReplace;
//first row has the header => Cells(1,j)
string header_name = xlRange_Dataset.Cells[i, j].Value.ToString();
string[] h = header_name.Split('/');
//toReplace = h.Length == 1? h[0]: h[1];
toReplace = h[h.Length - 1];
//xlRange_Choices.AutoFilter(1, "");
int rowIndex;
if (h.Length == 3 && !string.IsNullOrEmpty(multiple_choice_str)) //type.StartsWith("select_multiple"))
{
//search string in choices workbook if multiple choice
xlRange_Choices.AutoFilter(1, multiple_choice_str);
rowIndex = findCodeRowIndex(
xlRange_Choices.SpecialCells(Excel.XlCellType.xlCellTypeVisible, Type.Missing),
toReplace);
}
else
{
//search string in survey workbook if not multiple choice
rowIndex = findCodeRowIndex(xlRange_Survey, toReplace);
if (!string.IsNullOrEmpty(multiple_choice_str))
{
multiple_choice_str = null;
}
}
if (rowIndex != -1)
{
Excel._Worksheet xlWorksheet;
if (string.IsNullOrEmpty(multiple_choice_str))
{
xlWorksheet = xlWorksheet_Survey;
}
else
{
xlWorksheet = xlWorksheet_Choices;
}
string label =
xlWorksheet.Cells[rowIndex, DATA_LABEL_POSITION].Value.ToString();
xlRange_Dataset.Cells[i, j].Value = label;
if (xlWorksheet == xlWorksheet_Survey)
{
string type = xlWorksheet.Cells[rowIndex, DATA_TYPE_POSITION].Value.ToString();
if (type.StartsWith("select_multiple"))
{
multiple_choice_str = type.Split(' ')[1];
}
else if (type.StartsWith("select_one"))
{
string single_choice_str = type.Split(' ')[1];
//changeLabelOfRowsBelow()
//xlWorksheet_Choices.AutoFilterMode = false;
bool tmp = xlRange_Choices.AutoFilter(1, single_choice_str);
var a = 1;
do
{
a++;
try
{
string cell = xlWorksheet_Dataset.Cells[a, j].Value.ToString();
int row = string.IsNullOrEmpty(cell)?-1:findCodeRowIndex(
xlRange_Choices.SpecialCells(Excel.XlCellType.xlCellTypeVisible, Type.Missing),
cell);
if (row != -1)
{
string lbl = xlWorksheet_Choices.Cells[row, DATA_LABEL_POSITION].Value.ToString();
xlRange_Dataset.Cells[a, j].Value = lbl;
}
}
catch (Exception ex) {
// if (a == 2 && j == 1)
// MessageBox.Show(ex.Message);
}
Dispatcher.Invoke(DispatcherPriority.Normal, new Action(() => {
// ProgressIndicator.BusyContent = string.Format(" Column #{0}", j.ToString());
lblTest.Content = string.Format(" Column {0} / Row {1}", j, a);
}));
} while (a < rowCount_Dataset);
}
}
}
}
xlWorkbook_Results.SaveAs(converted_file);
MessageBox.Show("File Saved!");
//int rowCount = xlRange_Dataset.Rows.Count;
//int rowCount2 = xlRange_Choices.Rows.Count;
//lblTest.Content = string.Format("row count: {0} / {1}", rowCount, rowCount2);
//cleanup
GC.Collect();
GC.WaitForPendingFinalizers();
//rule of thumb for releasing com objects:
// never use two dots, all COM objects must be referenced and released individually
// ex: [somthing].[something].[something] is bad
//release com objects to fully kill excel process from running in the background
Marshal.ReleaseComObject(xlRange_Dataset);
Marshal.ReleaseComObject(xlWorksheet_Dataset);
Marshal.ReleaseComObject(xlRange_Survey);
Marshal.ReleaseComObject(xlWorksheet_Survey);
Marshal.ReleaseComObject(xlRange_Choices);
Marshal.ReleaseComObject(xlWorksheet_Choices);
//close and release
xlWorkbook_Results.Close();
Marshal.ReleaseComObject(xlWorkbook_Results);
xlWorkbook_XLSForm.Close();
Marshal.ReleaseComObject(xlWorkbook_XLSForm);
//quit and release
xlApp.Quit();
Marshal.ReleaseComObject(xlApp);
/*
* //- end of task
* }).ContinueWith((task) => {
* ProgressIndicator.IsBusy = false;
* }, TaskScheduler.FromCurrentSynchronizationContext());
*
*/
}
private void AutoFilter(MSExcel.Range range)
{
range.AutoFilter(1);
}
public static void ExportQueryResults(string queryText, string worksheetName, string fileName)
{
using (SqlConnection cnn = new SqlConnection("context connection=true"))
{
//the temp list to hold the results in
List <object[]> results = new List <object[]>();
cnn.Open();
//create the sql command
SqlCommand cmd = new SqlCommand(queryText, cnn);
using (SqlDataReader reader = cmd.ExecuteReader())
{
int fieldCount = reader.FieldCount;
object[] headers = new object[fieldCount];
for (int i = 0; i < fieldCount; i++)
{
headers[i] = reader.GetName(i);
}
//read the results
while (reader.Read())
{
object[] values = new object[fieldCount];
for (int i = 0; i < fieldCount; i++)
{
values[i] = reader[i];
}
results.Add(values);
}
//convert the results into a 2-d array to export into Excel
object[,] exportVals = new object[results.Count, fieldCount];
for (int row = 0; row < results.Count; row++)
{
for (int col = 0; col < fieldCount; col++)
{
exportVals[row, col] = results[row][col];
}
}
Excel.Application _app = new Excel.Application();
Excel.Workbook _book = _app.Workbooks.Add(Missing.Value);
Excel.Worksheet _sheet = (Excel.Worksheet)_book.ActiveSheet;
Excel.Range _range = (Excel.Range)_sheet.Cells[1, 1];
_app.DisplayAlerts = false;
//set the headers and freeze the panes
_range = _sheet.get_Range(_sheet.Cells[1, 1], _sheet.Cells[1, fieldCount]);
_range.NumberFormat = "@";
_range.HorizontalAlignment = Excel.XlHAlign.xlHAlignLeft;
_range.Value2 = headers;
_range.Font.Bold = true;
_range = _sheet.get_Range(_sheet.Cells[2, 1], _sheet.Cells[2, 1]);
_range.EntireRow.Select();
_range.Application.ActiveWindow.FreezePanes = true;
_range = _sheet.get_Range(_sheet.Cells[2, 1], _sheet.Cells[results.Count, fieldCount]);
_range.Value2 = exportVals;
_range = _sheet.get_Range(_sheet.Cells[1, 1], _sheet.Cells[exportVals.Length, fieldCount]);
_range.AutoFilter(1, Type.Missing, Excel.XlAutoFilterOperator.xlAnd, Type.Missing, true);
_sheet.Cells.Columns.AutoFit();
_sheet.Range["A1"].Select();
_sheet.Name = worksheetName;
//remove any extra worksheets
foreach (Excel.Worksheet sht in _book.Worksheets)
{
if (sht.Name != worksheetName)
{
sht.Delete();
}
}
_book.SaveAs(fileName
, Excel.XlFileFormat.xlExcel5
, Missing.Value
, Missing.Value
, false
, false
, Excel.XlSaveAsAccessMode.xlNoChange
, Missing.Value
, Missing.Value
, Missing.Value
, Missing.Value
, Missing.Value);
//_book.Close(Missing.Value, Missing.Value, Missing.Value);
_app.Application.Quit();
GC.Collect();
GC.WaitForPendingFinalizers();
Marshal.ReleaseComObject(_range);
Marshal.ReleaseComObject(_sheet);
Marshal.ReleaseComObject(_book);
Marshal.ReleaseComObject(_app);
_range = null;
_sheet = null;
_book = null;
_app = null;
GC.Collect();
GC.WaitForPendingFinalizers();
}
}
}
private void CreateSheet(Excel.Worksheet oSheet, DateTime dFechaInicio, ref DateTime dFecha, DateTime dFechaFin, ref int contador)
{
bool swFound = false;
int nDay = int.Parse(dFecha.ToString("dd"));
int nMonth = int.Parse(dFecha.ToString("MM"));
int nHour = int.Parse(dFecha.ToString("HH"));
int nMinute = int.Parse(dFecha.ToString("mm"));
int nHourIni = nHour;
int nMinuteIni = nMinute;
int nHourFin = int.Parse(dFechaFin.ToString("HH"));
int nMinuteFin = int.Parse(dFechaFin.ToString("mm"));
int nDayOfWeek = NumberOfDayOfWeek(dFecha);
object missing = Type.Missing;
//string sFechaInicio = null;
//string sFechaFin = null;
// Crear columna 1 con las programaciones de los comandos
oSheet.Name = dFecha.ToString("dd MM yyyy");
int nRow = 1;
oSheet.Cells[nRow, 1] = "Programación";
foreach (CronRegister myCronRegister in myLinesList)
{
if (myCronRegister.bChecked)
{
nRow++;
oSheet.Cells[nRow, 1] = myCronRegister.sMinute + " " +
myCronRegister.sHour + " " +
myCronRegister.sDay_of_month + " " +
myCronRegister.sMonth + " " +
myCronRegister.sDay_of_week;
}
}
// Crear columna 2 con los nombres de los comandos
nRow = 1;
oSheet.Cells[nRow, 2] = "Comando";
foreach (CronRegister myCronRegister in myLinesList)
{
if (myCronRegister.bChecked)
{
nRow++;
string sCommand = null;
bool flag = false;
foreach (string myCommand in myCronRegister.listCommand)
{
if (flag)
{
sCommand += " ";
}
else
{
flag = true;
}
sCommand += myCommand;
}
oSheet.Cells[nRow, 2] = sCommand;
}
}
int nCol = 2; int nMaxCol = nCol;
while (dFecha <= dFechaFin)
{
nCol++;
if (nMaxCol < nCol)
{
nMaxCol = nCol;
}
//((Microsoft.Office.Interop.Excel.Range)oSheet.Cells[1, nCol]).Name = "H" + dFecha.ToString("HH") + "M" + dFecha.ToString("mm");
//((Microsoft.Office.Interop.Excel.Range)oSheet.Cells[1, nCol]).
// Establecer HH:MM en la columna
oSheet.Cells[1, nCol] = "[" + dFecha.ToString("HH") + ":" + dFecha.ToString("mm") + "]";
//oSheet.Cells.Name = "H" + dFecha.ToString("HH") + "M" + dFecha.ToString("mm");
nRow = 1;
foreach (CronRegister myCronRegister in myLinesList)
{
if (myCronRegister.bChecked)
{
swFound = false;
foreach (int dayOfWeek in myCronRegister.nDays_of_week)
{
if (nDayOfWeek == dayOfWeek)
{
swFound = true;
break;
}
}
if (swFound)
{
swFound = false;
foreach (int month in myCronRegister.nMonths)
{
if (nMonth == month)
{
swFound = true;
break;
}
}
}
if (swFound)
{
swFound = false;
foreach (int dayOfMonth in myCronRegister.nDays_of_month)
{
if (nDay == dayOfMonth)
{
swFound = true;
break;
}
}
}
if (swFound)
{
swFound = false;
foreach (int hour in myCronRegister.nHours)
{
if (nHour == hour)
{
swFound = true;
break;
}
}
}
if (swFound)
{
swFound = false;
foreach (int minute in myCronRegister.nMinutes)
{
if (nMinute == minute)
{
swFound = true;
break;
}
}
}
nRow++;
if (swFound)
{
oSheet.Cells[nRow, nCol] = "X";
oSheet.Cells[nRow, nCol].Name = "H" + dFecha.ToString("HH") + "_" + dFecha.ToString("mm") + "_" + contador.ToString();
contador++;
}
//oSheet.Cells[nRow, nCol].Name = "pepe_" + contador.ToString();
//try
//{
//}
//catch (Exception ex)
//{
// Console.WriteLine(ex.Message);
//}
//=CONTAR.SI(RN2:RN202;"X")
}
}
//contador = 1;
DateTime newDate = dFecha.AddMinutes(1);
dFecha = newDate;
nHour = int.Parse(dFecha.ToString("HH"));
nMinute = int.Parse(dFecha.ToString("mm"));
if ((nMinute > nMinuteFin) && (nHour == nHourFin))
{
newDate = dFecha.AddDays(1);
dFecha = newDate;
dFecha = DateTime.Parse(newDate.ToString("yyyy/MM/dd") + " " + dFechaInicio.ToString("HH:mm"));
break;
}
if ((nHour == 0) && (nMinute == 0))
{
break;
}
}
nCol = 3;
while (nCol <= nMaxCol)
{
string colInicial = oSheet.Columns[nCol].Address + "2";
colInicial = colInicial.Substring(4);
string colFinal = oSheet.Columns[nCol].Address + nRow.ToString();
colFinal = colFinal.Substring(4);
string colFormula = oSheet.Columns[nCol].Address + (nRow + 1).ToString();
colFormula = colFormula.Substring(4);
try
{
//oSheet.Cells[nRow + 1, nCol].Formula = "=CONTAR.SI(" + colInicial + ":" + colFinal + ";\"X\")";
Excel.Range rangeFormula = oSheet.get_Range(colFormula, colFormula);
rangeFormula.Formula = "=COUNTIF(" + colInicial + ":" + colFinal + "," + '"' + "X" + '"' + ")";
}
catch (COMException ex)
{
Console.Write(ex.ErrorCode);
}
nCol++;
}
Excel.Range last = oSheet.Cells.SpecialCells(Excel.XlCellType.xlCellTypeLastCell, missing);
Excel.Range rangeAll = oSheet.get_Range("A1", last);
Excel.Range rangeHours = oSheet.get_Range("C1", last);
Excel.Range rangeA2 = oSheet.get_Range("A2", "A2");
Excel.Range rangeProg = oSheet.get_Range("A1", "A1");
Excel.Range rangeCommand = oSheet.get_Range("B1", "B1");
//rangeAll.Columns.AutoFit();
rangeProg.Columns.ColumnWidth = 80;
rangeCommand.Columns.ColumnWidth = 100;
rangeHours.Columns.ColumnWidth = 9;
//rangeProgram.Columns.AutoFit();
//range.Columns.NumberFormat = "@";
rangeAll.AutoFilter(1, missing, Excel.XlAutoFilterOperator.xlAnd, missing, true);
rangeA2.Activate();
rangeA2.Select();
rangeA2.Application.ActiveWindow.FreezePanes = true;
}
/// <summary>
/// Adds command worksheet to workbook. Used for general command explanations and option lists
/// </summary>
private void CreateCommandWorksheet()
{
// Attempts to find a currently existing Command worksheet
Excel.Workbook workbook = Globals.ThisAddIn.Application.ActiveWorkbook;
var worksheet = workbook.GetWorksheets().FirstOrDefault(x => x.Name == "Commands");
// Adds a worksheet named Commands if it does not already exist
worksheet = workbook.CreateNewWorksheet("Commands");
// Adds column headers to command table
worksheet.Range["A1"].Value = "Command Type";
worksheet.Range["B1"].Value = "Command";
worksheet.Range["C1"].Value = "Options";
worksheet.Range["D1"].Value = "Reference";
worksheet.Range["E1"].Value = "New/Reference Name";
worksheet.Range["F1"].Value = "Target Value";
worksheet.Range["G1"].Value = "Auxillary Value";
// Add option ranges
AddOptions(workbook, nameof(WorkbookOptions), "J", OptionHelper.GetWorkbookOptions());
AddOptions(workbook, nameof(ReferenceOptions), "K", OptionHelper.GetReferenceOptions());
AddOptions(workbook, nameof(RangeOptions), "L", OptionHelper.GetRangeOptions());
AddOptions(workbook, nameof(ExcelAutoFilterOptions), "M", OptionHelper.GetExcelAutoFilterOptions());
AddOptions(workbook, nameof(MatchValueOptions), "N", OptionHelper.GetMatchValueOptions());
// Add command ranges
int counter = 2;
counter = AddCommands(workbook, nameof(WorkbookCommands), counter, CommandHelper.GetWorkbookCommands());
counter = AddCommands(workbook, nameof(WorksheetCommands), counter, CommandHelper.GetWorksheetCommands());
counter = AddCommands(workbook, nameof(RangeCommands), counter, CommandHelper.GetRangeCommands());
counter = AddCommands(workbook, nameof(CodeCommands), counter, CommandHelper.GetCodeCommands());
counter = AddCommands(workbook, nameof(FilterCommands), counter, CommandHelper.GetFilterCommands());
counter = AddCommands(workbook, nameof(DataCommands), counter, CommandHelper.GetDataCommands());
#region Styling
// Styles the command table
var stylingRange = (Excel.Range)worksheet.Range["A:G"];
stylingRange.ColumnWidth = 45;
stylingRange.Cells.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
stylingRange.Cells.VerticalAlignment = Excel.XlVAlign.xlVAlignCenter;
stylingRange.Cells.WrapText = true;
// Selects and styles the command headers
var topRange = worksheet.Range["A1:G1"];
topRange.Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.CornflowerBlue);
topRange.Font.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Black);
topRange.Font.Bold = true;
// Alternates color command table rows for easier reading
for (int o = 3; o < counter - 1; o++)
{
Excel.Range colorRange;
if (o % 2 != 0)
{
colorRange = worksheet.Range[$"A{o}:G{o}"];
colorRange.Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.LightGray);
colorRange.Font.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Black);
}
}
// Adds all around border to command table
Excel.Range borderRange = worksheet.Range[$"A1:G{counter-2}"];
borderRange.Cells.Borders.LineStyle = Excel.XlLineStyle.xlContinuous;
// Adds filter ability to command table for users
borderRange.AutoFilter(1);
// Styles the options tables
stylingRange = (Excel.Range)worksheet.Range["J:N"];
stylingRange.ColumnWidth = 25;
stylingRange.Cells.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
#endregion
}
public void filter(String rangeFrom, String rangeTo)
{
workSheet.EnableAutoFilter = true;
Excel.Range ranges = workSheet.get_Range(rangeFrom, rangeTo);
ranges.AutoFilter("1", "<>", Microsoft.Office.Interop.Excel.XlAutoFilterOperator.xlOr, "", true);
}
private void btnAccept_Click(object sender, EventArgs e)
{
svdReportStock.FileName = "Concentrado_" + DateTime.Now.ToString("ddMMyyyy_hhmmss") + ".xlsx";
if (svdReportStock.ShowDialog() == System.Windows.Forms.DialogResult.OK)
{
Cursor.Current = Cursors.WaitCursor;
string type = null;
if (this.cmbType.SelectedIndex > 0)
{
type = this.cmbType.Text;
}
var lConcentrated = new posb.Reports().Concentrated(type, this.dtpDate1.Value, this.dtpDate2.Value);
if (lConcentrated.Count < 1)
{
this.Alert("No se encontraron registros con las fechas indicadas");
return;
}
int index = 3;
Microsoft.Office.Interop.Excel.Application xlApp = null;
Workbook xlWorkBook = null;
Worksheet xlWorkSheetItems = null;
var cc = new ColorConverter();
object misValue = System.Reflection.Missing.Value;
try
{
xlApp = new Microsoft.Office.Interop.Excel.Application();
xlApp.Visible = false;
xlApp.DisplayAlerts = false;
xlApp.EnableEvents = false;
xlWorkBook = xlApp.Workbooks.Open(this.GetPath() + "\\Templates\\" + this.AppSet <string>("ConcentratedReport"), Type.Missing, Type.Missing, Type.Missing, Type.Missing,
Type.Missing, Type.Missing, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, Type.Missing,
Type.Missing, Type.Missing, Type.Missing, Type.Missing,
Type.Missing, Microsoft.Office.Interop.Excel.XlCorruptLoad.xlNormalLoad);
//Agrega la hoja de items
xlWorkSheetItems = (Microsoft.Office.Interop.Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);
foreach (var concentrated in lConcentrated)
{
xlWorkSheetItems.Cells[index, 1] = concentrated.Type;
(xlWorkSheetItems.Cells[index, 2] as Microsoft.Office.Interop.Excel.Range).NumberFormat = "@";
xlWorkSheetItems.Cells[index, 2] = concentrated.Id.ToString().PadLeft(10, '0');
(xlWorkSheetItems.Cells[index, 3] as Microsoft.Office.Interop.Excel.Range).NumberFormat = "@";
xlWorkSheetItems.Cells[index, 3] = concentrated.Name;
(xlWorkSheetItems.Cells[index, 4] as Microsoft.Office.Interop.Excel.Range).NumberFormat = "@";
xlWorkSheetItems.Cells[index, 4] = concentrated.Description;
(xlWorkSheetItems.Cells[index, 5] as Microsoft.Office.Interop.Excel.Range).NumberFormat = "$###,##";
xlWorkSheetItems.Cells[index, 5] = concentrated.Amount;
(xlWorkSheetItems.Cells[index, 6] as Microsoft.Office.Interop.Excel.Range).NumberFormat = "@";
xlWorkSheetItems.Cells[index, 6] = concentrated.CreatedDate.Value.ToString("dd/MM/yyyy");
index++;
}
xlWorkSheetItems.Cells[index + 2, 4].Font.Size = 13;
xlWorkSheetItems.Cells[index + 2, 4].Font.Bold = true;
xlWorkSheetItems.Cells[index + 2, 4].HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignRight;
xlWorkSheetItems.Cells[index + 2, 4] = "Total:";
(xlWorkSheetItems.Cells[index + 2, 5] as Microsoft.Office.Interop.Excel.Range).NumberFormat = "$###,##";
xlWorkSheetItems.Cells[index + 2, 5].Font.Size = 14;
xlWorkSheetItems.Cells[index + 2, 5].Font.Bold = true;
xlWorkSheetItems.Cells[index + 2, 5].Formula = string.Format("=SUBTOTAL(9,E2:E{0})", index);
//Mantiene el encabezado fijo
xlWorkSheetItems.Application.ActiveWindow.SplitRow = 2;
xlWorkSheetItems.Application.ActiveWindow.FreezePanes = true;
//Agrega autofiltros
Microsoft.Office.Interop.Excel.Range firstRow = (Microsoft.Office.Interop.Excel.Range)xlWorkSheetItems.Rows[2];
firstRow.Activate();
firstRow.Select();
firstRow.AutoFilter(2,
Type.Missing,
Microsoft.Office.Interop.Excel.XlAutoFilterOperator.xlAnd,
Type.Missing,
true);
xlWorkSheetItems.Cells[2, 1].Select();
//Ajusta el ancho de las columnas a su contenido
Microsoft.Office.Interop.Excel.Range aRange = xlWorkSheetItems.get_Range("A1", "ZZ1000000");
aRange.EntireColumn.AutoFit();
xlApp.EnableEvents = true;
xlWorkBook.SaveAs(svdReportStock.FileName, Microsoft.Office.Interop.Excel.XlFileFormat.xlOpenXMLWorkbook, Type.Missing, Type.Missing,
Type.Missing, Type.Missing,
Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange,
Microsoft.Office.Interop.Excel.XlSaveConflictResolution.xlLocalSessionChanges, Type.Missing, Type.Missing, Type.Missing, false);
xlWorkBook.Close(true, misValue, misValue);
xlApp.Application.Quit();
xlApp.Quit();
if (this.Confirm("¿Deseas abrir el reporte?"))
{
Process.Start(svdReportStock.FileName);
}
}
catch (Exception ex)
{
this.Alert("Error: " + ex.Message);
}
finally
{
this.ReleasingObjects(xlWorkSheetItems, xlWorkBook, xlApp);
}
}
}
public void Tranforms(String[] FileNames, BackgroundWorker woker, DoWorkEventArgs e)
{
if (FileNames[0] != "")
{
Excel.Application App;
Excel.Workbook workbook;
Excel.Worksheet worksheet;
int z = 1;
App = new Excel.Application();
workbook = App.Workbooks.Add();
worksheet = workbook.ActiveSheet;
App.Visible = false;
String path = FileNames[0];
String pathOnly = Path.GetDirectoryName(path);
String fileName;
long txtrow = 0;
int row = 0;
double num = 0;
for (int j = 0; j < FileNames.Length; j++)
{
path = FileNames[j];
using (StreamReader sr = new StreamReader(path, Encoding.UTF8))
{
while (!sr.EndOfStream)
{
txtrow++;
sr.ReadLine();
}
}
}
for (int j = 0; j < FileNames.Length; j++)
{
path = FileNames[j];
fileName = Path.GetFileName(path);
worksheet = workbook.ActiveSheet;
int RowLast = 0;
// long txtrow = 0;
worksheet.Name = fileName.Remove(fileName.Length - 4, 4);
//using (StreamReader sr = new StreamReader(path, Encoding.UTF8))
//{
// while (!sr.EndOfStream)
// {
// txtrow++;
// sr.ReadLine();
// }
// }
using (StreamReader sr = new StreamReader(path, Encoding.UTF8))
{
while (!sr.EndOfStream)
{
string s = sr.ReadLine();
string[] temp = s.Split('|');
for (int i = 0; i < temp.Length; i++)
{
worksheet.Cells[z, i + 1] = temp[i];
}
row++;
double sum = (100 / ((double)(txtrow * FileNames.Length))) * ((double)(j + 1) * (double)(row));
num = (num > sum) ? num : sum; // 추후 로딩을 위한 값
if (num != 0)
{
woker.ReportProgress((int)(num));
}
if (z == 1)
{
RowLast = temp.Length;
}
z++;
}
Excel.Range heder = worksheet.Range[worksheet.Cells[1, 1], worksheet.Cells[z, RowLast]];
heder.Columns.AutoFit();
Excel.Range filterRng = worksheet.Range[worksheet.Cells[1, 1], worksheet.Cells[z, RowLast]];
if (RowLast != 1)
{
filterRng.AutoFilter(1, Type.Missing, Excel.XlAutoFilterOperator.xlAnd, Type.Missing, true);
}
}
z = 1;
if (j < FileNames.Length - 1)
{
workbook.Worksheets.Add(After: workbook.Sheets[workbook.Sheets.Count]);
}
}
System.IO.FileInfo fi = new System.IO.FileInfo(path + "\\DB_EXProt.xls");
if (fi.Exists)
{
MessageBox.Show("이미 DB_ExPort.xls 파일이 존재합니다.");
}
else
{
workbook.SaveAs(pathOnly + "\\DB_ExPort.xls", Excel.XlFileFormat.xlWorkbookNormal);
}
MessageBox.Show("DB_EXPort.xls 생성 완료!!", "생성 완료",
MessageBoxButtons.OK,
MessageBoxIcon.None,
MessageBoxDefaultButton.Button1, MessageBoxOptions.DefaultDesktopOnly);
workbook.Save();
workbook.Close(0);
App.Quit();
ExcelKill();
}
else
{
MessageBox.Show("경로를 설정해 주세요.");
}
}
private void button1_Click(object sender, EventArgs e)
{
/// check if user has selected a file - if not then warn the user and return
/// verify a file has been selected by observing the label lbl_1.
if (lbl_1.Text == "Please Select a File")
{
warningLabel.ForeColor = Color.Red;
warningLabel.Text = "Please select a file before continuing";
return;
}
warningLabel.Text = "";
/// check if user has selected a number of files
if (comboBox1.SelectedIndex == -1)
{
warningLabel.ForeColor = Color.Red;
warningLabel.Text = "Please select the number of samples in the file";
return;
}
warningLabel.Text = "";
string[] filePath = lbl_1.Text.Split('\n');
foreach (string sFileName in filePath)
{
//https://coderwall.com/p/app3ya/read-excel-file-in-c
//Create COM Objects. Create a COM object for everything that is referenced
Excel.Application xlApp = new Excel.Application();
xlApp.Visible = false;
Excel.Workbook xlWorkbook = xlApp.Workbooks.Open(sFileName);
Excel._Worksheet xlWorksheet = xlWorkbook.Sheets[1];
Excel.Range xlRange = xlWorksheet.UsedRange;
//create COM objects for copy of original document
Excel.Application xlAppCopy = new Excel.Application();
xlAppCopy.Visible = false;
Excel.Workbook xlWorkbookCopy = xlAppCopy.Workbooks.Add();
Excel._Worksheet xlWorksheetCopy = xlWorkbookCopy.Sheets[1];
//Copy all contents from selected file to the new excel doc
int nRows = xlRange.Rows.Count;
int nEndDestinationCopy = nRows;
string endDestinationCopy = "P" + nEndDestinationCopy.ToString(); // This will only work if all the docs are P columns wide!
Excel.Range xlRangeCopy = xlWorksheetCopy.get_Range("A1", endDestinationCopy);
xlRangeCopy.Value2 = xlRange.Value2;
//create COM objects for output file
Excel.Application xlApp2 = new Excel.Application();
xlApp2.Visible = false;
Excel.Workbook xlWorkbook2 = xlApp2.Workbooks.Add();
Excel._Worksheet xlWorksheet2 = xlWorkbook2.Sheets[1];
//insert HODS codes
xlWorksheet2.Cells[1, 1] = box_0101.Text;
xlWorksheet2.Cells[1, 2] = box_0102.Text;
xlWorksheet2.Cells[1, 3] = box_0103.Text;
xlWorksheet2.Cells[1, 4] = box_0104.Text;
xlWorksheet2.Cells[1, 5] = box_0105.Text;
xlWorksheet2.Cells[1, 6] = box_0106.Text;
xlWorksheet2.Cells[1, 7] = box_0107.Text;
xlWorksheet2.Cells[1, 8] = box_0108.Text;
//LOOP THROUGH SAMPLES
//for loop to repeat for each sample. Can reinstate this later.
for (int sample = 1; sample < comboBox1.SelectedIndex + 2; sample++)
{
//i is the row we're interested in
int i = sample + 2;
//https://docs.microsoft.com/en-us/dotnet/api/microsoft.office.interop.excel.range.autofilter?view=excel-pia
//PUT SAMPLE NAME IN EXCEL
///SORT AND FILTER- i is column number
xlRangeCopy.Sort(xlRangeCopy.Columns[i], Excel.XlSortOrder.xlAscending, Type.Missing, Type.Missing, Excel.XlSortOrder.xlAscending, Type.Missing, Excel.XlSortOrder.xlAscending, Excel.XlYesNoGuess.xlYes);
xlRangeCopy.AutoFilter(i, "<100");
//COUNT FILTERED ROWS
//https://stackoverflow.com/questions/41731714/counting-rows-of-filtered-excel-range-in-c-sharp
//NOTE, this includes the first row in the count, so -1
//this counts visible cells
Excel.Range xlRange3 = xlRangeCopy.SpecialCells(Excel.XlCellType.xlCellTypeVisible);
// -1 because of headers
int nFilteredRows = xlRange3.Rows.Count - 1;
string sampleLetter = ((char)(sample + 64)).ToString();
//MOVE FILTERED ROWS- will need to change the values in get range to fit the sample and number of filtered genes
//-1 becaause to get 4 rows we need A2:A5 and 5-2 is 4-1
int nStartSource = 2;
int nEndSource = nStartSource + nFilteredRows - 1;
int nStartDestination = 2;
int nEndDestination = nStartDestination + nFilteredRows - 1;
string startSource = "A" + nStartSource.ToString();
string startDestination = sampleLetter + nStartDestination.ToString();
string endSource = "A" + nEndSource.ToString();
string endDestination = sampleLetter + nEndDestination.ToString();
Excel.Range xlRange2 = xlWorksheet2.get_Range(startDestination, endDestination);
Excel.Range sourceRng = xlWorksheetCopy.get_Range(startSource, endSource);
xlRange2.Value2 = sourceRng.Value2;
xlRange2.RemoveDuplicates(1, Excel.XlYesNoGuess.xlNo);
//REMOVE FILTER
xlRangeCopy.AutoFilter(i);
Marshal.ReleaseComObject(sourceRng);
Marshal.ReleaseComObject(xlRange2);
Marshal.ReleaseComObject(xlRange3);
}
//TRANSPOSE
//Access range of data in worksheet, and dimensions
Excel.Range xlRange2Used = xlWorksheet2.UsedRange;
int rowsXlRange2Used = xlRange2Used.Rows.Count;
int colsXlRange2Used = xlRange2Used.Columns.Count;
//formulate location for transposed range
string newRangeStart = "A" + (rowsXlRange2Used + 2).ToString();
string newRangeEnd = ((char)(rowsXlRange2Used + 64)).ToString() + (rowsXlRange2Used + colsXlRange2Used + 1).ToString();
Excel.Range xlRange2Replace = xlWorksheet2.get_Range(newRangeStart, newRangeEnd);
//transpose into new location
xlWorksheet2.Range[newRangeStart + ":" + newRangeEnd].Value = xlApp2.WorksheetFunction.Transpose(xlRange2Used);
//delete columns with untransposed information
xlRange2Used.EntireRow.Delete();
//SAVE OUTPUT DOCUMENT
string fileName = Path.GetFileName(sFileName); //retreives the filename from the path
string directoryName = Path.GetDirectoryName(sFileName); //retreives path of the directory of selected file
xlWorkbook2.SaveAs(directoryName + "/" + "output_" + fileName);
label_output.Text = "Output file is complete: " + directoryName + "/" + "output_" + fileName;
//Close documents without displaying any prompt boxes
xlApp2.DisplayAlerts = false;
xlApp2.Quit();
xlAppCopy.DisplayAlerts = false;
xlAppCopy.Quit();
xlApp.DisplayAlerts = false;
xlApp.Quit();
//cleanup
GC.Collect();
GC.WaitForPendingFinalizers();
//rule of thumb for releasing com objects:
// never use two dots, all COM objects must be referenced and released individually
// ex: [somthing].[something].[something] is bad
//release com objects to fully kill excel process from running in the background
Marshal.ReleaseComObject(xlRange);
Marshal.ReleaseComObject(xlRange2Replace);
Marshal.ReleaseComObject(xlRange2Used);
Marshal.ReleaseComObject(xlRangeCopy);
Marshal.ReleaseComObject(xlWorksheet);
Marshal.ReleaseComObject(xlWorksheetCopy);
Marshal.ReleaseComObject(xlWorksheet2);
Marshal.ReleaseComObject(xlWorkbook);
Marshal.ReleaseComObject(xlWorkbook2);
Marshal.ReleaseComObject(xlWorkbookCopy);
Marshal.ReleaseComObject(xlApp);
Marshal.ReleaseComObject(xlApp2);
Marshal.ReleaseComObject(xlAppCopy);
}
}
/* Data handling */
public void AutoFilter()
{
_Range.AutoFilter("1", xlMissing, Excel.XlAutoFilterOperator.xlOr, xlMissing, xlMissing);
}
