/// <summary> /// Generate a klip file from the data in the given sheet, taking into account rows minRow to maxRow /// </summary> /// <param name="aSheet"></param> /// <param name="minRow"></param> /// <param name="maxRow"></param> public void GenerateKlip(Excel.Worksheet aSheet, int minRow, int maxRow) { if (aSheet == null) { return; } string klipTemplate = KlipTemplate(aSheet.Parent.Name, aSheet.Name); Excel.Application app = Globals.KlipAddIn.Application; Excel.Worksheet sh = aSheet; Excel.Workbook wbKlip = app.Workbooks.Add(klipTemplate); sh.Activate(); //the template has a colunm mapping, linking the 2 file formats if (wbKlip.Names.Item("Mapping").RefersToRange is Excel.Range mappingRg) { List <string> mapping = new List <string>(); List <string> defaults = new List <string>(); for (int r = mappingRg.Row + 1; r < mappingRg.Row + mappingRg.Rows.Count; r++) { mapping.Add(mappingRg.Cells[r, 2].Text); defaults.Add(mappingRg.Cells[r, 3].Text); } Dictionary <string, int> colDict = sh.ColumnDictionary(); List <int> colsToPrint = new List <int>(mapping.Count); int col; for (int r = 0; r < mapping.Count; r++) { if (colDict.TryGetValue(mapping[r], out col)) { colsToPrint.Add(col); } else { colsToPrint.Add(-1); } } Excel.Range data = sh.Range[sh.Cells[minRow, 1], sh.Cells[maxRow, colDict.Count]]; object[,] matrix = data.Value2; List <int> hiddenrows = new List <int>(); for (int i = minRow; i <= maxRow; i++) { if (sh.Rows[i].Hidden) { hiddenrows.Add(i - minRow + matrix.GetLowerBound(0)); } } Excel.Worksheet KlipSh = WriteKlipSheet(wbKlip, matrix, hiddenrows, colsToPrint, defaults); //do some formatting to make sure it matches what Klip is expecting Dictionary <string, int> klipColDict = KlipSh.ColumnDictionary(); if (klipColDict.TryGetValue("MES_MFCT_ORDER_ID", out col)) { KlipSh.Columns[col].EntireColumn.NumberFormat = "0"; } if (klipColDict.TryGetValue("MFCT_ORDER_NO", out col)) { KlipSh.Columns[col].EntireColumn.NumberFormat = "0"; } if (klipColDict.TryGetValue("DUE_DATE", out col)) { KlipSh.Columns[col].EntireColumn.NumberFormat = "m/d/yyyy"; } string tms = DateTime.Now.ToString("yyyyMMddHHmmss"); KlipSh.ExportToCSV("C:\\KlipFiles", string.Format("KlipIn_MDP{0}.csv", tms)); wbKlip.Close(false); } }