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!");
}
