/// <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);
}
}
