private void SetParameters()
{
string xlsPath;
SpreadsheetGear.IWorkbook workbook = null;
SpreadsheetGear.IWorksheet sheet = null;
SpreadsheetGear.IRange range = null;
SetStatus("Setting Parameter Data");
try
{
xlsPath = EnsureXLSFile();
workbook = SpreadsheetGear.Factory.GetWorkbook(xlsPath);
sheet = workbook.Worksheets["Data"];
foreach (string key in m_Inputs.Parameters.Keys)
{
range = null;
try
{
range = sheet.Cells[key];
}
catch (Exception)
{
}
if (range != null)
{
range.Value = m_Inputs.Parameters[key];
}
}
workbook.WorkbookSet.CalculateFull();
workbook.Save();
}
finally
{
workbook?.Close();
}
}
//end getfromfile
/// <summary>
/// return datatable of origin names when there is a start range but no end range
/// iterate through from start cell and end when an empty cell is reached
/// </summary>
/// <param name="dir">location of excel worknook</param>
/// <param name="comapnygroup">identifies worksheet to use depending on company</param>
/// <param name="xlsheet">worksheet to use within workbbok</param>
/// <param name="startrange">start cell on worksheet colunm/row value e.g. A1 or A1:A50</param>
/// <returns>indexed datatable of items</returns>
public static DataTable getfromfile(string dir, string companygroup, string xlsheet, string xlstart)
{
//*****
//100212 check against company id, if no file just use "officepricer" prefix
//namecustomcontroller _name = new namecustomcontroller();
//string _cg = _name.get_company_group(companyid);
string _source = companygroup != "0" ? get_latest_pricer(dir, companygroup) : get_latest_pricer(dir); //find latest upload of pricer
//****
string _copy = companygroup.ToString() + "_" + DateTime.Now.ToString("ddMMyyHHmmss") + ".xls";
fso_copy_file(dir + _source, dir + _copy);
SpreadsheetGear.IWorkbook _wb = SpreadsheetGear.Factory.GetWorkbook(HttpContext.Current.Server.MapPath(dir + _copy));
//disable password protection for now
_wb.Unprotect("Trueblue");
//is this necessary in web apps - seems primarily for threading in winforms
//_wb.WorkbookSet.GetLock(); //acquire lock
//get range
SpreadsheetGear.IRange _range = _wb.Worksheets[xlsheet].Range[xlstart];
//_range.Replace("", "*", SpreadsheetGear.LookAt.Whole, SpreadsheetGear.SearchOrder.ByColumns, false);
DataTable _dt = new DataTable();
//_dt =_range.GetDataTable(SpreadsheetGear.Data.GetDataFlags.NoColumnHeaders);
//add a datatabe name and a column name so e.g. can data bind to combo
_dt.Columns.Add("item_index", typeof(int));
_dt.Columns.Add("item", typeof(string));
_dt.TableName = "item_table";
_dt.PrimaryKey = new DataColumn[] { _dt.Columns["Item_index"] };
//_dt.Columns[0].ColumnName = "item";
Boolean _end = false;
int _ix = 0;
while (_end == false)
{
DataRow _dr = _dt.NewRow();
string _s = _range.Rows[_ix, 0].Value != null ? _range.Rows[_ix, 0].Value.ToString() : null;
if (!string.IsNullOrEmpty(_s))
{
_ix += 1;
_dr["item"] = _s;
_dr["item_index"] = _ix;
_dt.Rows.Add(_dr);
}
else
{
_end = true;
}
}
_wb.Protect("Trueblue", true, true);
_wb.Close();
//_wb.WorkbookSet.ReleaseLock();
//delete teporary copy
fso_kill_file(dir + _copy);
return(_dt);
}
private void OutPutExcel_N4Nguphap(DataTable adtData)
{
string templatePath = Common.GetTemplate("N4Nguphap_テンプレート.xls");
SpreadsheetGear.IWorkbook workbook = SpreadsheetGear.Factory.GetWorkbook(templatePath);
try
{
SpreadsheetGear.IWorksheet worksheet = workbook.Worksheets["N4Nguphap"];
SpreadsheetGear.IRange range = null;
//tb.Columns.Add("id");
//tb.Columns.Add("maucau");
//tb.Columns.Add("cachchia");
//tb.Columns.Add("ynghia");
//tb.Columns.Add("vidu");
string dataA, dataB, dataC, dataD, dataE;
string[] numofLineA, numofLineB, numofLineC, numofLineD, numofLineE;
for (int i = 0, addressY = 2, plus = 0; i < adtData.Rows.Count; i++, addressY++, plus = 0)
{
dataA = adtData.Rows[i]["id"].ToString();
dataB = adtData.Rows[i]["maucau"].ToString();
dataC = adtData.Rows[i]["cachchia"].ToString();
dataD = adtData.Rows[i]["ynghia"].ToString();
dataE = adtData.Rows[i]["vidu"].ToString();
numofLineA = dataA.Split(new string[] { "\n" }, StringSplitOptions.RemoveEmptyEntries);
numofLineB = dataB.Split(new string[] { "\n" }, StringSplitOptions.RemoveEmptyEntries);
numofLineC = dataC.Split(new string[] { "\n" }, StringSplitOptions.RemoveEmptyEntries);
numofLineD = dataD.Split(new string[] { "\n" }, StringSplitOptions.RemoveEmptyEntries);
numofLineE = dataE.Split(new string[] { "\n" }, StringSplitOptions.RemoveEmptyEntries);
for (int plusP = 0; plusP < numofLineA.Length; plusP++)
{
range = worksheet.Cells["A" + (addressY + plusP)];
range.Value = numofLineA[plusP];
}
for (int plusP = 0; plusP < numofLineB.Length; plusP++)
{
range = worksheet.Cells["B" + (addressY + plusP)];
range.Value = numofLineB[plusP];
}
for (int plusP = 0; plusP < numofLineC.Length; plusP++)
{
range = worksheet.Cells["C" + (addressY + plusP)];
range.Value = numofLineC[plusP];
}
for (int plusP = 0; plusP < numofLineD.Length; plusP++)
{
range = worksheet.Cells["D" + (addressY + plusP)];
range.Value = numofLineD[plusP];
}
for (int plusP = 0; plusP < numofLineE.Length; plusP++)
{
range = worksheet.Cells["E" + (addressY + plusP)];
range.Value = numofLineE[plusP];
}
plus = Math.Max(numofLineA.Length, numofLineB.Length);
plus = Math.Max(numofLineC.Length, plus);
plus = Math.Max(numofLineD.Length, plus);
plus = Math.Max(numofLineE.Length, plus);
addressY = addressY + plus - 1;
}
string outPath = "";
Common.SaveExcelTemplate(workbook, "N4文法", "xls", out outPath);
if (File.Exists(outPath))
{
System.Diagnostics.Process.Start(outPath);
}
}
catch (Exception)
{
throw;
}
finally
{
workbook.Close();
}
}
private static void ReadXlsxFileIntoList()
{
Console.WriteLine("ProcessInputXlsxFile".PadRight(30, '.') + "ReadXLSXFileIntoList() -- started");
StaticVariable.ConsoleOutput.Add("ProcessInputXlsxFile".PadRight(30, '.') + "ReadXLSXFileIntoList() -- started");
StaticVariable.ProgressDetails.Add(Environment.NewLine + "ProcessInputXlsxFile::ReadXLSXFileIntoList()");
StaticVariable.ProgressDetails.Add(Constants.FiveSpacesPadding + "Any line containing 'DefaultXX' will be ignored, as will all headers");
string[] worksheetsTypes = { Constants.Duration, Constants.Capped, Constants.Pulse };
List <string> workSheetsNotUsed = new List <string>();
List <string> discardedLines = new List <string>();
List <string> workSheetsUsed = new List <string>();
SpreadsheetGear.IWorkbook workbook = SpreadsheetGear.Factory.GetWorkbook(StaticVariable.InputFile);
foreach (string wksheet in worksheetsTypes)
{
try
{
SpreadsheetGear.IWorksheet worksheet = workbook.Worksheets[wksheet];
SpreadsheetGear.IRange cells = worksheet.Cells;
workSheetsUsed.Add(wksheet);
}
catch (Exception)
{
workSheetsNotUsed.Add(wksheet);
}
}
foreach (string wksheet in workSheetsUsed)
{
SpreadsheetGear.IWorksheet worksheet = workbook.Worksheets[wksheet];
SpreadsheetGear.IRange cells = worksheet.Cells;
var currentColumn = 0;
for (currentColumn = 0; currentColumn < cells.ColumnCount; currentColumn++)
{
if (cells[0, currentColumn].Text.ToUpper().Equals(Constants.FinalColumnName))
{
currentColumn++;
break;
}
}
var maximumNumberOfColumns = currentColumn;
try
{
foreach (SpreadsheetGear.IRange row in worksheet.UsedRange.Rows)
{
StringBuilder sb = new StringBuilder();
for (int i = 0; i < maximumNumberOfColumns; i++)
{
sb.Append(row[0, i].Value + "\t"); //0.0400 being chopped to 0.04.
}
string sAdjustSb = sb.ToString().TrimEnd('\t');
if (sAdjustSb.Contains(";") && !DiscardHeaderLine(sAdjustSb))
{
discardedLines.Add("- " + sAdjustSb.Substring(0, sAdjustSb.IndexOf('\t')));
}
else if (!string.IsNullOrEmpty(sAdjustSb) && !DiscardHeaderLine(sAdjustSb))
{
ValidateData.CheckForCommasInLine(sAdjustSb);
StaticVariable.InputXlsxFileDetails.Add(ValidateData.CapitaliseWord(sAdjustSb));
}
}
}
catch (Exception e)
{
StaticVariable.ProgressDetails.Add(Environment.NewLine + "ProcessInputXlsxFile::ReadXLSXFileIntoList()");
StaticVariable.ProgressDetails.Add(Constants.FiveSpacesPadding + "Error in reading in XLSX line into list. Is there any data? ");
StaticVariable.ProgressDetails.Add(Constants.FiveSpacesPadding + e.Message);
}
}
workbook.Close();
if (workSheetsNotUsed.Any())
{
StaticVariable.ProgressDetails.Add(Environment.NewLine + "ProcessInputXlsxFile::ReadXLSXFileIntoList()");
foreach (var entry in workSheetsNotUsed)
{
StaticVariable.ProgressDetails.Add(Constants.FiveSpacesPadding + entry + " rates are not being used. Delete this worksheet");
}
}
foreach (var entry in workSheetsUsed)
{
StaticVariable.ProgressDetails.Add(Constants.FiveSpacesPadding + entry + " rates are being used. ");
}
if (discardedLines.Any())
{
StaticVariable.ProgressDetails.Add(Environment.NewLine + "ProcessInputXlsxFile::ReadXLSXFileIntoList()");
StaticVariable.ProgressDetails.Add(Constants.FiveSpacesPadding + "Customer destinations discarded.");
discardedLines.Sort();
foreach (var entry in discardedLines)
{
StaticVariable.ProgressDetails.Add(Constants.FiveSpacesPadding + entry);
}
}
StaticVariable.ProgressDetails.Add(Environment.NewLine + "ProcessInputXlsxFile".PadRight(30, '.') + "ReadXLSXFileIntoList()-- completed");
Console.WriteLine("ProcessInputXlsxFile".PadRight(30, '.') + "ReadXLSXFileIntoList() -- finished");
StaticVariable.ConsoleOutput.Add("ProcessInputXlsxFile".PadRight(30, '.') + "ReadXLSXFileIntoList() -- finished");
}
private void OutPutExcel_2000共通単語(DataTable adtData)
{
string templatePath = Common.GetTemplate("2000共通単語_テンプレート.xls");
SpreadsheetGear.IWorkbook workbook = SpreadsheetGear.Factory.GetWorkbook(templatePath);
try
{
SpreadsheetGear.IWorksheet worksheet = workbook.Worksheets["2000共通単語"];
SpreadsheetGear.IRange range = null;
for (int i = 0, addressY = 2; i < adtData.Rows.Count; i++, addressY++)
{
range = worksheet.Cells["A" + addressY];
range.Value = adtData.Rows[i]["id"];
range = worksheet.Cells["B" + addressY];
range.Value = adtData.Rows[i]["jp"];
if (adtData.Rows[i]["read"].ToString().Length > 0 || adtData.Rows[i]["tooltipText"].ToString().Length > 0)
{
range.AddComment(adtData.Rows[i]["read"].ToString().Length > 0 ? (adtData.Rows[i]["read"].ToString() + Environment.NewLine + adtData.Rows[i]["tooltipText"].ToString()) : adtData.Rows[i]["tooltipText"].ToString());
SpreadsheetGear.IComment icomment = range.Comment;
using (Graphics g = this.CreateGraphics())
{
string item = icomment.ToString();
SizeF sizeF = g.MeasureString(item, Font);
icomment.Shape.Width = sizeF.Width;
icomment.Shape.Height = sizeF.Height;
}
}
range = worksheet.Cells["C" + addressY];
range.Value = adtData.Rows[i]["read"];
range.WrapText = false;
range = worksheet.Cells["D" + addressY];
range.Value = adtData.Rows[i]["vi"];
range.WrapText = false;
range = worksheet.Cells["E" + addressY];
range.Value = adtData.Rows[i]["innerText"];
range.WrapText = false;
range = worksheet.Cells["F" + addressY];
range.Value = adtData.Rows[i]["outerHtml"];
range.WrapText = false;
range = worksheet.Cells["G" + addressY];
range.Value = adtData.Rows[i]["tooltipText"];
range.WrapText = false;
}
string outPath = "";
Common.SaveExcelTemplate(workbook, "2000共通単語", "xls", out outPath);
if (File.Exists(outPath))
{
System.Diagnostics.Process.Start(outPath);
}
}
catch (Exception)
{
throw;
}
finally
{
workbook.Close();
}
}
