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