public ExcelSheet(ExcelWorkbook wb) { if (wb == null) throw new ArgumentException("Workbook cannot be null!"); this.Workbook = wb; }
public void InitTest() { _workbook = new ExcelWorkbook(); _workbook.Name = "Test-WB"; _sheet1 = _workbook.NewSheet("Test-Sheet 1"); _sheet2 = _workbook.NewSheet("Test-Sheet 2"); }
/// <summary> /// Workbook /// </summary> public Workbook() { Styles = new StyleCollection(); Properties = new DocumentProperties(); DocumentSettings = new DocumentSettings(); ExcelWorkbook = new ExcelWorkbook(); WorkSheets = new WorksheetCollection(); }
public ExcelWorkbook GenerateExcel(Dictionary<string, List<ExcelReportRow>> data, Dictionary<Guid, string> selectedShops) { ExcelWorkbook workbook = new ExcelWorkbook("Südameapteegi_ravimiinfo", "Südameapteegi ravimiinfo süsteem, fail on genereeritud " + DateTime.Now, "Südameapteek", false) { Styles = new List<ExcelStyle>(1) }; workbook.Styles.Add(new ExcelStyle(false, "black", false) { ID = "reg" }); workbook.Styles.Add(new ExcelStyle(true, "black", false) { ID = "bold" }); workbook.Styles.Add(new ExcelStyle(true, "red", false) { ID = "warn" }); workbook.Worksheet.Table = new Helmes.ExcelLib.ExcelTable(); ExcelRow headerRow=new ExcelRow(); //if (data != null && data.Count > ushort.MaxValue) //{ // ExcelRow warningRow = new ExcelRow(); // warningRow.Cells.Add(new ExcelCell("warn", "NB! Failis on kokku " + data.Count + " rida, mida on rohkem kui " + ushort.MaxValue + ", mida saab Excelis maksimaalselt näidata. Seetõttu on fail poolik ning tuleb kitsendada väljavõtte filtrit!!")); // workbook.Worksheet.Table.Rows.Add(warningRow); //} headerRow.Cells.Add(new ExcelCell("bold", "Apt kood")); headerRow.Cells.Add(new ExcelCell("bold", "Ravimi nimetus")); headerRow.Cells.Add(new ExcelCell("bold", "ATC")); foreach (KeyValuePair<Guid,string> selectedShop in selectedShops) { headerRow.Cells.Add(new ExcelCell("bold", selectedShop.Value)); } workbook.Worksheet.Table.Rows.Add(headerRow); foreach (KeyValuePair<string,List<ExcelReportRow>> reportRow in data) { ExcelRow dataRow = new ExcelRow(); dataRow.Cells.Add(new ExcelCell("reg", reportRow.Key)); if(reportRow.Value!=null) { dataRow.Cells.Add(new ExcelCell("reg", reportRow.Value.FirstOrDefault().MedName)); dataRow.Cells.Add(new ExcelCell("reg", reportRow.Value.FirstOrDefault().AtcCode)); foreach (KeyValuePair<Guid, string> selectedShop in selectedShops) { ExcelReportRow rpr = reportRow.Value.FirstOrDefault(w => w.ShopId == selectedShop.Key); if (rpr != null) { dataRow.Cells.Add(new ExcelCell("reg", rpr.Price)); }else { dataRow.Cells.Add(new ExcelCell()); } } } workbook.Worksheet.Table.Rows.Add(dataRow); } return workbook; }
public ExcelCell(ExcelWorkbook wb, ExcelSheet sheet, UInt32 row, UInt32 column) { if (wb == null) throw new ArgumentException("Workbook cannot be null!"); if (sheet == null) throw new ArgumentException("Sheet cannot be null!"); //this.Style = new Style(wb); //TODO!! Set StyleIndex to the wb's default style this.Workbook = wb; this.Sheet = sheet; this.Column = column; this.Row = row; }
public DefaultStyle(ExcelWorkbook wb) : base(wb) { this.FontFamily = "Calibri"; this.FontSize = 11; this.Bold = false; this.Italic = false; this.Strikeout = false; this.Underline = false; this.WrapText = false; this.ShrinkToFit = false; this.MergeCells = false; this.Background = null; }
/// <summary> /// Initializes this instance of ExcelStyle, copying the style /// values from the Workbook provided. /// </summary> /// <param name="wb">The Workbook we are working with.</param> public Style(ExcelWorkbook wb) { this._wb = wb; //this.FontFamily = wb.Style.FontFamily; //this.FontSize = wb.Style.FontSize; //this.Bold = wb.Style.Bold; //this.Italic = wb.Style.Italic; //this.Strikeout = wb.Style.Strikeout; //this.Underline = wb.Style.Underline; //this.WrapText = wb.Style.WrapText; //this.ShrinkToFit = wb.Style.ShrinkToFit; //this.MergeCells = wb.Style.MergeCells; //this.Background = wb.Style.Background; }
internal ExcelVbaProject(ExcelWorkbook wb) { _wb = wb; _pck = _wb._package.Package; References = new ExcelVbaReferenceCollection(); Modules = new ExcelVbaModuleCollection(this); var rel = _wb.Part.GetRelationshipsByType(schemaRelVba).FirstOrDefault(); if (rel != null) { Uri = PackUriHelper.ResolvePartUri(rel.SourceUri, rel.TargetUri); Part = _pck.GetPart(Uri); GetProject(); } else { Lcid = 0; Part = null; } }
internal static DependencyChain Create(ExcelWorkbook wb, ExcelCalculationOption options) { var depChain = new DependencyChain(); foreach (var ws in wb.Worksheets) { if (!(ws is ExcelChartsheet)) { GetChain(depChain, wb.FormulaParser.Lexer, ws.Cells, options); GetWorksheetNames(ws, depChain, options); } } foreach (var name in wb.Names) { if (name.NameValue==null) { GetChain(depChain, wb.FormulaParser.Lexer, name, options); } } return depChain; }
internal static DependencyChain Create(ExcelWorkbook wb, ExcelCalculationOption options) { var depChain = new DependencyChain(); foreach (var ws in wb.Worksheets) { if (!(ws is ExcelChartsheet)) { GetChain(depChain, wb.FormulaParser.Lexer, ws.Cells, options); GetWorksheetNames(ws, depChain, options); } } foreach (var name in wb.Names) { if (name.NameValue == null) { GetChain(depChain, wb.FormulaParser.Lexer, name, options); } } return(depChain); }
public void ExportExcel() { string path = HostingEnvironment.MapPath("/excel/CDVT/BaoCaoTrungTu_Template.xlsx"); FileInfo file = new FileInfo(path); using (ExcelPackage excelPackage = new ExcelPackage(file)) { ExcelWorkbook excelWorkbook = excelPackage.Workbook; ExcelWorksheet excelWorksheet = excelWorkbook.Worksheets.First(); using (QuangHanhManufacturingEntities db = new QuangHanhManufacturingEntities()) { string query = " select MONTH(c.date_created) as Thang, YEAR(c.date_created) as Nam,a.Equipment_category_id as Ma, " + "a.equipment_name as Tenthietbi, a.mark_code as Sohieu, a.equipmentId as Matscd, c.date_created as Ngayquyetdinh, " + "SUM(case b.next_remodel_type when N'Tiểu tu' then 1 else 0 end) as Tieutu, " + "SUM(case b.next_remodel_type when N'Trung tu' then 1 else 0 end) as Trungtu, " + "SUM(case b.next_remodel_type when N'Đại tu' then 1 else 0 end) as Daitu " + "from Equipment a,Documentary_big_maintain_details b, Documentary c " + " where a.equipmentId = b.equipmentId and b.documentary_id = c.documentary_id " + "group by b.documentary_id, a.equipmentId, c.date_created, a.Equipment_category_id, a.equipment_name, a.mark_code "; List <TrungTuReport> listdata = db.Database.SqlQuery <TrungTuReport>(query).ToList(); int k = 0; for (int i = 3; i < listdata.Count + 3; i++) { excelWorksheet.Cells[i, 1].Value = (k + 1); excelWorksheet.Cells[i, 2].Value = listdata.ElementAt(k).Ma; excelWorksheet.Cells[i, 3].Value = listdata.ElementAt(k).Tenthietbi; excelWorksheet.Cells[i, 4].Value = listdata.ElementAt(k).Sohieu; excelWorksheet.Cells[i, 5].Value = listdata.ElementAt(k).Matscd; excelWorksheet.Cells[i, 6].Value = listdata.ElementAt(k).Ngayquyetdinh.ToString("hh:mm tt dd/MM/yyyy"); excelWorksheet.Cells[i, 7].Value = listdata.ElementAt(k).Tieutu; excelWorksheet.Cells[i, 8].Value = listdata.ElementAt(k).Trungtu; excelWorksheet.Cells[i, 9].Value = listdata.ElementAt(k).Daitu; k++; } string location = HostingEnvironment.MapPath("/excel/CDVT/download"); excelPackage.SaveAs(new FileInfo(location + "/BaoCaoTrungTu.xlsx")); } } }
private static void SetTabAccount(ExcelWorkbook workbook, List <Account> accounts) { var ws = workbook.Worksheets.Add("Conta Bancária"); ws.Cells["A1:F1"].Style.Border.Bottom.Style = ws.Cells["F1"].Style.Border.Right.Style = ExcelBorderStyle.Thick; ws.Cells["A1:F1"].Style.Font.Bold = true; ws.View.ShowGridLines = false; ws.DefaultColWidth = 40; ws.Cells["A1"].Value = "Banco"; ws.Cells["B1"].Value = "Agência"; ws.Cells["C1"].Value = "Número da conta"; ws.Cells["D1"].Value = "Titular"; ws.Cells["E1"].Value = "Limite"; ws.Cells["F1"].Value = "Saldo"; for (int i = 0, row = 2; i < accounts.Count; i++, row++) { var item = accounts[i]; ws.Cells[$"A{row}"].Value = item.Bank; ws.Cells[$"B{row}"].Value = item.Agency; ws.Cells[$"C{row}"].Value = item.AccountBank; ws.Cells[$"D{row}"].Value = item.Holder; ws.Cells[$"E{row}"].Value = item.Limit.ToString(); ws.Cells[$"F{row}"].Value = item.Balance.ToString(); ws.Cells[$"E{row}"].Style.Numberformat.Format = ws.Cells[$"F{row}"].Style.Numberformat.Format = currencyFormat; } var range = ws.Cells[$"A1:F{accounts.Count + 1}"]; range.Style.Border.Top.Style = range.Style.Border.Left.Style = range.Style.Border.Bottom.Style = range.Style.Border.Right.Style = ExcelBorderStyle.Thin; }
public static void LoadFromCurrentDirectory() { Stopwatch sw = new Stopwatch(); sw.Start(); string[] allFiles = Directory.GetFiles(savePath, "@*"); int cCount = 0; Console.WriteLine($"{DateTime.Now.ToString("HH:mm:ss", CultureInfo.CurrentCulture)} Loading characters: {cCount}/{allFiles.Length}"); foreach (string file in allFiles) { string mention = "<" + Path.GetFileNameWithoutExtension(file).Split('_').First() + ">"; using (Stream strm = File.Open(file, FileMode.Open)) { MemoryStream memoryStream = new MemoryStream(); strm.CopyTo(memoryStream); using (ExcelPackage package = new ExcelPackage(memoryStream)) { PlayableCharacter character = null; ExcelWorkbook workbook = package.Workbook; ExcelWorksheet worksheet = workbook.Worksheets["Feuille de personnage"]; if (worksheet != null)//Fiche de perso anima { character = new AnimaCharacter(worksheet, mention); } worksheet = workbook.Worksheets["Stat"]; if (worksheet != null)//Fiche de perso L5R => peut encore changer { character = new L5RCharacter(worksheet, mention); } Characters.Add(character); } } cCount++; Console.Clear(); Console.WriteLine($"{DateTime.Now.ToString("HH:mm:ss", CultureInfo.CurrentCulture)} Loading characters: {cCount}/{allFiles.Length}"); } sw.Stop(); Console.WriteLine($"{DateTime.Now.ToString("HH:mm:ss", CultureInfo.CurrentCulture)} All characters loaded in : {sw.ElapsedMilliseconds}ms"); }
/// <summary> /// Saving in xlsx format session results for each group in table form. /// </summary> /// <param name="data_table">List contains <see cref="SessionResultTable"/></param> /// <param name="filePath">The path to the file</param> /// <param name="open_after_creation">Open file after creation</param> public static void CreateReportFile(IEnumerable <SessionResultTable> data_table, string filePath, bool open_after_creation = false) { FileWorker.DeleteFileIfExists(filePath); FileInfo file = new FileInfo(filePath); ExcelPackage.LicenseContext = LicenseContext.NonCommercial; using (ExcelPackage excelPackage = new ExcelPackage(file)) { ExcelWorkbook excelWorkBook = excelPackage.Workbook; foreach (var data in data_table) { ExcelWorksheet workSheet = excelPackage.Workbook.Worksheets.Add(data.GroupName); SetHeaderStyle(workSheet, 1); SetColumnWidth(workSheet, 1, 4, 30, 25, 10, 12, 10); SetSheetStyle(workSheet, 1, 1, 6, 1); SetColumnNames(workSheet, 1, 1, "№", "Student", "Subject", "Mark", "Date", "Test form"); var list = data.sessionResults.ToList(); int i = 2, num = 1; foreach (var item in list) { SetRowStyle(workSheet, 1, i, 6, i); workSheet.Cells[i, 1].Value = num++; workSheet.Cells[i, 2].Value = $"{item.Surname} {item.Name} {item.MiddleName}"; workSheet.Cells[i, 3].Value = item.Subject; workSheet.Cells[i, 4].Value = item.Mark; workSheet.Cells[i, 5].Value = item.Date.ToString("dd.MM.yyyy"); workSheet.Cells[i, 6].Value = item.TestForm; i++; } workSheet.Cells["A" + i.ToString() + ":F" + i.ToString()].Style.Border.Top.Style = OfficeOpenXml.Style.ExcelBorderStyle.Medium; } excelPackage.Save(); if (open_after_creation) { FileWorker.Open(filePath); } } }
public void ExportEntriesToExcel(string filePath, List <Entry> entries, List <BuildingComponents> bc, List <OtherImprovements> oi, List <Address> a, List <MailingAddress> ma, List <ComparableData> cd) { FileInfo file = new FileInfo(filePath); using (ExcelPackage exlPackage = new ExcelPackage(file)) { ExcelWorkbook wb = exlPackage.Workbook; //Save parsed entries into Property Info Sheet in excel ExcelWorksheet wsEntries = exlPackage.Workbook.Worksheets["Property Info"]; wsEntries.Cells.LoadFromCollection(entries, true); //Make a new sheet called Building Components and save parsed entries into the sheet ExcelWorksheet wsBldgComponents = exlPackage.Workbook.Worksheets.Add("Building Components"); wsBldgComponents.Cells.LoadFromCollection(bc, true); //Make a new sheet called Other Improvements and save parsed entries into the sheet ExcelWorksheet wsOtherImprovements = exlPackage.Workbook.Worksheets.Add("Other Improvements"); wsOtherImprovements.Cells.LoadFromCollection(oi, true); //Make a new sheet called Address and save formatted address into the sheet ExcelWorksheet wsAddress = exlPackage.Workbook.Worksheets.Add("Address"); wsAddress.Cells.LoadFromCollection(a, true); //Make a new sheet called Mailing Address and save formatted mailing address into the sheet ExcelWorksheet wsMailing = exlPackage.Workbook.Worksheets.Add("Mailing Address"); wsMailing.Cells.LoadFromCollection(ma, true); //Make a new sheet called Comparables and save parsed entries into the sheet ExcelWorksheet wsComparables = exlPackage.Workbook.Worksheets.Add("Comparables"); wsComparables.Cells.LoadFromCollection(cd, true); //Save all changes into the excel file exlPackage.Save(); } //Format property and mailing address sheet FormatAddress(file); }
public IEnumerable <string> LoadWordFromExcelAsync(string path, string sheetName = null) { if (!File.Exists(path)) { throw new FileNotFoundException(path + "not been found."); } List <string> words = new List <string>(); ExcelPackage.LicenseContext = LicenseContext.NonCommercial; //Set excel license FileInfo fileInfo = new FileInfo(path); using (ExcelPackage package = new ExcelPackage(fileInfo)) { ExcelWorkbook workbook = package.Workbook; //if (workbook.Worksheets.Count == 0) throw new Exception("Cannot found worksheet in workbook."); if (sheetName != null) { ExcelWorksheet worksheet = workbook.Worksheets[sheetName]; for (int row = 2; row <= worksheet.Dimension.End.Row; row++) { words.Add(worksheet.Cells[row, 1].ToString().Trim()); } return(words); } int sheetCount = workbook.Worksheets.Count - 1; for (; sheetCount >= 0; sheetCount--) { ExcelWorksheet worksheet = workbook.Worksheets[sheetCount]; for (int row = 2; row <= worksheet.Dimension.End.Row; row++) { if (worksheet.Cells[row, 1].Value == null) { break; } words.Add(worksheet.Cells[row, 1].Value.ToString().Trim()); } } return(words); } }
public ActionResult DescargarEstadisticas(Int32 Anio) { var ruta = Server.MapPath(@"~\Files\ReporteVisita.xlsx"); try { using (FileStream fs = System.IO.File.OpenRead(ruta)) using (ExcelPackage excelPackage = new ExcelPackage(fs)) { ExcelWorkbook excelWorkBook = excelPackage.Workbook; ExcelWorksheet excelWorksheet = excelWorkBook.Worksheets.FirstOrDefault(); if (excelWorksheet != null) { var LstVisitas = context.Visita.Where(x => x.Fecha.Year == Anio).OrderByDescending(x => x.Fecha).ToList(); Int32 row = 3; foreach (var item in LstVisitas) { excelWorksheet.Cells[row, 1].Value = item.Usuario.Nombres + " " + item.Usuario.Apellidos; excelWorksheet.Cells[row, 2].Value = item.Tipo; excelWorksheet.Cells[row, 3].Value = item.Departamento.Numero; excelWorksheet.Cells[row, 4].Value = item.Edificio.Nombre; excelWorksheet.Cells[row, 5].Value = item.Fecha.ToString("dd/MM/yyyy hh:mm:ss"); row++; } } var fileStreamResult = new FileContentResult(excelPackage.GetAsByteArray(), "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"); fileStreamResult.FileDownloadName = "Reporte de Accesos_" + DateTime.Now.ToShortDateString() + ".xlsx"; return(fileStreamResult); } } catch (Exception ex) { PostMessage(MessageType.Error, ex.Message + " " + (ex.InnerException != null ? ex.InnerException.Message : "")); return(RedirectToAction("CuadroMoroso")); } }
protected void GenerateChemicalLibraryExcel(IEnumerable <ChemicalLibrarySearchResultGridViewModel> items, string fileName) { string excelTemplateFilePath = System.IO.Path.Combine(Server.MapPath("~"), @"Content\TemplateFiles\ChemicalLibraryTemplate.xlsx"); using (FileStream sourceXlsDataStream = new FileStream(excelTemplateFilePath, FileMode.Open, FileAccess.Read)) { ExcelWorkbook workbook = new ExcelWorkbook(sourceXlsDataStream); workbook.LicenseKey = Excel.GetWinnovativeExcelLicenseKey(); // Retrieve the first Worksheet ExcelWorksheet worksheet = workbook.Worksheets[0]; // Loop through Chemical GridView and Add All to the Worksheet string chemicalName = ""; string commonSynonymName = ""; int rowIndex = 2; foreach (var item in items) { // Strip out any invalid XML characters from ChemicalName // and CommonSynonymName before populating the cell: chemicalName = item.ChemicalName != null?Regex.Replace(item.ChemicalName, @"[^\u0009\u000a\u000d\u0020-\uD7FF\uE000-\uFFFD]", "") : ""; commonSynonymName = item.CommonSynonymName != null?Regex.Replace(item.CommonSynonymName, @"[^\u0009\u000a\u000d\u0020-\uD7FF\uE000-\uFFFD]", "") : ""; worksheet[rowIndex, 1].Text = chemicalName != null ? chemicalName : ""; worksheet[rowIndex, 2].Text = commonSynonymName != null ? commonSynonymName : ""; worksheet[rowIndex, 3].Text = item.CASNumber != null ? item.CASNumber : ""; worksheet[rowIndex, 4].Text = item.IsSynonym != null ? item.IsSynonym : ""; worksheet[rowIndex, 5].Text = item.CCLFQID != null ? item.CCLFQID : ""; worksheet[rowIndex, 6].Value = item.USEPASRSNumber != null ? item.USEPASRSNumber : ""; rowIndex++; } SetDownloadFileHeader("Application/vnd.openxmlformats-officedocument.spreadsheetml.sheet", fileName); workbook.Save(Response.OutputStream); Response.End(); } }
private static void CreateNamedStyles(ExcelWorkbook wb) { var titleStyle = wb.Styles.CreateNamedStyle("Title").Style; titleStyle.Font.Bold = true; titleStyle.Font.Size = 18; var h1Style = wb.Styles.CreateNamedStyle("Heading1").Style; h1Style.Font.Bold = true; h1Style.Font.Size = 15; var h2Style = wb.Styles.CreateNamedStyle("Heading2").Style; h2Style.Font.Bold = true; h2Style.Font.Size = 13; var h3Style = wb.Styles.CreateNamedStyle("Heading3").Style; h3Style.Font.Bold = true; h3Style.Font.Size = 11; }
public bool Open() { try { ep = new ExcelPackage(new FileInfo(filePath)); if (ep == null) { return(false); } book = ep.Workbook; sheetCount = book.Worksheets.Count; currentSheetIndex = 0; currentSheet = book.Worksheets[1]; ifOpen = true; } catch (Exception ex) { return(false); } return(true); }
public HttpResponseMessage Get_BangGiaChung() { string fileurl = ""; var package = GetTemplateWorkbook("DS-BangGiaChung.xlsx", "BangGiaChung.xlsx", out fileurl); ExcelWorkbook workBook = package.Workbook; if (workBook != null) { var ws = workBook.Worksheets.FirstOrDefault(); //Worksheets["DS"]; var data = BS_CUS_CRM_CONTRACT_NhomGiaPhuongTien.get_CUS_CRM_CONTRACT_NhomGiaPhuongTien(db, PartnerID, QueryStrings); var gialist = BS_CUS_CRM_CONTRACT_BangGia.get_CUS_CRM_CONTRACT_BangGia(db, PartnerID, QueryStrings); int rowid = 3; foreach (var item in data) { ws.Cells["B" + rowid].Value = item.Sort; //STT ws.Cells["C" + rowid].Value = item.Code; ws.Cells["D" + rowid].Value = item.Name; var gia = gialist.FirstOrDefault(d => d.IDNhomGiaPhuongTien == item.ID); if (gia != null) { ws.Cells["E" + rowid].Value = gia.SoGioQuyDinhCa; ws.Cells["F" + rowid].Value = gia.DonGiaCa; ws.Cells["G" + rowid].Value = gia.DonGiaTangCa; ws.Cells["H" + rowid].Formula = string.Format("F{0}*G{0}", rowid); ws.Cells["I" + rowid].Value = gia.DonGiaNgayLe; ws.Cells["J" + rowid].Formula = string.Format("F{0}*I{0}", rowid); } rowid++; } package.Save(); } return(downloadFile(fileurl)); }
public ExcelMenuPage(ExcelWorkbook workbook) { var sheet = workbook.Worksheets[menuSheetName]; if (sheet == null) { return; } var table = sheet.Tables.First(); if (table == null) { return; } menu = new RowDataTable(table); DeleteRowsWithoutLevels(); AddCorrectLevelColumn(); AddFullPathColumn(); }
public void BeginRead() { Logger.Information("BeginRead {0}", ExcelFile); if (File.Exists(ExcelFile)) { Logger.Information("ExcelFile exist"); _excelPackage = new ExcelPackage(new FileInfo(ExcelFile)); Logger.Information("Package created"); if (_excelPackage.Workbook == null) { Logger.Error("No workbook found in the specified excel"); Environment.Exit(-2); } _excelWorkbook = _excelPackage.Workbook; if (_excelWorkbook.Worksheets == null || _excelWorkbook.Worksheets.Count <= 0) { Logger.Error("No worksheets found in the specified excel"); Environment.Exit(-3); } _estimateWorksheet = _excelPackage.Workbook.Worksheets["Estimates"]; _actualWorksheet = _excelPackage.Workbook.Worksheets["Actuals"]; if (_estimateWorksheet == null) { Logger.Warning("Estimates worksheet is not found!"); } if (_actualWorksheet == null) { Logger.Warning("Actuals worksheet is not found"); } Logger.Information("Estimates, Actuals worksheets found, proceed to create command readers"); return; } Logger.Error("Specified excel file not found"); Environment.Exit(-1); }
internal ExcelVbaProject(ExcelWorkbook wb) { _wb = wb; _pck = _wb.Package.Package; References = new ExcelVbaReferenceCollection(); Modules = new ExcelVbaModuleCollection(this); var rel = _wb.Part.GetRelationshipsByType(schemaRelVba).FirstOrDefault(); if (rel != null) { Uri = UriHelper.ResolvePartUri(rel.SourceUri, rel.TargetUri); Part = _pck.GetPart(Uri); #if !MONO GetProject(); #endif } else { Lcid = 0; Part = null; } }
public static void Generate(ExcelWorkbook workbook) { var data = new Dictionary <string, string>(); var className = workbook.Name.ToWorkbookClassName(); var settings = workbook.WorkbookSettings; data.Add(TemplateKeys.NAMESPACE, Settings.ImporterNamespace); data.Add(TemplateKeys.CLASS_NAME, className); data.Add(TemplateKeys.MENU_PATH, GetMenuPath(className)); data.Add(TemplateKeys.MENU_PRIORITY, MenuConstants.Priority3.ToReplaceSafe()); data.Add(TemplateKeys.EXCEL_FILEPATH, workbook.FilePath); data.Add(TemplateKeys.IMPORT_BASEPATH, Settings.ImportAssetPath); data.Add(TemplateKeys.PREFIX_ASSETNAME, settings.PrefixAssetNames.ToReplaceSafe()); data.Add(TemplateKeys.SHEET_IMPORT_STATEMENTS, GenerateImportStatements(workbook)); data.Add(TemplateKeys.SHEET_NAMESPACE, Settings.SheetNamespace); Templates.Write( Templates.WorkbookImporterTemplate, GetFilePath(className), data); }
/// <summary> /// For each session, display the xlsx joint table with the average / minimum / maximum score for each group. /// </summary> /// <param name="data_table">List contains <see cref="PointsByGroupTable"/></param> /// <param name="filePath">The path to the file</param> /// <param name="open_after_creation">Open file after creation</param> public static void CreateReportFile(IEnumerable <PointsByGroupTable> data_table, string filePath, bool open_after_creation = false) { FileWorker.DeleteFileIfExists(filePath); FileInfo file = new FileInfo(filePath); ExcelPackage.LicenseContext = LicenseContext.NonCommercial; using (ExcelPackage excelPackage = new ExcelPackage(file)) { ExcelWorkbook excelWorkBook = excelPackage.Workbook; foreach (var data in data_table) { ExcelWorksheet workSheet = excelPackage.Workbook.Worksheets.Add(data.SessionPeriod); SetHeaderStyle(workSheet, 1); SetColumnWidth(workSheet, 1, 4, 15, 10, 10, 10); SetSheetStyle(workSheet, 1, 1, 5, 1); SetColumnNames(workSheet, 1, 1, "№", "Group Name", "Minimum Score", "Average Score", "Maximum Score"); var list = data.pointsByGroups.ToList(); int i = 2, num = 1; foreach (var item in list) { SetRowStyle(workSheet, 1, i, 5, i); workSheet.Cells[i, 1].Value = num++; workSheet.Cells[i, 2].Value = item.GroupName; workSheet.Cells[i, 3].Value = item.MinimumScore; workSheet.Cells[i, 4].Value = item.AverageScore; workSheet.Cells[i, 5].Value = item.MaximumScore; i++; } workSheet.Cells["A" + i.ToString() + ":E" + i.ToString()].Style.Border.Top.Style = OfficeOpenXml.Style.ExcelBorderStyle.Medium; } excelPackage.Save(); if (open_after_creation) { FileWorker.Open(filePath); } } }
public static XDocument BuildXml(ExcelWorkbook xlWorkbook) { SurveyXml BuildXml = new SurveyXml(); XmlDocument RootXml = new XmlDocument(); string RootXmlpath = System.Web.HttpContext.Current.Server.MapPath("~\\Content\\Xml\\RootTemplate.xml"); RootXml.Load(RootXmlpath); XDocument XRoot = SurveyXml.ToXDocument(RootXml); XDocument NewXmlDoc = new XDocument(XRoot); // Get page list from Excel List <SurveyPageDTO> PageList = SetPageValues(xlWorkbook); // Build xml var xml = BuildXml.BuildNewXml(PageList, NewXmlDoc); return(xml); }
public ProcessWdiData(string inputFile) { System.Globalization.CultureInfo ci = new System.Globalization.CultureInfo(System.Threading.Thread.CurrentThread.CurrentCulture.Name); ci.NumberFormat.NumberDecimalSeparator = "."; System.Threading.Thread.CurrentThread.CurrentCulture = ci; Program.LogMessage("Opening ExcelPackage..."); _excelPackage = new ExcelPackage(new FileInfo(inputFile)); //_excelPackage = new ExcelPackage(File.Open(inputFile, FileMode.Open, FileAccess.Read)); _excelWorkbook = _excelPackage.Workbook; //Program.LogMessage("Opening ExcelReaderFactory..."); //var excelDataReader = ExcelReaderFactory.CreateOpenXmlReader(stream); //excelDataReader.IsFirstRowAsColumnNames = true; //_inputFileDataSet = excelDataReader.AsDataSet(); //_inputConnectionString = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + inputFile + ";Extended Properties=\"Excel 12.0 Xml;HDR=YES;READONLY=TRUE;IMEX=1;ImportMixedTypes=Text;TypeGuessRows=0\""; }
public void AdaptDescription(string sheetName) { var fileinfo = new FileInfo(this.filePath); if (fileinfo.Exists) { using (ExcelPackage excelPackage = new ExcelPackage(fileinfo)) { ExcelWorkbook excelWorkBook = excelPackage.Workbook; ExcelWorksheet excelWorksheet = excelWorkBook.Worksheets.Where(s => s.Name == sheetName).FirstOrDefault(); var column = excelWorksheet.Cells["1:1"].First(c => c.Value.ToString() == "Description").Start.Column; int rangeMaxRows = excelWorksheet.Dimension.End.Row; fileLogger.LogInfo($"-------------------------- DESCRIPTION ADAPTATION: {sheetName} --------------------------", infofilePathToWrite); for (int i = 1; i <= rangeMaxRows; i++) { string cellStr = excelWorksheet.Cells[i, column].Value.ToString(); cellStr = Regex.Replace(cellStr, @"[^a-zA-Z0-9-]+", " "); if (cellStr.Length > DescriptionStringMaxLength) { fileLogger.LogInfo($"LENGTH CUT; length > 135", infofilePathToWrite); cellStr = cellStr.Substring(0, DescriptionStringMaxLength); } if (cellStr != excelWorksheet.Cells[i, column].Value.ToString()) { fileLogger.LogInfo($"{excelWorksheet.Cells[i, column].Value } -->", infofilePathToWrite); fileLogger.LogInfo(cellStr, infofilePathToWrite); } excelWorksheet.Cells[i, column].Value = cellStr; } excelPackage.Save(); } } }
public void Import(byte[] content) { MemoryStream excelStream = new MemoryStream(content); using (var package = new ExcelPackage(excelStream)) { ExcelWorkbook workBook = package.Workbook; if (workBook != null) { if (workBook.Worksheets.Count > 0) { int startRow = 2; int startCol = 1; int errorCount = 0; ExcelWorksheet currentWorksheet = workBook.Worksheets[1]; for (int row = startRow; row <= currentWorksheet.Dimension.End.Row; row++) { try { if (currentWorksheet.Cells[row, startCol].Value == null) { break; } } catch (Exception ex) { errorCount++; // ignore and continue; } } if (errorCount == 0) { _context.SaveChanges(); } } } } }
private void CreateASheets(ExcelWorkbook workbook) { var originalSheetA = workbook.Worksheets["А"]; var originalSheetA1 = workbook.Worksheets["А1"]; var companiesForSheetA = reportCompanies .Where(x => x.ControlGrounds.Any()) .Where(x => x.ProjectCompany.State == State.Foreign); foreach (var company in companiesForSheetA) { var sheetA = workbook.Worksheets.Add( originalSheetA.Name + SheetNameDelimeter + company.ShortNumber, originalSheetA); sheets.Add(new KikSheetA(sheetA, company, ++pageNumber)); var sheetA1 = workbook.Worksheets.Add( originalSheetA1.Name + SheetNameDelimeter + company.ShortNumber, originalSheetA1); sheets.Add(new KikSheetA1(sheetA1, company, ++pageNumber)); } workbook.Worksheets.Delete(originalSheetA); workbook.Worksheets.Delete(originalSheetA1); }
/// <summary> /// Append the required format depending on the COBieAllowedType /// </summary> /// <param name="type">COBieAllowedType</param> /// <param name="cellStyle">ICellStyle, style to ally formate to</param> private void AppendFormat(COBieAllowedType type, ICellStyle cellStyle) { string formatString = null; switch (type) { case COBieAllowedType.ISODate: formatString = "yyyy-MM-dd"; break; case COBieAllowedType.ISODateTime: formatString = "yyyy-MM-ddThh:mm:ss"; break; case COBieAllowedType.AlphaNumeric: break; case COBieAllowedType.Email: break; case COBieAllowedType.Numeric: break; case COBieAllowedType.Text: break; case COBieAllowedType.AnyType: break; default: break; } if (formatString != null) { IDataFormat dataFormat = ExcelWorkbook.CreateDataFormat(); cellStyle.DataFormat = dataFormat.GetFormat(formatString); } }
private static void Main(string[] args) { // open excel DirectoryInfo sourceDir = new DirectoryInfo(Directory.GetCurrentDirectory()); FileInfo sourceExcel = sourceDir.GetFiles("*.xlsx").FirstOrDefault(); List <Shelf> shelves = new List <Shelf>(); // for each file in the source directory perform conversion and html creation using (ExcelPackage package = new ExcelPackage(sourceExcel)) { ExcelWorkbook wb = package.Workbook; List <MappingData> creationMetadata = GetCreationMetadata(wb); shelves = CreateShelves(creationMetadata, wb); } StringBuilder result = new StringBuilder(); foreach (Shelf shelf in shelves) { result.Append(shelf.GenerateHTML()); } string htmlShelves = result.ToString(); string mainTemplate = File.ReadAllText($"{Directory.GetCurrentDirectory()}\\magazzinoTemplate.html"); string finalDocument = mainTemplate.Replace("%dataPlaceholder%", htmlShelves); File.WriteAllText($"{Directory.GetCurrentDirectory()}\\final.html", finalDocument); // send to e-mail Console.WriteLine("Operation Complete!"); Console.ReadKey(); }
public void ProcessPriceList(ExcelWorkbook workbook) { ExcelWorksheet worksheet = workbook.Worksheets[3]; for (int i = 6; i <= worksheet.Cells.Rows; i++) { if (worksheet.Cells[i, 1] != null && worksheet.Cells[i, 1].Value != null) { string category = worksheet.Cells[i, 1].Value as string; string subCategory = worksheet.Cells[i, 2].Value as string; string name = worksheet.Cells[i, 3].Value as string; Category cat = FindCreateCategory(category, subCategory); PriceListLineItem item = context.PriceList.FirstOrDefault(pl => pl.CategoryId == cat.Id && pl.Name == name); if (item == null) { item = new PriceListLineItem(); context.PriceList.Add(item); item.CategoryId = cat.Id; item.Name = name; item.MaterialSupplier = worksheet.Cells[i, 4].Value as string; item.ModelNumber = worksheet.Cells[i, 5].Value?.ToString(); item.UOM = (UOM)Enum.Parse(typeof(UOM), (worksheet.Cells[i, 6].Value as string)); item.MaterialUnitCost = (decimal)(((double?)(worksheet.Cells[i, 7].Value) ?? 0.0)); item.Tax = (decimal)((double)worksheet.Cells[i, 8].Value); item.MaterialTotalCost = (decimal)((double)worksheet.Cells[i, 9].Value); item.LaborRate = (decimal)((double)worksheet.Cells[i, 10].Value); item.LaborHours = (decimal)(((double?)(worksheet.Cells[i, 11].Value) ?? 0.0)); item.LaborSubtotal = (decimal)((double)worksheet.Cells[i, 12].Value); item.LaborTotal = (decimal)((double)worksheet.Cells[i, 13].Value); item.ProfitMargin = (decimal)((double)worksheet.Cells[i, 14].Value); item.ProfitTotal = (decimal)((double)worksheet.Cells[i, 15].Value); item.TotalCost = (decimal)((double)worksheet.Cells[i, 16].Value); } } } }
private static void SetTabExpenses(ExcelWorkbook workbook, List <EntryExpenses> entries, string title) { var ws = workbook.Worksheets.Add(title); ws.Cells["A1:E1"].Style.Border.Bottom.Style = ws.Cells["E1"].Style.Border.Right.Style = ExcelBorderStyle.Thick; ws.Cells["A1:E1"].Style.Font.Bold = true; ws.View.ShowGridLines = false; ws.DefaultColWidth = 40; ws.Cells["A1"].Value = "Descrição"; ws.Cells["B1"].Value = "Valor"; ws.Cells["C1"].Value = "Data"; ws.Cells["D1"].Value = "Categoria"; ws.Cells["E1"].Value = "Pagamento"; for (int i = 0, row = 2; i < entries.Count; i++, row++) { var entry = entries[i]; ws.Cells[$"A{row}"].Value = entry.Description; ws.Cells[$"B{row}"].Value = entry.EntryType == EntryType.Expense ? entry.Value * -1 : entry.Value; ws.Cells[$"C{row}"].Value = entry.Date; ws.Cells[$"D{row}"].Value = entry.CaptionCategories; ws.Cells[$"E{row}"].Value = entry.PaymentMethod.ToString(); ws.Cells[$"B{row}"].Style.Numberformat.Format = currencyFormat; ws.Cells[$"C{row}"].Style.Numberformat.Format = "dd/mm/yyyy"; } var range = ws.Cells[$"A1:E{entries.Count + 1}"]; range.Style.Border.Top.Style = range.Style.Border.Left.Style = range.Style.Border.Bottom.Style = range.Style.Border.Right.Style = ExcelBorderStyle.Thin; }
private List <Person> GetPeople(ExcelWorkbook det) { List <Person> persons = new List <Person>(); ExcelWorksheet ws = det.Worksheets[@"Persons"]; int firstNameCol = 3; int lastNameCol = 2; int roleRow = 6; int lastRow = ws.Dimension.End.Row; for (int idx = theProcessor.DataStartRow; idx < lastRow; idx++) { if (RowEmpty(idx, ws)) { break; } string ln = ws.Cells[idx, lastNameCol].Value.ToString(); string fn = ws.Cells[idx, firstNameCol].Value.ToString(); string role = ws.Cells[idx, roleRow].Value?.ToString().ToLower() ?? ""; if (ln.Length != 0 && fn.Length != 0) { persons.Add(new Person { LastName = ln, FirstName = fn, IsPI = role.Equals("pi") }); } } persons.Sort(); if (theProcessor.IsMaster) { List <Person> dupfree = persons.Distinct(new PersonComparator()).ToList <Person>(); persons = dupfree; } persons.Sort(); return(persons); }
public HttpResponseMessage Get_HoiNghiHoiThao() { string fileurl = ""; var package = GetTemplateWorkbook("DS-BaoCaoHoiNghiHoiThao.xlsx", "DS-BaoCaoHoiNghiHoiThao.xlsx-" + DateTime.Now.ToString("HHmmss") + ".xlsx", out fileurl); ExcelWorkbook workBook = package.Workbook; if (workBook != null) { var ws = workBook.Worksheets.FirstOrDefault(); //Worksheets["DS"]; var data = BS_PRO_HoiNghiHoiThao_DangKyDeTai.getExcel_PRO_HoiNghiHoiThao_DangKyDeTaiTheoHoiNghi(db, QueryStrings); int rowid = 3; int sort = 1; foreach (var item in data) { int col = 2; ws.Cells[rowid, col].Value = sort; col++; ws.Cells[rowid, col].Value = item.TenHoiNghi; col++; ws.Cells[rowid, col].Value = item.DiaDiem; col++; ws.Cells[rowid, col].Value = item.ThoiGian.ToString("dd/MM/yyy HH:mm:ss"); col++; ws.Cells[rowid, col].Value = item.ThoiGianHetHan.Value.ToString("dd/MM/yyy HH:mm:ss"); col++; ws.Cells[rowid, col].Value = item.TongSoNguoiDangKy; col++; ws.Cells[rowid, col].Value = item.TongSoDeTaiDangKy; col++; ws.Cells[rowid, col].Value = item.TenNCV; col++; ws.Cells[rowid, col].Value = item.TenDeTai; col++; ws.Cells[rowid, col].Value = item.HinhThucDangKy; col++; ws.Cells[rowid, col].Value = item.TrangThai; col++; ws.Cells[rowid, col].Value = !string.IsNullOrEmpty(item.TenNCV) ? item.CreatedDate.ToString("dd/MM/yyy HH:mm:ss") : ""; col++; rowid++; sort++; } package.Save(); } return(downloadFile(fileurl)); }
/// <summary> /// 打开Package并初始化WorkBook,WorkSheet /// </summary> public void OpenReportTempalte() { if (package != null) { CloseReportTemplate(); } // Create an instance of Microsoft Excel, make it visible, // and open Book1.xls. //m_oExcelApp = new Excel.ApplicationClass(); //m_oExcelApp = GetExcelApplication(); //workbooks = m_oExcelApp.Workbooks; //edit by Ly 2015.06.17 GetExcelPackage(); workbook = package.Workbook; worksheet = workbook.Worksheets[m_nReportIndex]; //m_oExcelApp.AskToUpdateLinks = false; // IMPORTANT: IF YOU ARE USING EXCEL Version >= 10.0 Use function // prototype in "EXCEL VERSION 10.0" section. // For Excel Version 9.0 use default "EXCEL VERSION = 9.0". // This application is not tested with versions lower than Excel 9.0 // Or greater than 10.0 // EXCEL VERSION 10.0 //workbook = workbooks.Open(m_sReportTemplate, m_oMissing, m_oMissing, // m_oMissing, m_oMissing, m_oMissing, m_oMissing, m_oMissing, m_oMissing, // m_oMissing, m_oMissing, m_oMissing, m_oMissing, m_oMissing, m_oMissing); // END EXCEL VERSION 10.0 // EXCEL VERSION 9.0 //workbook = workbooks.Open(m_sReportTemplate, m_oMissing, m_oMissing, // m_oMissing, m_oMissing, m_oMissing, m_oMissing, m_oMissing, m_oMissing, // m_oMissing, m_oMissing, m_oMissing, m_oMissing,m_oMissing,m_oMissing); // END EXCEL VERSION 9.0 //worksheet = (_Worksheet)workbook.Worksheets[m_nReportIndex]; }
private void Initialize() { if (NoTemplate) { this.package = new ExcelPackage(); } else { FileInfo templateFile = new FileInfo(mappedHomePath + fileName); this.package = new ExcelPackage(templateFile); } this.workbook = this.Package.Workbook; if (workbook.Worksheets.Count > 0) { this.worksheet = this.Workbook.Worksheets.First(); } else { this.worksheet = this.workbook.Worksheets.Add("Content"); } }
public void SaveExcel(Project _Project) { Console.WriteLine(" 保存数据至Excel中......"); string strPath = @"Xls\"; if (!Directory.Exists(strPath)) { Directory.CreateDirectory(strPath); } strPath = strPath + _Project.ProjectPresell + "_" + _Project.ProjectID + "_" + _Project.ProjectName + ".xls"; //保存的路径和文件名 Stopwatch watch = new Stopwatch(); watch.Start(); ExcelWorkbook wbook = new ExcelWorkbook(); wbook.SetDefaultFont("Arial", 10); ExcelWorksheet wsheet; ExcelCellStyle style; wbook.CreateSheet("DB_ESTATE"); wsheet = wbook.GetSheet("DB_ESTATE"); style = wbook.CreateStyle(); style.Pattern = EnumFill.Solid; style.PatternForeColour = EnumColours.Grey25; style.Font.Size = 11; style.Font.Bold = true; string[] arrESTATE_Title = { "物业ID", "城市", "行政区", "行政区英文名称", "片区", "片区英文名称", "物业名称", "物业名称拼音索引", "物业名称英文名称", "物业别名", "物业地址", "物业类型及规模", "环线位置", "周围环境", "楼盘概况", "占地面积", "总建筑面积", "土地使用分区", "容积率", "绿化率", "项目规划", "建筑类型", "建筑设计", "园林设计", "车位数量", "目标客户群", "物业管理方式", "核心卖点", "项目自身设施", "项目优劣势", "周边配套"}; for (int o = 0; o < arrESTATE_Title.Length; o++) { wsheet.AddCell((ushort)(o + 1), 1, arrESTATE_Title[o], style); } DB_ESTATE dbEstate = _Project.Estate; wsheet.AddCell(1, 2, dbEstate.WYID); wsheet.AddCell(2, 2, dbEstate.CS); wsheet.AddCell(3, 2, dbEstate.XZQ); wsheet.AddCell(5, 2, dbEstate.XZQ); wsheet.AddCell(7, 2, dbEstate.WYMC); wsheet.AddCell(11, 2, dbEstate.WYDZ); wsheet.AddCell(16, 2, dbEstate.ZDMJ); wsheet.AddCell(17, 2, dbEstate.ZJZMJ); wbook.CreateSheet("DB_BLOCK"); wsheet = wbook.GetSheet("DB_BLOCK"); style = wbook.CreateStyle(); style.Pattern = EnumFill.Solid; style.PatternForeColour = EnumColours.Grey25; style.Font.Size = 11; style.Font.Bold = true; string[] arrBLOCK_Title = { "物业ID", "大楼ID", "期数", "期数英文名称", "楼栋中文名称", "楼栋英文名称", "楼栋别名1", "楼栋别名2", "座落位置", "楼栋结构", "竣工日期", "总单元数目", "建筑总面积", "用途(L1)", "用途(L2)", "车位类型", "户外车位", "有盖车位", "电梯数量", "扶梯数量", "售楼书号", "物业管理费", "物业管理费币值", "管理公司", "发展商", "单栋大楼名称地址", "楼盘简述", "路/街/里/弄", "路/街/里/弄(英文)", "街号由", "街号由尾码", "街号至", "街号至尾码", "县", "镇","村", "房屋所有权证号", "土地使用证证号", "地块编号", "土地使用权起始日", "土地使用权终止日", "土地使用权条款", "地上层数", "地下层数", "区域类别", "周围环境", "发展趋势", "楼栋概况", "楼梯及设施", "宗地号(报告相关)", "使用条款(报告相关)", "竣工日期(报告相关)", "市场可售性备注"}; for (int o = 0; o < arrBLOCK_Title.Length; o++) { wsheet.AddCell((ushort)(o + 1), 1, arrBLOCK_Title[o], style); } ushort iRow = 1; foreach(DB_BLOCK block in _Project.BlockList.ToArray()){ iRow++; wsheet.AddCell(1, iRow, block.WYID); wsheet.AddCell(2, iRow, block.DLID); wsheet.AddCell(5, iRow, block.LDZWMC); wsheet.AddCell(11, iRow, block.JGRQ); wsheet.AddCell(25, iRow, block.FZS); } wbook.CreateSheet("DB_FLAT"); wsheet = wbook.GetSheet("DB_FLAT"); style = wbook.CreateStyle(); style.Pattern = EnumFill.Solid; style.PatternForeColour = EnumColours.Grey25; style.Font.Size = 10; style.Font.Bold = true; string[] strDBFLAT_Title = { "大楼ID", "层数列(数字)", "层数名", "单元", "单元名称", "建筑面积(平方米)", "套内面积(平方米)", "天井(平方米)", "露台(平方米)", "平台(平方米)", "天台(平方米)", "花园(平方米)", "阳台(平方米)", "窗台(平方米)", "阁楼(平方米)", "储物室(平方米)", "空调室(平方米)", "用途(L1)", "用途(L2)", "房", "厅", "座向", "单元结构", "备注", "法定用途", "合并情况"}; for (int o = 0; o < strDBFLAT_Title.Length; o++) { wsheet.AddCell((ushort)(o + 1), 1, strDBFLAT_Title[o], style); } iRow = 1; foreach (DB_FLAT flat in _Project.FlatList.ToArray()) { iRow++; wsheet.AddCell(1, iRow, flat.DLID); wsheet.AddCell(2, iRow, flat.CSL); wsheet.AddCell(3, iRow, flat.CSM); wsheet.AddCell(4, iRow, flat.CSL + flat.DY); wsheet.AddCell(5, iRow, flat.DY); wsheet.AddCell(6, iRow, flat.JZMJ); wsheet.AddCell(7, iRow, flat.TNMJ); wsheet.AddCell(18, iRow, flat.YTL1); wsheet.AddCell(20, iRow, flat.F); wsheet.AddCell(21, iRow, flat.T); } wbook.Save(strPath); watch.Stop(); }
/// <summary> /// This method follows the calculation chain to get the order of the calculation /// Goto (!) is used internally to prevent stackoverflow on extremly larget dependency trees (that is, many recursive formulas). /// </summary> /// <param name="depChain">The dependency chain object</param> /// <param name="lexer">The formula tokenizer</param> /// <param name="wb">The workbook where the formula comes from</param> /// <param name="ws">The worksheet where the formula comes from</param> /// <param name="f">The cell function object</param> /// <param name="options">Calcultaiton options</param> private static void FollowChain(DependencyChain depChain, ILexer lexer, ExcelWorkbook wb, ExcelWorksheet ws, FormulaCell f, ExcelCalculationOption options) { Stack<FormulaCell> stack = new Stack<FormulaCell>(); iterateToken: while (f.tokenIx < f.Tokens.Count) { var t = f.Tokens[f.tokenIx]; if (t.TokenType == TokenType.ExcelAddress) { var adr = new ExcelFormulaAddress(t.Value); if (adr.Table != null) { adr.SetRCFromTable(ws._package, new ExcelAddressBase(f.Row, f.Column, f.Row, f.Column)); } if (adr.WorkSheet == null && adr.Collide(new ExcelAddressBase(f.Row, f.Column, f.Row, f.Column))!=ExcelAddressBase.eAddressCollition.No) { throw (new CircularReferenceException(string.Format("Circular Reference in cell {0}", ExcelAddressBase.GetAddress(f.Row, f.Column)))); } if (adr._fromRow > 0 && adr._fromCol > 0) { if (string.IsNullOrEmpty(adr.WorkSheet)) { if (f.ws == null) { f.ws = ws; } else if (f.ws.SheetID != f.SheetID) { f.ws = wb.Worksheets.GetBySheetID(f.SheetID); } } else { f.ws = wb.Worksheets[adr.WorkSheet]; } if (f.ws != null) { f.iterator = new CellsStoreEnumerator<object>(f.ws._formulas, adr.Start.Row, adr.Start.Column, adr.End.Row, adr.End.Column); goto iterateCells; } } } else if (t.TokenType == TokenType.NameValue) { string adrWb, adrWs, adrName; ExcelNamedRange name; ExcelAddressBase.SplitAddress(t.Value, out adrWb, out adrWs, out adrName, f.ws==null ? "" : f.ws.Name); if (!string.IsNullOrEmpty(adrWs)) { if (f.ws == null) { f.ws = wb.Worksheets[adrWs]; } if(f.ws.Names.ContainsKey(t.Value)) { name = f.ws.Names[adrName]; } else if (wb.Names.ContainsKey(adrName)) { name = wb.Names[adrName]; } else { name = null; } if(name != null) f.ws = name.Worksheet; } else if (wb.Names.ContainsKey(adrName)) { name = wb.Names[t.Value]; if (string.IsNullOrEmpty(adrWs)) { f.ws = name.Worksheet; } } else { name = null; } if (name != null) { if (string.IsNullOrEmpty(name.NameFormula)) { if (name.NameValue == null) { f.iterator = new CellsStoreEnumerator<object>(f.ws._formulas, name.Start.Row, name.Start.Column, name.End.Row, name.End.Column); goto iterateCells; } } else { var id = ExcelAddressBase.GetCellID(name.LocalSheetId, name.Index, 0); if (!depChain.index.ContainsKey(id)) { var rf = new FormulaCell() { SheetID = name.LocalSheetId, Row = name.Index, Column = 0 }; rf.Formula = name.NameFormula; rf.Tokens = name.LocalSheetId == -1 ? lexer.Tokenize(rf.Formula).ToList() : lexer.Tokenize(rf.Formula, wb.Worksheets.GetBySheetID(name.LocalSheetId).Name).ToList(); depChain.Add(rf); stack.Push(f); f = rf; goto iterateToken; } else { if (stack.Count > 0) { //Check for circular references foreach (var par in stack) { if (ExcelAddressBase.GetCellID(par.SheetID, par.Row, par.Column) == id) { throw (new CircularReferenceException(string.Format("Circular Reference in name {0}", name.Name))); } } } } } } } f.tokenIx++; } depChain.CalcOrder.Add(f.Index); if (stack.Count > 0) { f = stack.Pop(); goto iterateCells; } return; iterateCells: while (f.iterator != null && f.iterator.Next()) { var v = f.iterator.Value; if (v == null || v.ToString().Trim() == "") continue; var id = ExcelAddressBase.GetCellID(f.ws.SheetID, f.iterator.Row, f.iterator.Column); if (!depChain.index.ContainsKey(id)) { var rf = new FormulaCell() { SheetID = f.ws.SheetID, Row = f.iterator.Row, Column = f.iterator.Column }; if (f.iterator.Value is int) { rf.Formula = f.ws._sharedFormulas[(int)v].GetFormula(f.iterator.Row, f.iterator.Column, ws.Name); } else { rf.Formula = v.ToString(); } rf.ws = f.ws; rf.Tokens = lexer.Tokenize(rf.Formula, f.ws.Name).ToList(); ws._formulaTokens.SetValue(rf.Row, rf.Column, rf.Tokens); depChain.Add(rf); stack.Push(f); f = rf; goto iterateToken; } else { if (stack.Count > 0) { //Check for circular references foreach (var par in stack) { if (ExcelAddressBase.GetCellID(par.ws.SheetID, par.iterator.Row, par.iterator.Column) == id) { if (options.AllowCirculareReferences == false) { throw (new CircularReferenceException(string.Format("Circular Reference in cell {0}!{1}", par.ws.Name, ExcelAddress.GetAddress(f.Row, f.Column)))); } else { f = stack.Pop(); goto iterateCells; } } } } } } f.tokenIx++; goto iterateToken; }
/// <summary> /// Serialize constructor from a file /// </summary> /// <param name="file">save file as...</param> public Serializing(string file) { Console.WriteLine("Serialize activating..."); if (string.IsNullOrEmpty(file)) throw new ArgumentNullException("file"); using (var stream = File.Open(file, FileMode.Open)) { var serial = new XmlSerializer(typeof(ExcelWorkbook)); book = (ExcelWorkbook) serial.Deserialize(stream); } if (book == null) throw new NullReferenceException("template must not be null. may be failed to making excel template."); if (book.ConnectionString != null) { this.conn = new ConnectClass(DbProviderFactories.GetFactory(book.ConnectionString.Type), book.ConnectionString.Text); if (book.ConnectionString.Timeout < 0) conn.CommandTimeout = 0; else if (book.ConnectionString.Timeout < 0) conn.CommandTimeout = book.ConnectionString.Timeout; } Console.WriteLine("Serialize initialzed."); }
/// <summary> /// Copies existing <see cref="ExcelFunction"/>´s from one workbook to another. /// </summary> /// <param name="otherWorkbook">The workbook containing the forumulas to be copied.</param> public void CopyFunctionsFrom(ExcelWorkbook otherWorkbook) { var functions = otherWorkbook.FormulaParserManager.GetImplementedFunctions(); foreach (var func in functions) { AddOrReplaceFunction(func.Key, func.Value); } }
static void SaveFileXls(string strYear, string strMonth) { Console.WriteLine(""); Console.WriteLine("开始查询" + strYear + "年" + strMonth + "月数据"); string strHtml = ""; Estate[] arrEstate; Stopwatch watch = new Stopwatch(); watch.Start(); ExcelWorkbook wbook; ExcelWorksheet wsheet; ExcelCellStyle style; //style.BottomLineStyle = EnumLineStyle.Thick; //style.TopLineStyle = EnumLineStyle.Medium; DateTime dt = System.DateTime.Now; wbook = new ExcelWorkbook(); wbook.SetDefaultFont("Arial", 10); wbook.CreateSheet("详细成交数据"); wsheet = wbook.GetSheet("详细成交数据"); wbook.SetActiveSheet = "详细成交数据"; style = wbook.CreateStyle(); style.Pattern = EnumFill.Solid; style.PatternForeColour = EnumColours.Grey25; style.Font.Size = 11; style.Font.Bold = true; string[] strDBBP_Title = { "区域", "物业地址", "栋苑", "面积(㎡)", "成交价(万)", "单价(元/㎡)", "成交日期" }; for (int o = 0; o < strDBBP_Title.Length; o++) { wsheet.AddCell((ushort)(o + 1), 1, strDBBP_Title[o], style); } int intRow = 1; int intMaxPage = 5; int intPage = 0; int intOffset = 0; do { intPage++; intOffset = (intPage - 1) * 20; strHtml = GetXmlHttp(string.Format(strUrl, strYear, strMonth, intOffset.ToString())); arrEstate = GetEstate(strHtml); for (int o = 0; o < arrEstate.Length; o++) { if (arrEstate[o] != null) { intRow++; ushort intX = (ushort)intRow; wsheet.AddCell(1, intX, arrEstate[o].QY); wsheet.AddCell(2, intX, arrEstate[o].WYDZ); wsheet.AddCell(3, intX, arrEstate[o].DY); wsheet.AddCell(4, intX, arrEstate[o].MJ); wsheet.AddCell(5, intX, arrEstate[o].CJJ); wsheet.AddCell(6, intX, arrEstate[o].DJ); wsheet.AddCell(7, intX, arrEstate[o].CJRQ); } else break; } if (intPage == 1) { string strMaxPage = GetNoHtml(GetstrCenter(strHtml, "pageJump_Start", "pageJump_End")); strMaxPage = GetstrCenter(strMaxPage, "个主题 第", "页"); intMaxPage = int.Parse(strMaxPage.Split('/')[1]); } } while (intPage < intMaxPage); string strPath = @"Xls\"; if (!Directory.Exists(strPath)) { Directory.CreateDirectory(strPath); } strPath = strPath + "成交数据" + strYear + "_" + strMonth + ".xls"; //保存的路径和文件名 Console.WriteLine("" + strYear + "年" + strMonth + "月数据已经产生。"); wbook.Save(strPath); watch.Stop(); }
static void SaveFileXls(string strYear, string strMonth, string strDay) { Console.WriteLine(""); Console.WriteLine("开始查询" + strYear + "年" + strMonth + "月" + strDay + "日的出售数据"); List<EstateSell> listEstateSell = new List<EstateSell>(); Stopwatch watch = new Stopwatch(); watch.Start(); ExcelWorkbook wbook; ExcelWorksheet wsheet; ExcelCellStyle style; //style.BottomLineStyle = EnumLineStyle.Thick; //style.TopLineStyle = EnumLineStyle.Medium; DateTime dt = System.DateTime.Now; wbook = new ExcelWorkbook(); wbook.SetDefaultFont("Arial", 10); wbook.CreateSheet("今日出售数据"); wsheet = wbook.GetSheet("今日出售数据"); wbook.SetActiveSheet = "今日出售数据"; style = wbook.CreateStyle(); style.Pattern = EnumFill.Solid; style.PatternForeColour = EnumColours.Grey25; style.Font.Size = 11; style.Font.Bold = true; string[] strDBBP_Title = { "标题", "区域", "楼盘", "户型", "面积(㎡)", "成交价(万)", "单价(元/㎡)", "时间" }; for (int o = 0; o < strDBBP_Title.Length; o++) { wsheet.AddCell((ushort)(o + 1), 1, strDBBP_Title[o], style); } int intRow = 1; int intMaxPage = 1000; int intPage = 0; int intOffset = 0; int intError = 0; bool blState = true; do { intPage++; intOffset = (intPage - 1) * 24; //intOffset = 1080; try { //if (intPage == 1) //{ // string strMaxPage = GetNoHtml(GetstrCenter(strHtml, "共<span class=\"zi_333333_12\">", "</span>")); // intMaxPage = int.Parse(Math.Ceiling(int.Parse(strMaxPage) / 24.0).ToString()); //} //Console.WriteLine("intMaxPage : " + intMaxPage); Console.Write(" 加载第 " + intPage + " 页数据"); string s = GetXmlHttp(string.Format(strUrl, intOffset.ToString())); List<EstateSell> listTemp = GetarrEstateSell(s); DateTime inputSJ = DateTime.Parse(strYear + "-" + strMonth + "-" + strDay); for (int o = 0; o < listTemp.Count; o++) { if (listTemp[o] != null) { DateTime searchSJ = DateTime.Parse(listTemp[o].SJ); if (inputSJ == searchSJ) { listEstateSell.Add(listTemp[o]); } else if (searchSJ < inputSJ) { blState = false; break; } } else break; } Console.WriteLine("成功。"); intError = 0; } catch (Exception ex) { Console.WriteLine("失败。错误提示:" + ex.Message); intError++; if (intError < 3) { Console.WriteLine(" 等待10秒后重新加载..."); Thread.Sleep(10000); intPage--; } else { Console.WriteLine(" 连续加载3次失败,跳过当前页。"); } } } while (intPage < intMaxPage & blState); foreach (EstateSell es in listEstateSell) { intRow++; ushort intX = (ushort)intRow; wsheet.AddCell(1, intX, es.BT); wsheet.AddCell(2, intX, es.QY); wsheet.AddCell(3, intX, es.LP); wsheet.AddCell(4, intX, es.HX); wsheet.AddCell(5, intX, es.MJ); wsheet.AddCell(6, intX, es.CJJ); wsheet.AddCell(7, intX, es.DJ); wsheet.AddCell(8, intX, es.SJ); } string strPath = @"Xls\"; if (!Directory.Exists(strPath)) { Directory.CreateDirectory(strPath); } strPath = strPath + "出售数据" + strYear + "-" + strMonth + "-" + strDay + ".xls"; //保存的路径和文件名 Console.WriteLine(""); Console.WriteLine("" + strYear + "年" + strMonth + "月" + strDay + "日的数据已经产生。"); wbook.Save(strPath); watch.Stop(); }
public ExcelRange(ExcelWorkbook wb, ExcelSheet sheet) { throw new NotImplementedException("Write test first!"); }