public void Close()
{
Workbook.RefreshAll();
ExcelApp.Calculate();
Workbook.Save();
Workbook.Close();
Marshal.ReleaseComObject(Worksheet);
Marshal.ReleaseComObject(Workbook);
ExcelApp.Quit();
}
public void Konwertuj(string rodzWarstw, string nrJezd, string pasRuch, string rodzPasa, DaneProbki daneProbki, string nzPlikForm)
{
int start = System.Environment.TickCount;
app.Calculation = Excel.XlCalculation.xlCalculationManual;
PrzeniesWynikiDoSprawozdania(rodzWarstw, nrJezd, pasRuch, rodzPasa);
PrzeniesDaneProbkiDoSprawozdania(daneProbki);
app.Calculate();
ZapiszPlikWynikowy(nzPlikForm);
app.Calculation = Excel.XlCalculation.xlCalculationAutomatic;
int end = System.Environment.TickCount;
int czas = end - start;
string tekst = "\nKonwertowanie ukończone\nczas trwania operacji: " + czas + " ms";
przypiszTekstDel = new przypiszTekstDelegate(przypiszTekstDoInfo);
TextBoxInfo.Dispatcher.Invoke(przypiszTekstDel, tekst);
}
/// <summary>
/// Returns a cell value as object, using row and column syntax
/// </summary>
/// <remarks>Throws an exception if operation fails</remarks>
/// <param name="sheet">Sheetname, string</param>
/// <param name="row">Row, integer</param>
/// <param name="col">Column, integer</param>
/// <returns>Cellvalue as object</returns>
public object GetCellValue(string sheet, int row, int col)
{
try
{
if (_worksheet == null || _worksheet.Name != sheet)
{
_worksheet = _workbook.Worksheets[sheet];
}
_excelApp.Calculate();
return(_worksheet.Cells[row, col].Value);
}
catch (Exception ex)
{
throw new Exception(string.Format("Could not read worksheet:{0} row:{1} col:{2}", sheet, row, col), ex);
}
}
public static string Calc(string str)
{
string value = "";
Excel.Application xlApp = null;
Excel.Workbook wb = null;
Excel.Worksheet ws = null;
try
{
xlApp = new Excel.Application();
xlApp.UseSystemSeparators = true;
/*
* string culture = System.Threading.Thread.CurrentThread.CurrentCulture.ToString();//"en-GB";
* CultureInfo ci = new CultureInfo(culture);
* Thread.CurrentThread.CurrentCulture = CultureInfo.CreateSpecificCulture("nb-NO");
*
* xlApp.UseSystemSeparators = false;
* xlApp.DecimalSeparator = ci.NumberFormat.NumberDecimalSeparator;
* xlApp.ThousandsSeparator = ci.NumberFormat.NumberGroupSeparator;
*
* System.Globalization.CultureInfo.CurrentCulture.TextInfo.ListSeparator = ";";
*/
xlApp.Visible = false;
xlApp.DisplayAlerts = false;
wb = xlApp.Workbooks.Add(Excel.XlWBATemplate.xlWBATWorksheet);
ws = (Excel.Worksheet)wb.Worksheets[1];
//ws.get_Range("A1", "A1").Value2 = str;
ws.get_Range("A1", "A1").FormulaLocal = str; // enter formula using the local culture
xlApp.Calculate();
value = ws.get_Range("A1", "A1").Value2.ToString();
int eCode = 0;
if (int.TryParse(value, out eCode))
{
if (eCode == -2146826281)
{
value = "#DIV/0!";
}
else if (eCode == -2146826246)
{
value = "#N/A";
}
else if (eCode == -2146826259)
{
value = "#NAME?";
}
else if (eCode == -2146826288)
{
value = "#NULL!";
}
else if (eCode == -2146826252)
{
value = "#NUM!";
}
else if (eCode == -2146826265)
{
value = "#REF!";
}
else if (eCode == -2146826273)
{
value = "#VALUE!";
}
}
wb.Close(false);
xlApp.Quit();
}
catch (Exception ex)
{
value = "Error: " + ex.Message;
}
finally
{
try
{
wb.Close(false);
xlApp.Quit();
releaseObject(ws);
releaseObject(wb);
releaseObject(xlApp);
}
catch
{
releaseObject(ws);
releaseObject(wb);
releaseObject(xlApp);
}
}
return(value);
}
static void Main(string[] args)
{
// set english culture (for english function names and . decimal)
System.Threading.Thread.CurrentThread.CurrentCulture = System.Globalization.CultureInfo.CreateSpecificCulture("en-GB");
// list seperator is now , Trying to change it to ; doesnt work :(
// System.Globalization.CultureInfo.CurrentCulture.TextInfo.ListSeparator = ";";
// show manual
bool showMan = false;
// check pipe
String pipedText = "";
bool isKeyAvailable;
bool piped = false;
try { isKeyAvailable = System.Console.KeyAvailable; }
catch { pipedText = System.Console.In.ReadToEnd(); piped = true; }
// if no args or pipe, show manual
if (piped == false && args.Length == 0)
{
showMan = true;
}
// set default values
string infile = "";
string paste = "";
List <string> macro = new List <string>();
int[] cellA = new int[2] {
0, 1
};
int[] cellB = new int[2] {
0, 1
};
int[] outCellA = new int[2] {
0, 1
};
int[] outCellB = new int[2] {
0, 0
};
string sheet = "";
string active = "";
// warnings are off by default, since excel will warn about ANYTHING, which interupts the program and leads to errors.
// f.eks. log charts will make the program fail, when they are given blank data in the step before new data is provided.
bool warning = false;
// save on exit
bool save = true;
// convert error codes to text in output
bool outErr = true;
// paste as text
bool asText = false;
// blehh..
string errLine = "--------------------------------------------------------------------------------";
// tab as default output space character
string cellSpacer = "\t";
// all charts that should be saved
List <string> charts = new List <string>();
// check input arguments
int iarg = 0;
for (int i = 0; i < args.Length; i++)
{
if (args[i].StartsWith("-"))
{
// show manual
if (args[i] == "-help" || args[i] == "--help" || args[i] == "-?")
{
showMan = true;
}
// specify macro to run
if (args[i] == "-m")
{
try {
macro.Add(args[i + 1]);
i++;
}
catch {
Error("No macro name given for -m.", 1);
}
}
// paste input as text?
if (args[i] == "-t")
{
asText = true;
}
// dont save
if (args[i] == "-n")
{
save = false;
}
// hide warnings
if (args[i] == "-w")
{
warning = true;
}
// set space character
if (args[i] == "-b")
{
cellSpacer = " ";
if (args.Length > i + 1)
{
if (args[i + 1].Length == 1)
{
cellSpacer = args[i + 1];
i++;
}
}
}
// set paste sheet
if (args[i] == "-p")
{
try
{
active = args[i + 1];
i++;
}
catch
{
Error("No paste name given for -p.", 1);
}
}
// set output sheet
if (args[i] == "-s")
{
try
{
sheet = args[i + 1];
i++;
}
catch
{
Error("No sheet name given for -s.", 1);
}
}
// blank errors
if (args[i] == "-#")
{
outErr = false;
}
}
else
{
// excel file
if (iarg == 0)
{
infile = args[i];
}
// paste file
else if (iarg == 1 && !piped)
{
paste = args[i];
if (paste == "~" || paste == "")
{
paste = "";
iarg++; iarg++;
}
}
// input cell ref
else if (iarg == 2)
{
string[] cellArr = args[i].Split(':');
if (cellArr.Length == 1)
{
cellA = ExcelCellRef(cellArr[0]);
}
else
{
cellA = ExcelCellRef(cellArr[0]);
cellB = ExcelCellRef(cellArr[1]);
iarg++;
}
}
else if (iarg == 3)
{
cellB = ExcelCellRef(args[i]);
}
// output cell ref
else if (iarg == 4)
{
string[] cellArr = args[i].Split(':');
if (cellArr.Length == 1)
{
outCellA = ExcelCellRef(cellArr[0]);
}
else
{
outCellA = ExcelCellRef(cellArr[0]);
outCellB = ExcelCellRef(cellArr[1]);
iarg++;
}
}
else if (iarg == 5)
{
outCellB = ExcelCellRef(args[i]);
}
// output charts
else if (iarg > 5)
{
charts.Add(args[i]);
}
iarg++;
}
}
// Print header
if (showMan)
{
Print(@"Usage: excel [OPTIONS] ExcelFile PasteFile Cell1 Cell2 OutCell1 OutCell2 [Chart1 [Chart2 ..]]
or: excel =FORMULA
Opens ExcelFile and places the contents of PasteFile from position given.
Position is the range between Cell1 and Cell2. Unused cells are cleared.
Echos all filled rows in the range between OutCell1 and OutCell2.
Any charts named will be saved to <ExcelFile_ChartN>.png.
'Sheet.ChartN' can be used if multiple charts has the same name.
-p PasteSheet Select the sheet that should be pasted to.
-s OutSheet Select the sheet that should be outputted.
-m Macro Run macro after paste. If -m is used multiples
times, more than 1 macro can be executed.
-n Do not save workbook
-w display Excel dialogs. Default is to surpress.
-# Replace errors with blanks in output
-b [CHAR] Set cell-spacing character in output to
'space' or 'CHAR' (default is 'tab')
-t Insert PasteFile as text instead of numbers
If '~' is specified as PasteFile no file is loaded and Cell1 and Cell2
should not be specified.
Version 1.0. Report bugs to <*****@*****.**>");
Environment.Exit(0);
}
if (infile.StartsWith("="))
{
string result = "";
try
{
result = ExcelMath.Calc(infile);
}
catch (Exception ex) {
Console.Error.WriteLine("Error: " + ex.Message);
#if DEBUG
Console.ReadKey();
#endif
Environment.Exit(1);
}
Print(result);
#if DEBUG
Console.ReadKey();
#endif
Environment.Exit(0);
}
// open file
if (piped == false && infile == "")
{
Error("No file given.", 1);
}
System.IO.TextReader stream = new StringReader(pipedText);
if (!piped && paste.Length > 0)
{
try { stream = new StreamReader(paste); }
catch (Exception e) { Error("Unable to open file: " + paste + "\n\n" + errLine + "\n\n" + e.ToString(), 1); }
}
// number of lines and columns
int N = 0;
int C = 0;
// input data, as numbers and text. only one will be used
double[,] cells = null;
string[,] sCells = null;
// if pasted text
if (paste.Length > 0)
{
String line;
List <string[]> strings = new List <string[]>();
// trim all lines and split between words
while ((line = stream.ReadLine()) != null)
{
line = line.Replace(",", " ");
line = line.Replace("\t", " ");
line = line.Trim();
line = System.Text.RegularExpressions.Regex.Replace(line, @"\s+", " ");
if (asText)
{
//if (line.Length > 0)
strings.Add(line.Split(' '));
}
else if (line.Length > 0 && !line.StartsWith("#"))
{
strings.Add(line.Split(' '));
}
}
// set number of rows and columns
N = strings.Count();
C = 0;
foreach (string[] str in strings)
{
if (str.Length > C)
{
C = str.Length;
}
}
// convert input data to a format the Excel-interop understands: var[,]
if (asText)
{
sCells = new string[N, C];
}
else
{
cells = new double[N, C];
}
// parse all cells and add to array
for (int i = 0; i < N; i++)
{
for (int j = 0; j < strings[i].Length; j++)
{
try
{
if (asText)
{
sCells[i, j] = strings[i][j];
}
else
{
cells[i, j] = double.Parse(strings[i][j]);
}
}
catch (Exception e) { Error("Unable to parse number in paste file, line " + (i + 1) + ", column " + (j + 1) + ":\n" + strings[i][j] + "\n\n" + errLine + "\n\n" + e.ToString(), 1); }
}
}
}
Excel.Range startCell;
Excel.Range endCell;
try
{
// open excel app
oXL = new Excel.Application();
if (!warning)
{
oXL.DisplayAlerts = false;
}
try
{
// try to open the selected excel file
// we turn of errors, since excel prompts for macro-enabled files and other things
oXL.DisplayAlerts = false;
// we need the absolute file path, since excel defaults to the user home dir, not the current working dir :S
oWB = oXL.Workbooks.Open(Path.GetFullPath(infile));
// turn back on warnings if wanted
if (warning)
{
oXL.DisplayAlerts = true;
}
}
catch (Exception e) { throw new System.Exception("Unable to open file: " + Path.GetFullPath(infile) + "\n\n" + errLine + "\n\n" + e.ToString()); }
// set the active sheet
if (active.Length > 0)
{
try
{
oSheet = (Excel._Worksheet)oWB.Sheets[active];
}
catch (Exception e) { throw new System.Exception("Unable to select worksheet: " + active + "\n\n" + errLine + "\n\n" + e.ToString()); }
}
else
{
oSheet = (Excel._Worksheet)oWB.Worksheets[1];
}
// insert data
if (paste.Length > 0)
{
// if only columns are specified, find the amount of rows used
if (cellA[0] == 0 && cellB[0] == 0)
{
string cell = GetExcelColumnName(cellA[1]) + ":" + GetExcelColumnName(cellB[1]);
Excel.Range r = (Excel.Range)oSheet.UsedRange.Columns[cell, Type.Missing];
cellA[0] = 1;
cellB[0] = r.Rows.Count;
}
// select and paste values
try
{
startCell = (Excel.Range)oSheet.Cells[cellA[0], cellA[1]];
endCell = (Excel.Range)oSheet.Cells[cellB[0], cellB[1]];
oSheet.get_Range(startCell, endCell).Value = null;
endCell = (Excel.Range)oSheet.Cells[cellA[0] + N - 1, cellA[1] + C - 1];
if (asText)
{
oSheet.get_Range(startCell, endCell).Value2 = sCells;
}
else
{
oSheet.get_Range(startCell, endCell).Value2 = cells;
}
}
catch (Exception e)
{
string inputCell = GetExcelColumnName(cellA[1]) + cellA[0] + ":" + GetExcelColumnName(cellB[1]) + cellB[0];
throw new System.Exception("Unable to select input cells:\n\n " + inputCell + "\n\n" + errLine + "\n\n" + e.ToString());
}
}
// run macro
for (int i = 0; i < macro.Count; i++)
{
try
{
oXL.Run(macro[i]);
}
catch (Exception e) { throw new System.Exception("Unable to run macro: " + macro[i] + "\n\n" + errLine + "\n\n" + e.ToString()); }
}
// force workbook refresh
oXL.Calculate();
// go to result sheet
if (sheet.Length > 0)
{
try
{
oSheet = (Excel._Worksheet)oWB.Sheets[sheet];
}
catch (Exception e) { throw new System.Exception("Unable to select output sheet:" + sheet + "\n\n" + errLine + "\n\n" + e.ToString()); }
}
// save charts
foreach (Excel.Worksheet cSheet in oWB.Worksheets)
{
// loop trough all charts
Excel.ChartObjects xlCharts = (Excel.ChartObjects)cSheet.ChartObjects(Type.Missing);
for (int i = 1; i <= xlCharts.Count; i++)
{
Excel.ChartObject oChart = (Excel.ChartObject)xlCharts.Item(i);
Excel.Chart chart = oChart.Chart;
string chartName = "";
if (charts.Contains(cSheet.Name + "." + oChart.Name))
{
chartName = cSheet.Name + "." + oChart.Name;
}
else if (charts.Contains(oChart.Name))
{
chartName = oChart.Name;
}
// if chart is specified for output, save it
if (chartName.Length > 0)
{
int id = charts.FindIndex(s => s == chartName);
charts.RemoveAt(id);
try
{
// we need full path name again.. excel defaults to user home dir...
string saveas = Path.GetFullPath(infile);
saveas = Path.GetDirectoryName(saveas) + "\\" + Path.GetFileNameWithoutExtension(saveas);
saveas = saveas + "_" + chartName + ".png";
chart.Export(saveas, "PNG");
}
catch (Exception e) { throw new System.Exception("Unable to save chart '" + chartName + "':\n\n" + errLine + "\n\n" + e.ToString()); }
}
}
}
// if any charts was not found; throw an error.
if (charts.Count > 0)
{
string list = "";
foreach (string s in charts)
{
list += s + ", ";
}
throw new Exception("Unable to find chart(s): " + list);
}
// if only columns are specified, find amount of rows to use
if (outCellA[0] == 0 && outCellB[0] == 0)
{
if (outCellB[1] == 0)
{
outCellB[1] = oSheet.UsedRange.Columns.Count;
}
string cell = GetExcelColumnName(outCellA[1]) + ":" + GetExcelColumnName(outCellB[1]);
Excel.Range r = (Excel.Range)oSheet.UsedRange.Columns[cell, Type.Missing];
outCellA[0] = 1;
outCellB[0] = r.Rows.Count;
}
// select the output cell range
try
{
startCell = (Excel.Range)oSheet.Cells[outCellA[0], outCellA[1]];
endCell = (Excel.Range)oSheet.Cells[outCellB[0], outCellB[1]];
}
catch (Exception e)
{
string outcell = GetExcelColumnName(outCellA[1]) + outCellA[0] + ":" + GetExcelColumnName(outCellB[1]) + outCellB[0];
throw new System.Exception("Unable to select output cells:\n " + outcell + "\n\n" + errLine + "\n\n" + e.ToString());
}
// get output from selected cells
object[,] arr = null;
try
{
Excel.Range r = (Excel.Range)oSheet.get_Range(startCell, endCell);
// if only 1 cell is selected, excel will return an object instead of object array!
if (r.Cells.Count == 1)
{
arr = new object[2, 2];
arr[1, 1] = r.Cells.Value2;
}
else
{
arr = r.Cells.Value2 as object[, ];
}
}
catch (Exception e)
{
string outcell = GetExcelColumnName(outCellA[1]) + outCellA[0] + ":" + GetExcelColumnName(outCellB[1]) + outCellB[0];
throw new System.Exception("Invalid OutCells given. Unable to retrieve data:\n " + outcell + "\n\n" + errLine + "\n\n" + e.ToString());
}
List <string> results = new List <string>();
int last = 0;
N = outCellB[0] - outCellA[0] + 1;
C = outCellB[1] - outCellA[1] + 1;
// loop trough output rows
for (int i = 1; i <= N; i++)
{
// loop trough output columns
string s = "";
for (int j = 1; j <= C; j++)
{
// check if cell contains an error
if (arr[i, j] is Int32)
{
if (outErr)
{
int eCode = (int)arr[i, j];
string e = "";
if (eCode == -2146826281)
{
e = "#DIV/0!";
}
else if (eCode == -2146826246)
{
e = "#N/A";
}
else if (eCode == -2146826259)
{
e = "#NAME?";
}
else if (eCode == -2146826288)
{
e = "#NULL!";
}
else if (eCode == -2146826252)
{
e = "#NUM!";
}
else if (eCode == -2146826265)
{
e = "#REF!";
}
else if (eCode == -2146826273)
{
e = "#VALUE!";
}
// no more error codes exists (?) as of 2013.. But to be sure / support future ones:
else
{
e = "#ERR" + eCode.ToString();
}
s = s + e + " " + cellSpacer;
}
else
{
s = s + " " + cellSpacer;
}
}
else if (arr[i, j] != null)
{
s = s + arr[i, j].ToString() + cellSpacer;
}
else
{
s = s + " " + cellSpacer;
}
}
// remove cellspacer from last column
if (C > 0)
{
results.Add(s.Remove(s.Length - 1).TrimEnd());
}
// record last row column with content
if (s.Replace(cellSpacer, " ").TrimEnd().Length > 0)
{
last = results.Count();
}
}
// write output to console
for (int i = 0; i < last; i++)
{
Console.WriteLine(results[i]);
}
// save file
if (save)
{
// if macros are enabled, excel would prompt about saving
oXL.DisplayAlerts = false;
oWB.Save();
}
}
// catch any exception
catch (Exception theException)
{
Error(errLine + "\n Error: " + theException.Message, 1);
}
finally
{
// clean up and exit
CleanUp();
}
#if DEBUG
Console.ReadKey();
#endif
}
void readAPD(string filename = "C:\\Users\\i028512\\Documents\\Visual Studio 2017\\Projects\\SimpleAPDImporter\\r83410048802.txt", string from = null, string to = null)
{
//^\s\d+(\.\w+)?\s{2,}((\S+\s)*)\s{2,}(((\-?\d*\.\d+)\s*)|(\*{2,}\s*))+$
StreamReader reader = new FileInfo(filename).OpenText();
string line;
Excel.Application application = new Excel.Application();
try {
application.ScreenUpdating = false;
Excel.Workbook newWorkbook = application.Workbooks.Add();
Excel.Worksheet sheet = null;
application.Calculation = Excel.XlCalculation.xlCalculationManual;
int i = 3;
string chapterName = null;
string escapedChapterName = null;
string[] titleString = null;
int fromIndex = 0, toIndex = 0;
while ((line = reader.ReadLine()) != null)
{
if (title.IsMatch(line))
{
titleString = line.Split(new char[] { ' ', '\t' }, StringSplitOptions.RemoveEmptyEntries);
toIndex = titleString.Length - 1;
if (from != null)
{
fromIndex = Array.IndexOf(titleString, from);
}
if (to != null)
{
toIndex = Array.IndexOf(titleString, to);
}
}
else
if (newChapter.IsMatch(line))
{
StartNewSheet(line, newWorkbook, ref sheet, ref i, ref chapterName, ref escapedChapterName, titleString, fromIndex, toIndex);
}
else
{
if ((chapterName != null) && (line.StartsWith(chapterName)))
{
sheet.Cells[2][i].Value2 = "Total Calculated";
Excel.Range range = sheet.Range[sheet.Cells[3][i], sheet.Cells[3 + titleString.Length - 1][i]];
range.NumberFormat = "0.00";
sheet.Cells[3][i].FormulaR1C1 = "=SUM(R3C:R[-1]C)";
range.FillRight();
i++;
sheet.Cells[2][i].Value2 = "Total";
SetValues(sheet, i, line.Substring(chapterName.Length), fromIndex, toIndex);
}
else
{
Match match = parts.Match(line);
if (match.Success)
{
HandleLine(line, sheet, ref i, fromIndex, toIndex);
}
}
}
}
SetCellSize(sheet);
} finally {
application.Visible = true;
application.ScreenUpdating = true;
application.Calculate();
application.Calculation = Excel.XlCalculation.xlCalculationAutomatic;
}
}
public bool ProcessCalibration(Guid calibrationId, long affiliateId)
{
var baseAffPath = Path.Combine(Util.AppSettings.CalibrationModelPath, affiliateId.ToString());
if (!Directory.Exists(baseAffPath))
{
Directory.CreateDirectory(baseAffPath);
}
var qry = Queries.CalibrationInput_EAD_CCF(calibrationId);
var _dt = DataAccess.i.GetData(qry);
//DataView dv = _dt.DefaultView;
//dv.Sort = "Account_No,Snapshot_Date";
var dt = _dt;// dv.ToTable();
var rowCount = dt.Rows.Count + 1;
if (dt.Rows.Count == 0)
{
return(true);
}
var counter = Util.AppSettings.GetCounter(dt.Rows.Count);
var path = $"{Path.Combine(Util.AppSettings.CalibrationModelPath, counter.ToString(), "EAD_CCF.xlsx")}";
var path1 = $"{Path.Combine(baseAffPath, $"{Guid.NewGuid().ToString()}EAD_CCF.xlsx")}";
if (File.Exists(path1))
{
try { File.Delete(path1); } catch { };
}
ExcelPackage.LicenseContext = LicenseContext.NonCommercial;
using (var package = new ExcelPackage(new FileInfo(path)))
{
ExcelWorksheet worksheet = package.Workbook.Worksheets[1];//.FirstOrDefault();
// get number of rows in the sheet
int rows = worksheet.Dimension.Rows; // 10
worksheet.DeleteRow(dt.Rows.Count + 1, rows - (dt.Rows.Count + 1));
// loop through the worksheet rows
package.Workbook.CalcMode = ExcelCalcMode.Automatic;
for (int i = 0; i < dt.Rows.Count; i++)// DataRow dr in dt.Rows)
{
Log4Net.Log.Info(i);
DataRow dr = dt.Rows[i];
var itm = DataAccess.i.ParseDataToObject(new EAD_CCF_Summary(), dr);
if (string.IsNullOrEmpty(itm.Account_No) && itm.Snapshot_Date.ToString().Contains("0001"))
{
continue;
}
worksheet.Cells[i + 2, 1].Value = itm.Customer_No ?? "";
worksheet.Cells[i + 2, 2].Value = itm.Account_No ?? "";
worksheet.Cells[i + 2, 3].Value = itm.Settlement_Account ?? "";
worksheet.Cells[i + 2, 4].Value = itm.Product_Type ?? "";
if (!itm.Snapshot_Date.ToString().Contains("0001"))
{
worksheet.Cells[i + 2, 5].Value = itm.Snapshot_Date;
}
if (itm.Contract_Start_Date != null)
{
worksheet.Cells[i + 2, 6].Value = itm.Contract_Start_Date;
}
if (itm.Contract_End_Date != null)
{
worksheet.Cells[i + 2, 7].Value = itm.Contract_End_Date;
}
worksheet.Cells[i + 2, 8].Value = itm.Limit;
worksheet.Cells[i + 2, 9].Value = itm.Outstanding_Balance;
worksheet.Cells[i + 2, 10].Value = itm.Classification ?? "";
}
//package.Workbook.Worksheets[1].Calculate();
//package.Workbook.Worksheets[0].Calculate();
//ExcelCalculationOption o = new ExcelCalculationOption();
//o.AllowCircularReferences = true;
//package.Workbook.Calculate(o);
var fi = new FileInfo(path1);
package.SaveAs(fi);
}
string txtLocation = Path.GetFullPath(path1);
object _missingValue = System.Reflection.Missing.Value;
Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.Application();
var theWorkbook = excel.Workbooks.Open(txtLocation,
_missingValue,
false,
_missingValue,
_missingValue,
_missingValue,
true,
_missingValue,
_missingValue,
true,
_missingValue,
_missingValue,
_missingValue);
try
{
//Sort
Worksheet calculationSheet = theWorkbook.Sheets[2];
Range sortRange = calculationSheet.Range["A2", "J" + rowCount.ToString()];
sortRange.Sort(sortRange.Columns[9], XlSortOrder.xlDescending, DataOption1: XlSortDataOption.xlSortTextAsNumbers); //Outstanding balance
sortRange.Sort(sortRange.Columns[5], XlSortOrder.xlAscending); //Snapshot date
sortRange.Sort(sortRange.Columns[3], XlSortOrder.xlDescending, DataOption1: XlSortDataOption.xlSortTextAsNumbers); // Settlement Account
//refresh and calculate to modify
theWorkbook.RefreshAll();
excel.Calculate();
Worksheet worksheet1 = theWorkbook.Sheets[1];
var r = new CalibrationResult_EAD_CCF_Summary();
try { r.OD_TotalLimitOdDefaultedLoan = double.Parse(worksheet1.Cells[2, 2].Value.ToString()); } catch { }
try{ r.OD_BalanceAtDefault = double.Parse(worksheet1.Cells[3, 2].Value.ToString()); } catch { }
try{ r.OD_Balance12MonthBeforeDefault = double.Parse(worksheet1.Cells[4, 2].Value.ToString()); } catch { }
try{ r.OD_TotalConversation = double.Parse(worksheet1.Cells[5, 2].Value.ToString()); } catch { }
try{ r.OD_CCF = double.Parse(worksheet1.Cells[6, 2].Value.ToString()); } catch { }
try{ r.Card_TotalLimitOdDefaultedLoan = double.Parse(worksheet1.Cells[2, 3].Value.ToString()); } catch { }
try{ r.Card_BalanceAtDefault = double.Parse(worksheet1.Cells[3, 3].Value.ToString()); } catch { }
try{ r.Card_Balance12MonthBeforeDefault = double.Parse(worksheet1.Cells[4, 3].Value.ToString()); } catch { }
try{ r.Card_TotalConversation = double.Parse(worksheet1.Cells[5, 3].Value.ToString()); } catch { }
try{ r.Card_CCF = double.Parse(worksheet1.Cells[6, 3].Value.ToString()); } catch { }
try{ r.Overall_TotalLimitOdDefaultedLoan = double.Parse(worksheet1.Cells[2, 4].Value.ToString()); } catch { }
try{ r.Overall_BalanceAtDefault = double.Parse(worksheet1.Cells[3, 4].Value.ToString()); } catch { }
try{ r.Overall_Balance12MonthBeforeDefault = double.Parse(worksheet1.Cells[4, 4].Value.ToString()); } catch { }
try{ r.Overall_TotalConversation = double.Parse(worksheet1.Cells[5, 4].Value.ToString()); } catch { }
try{ r.Overall_CCF = double.Parse(worksheet1.Cells[6, 4].Value.ToString()); } catch { }
if (ECLNonStringConstants.i.ExcelDefaultValue.Contains(r.OD_TotalLimitOdDefaultedLoan))
{
r.OD_TotalLimitOdDefaultedLoan = 0;// =0;
}
if (ECLNonStringConstants.i.ExcelDefaultValue.Contains(r.OD_BalanceAtDefault))
{
r.OD_BalanceAtDefault = 0;// =0;
}
if (ECLNonStringConstants.i.ExcelDefaultValue.Contains(r.OD_Balance12MonthBeforeDefault))
{
r.OD_Balance12MonthBeforeDefault = 0;// =0;
}
if (ECLNonStringConstants.i.ExcelDefaultValue.Contains(r.OD_TotalConversation))
{
r.OD_TotalConversation = 0;// =0;
}
if (ECLNonStringConstants.i.ExcelDefaultValue.Contains(r.OD_CCF))
{
r.OD_CCF = 0;// =0;
}
if (ECLNonStringConstants.i.ExcelDefaultValue.Contains(r.Card_TotalLimitOdDefaultedLoan))
{
r.Card_TotalLimitOdDefaultedLoan = 0;// =0;
}
if (ECLNonStringConstants.i.ExcelDefaultValue.Contains(r.Card_BalanceAtDefault))
{
r.Card_BalanceAtDefault = 0;// =0;
}
if (ECLNonStringConstants.i.ExcelDefaultValue.Contains(r.Card_Balance12MonthBeforeDefault))
{
r.Card_Balance12MonthBeforeDefault = 0;// =0;
}
if (ECLNonStringConstants.i.ExcelDefaultValue.Contains(r.Card_TotalConversation))
{
r.Card_TotalConversation = 0;// =0;
}
if (ECLNonStringConstants.i.ExcelDefaultValue.Contains(r.Card_CCF))
{
r.Card_CCF = 0;// =0;
}
if (ECLNonStringConstants.i.ExcelDefaultValue.Contains(r.Overall_TotalLimitOdDefaultedLoan))
{
r.Overall_TotalLimitOdDefaultedLoan = 0;// =0;
}
if (ECLNonStringConstants.i.ExcelDefaultValue.Contains(r.Overall_BalanceAtDefault))
{
r.Overall_BalanceAtDefault = 0;// =0;
}
if (ECLNonStringConstants.i.ExcelDefaultValue.Contains(r.Overall_Balance12MonthBeforeDefault))
{
r.Overall_Balance12MonthBeforeDefault = 0;// =0;
}
if (ECLNonStringConstants.i.ExcelDefaultValue.Contains(r.Overall_TotalConversation))
{
r.Overall_TotalConversation = 0;// =0;
}
if (ECLNonStringConstants.i.ExcelDefaultValue.Contains(r.Overall_CCF))
{
r.Overall_CCF = 0;// =0;
}
if (ECLNonStringConstants.i.ExcelDefaultValue.Contains(r.OD_CCF))
{
r.OD_CCF = 0;// =0;
}
if (ECLNonStringConstants.i.ExcelDefaultValue.Contains(r.Card_CCF))
{
r.Card_CCF = 0;// =0;
}
if (ECLNonStringConstants.i.ExcelDefaultValue.Contains(r.Overall_CCF))
{
r.Overall_CCF = 0;// =0;
}
theWorkbook.Save();
theWorkbook.Close(true);
excel.Quit();
qry = Queries.CalibrationResult_EAD_CCF_Summary_Update(calibrationId, r.OD_TotalLimitOdDefaultedLoan ?? 0, r.OD_BalanceAtDefault ?? 0, r.OD_Balance12MonthBeforeDefault ?? 0,
r.OD_TotalConversation ?? 0, r.OD_CCF ?? 0, r.Card_TotalLimitOdDefaultedLoan ?? 0, r.Card_BalanceAtDefault ?? 0, r.Card_Balance12MonthBeforeDefault ?? 0,
r.Card_TotalConversation ?? 0, r.Card_CCF ?? 0, r.Overall_TotalLimitOdDefaultedLoan ?? 0, r.Overall_BalanceAtDefault ?? 0, r.Overall_Balance12MonthBeforeDefault ?? 0, r.Overall_TotalConversation ?? 0,
r.Overall_CCF ?? 0);
DataAccess.i.ExecuteQuery(qry);
}
catch (Exception ex)
{
Log4Net.Log.Error(ex);
theWorkbook.Save();
theWorkbook.Close(true);
excel.Quit();
}
//File.Delete(path1);
return(true);
}
private void btnExportExel_Click(object sender, EventArgs e)
{
if (XtraMessageBox.Show("Bán có chắc muốn in hóa đơn?", "THÔNG BÁO", MessageBoxButtons.YesNo, MessageBoxIcon.Information) == DialogResult.Yes)
{
if ((!string.IsNullOrEmpty(txtForm.Text)) || (!string.IsNullOrEmpty(txtTo.Text)) ||
(String.CompareOrdinal(txtForm.Text, txtTo.Text)) > 0)
{
XtraMessageBox.Show("Nhập sai", "Error Message!", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
else
{
//var app = (Microsoft.Office.Interop.Excel.Application) Marshal.GetActiveObject("Excel.Application");
var app = new Microsoft.Office.Interop.Excel.Application {Visible = false};
string path = Path.Combine(Environment.CurrentDirectory, @"Yokowo.xls");
object missingValue = System.Reflection.Missing.Value;
Workbook book = app.Workbooks.Open(path, missingValue,
false,
missingValue,
missingValue,
missingValue,
true,
missingValue,
missingValue,
true,
missingValue,
missingValue,
missingValue);
var sheet = (Worksheet)book.Worksheets[5];
// day
Range date = sheet.Range["E8", "E8"];
date.Value2 = DateTime.Now.Date.Day;
// month
Range month = sheet.Range["G8", "G8"];
month.Value2 = DateTime.Now.Date.Month;
// day
Range year = sheet.Range["H8", "H8"];
year.Value2 = DateTime.Now.Date.Year;
Range cusName = sheet.Range["F12", "F12"];
cusName.Value2 = txtCusName.Text;
// cust name
Range custName = sheet.Range["D13", "D13"];
custName.Value2 = txtCustName.Text;
// address
Range address = sheet.Range["D14", "D14"];
address.Value2 = txtAddress.Text;
// tax code
Range taxCode = sheet.Range["D15", "D15"];
taxCode.Value2 = txtTaxCode.Text;
// Del Term
Range delTerm = sheet.Range["E16", "E16"];
delTerm.Value2 = txtDelTerm.Text;
// Del Place
Range delPlace = sheet.Range["D17", "D17"];
delPlace.Value2 = txtDelPlace.Text;
// name buyer
Range buyerName = sheet.Range["B18", "B18"];
buyerName.Value2 = txtBuyerName.Text;
// Tel
Range tel = sheet.Range["F18", "F18"];
tel.Value2 = txtTel.Text;
// fax
Range fax = sheet.Range["H18", "H18"];
fax.Value2 = txtFax.Text;
// pay type
Range payType = sheet.Range["G49", "G49"];
payType.Value2 = txtPayType.Text;
// pay term
Range payTerm = sheet.Range["G51", "G51"];
payTerm.Value2 = txtPayTerm.Text;
// currency
Range currency = sheet.Range["I52", "I52"];
currency.Value2 = _customer.CURRENCY;
//book.RefreshAll();
app.Calculate();
book.Save();
book.Close(true, null, null);
app.Quit();
System.Diagnostics.Process.Start(path);
}
}
else
{
DialogResult = DialogResult.No;
}
}
public void Start()
{
excelapp.Visible = false;
excelapp.DisplayAlerts = false;
while (true)
{
if (reports.Count > 0)
{
Report report = reports.Pop();
Console.WriteLine(DateTime.Now + ":项目" + report.item.name + "的报表开始生成");
Client.sw.WriteLine(DateTime.Now + ":项目" + report.item.name + "的报表开始生成");
Excel.Workbook excelreport = excelapp.Workbooks.Open(report.module.file);
int k = report.ranges_count;
for (int i = 0; i < k; i++)
{
Excel.Workbook csv;
Boolean csvfinished = false;
while (!File.Exists(report.csvfullnames[i]))
{
Thread.Sleep(1);
}
while (!csvfinished)
{
int times = 1;
try
{
csv = excelapp.Workbooks.Open(report.csvfullnames[i]);
csv.Sheets[1].UsedRange.Copy(excelreport.Sheets[report.sheets[i]].Cells[report.startrows[i], report.startcols[i]]);
csv.Close(false);
csvfinished = true;
Console.WriteLine(DateTime.Now + ":项目 " + report.item.name + " 的报表:" + report.file + " 的CSV" + report.csvfullnames[i] + "第" + times + "次打开成功");
Client.sw.WriteLine(DateTime.Now + ":项目 " + report.item.name + " 的报表:" + report.file + " 的CSV" + report.csvfullnames[i] + "第" + times + "次打开成功");
}
catch (Exception e)
{
Console.WriteLine(DateTime.Now + ":项目 " + report.item.name + " 的报表:" + report.file + " 的CSV" + report.csvfullnames[i] + "第" + times + "次打开失败;稍后进行" + (times + 1) + "次打开");
Console.WriteLine("失败原因：" + e.ToString());
Client.sw.WriteLine(DateTime.Now + ":项目 " + report.item.name + " 的报表:" + report.file + " 的CSV" + report.csvfullnames[i] + "第" + times + "次打开失败;稍后进行" + (times + 1) + "次打开");
Client.sw.WriteLine("失败原因：" + e.ToString());
times++;
}
}
}
report.file = myoutpath + @"\" + report.item.name + DateTime.Now.ToString("yyyyMMddhhmmss") + ".xlsm";
int kk = 1;
while (File.Exists(report.file))
{
report.file = report.file.Split('.')[0] + "(" + kk + ").xlsm";
}
excelapp.Calculate();
excelreport.SaveAs(report.file);
excelreport.Close(true);
string emailtxt = "Dear " + report.item.branding + ":\r\n你的报表：" + report.item.name + "在附件请查阅";
Email email = new Email(report, emailtxt);
EmailClient.emails.Push(email);
Client.items_finished.Push(report.item);
Console.WriteLine(DateTime.Now + ":项目" + report.item.name + "的报表:" + report.file + "已经生成等待发送邮件");
Client.sw.WriteLine(DateTime.Now + ":项目" + report.item.name + "的报表:" + report.file + "已经生成等待发送邮件");
}
Thread.Sleep(1);
}
}
public static string Calc(string str)
{
string value = "";
Excel.Application xlApp = null;
Excel.Workbook wb = null;
Excel.Worksheet ws = null;
try
{
xlApp = new Excel.Application();
xlApp.UseSystemSeparators = true;
/*
string culture = System.Threading.Thread.CurrentThread.CurrentCulture.ToString();//"en-GB";
CultureInfo ci = new CultureInfo(culture);
Thread.CurrentThread.CurrentCulture = CultureInfo.CreateSpecificCulture("nb-NO");
xlApp.UseSystemSeparators = false;
xlApp.DecimalSeparator = ci.NumberFormat.NumberDecimalSeparator;
xlApp.ThousandsSeparator = ci.NumberFormat.NumberGroupSeparator;
System.Globalization.CultureInfo.CurrentCulture.TextInfo.ListSeparator = ";";
*/
xlApp.Visible = false;
xlApp.DisplayAlerts = false;
wb = xlApp.Workbooks.Add(Excel.XlWBATemplate.xlWBATWorksheet);
ws = (Excel.Worksheet)wb.Worksheets[1];
//ws.get_Range("A1", "A1").Value2 = str;
ws.get_Range("A1", "A1").FormulaLocal = str; // enter formula using the local culture
xlApp.Calculate();
value = ws.get_Range("A1", "A1").Value2.ToString();
int eCode = 0;
if (int.TryParse(value, out eCode))
{
if (eCode == -2146826281) value = "#DIV/0!";
else if (eCode == -2146826246) value = "#N/A";
else if (eCode == -2146826259) value = "#NAME?";
else if (eCode == -2146826288) value = "#NULL!";
else if (eCode == -2146826252) value = "#NUM!";
else if (eCode == -2146826265) value = "#REF!";
else if (eCode == -2146826273) value = "#VALUE!";
}
wb.Close(false);
xlApp.Quit();
}
catch (Exception ex)
{
value = "Error: " + ex.Message;
}
finally
{
try
{
wb.Close(false);
xlApp.Quit();
releaseObject(ws);
releaseObject(wb);
releaseObject(xlApp);
}
catch
{
releaseObject(ws);
releaseObject(wb);
releaseObject(xlApp);
}
}
return value;
}
static void Main(string[] args)
{
// set english culture (for english function names and . decimal)
System.Threading.Thread.CurrentThread.CurrentCulture = System.Globalization.CultureInfo.CreateSpecificCulture("en-GB");
// list seperator is now , Trying to change it to ; doesnt work :(
// System.Globalization.CultureInfo.CurrentCulture.TextInfo.ListSeparator = ";";
// show manual
bool showMan = false;
// check pipe
String pipedText = "";
bool isKeyAvailable;
bool piped = false;
try { isKeyAvailable = System.Console.KeyAvailable; }
catch { pipedText = System.Console.In.ReadToEnd(); piped = true; }
// if no args or pipe, show manual
if (piped == false && args.Length == 0) showMan = true;
// set default values
string infile = "";
string paste = "";
List<string> macro = new List<string>();
int[] cellA = new int[2] { 0, 1 };
int[] cellB = new int[2] { 0, 1 };
int[] outCellA = new int[2] { 0, 1 };
int[] outCellB = new int[2] { 0, 0 };
string sheet = "";
string active = "";
// warnings are off by default, since excel will warn about ANYTHING, which interupts the program and leads to errors.
// f.eks. log charts will make the program fail, when they are given blank data in the step before new data is provided.
bool warning = false;
// save on exit
bool save = true;
// convert error codes to text in output
bool outErr = true;
// paste as text
bool asText = false;
// blehh..
string errLine = "--------------------------------------------------------------------------------";
// tab as default output space character
string cellSpacer = "\t";
// all charts that should be saved
List<string> charts = new List<string>();
// check input arguments
int iarg = 0;
for (int i = 0; i < args.Length;i++ )
{
if (args[i].StartsWith("-")) {
// show manual
if (args[i] == "-help" || args[i] == "--help" || args[i] == "-?") showMan = true;
// specify macro to run
if (args[i] == "-m") {
try {
macro.Add(args[i + 1]);
i++;
}
catch {
Error("No macro name given for -m.",1);
}
}
// paste input as text?
if (args[i] == "-t") asText = true;
// dont save
if (args[i] == "-n") save = false;
// hide warnings
if (args[i] == "-w") warning = true;
// set space character
if (args[i] == "-b")
{
cellSpacer = " ";
if (args.Length > i+1)
if (args[i + 1].Length == 1)
{
cellSpacer = args[i + 1];
i++;
}
}
// set paste sheet
if (args[i] == "-p")
{
try
{
active = args[i + 1];
i++;
}
catch
{
Error("No paste name given for -p.", 1);
}
}
// set output sheet
if (args[i] == "-s")
{
try
{
sheet = args[i + 1];
i++;
}
catch
{
Error("No sheet name given for -s.", 1);
}
}
// blank errors
if (args[i] == "-#") outErr = false;
}
else {
// excel file
if (iarg == 0) infile = args[i];
// paste file
else if (iarg == 1 && !piped)
{
paste = args[i];
if (paste == "~" || paste == "")
{
paste = "";
iarg++; iarg++;
}
}
// input cell ref
else if (iarg == 2)
{
string[] cellArr = args[i].Split(':');
if (cellArr.Length == 1) {
cellA = ExcelCellRef(cellArr[0]);
}
else {
cellA = ExcelCellRef(cellArr[0]);
cellB = ExcelCellRef(cellArr[1]);
iarg++;
}
}
else if (iarg == 3)
{
cellB = ExcelCellRef(args[i]);
}
// output cell ref
else if (iarg == 4)
{
string[] cellArr = args[i].Split(':');
if (cellArr.Length == 1)
{
outCellA = ExcelCellRef(cellArr[0]);
}
else
{
outCellA = ExcelCellRef(cellArr[0]);
outCellB = ExcelCellRef(cellArr[1]);
iarg++;
}
}
else if (iarg == 5)
{
outCellB = ExcelCellRef(args[i]);
}
// output charts
else if (iarg > 5)
{
charts.Add(args[i]);
}
iarg++;
}
}
// Print header
if (showMan)
{
Print(@"Usage: excel [OPTIONS] ExcelFile PasteFile Cell1 Cell2 OutCell1 OutCell2 [Chart1 [Chart2 ..]]
or: excel =FORMULA
Opens ExcelFile and places the contents of PasteFile from position given.
Position is the range between Cell1 and Cell2. Unused cells are cleared.
Echos all filled rows in the range between OutCell1 and OutCell2.
Any charts named will be saved to <ExcelFile_ChartN>.png.
'Sheet.ChartN' can be used if multiple charts has the same name.
-p PasteSheet Select the sheet that should be pasted to.
-s OutSheet Select the sheet that should be outputted.
-m Macro Run macro after paste. If -m is used multiples
times, more than 1 macro can be executed.
-n Do not save workbook
-w display Excel dialogs. Default is to surpress.
-# Replace errors with blanks in output
-b [CHAR] Set cell-spacing character in output to
'space' or 'CHAR' (default is 'tab')
-t Insert PasteFile as text instead of numbers
If '~' is specified as PasteFile no file is loaded and Cell1 and Cell2
should not be specified.
Version 1.0. Report bugs to <*****@*****.**>");
Environment.Exit(0);
}
if (infile.StartsWith("="))
{
string result = "";
try
{
result = ExcelMath.Calc(infile);
}
catch (Exception ex) {
Console.Error.WriteLine("Error: " + ex.Message);
#if DEBUG
Console.ReadKey();
#endif
Environment.Exit(1);
}
Print(result);
#if DEBUG
Console.ReadKey();
#endif
Environment.Exit(0);
}
// open file
if (piped == false && infile == "") Error("No file given.", 1);
System.IO.TextReader stream = new StringReader(pipedText);
if (!piped && paste.Length > 0)
{
try { stream = new StreamReader(paste); }
catch (Exception e) { Error("Unable to open file: " + paste + "\n\n"+errLine+"\n\n"+e.ToString(), 1); }
}
// number of lines and columns
int N = 0;
int C = 0;
// input data, as numbers and text. only one will be used
double[,] cells = null;
string[,] sCells = null;
// if pasted text
if (paste.Length > 0)
{
String line;
List<string[]> strings = new List<string[]>();
// trim all lines and split between words
while ((line = stream.ReadLine()) != null)
{
line = line.Replace(",", " ");
line = line.Replace("\t", " ");
line = line.Trim();
line = System.Text.RegularExpressions.Regex.Replace(line, @"\s+", " ");
if (asText)
{
//if (line.Length > 0)
strings.Add(line.Split(' '));
}
else if (line.Length > 0 && !line.StartsWith("#"))
strings.Add(line.Split(' '));
}
// set number of rows and columns
N = strings.Count();
C = 0;
foreach (string[] str in strings) if (str.Length > C) C = str.Length;
// convert input data to a format the Excel-interop understands: var[,]
if (asText) sCells = new string[N, C];
else cells = new double[N, C];
// parse all cells and add to array
for (int i = 0; i < N; i++)
{
for (int j = 0; j < strings[i].Length; j++)
{
try
{
if (asText) sCells[i, j] = strings[i][j];
else cells[i, j] = double.Parse(strings[i][j]);
}
catch (Exception e) { Error("Unable to parse number in paste file, line " + (i + 1) + ", column " + (j + 1) + ":\n" + strings[i][j] + "\n\n" + errLine + "\n\n" + e.ToString(), 1); }
}
}
}
Excel.Range startCell;
Excel.Range endCell;
try
{
// open excel app
oXL = new Excel.Application();
if (!warning) oXL.DisplayAlerts = false;
try
{
// try to open the selected excel file
// we turn of errors, since excel prompts for macro-enabled files and other things
oXL.DisplayAlerts = false;
// we need the absolute file path, since excel defaults to the user home dir, not the current working dir :S
oWB = oXL.Workbooks.Open(Path.GetFullPath(infile));
// turn back on warnings if wanted
if (warning) oXL.DisplayAlerts = true;
}
catch (Exception e) { throw new System.Exception("Unable to open file: " + Path.GetFullPath(infile) + "\n\n" + errLine + "\n\n" + e.ToString()); }
// set the active sheet
if (active.Length > 0)
{
try
{
oSheet = (Excel._Worksheet)oWB.Sheets[active];
}
catch (Exception e) { throw new System.Exception("Unable to select worksheet: " + active + "\n\n" + errLine + "\n\n" + e.ToString()); }
}
else
oSheet = (Excel._Worksheet)oWB.Worksheets[1];
// insert data
if (paste.Length > 0)
{
// if only columns are specified, find the amount of rows used
if (cellA[0] == 0 && cellB[0] == 0)
{
string cell = GetExcelColumnName(cellA[1]) + ":" + GetExcelColumnName(cellB[1]);
Excel.Range r = (Excel.Range)oSheet.UsedRange.Columns[cell, Type.Missing];
cellA[0] = 1;
cellB[0] = r.Rows.Count;
}
// select and paste values
try
{
startCell = (Excel.Range)oSheet.Cells[cellA[0], cellA[1]];
endCell = (Excel.Range)oSheet.Cells[cellB[0], cellB[1]];
oSheet.get_Range(startCell, endCell).Value = null;
endCell = (Excel.Range)oSheet.Cells[cellA[0] + N - 1, cellA[1] + C - 1];
if (asText) oSheet.get_Range(startCell, endCell).Value2 = sCells;
else oSheet.get_Range(startCell, endCell).Value2 = cells;
}
catch (Exception e)
{
string inputCell = GetExcelColumnName(cellA[1]) + cellA[0] + ":" + GetExcelColumnName(cellB[1]) + cellB[0];
throw new System.Exception("Unable to select input cells:\n\n " + inputCell + "\n\n" + errLine + "\n\n" + e.ToString());
}
}
// run macro
for (int i = 0; i < macro.Count; i++)
{
try
{
oXL.Run(macro[i]);
}
catch (Exception e) { throw new System.Exception("Unable to run macro: " + macro[i] + "\n\n" + errLine + "\n\n" + e.ToString()); }
}
// force workbook refresh
oXL.Calculate();
// go to result sheet
if (sheet.Length > 0)
{
try
{
oSheet = (Excel._Worksheet)oWB.Sheets[sheet];
}
catch (Exception e) { throw new System.Exception("Unable to select output sheet:" + sheet + "\n\n" + errLine + "\n\n" + e.ToString()); }
}
// save charts
foreach (Excel.Worksheet cSheet in oWB.Worksheets)
{
// loop trough all charts
Excel.ChartObjects xlCharts = (Excel.ChartObjects)cSheet.ChartObjects(Type.Missing);
for (int i = 1; i <= xlCharts.Count; i++)
{
Excel.ChartObject oChart = (Excel.ChartObject)xlCharts.Item(i);
Excel.Chart chart = oChart.Chart;
string chartName = "";
if (charts.Contains(cSheet.Name + "." + oChart.Name)) chartName = cSheet.Name + "." + oChart.Name;
else if (charts.Contains(oChart.Name)) chartName = oChart.Name;
// if chart is specified for output, save it
if (chartName.Length > 0)
{
int id = charts.FindIndex(s => s == chartName);
charts.RemoveAt(id);
try
{
// we need full path name again.. excel defaults to user home dir...
string saveas = Path.GetFullPath(infile);
saveas = Path.GetDirectoryName(saveas) + "\\" + Path.GetFileNameWithoutExtension(saveas);
saveas = saveas + "_" + chartName + ".png";
chart.Export(saveas, "PNG");
}
catch (Exception e) { throw new System.Exception("Unable to save chart '" + chartName + "':\n\n" + errLine + "\n\n" + e.ToString()); }
}
}
}
// if any charts was not found; throw an error.
if (charts.Count > 0)
{
string list = "";
foreach (string s in charts) list += s + ", ";
throw new Exception("Unable to find chart(s): " + list);
}
// if only columns are specified, find amount of rows to use
if (outCellA[0] == 0 && outCellB[0] == 0)
{
if (outCellB[1] == 0) outCellB[1] = oSheet.UsedRange.Columns.Count;
string cell = GetExcelColumnName(outCellA[1]) + ":" + GetExcelColumnName(outCellB[1]);
Excel.Range r = (Excel.Range)oSheet.UsedRange.Columns[cell, Type.Missing];
outCellA[0] = 1;
outCellB[0] = r.Rows.Count;
}
// select the output cell range
try
{
startCell = (Excel.Range)oSheet.Cells[outCellA[0], outCellA[1]];
endCell = (Excel.Range)oSheet.Cells[outCellB[0], outCellB[1]];
}
catch (Exception e)
{
string outcell = GetExcelColumnName(outCellA[1]) + outCellA[0] + ":" + GetExcelColumnName(outCellB[1]) + outCellB[0];
throw new System.Exception("Unable to select output cells:\n " + outcell + "\n\n" + errLine + "\n\n" + e.ToString());
}
// get output from selected cells
object[,] arr = null;
try
{
Excel.Range r = (Excel.Range)oSheet.get_Range(startCell, endCell);
// if only 1 cell is selected, excel will return an object instead of object array!
if (r.Cells.Count == 1)
{
arr = new object[2, 2];
arr[1, 1] = r.Cells.Value2;
}
else arr = r.Cells.Value2 as object[,];
}
catch (Exception e)
{
string outcell = GetExcelColumnName(outCellA[1]) + outCellA[0] + ":" + GetExcelColumnName(outCellB[1]) + outCellB[0];
throw new System.Exception("Invalid OutCells given. Unable to retrieve data:\n " + outcell + "\n\n" + errLine + "\n\n" + e.ToString());
}
List<string> results = new List<string>();
int last = 0;
N = outCellB[0] - outCellA[0] + 1;
C = outCellB[1] - outCellA[1] + 1;
// loop trough output rows
for (int i = 1; i <= N; i++)
{
// loop trough output columns
string s = "";
for (int j = 1; j <= C; j++)
{
// check if cell contains an error
if (arr[i, j] is Int32)
{
if (outErr)
{
int eCode = (int)arr[i, j];
string e = "";
if (eCode == -2146826281) e = "#DIV/0!";
else if (eCode == -2146826246) e = "#N/A";
else if (eCode == -2146826259) e = "#NAME?";
else if (eCode == -2146826288) e = "#NULL!";
else if (eCode == -2146826252) e = "#NUM!";
else if (eCode == -2146826265) e = "#REF!";
else if (eCode == -2146826273) e = "#VALUE!";
// no more error codes exists (?) as of 2013.. But to be sure / support future ones:
else e = "#ERR" + eCode.ToString();
s = s + e + " " + cellSpacer;
}
else s = s + " " + cellSpacer;
}
else if (arr[i, j] != null) s = s + arr[i, j].ToString() + cellSpacer;
else s = s + " " + cellSpacer;
}
// remove cellspacer from last column
if (C > 0) results.Add(s.Remove(s.Length - 1).TrimEnd());
// record last row column with content
if (s.Replace(cellSpacer, " ").TrimEnd().Length > 0) last = results.Count();
}
// write output to console
for (int i = 0; i < last; i++)
Console.WriteLine(results[i]);
// save file
if (save)
{
// if macros are enabled, excel would prompt about saving
oXL.DisplayAlerts = false;
oWB.Save();
}
}
// catch any exception
catch (Exception theException)
{
Error(errLine + "\n Error: " + theException.Message, 1);
}
finally
{
// clean up and exit
CleanUp();
}
#if DEBUG
Console.ReadKey();
#endif
}
private void btnExportExel_Click(object sender, EventArgs e)
{
if (XtraMessageBox.Show("Bán có chắc muốn in hóa đơn?", "THÔNG BÁO", MessageBoxButtons.YesNo, MessageBoxIcon.Information) == DialogResult.Yes)
{
if ((!string.IsNullOrEmpty(txtForm.Text)) || (!string.IsNullOrEmpty(txtTo.Text)) ||
(String.CompareOrdinal(txtForm.Text, txtTo.Text)) > 0)
{
XtraMessageBox.Show("Nhập sai", "Error Message!", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
else
{
//var app = (Microsoft.Office.Interop.Excel.Application) Marshal.GetActiveObject("Excel.Application");
var app = new Microsoft.Office.Interop.Excel.Application {
Visible = false
};
string path = Path.Combine(Environment.CurrentDirectory, @"Yokowo.xls");
object missingValue = System.Reflection.Missing.Value;
Workbook book = app.Workbooks.Open(path, missingValue,
false,
missingValue,
missingValue,
missingValue,
true,
missingValue,
missingValue,
true,
missingValue,
missingValue,
missingValue);
var sheet = (Worksheet)book.Worksheets[5];
// day
Range date = sheet.Range["E8", "E8"];
date.Value2 = DateTime.Now.Date.Day;
// month
Range month = sheet.Range["G8", "G8"];
month.Value2 = DateTime.Now.Date.Month;
// day
Range year = sheet.Range["H8", "H8"];
year.Value2 = DateTime.Now.Date.Year;
Range cusName = sheet.Range["F12", "F12"];
cusName.Value2 = txtCusName.Text;
// cust name
Range custName = sheet.Range["D13", "D13"];
custName.Value2 = txtCustName.Text;
// address
Range address = sheet.Range["D14", "D14"];
address.Value2 = txtAddress.Text;
// tax code
Range taxCode = sheet.Range["D15", "D15"];
taxCode.Value2 = txtTaxCode.Text;
// Del Term
Range delTerm = sheet.Range["E16", "E16"];
delTerm.Value2 = txtDelTerm.Text;
// Del Place
Range delPlace = sheet.Range["D17", "D17"];
delPlace.Value2 = txtDelPlace.Text;
// name buyer
Range buyerName = sheet.Range["B18", "B18"];
buyerName.Value2 = txtBuyerName.Text;
// Tel
Range tel = sheet.Range["F18", "F18"];
tel.Value2 = txtTel.Text;
// fax
Range fax = sheet.Range["H18", "H18"];
fax.Value2 = txtFax.Text;
// pay type
Range payType = sheet.Range["G49", "G49"];
payType.Value2 = txtPayType.Text;
// pay term
Range payTerm = sheet.Range["G51", "G51"];
payTerm.Value2 = txtPayTerm.Text;
// currency
Range currency = sheet.Range["I52", "I52"];
currency.Value2 = _customer.CURRENCY;
//book.RefreshAll();
app.Calculate();
book.Save();
book.Close(true, null, null);
app.Quit();
System.Diagnostics.Process.Start(path);
}
}
else
{
DialogResult = DialogResult.No;
}
}
private void UploadButton_Click(object sender, EventArgs e)
{
//main: open files, fill datatable, upload to SQL tmp database
DataTable myData = (DataTable)this.FileDropDataGridView.DataSource;
Boolean ResExist = false;
Boolean MEExist = false;
Boolean ClaimListExist = false;
if (UniqueAsAt(myData) > 1)
{
MessageBox.Show(this, "Error: AsAt should be the same for each file.");
return;
}
//load the version control userform with all versions for that asat choosen
frmUploader_VersionControl testDialog = new frmUploader_VersionControl();
testDialog.StartPosition = FormStartPosition.CenterScreen;
testDialog.GetVersion(myData.Rows[0][1].ToString());
testDialog.TopMost = true;
if (testDialog.ShowDialog(this) == DialogResult.OK)
{
//process start
this.toolStripStatusLabel1.Text = "Uploading";
//open new excel application to accomendate all the spreadsheets
//*********************************************************************************************************
Excel.Application myApp = new Excel.Application();
myApp.Visible = false;
//Excel.Application myApp = Globals.ThisAddIn.Application;
//*********************************************************************************************************
//set up the major event file
Excel.Workbook MEWkbk = null;
//set up the Claimsband file
Excel.Workbook CBWkbk = null;
//set up a reserving file
Excel.Workbook ResWkbk = null;
//set up a ClaimsList file
Excel.Workbook ClaimsListWkbk = null;
for (int i = 0; i < myData.Rows.Count; i++)
{
string openWkbkStatus = OpenWkbk(myData.Rows[i][0].ToString(), myApp);
if (myData.Rows[i][2].ToString() == "MajorEvent File")
{
MEWkbk = myApp.Workbooks[myData.Rows[i][0].ToString().Split('\\').Last()];
}
if (myData.Rows[i][2].ToString() == "ClaimsBand File")
{
CBWkbk = myApp.Workbooks[myData.Rows[i][0].ToString().Split('\\').Last()];
}
if (myData.Rows[i][2].ToString() == "Reserving File")
{
ResWkbk = myApp.Workbooks[myData.Rows[i][0].ToString().Split('\\').Last()];
}
if (myData.Rows[i][2].ToString() == "ClaimsList File")
{
ClaimsListWkbk = myApp.Workbooks[myData.Rows[i][0].ToString().Split('\\').Last()];
}
//progressbar
this.toolStripProgressBar1.Value = 40 * (i + 1) / (myData.Rows.Count);
}
//progressbar
this.toolStripProgressBar1.Value = 40;
//recalculate before populate datatable
myApp.Calculate();
//progressbar
this.toolStripProgressBar1.Value = 50;
//set up datatable to collect data from excel files
DataTable ResData = new DataTable();
DataTable MEData = new DataTable();
DataTable CBData = new DataTable();
DataTable ClaimsListData = new DataTable();
// Reserving files
if (ResWkbk != null)
{
ResExist = true;
//get the parameters for tab name, starting rows, starting columns...
DataTable parData = SQLResModule.GetWkbkParameterFromSQL();
//set up FX rates from reservering files
SQLResModule.SetFXRate(myApp);
//populate reserving datatable
ResData = SQLResModule.ReservingFileData(parData, myApp);
//Add AsAt Column
DataColumn AsAtCol = new DataColumn("AsAt");
AsAtCol.DefaultValue = myData.Rows[0][1].ToString();
ResData.Columns.Add(AsAtCol);
AsAtCol.SetOrdinal(0);
//Add Version Column
DataColumn VersionCol = new DataColumn("Version");
VersionCol.DefaultValue = testDialog.Version.ToString();
ResData.Columns.Add(VersionCol);
VersionCol.SetOrdinal(1);
//upload raw data to SQL
uploadToSQL(ResData, "tmp_Reservingfiles_InputData");
}
//prograss bar
this.toolStripProgressBar1.Value = 60;
//Major Event Data
DataTable MEuploadData = new DataTable();
if (MEWkbk != null)
{
MEExist = true;
try
{
Excel.Worksheet mySheet = MEWkbk.Worksheets["ME FlatFile"];
Excel.Range myRange = mySheet.UsedRange;
object[,] XlData = myRange.Value2;
MEData.Columns.Add("Event", System.Type.GetType("System.String"));
MEData.Columns.Add("Cedant", System.Type.GetType("System.String"));
MEData.Columns.Add("UWRef", System.Type.GetType("System.String"));
MEData.Columns.Add("UWY", System.Type.GetType("System.Int32"));
MEData.Columns.Add("DataType", System.Type.GetType("System.String"));
MEData.Columns.Add("SBFClass", System.Type.GetType("System.String"));
MEData.Columns.Add("ClaimType", System.Type.GetType("System.String"));
MEData.Columns.Add("RIType", System.Type.GetType("System.String"));
MEData.Columns.Add("Currency", System.Type.GetType("System.String"));
MEData.Columns.Add("Value", System.Type.GetType("System.Double"));
MEData.Columns.Add("TextValue", System.Type.GetType("System.String"));
var nColumn = myRange.Columns.Count;
var nRow = myRange.Rows.Count;
for (int row = 2; row <= nRow; row++)
{
DataRow XlDataRow = MEData.NewRow();
XlDataRow["Event"] = XlData[row, 2]?.ToString();
XlDataRow["Cedant"] = XlData[row, 3]?.ToString();
XlDataRow["UWRef"] = XlData[row, 4]?.ToString();
XlDataRow["UWY"] = Convert.ToInt32(XlData[row, 5]?.ToString());
XlDataRow["DataType"] = XlData[row, 6]?.ToString();
XlDataRow["SBFClass"] = XlData[row, 7]?.ToString();
XlDataRow["ClaimType"] = XlData[row, 8]?.ToString();
XlDataRow["RIType"] = XlData[row, 9]?.ToString();
XlDataRow["Currency"] = XlData[row, 10]?.ToString();
XlDataRow["Value"] = Convert.ToDouble(XlData[row, 11]?.ToString());
XlDataRow["TextValue"] = XlData[row, 12]?.ToString();
MEData.Rows.Add(XlDataRow);
}
//Add AsAt Column
DataColumn AsAtCol = new DataColumn("AsAt");
AsAtCol.DefaultValue = myData.Rows[0][1].ToString();
MEData.Columns.Add(AsAtCol);
AsAtCol.SetOrdinal(0);
//Add Version Column
DataColumn VersionCol = new DataColumn("Version");
VersionCol.DefaultValue = testDialog.Version.ToString();
MEData.Columns.Add(VersionCol);
VersionCol.SetOrdinal(1);
SQLModule.UploadMEToSQL(MEData, "tmp_ME_InputData");
SQLModule.MESQLStoredProcedure("sp_tmp_ME_PaidOS", testDialog.Version.ToString(), myData.Rows[0][1].ToString());
}
catch
{
}
}
//prograss bar
this.toolStripProgressBar1.Value = 70;
//Claim Band part
/*
* if (CBWkbk != null)
* {
* CBData.Columns.Add("AsAt", System.Type.GetType("System.String"));
* CBData.Columns.Add("Event", System.Type.GetType("System.String"));
* CBData.Columns.Add("Cedant", System.Type.GetType("System.String"));
* CBData.Columns.Add("UWRef", System.Type.GetType("System.String"));
* CBData.Columns.Add("UWY", System.Type.GetType("System.Int32"));
* CBData.Columns.Add("DataType", System.Type.GetType("System.String"));
* CBData.Columns.Add("SBFClass", System.Type.GetType("System.String"));
* CBData.Columns.Add("ClaimType", System.Type.GetType("System.String"));
* CBData.Columns.Add("RIType", System.Type.GetType("System.String"));
* CBData.Columns.Add("Currency", System.Type.GetType("System.String"));
* CBData.Columns.Add("Value", System.Type.GetType("System.Double"));
* CBData.Columns.Add("TextValue", System.Type.GetType("System.String"));
*
* try
* {
* foreach (Excel.Worksheet tmpSheet in CBWkbk.Worksheets)
* {
* switch (tmpSheet.Name.ToString())
* {
* case "Ultimates":
* case "Gross Premiums":
* ExtractClaimsBandData(tmpSheet, CBData, false, false);
* break;
*
* case "Gross Claims":
* ExtractClaimsBandData(tmpSheet, CBData, true, false);
* break;
*
* case "RI Premiums":
* ExtractClaimsBandData(tmpSheet, CBData, false, true);
* break;
*
* case "RI Claims":
* ExtractClaimsBandData(tmpSheet, CBData, true, true);
* break;
*
* default:
* break;
* }
* }
*
* }
* catch (Exception ex)
* {
* MessageBox.Show(ex.ToString());
* }
*
* AccessModule.UploadDataToAccess(CBData, @"U:\Reserving\Claims Band\Claims Band.accdb", @"ClaimsBand_FlatFile", true);
* }
*/
//ClaimsList part
if (ClaimsListWkbk != null)
{
ClaimListExist = true;
ClaimsListData.Columns.Add("SBFClass", System.Type.GetType("System.String"));
ClaimsListData.Columns.Add("UWRef", System.Type.GetType("System.String"));
ClaimsListData.Columns.Add("Bureau Claim Assured", System.Type.GetType("System.String"));
ClaimsListData.Columns.Add("Risk Code", System.Type.GetType("System.String"));
ClaimsListData.Columns.Add("UWY", System.Type.GetType("System.Int32"));
ClaimsListData.Columns.Add("Claim Ref", System.Type.GetType("System.String"));
ClaimsListData.Columns.Add("Claim Status", System.Type.GetType("System.String"));
ClaimsListData.Columns.Add("EventCode", System.Type.GetType("System.Int32"));
ClaimsListData.Columns.Add("Date of Loss", System.Type.GetType("System.String"));
ClaimsListData.Columns.Add("Date Last Movement", System.Type.GetType("System.String"));
ClaimsListData.Columns.Add("Movement - Date Entered", System.Type.GetType("System.String"));
ClaimsListData.Columns.Add("Reference", System.Type.GetType("System.String"));
ClaimsListData.Columns.Add("Loss Title", System.Type.GetType("System.String"));
ClaimsListData.Columns.Add("Current Narrative", System.Type.GetType("System.String"));
ClaimsListData.Columns.Add("Currency", System.Type.GetType("System.String"));
ClaimsListData.Columns.Add("Trust Fund Code", System.Type.GetType("System.String"));
ClaimsListData.Columns.Add("Paid Claims", System.Type.GetType("System.Double"));
ClaimsListData.Columns.Add("OS Claims", System.Type.GetType("System.Double"));
Excel.Range myClaimListRange = ClaimsListWkbk.Sheets["Eclipse Data"].UsedRange;
object[,] ClaimsListSheetData = myClaimListRange.Value2;
var nClaimListColumn = myClaimListRange.Columns.Count;
var nClaimListRow = myClaimListRange.Rows.Count;
for (int row = 2; row <= nClaimListRow - 6; row++)
{
if (ClaimsListSheetData[row, 1]?.ToString() != "" &&
ClaimsListSheetData[row, 1]?.ToString() != "Totals")
{
DataRow newRow = ClaimsListData.NewRow();
newRow["SBFClass"] = ClaimsListSheetData[row, 1]?.ToString();
newRow["UWRef"] = ClaimsListSheetData[row, 3]?.ToString();
newRow["Bureau Claim Assured"] = ClaimsListSheetData[row, 4]?.ToString();
newRow["Risk Code"] = ClaimsListSheetData[row, 5]?.ToString();
newRow["UWY"] = Convert.ToInt32(ClaimsListSheetData[row, 6]?.ToString());
newRow["Claim Ref"] = ClaimsListSheetData[row, 7]?.ToString();
newRow["Claim Status"] = ClaimsListSheetData[row, 8]?.ToString();
newRow["EventCode"] = Convert.ToInt32(ClaimsListSheetData[row, 9]?.ToString());
if (ClaimsListSheetData[row, 10]?.ToString() != "")
{
if (Convert.ToDouble(ClaimsListSheetData[row, 10]?.ToString()) > 0)
{
DateTime DateLossTo = DateTime.FromOADate(Convert.ToDouble(ClaimsListSheetData[row, 10]?.ToString()));
newRow["Date of Loss"] = DateLossTo;
}
}
if (ClaimsListSheetData[row, 12]?.ToString() != "")
{
DateTime DateLossTo = DateTime.FromOADate(Convert.ToDouble(ClaimsListSheetData[row, 12]?.ToString()));
newRow["Date Last Movement"] = DateLossTo;
}
if (ClaimsListSheetData[row, 13]?.ToString() != "")
{
DateTime DateLossTo = DateTime.FromOADate(Convert.ToDouble(ClaimsListSheetData[row, 13]?.ToString()));
newRow["Movement - Date Entered"] = DateLossTo;
}
newRow["Reference"] = ClaimsListSheetData[row, 14]?.ToString();
newRow["Loss Title"] = ClaimsListSheetData[row, 15]?.ToString();
newRow["Current Narrative"] = ClaimsListSheetData[row, 16]?.ToString();
newRow["Currency"] = ClaimsListSheetData[row, 17]?.ToString();
newRow["Trust Fund Code"] = ClaimsListSheetData[row, 18]?.ToString();
newRow["Paid Claims"] = Convert.ToDouble(ClaimsListSheetData[row, 19]?.ToString())
+ Convert.ToDouble(ClaimsListSheetData[row, 20]?.ToString());
newRow["OS Claims"] = Convert.ToDouble(ClaimsListSheetData[row, 21]?.ToString())
+ Convert.ToDouble(ClaimsListSheetData[row, 22]?.ToString());
ClaimsListData.Rows.Add(newRow);
}
}
ClaimListToSQL(ClaimsListData);
//call stored procedure in SQL
using (SqlConnection connectionSQL = new SqlConnection(@"Database=ADS;Server=CREREPSQL03;Integrated Security=True;connect timeout=30"))
{
connectionSQL.Open();
SqlCommand storedProcedureComm = new SqlCommand("sp_ClaimList_0_RunAll", connectionSQL);
storedProcedureComm.CommandType = CommandType.StoredProcedure;
try
{
storedProcedureComm.ExecuteNonQuery();
}
catch
{
MessageBox.Show("A problem with sp_ClaimList_0_RunAll.");
}
connectionSQL.Close();
}
}
//prograss bar
this.toolStripProgressBar1.Value = 80;
//upload various table into imp_tmp_UploadData
using (SqlConnection connectionSQL = new SqlConnection(@"Database=ADS;Server=CREREPSQL03;Integrated Security=True;connect timeout=30"))
{
connectionSQL.Open();
SqlCommand storedProcedureComm = new SqlCommand("sp_tmp_upd_3_uploadData", connectionSQL);
storedProcedureComm.CommandType = CommandType.StoredProcedure;
try
{
storedProcedureComm.Parameters.Add("@ResExist", SqlDbType.Bit).Value = ResExist;
storedProcedureComm.Parameters.Add("@MEExist", SqlDbType.Bit).Value = MEExist;
storedProcedureComm.Parameters.Add("@CLExist", SqlDbType.Bit).Value = ClaimListExist;
storedProcedureComm.ExecuteNonQuery();
}
catch (Exception ex)
{
MessageBox.Show(this, ex.ToString() + @"A problem with sp_tmp_upd_3_uploadData.");
}
connectionSQL.Close();
}
this.toolStripProgressBar1.Value = 90;
//close all excel spreadsheets
foreach (Excel.Workbook Wkbk in myApp.Workbooks)
{
Wkbk.Close(false);
}
//quite the excel application without saving
//**********************************************************************************************
myApp.Quit();
//**********************************************************************************************
//prograss bar
this.toolStripProgressBar1.Value = 100;
this.Activate();
this.toolStripStatusLabel1.Text = "Upload success";
}
//clear up
testDialog.Dispose();
}