/***************************************************** * xls read write * *****************************************************/ private void Form1_Load(object sender, EventArgs e) { xlsPath = parentMF.mainFolderPath + "\\Test.xls"; // create xls if not exists if (!File.Exists(xlsPath)) { wb = HSSFWorkbook.Create(InternalWorkbook.CreateWorkbook()); // create sheet sh = (HSSFSheet)wb.CreateSheet("Sheet1"); // 1 rows, 2 columns for (int i = 0; i < dgwValidateScreen.RowCount; i++) { var r = sh.CreateRow(i); for (int j = 0; j < 5; j++) { r.CreateCell(j); } } using (var fs = new FileStream(xlsPath, FileMode.Create, FileAccess.Write)) { wb.Write(fs); } } using (var fs = new FileStream(xlsPath, FileMode.Open, FileAccess.Read)) { wb = new HSSFWorkbook(fs); //for (int i = 0; i < wb.Count; i++) //{ // comboBox1.Items.Add(wb.GetSheetAt(i).SheetName); //} } }
static public void ExportToXLS(SpreadsheetComponentData spreadsheet, string path) { CultureInfo cc = Thread.CurrentThread.CurrentCulture, cuc = Thread.CurrentThread.CurrentUICulture; CultureInfo ci = new CultureInfo("en-US"); Thread.CurrentThread.CurrentCulture = ci; Thread.CurrentThread.CurrentUICulture = ci; HSSFWorkbook wb = HSSFWorkbook.Create(InternalWorkbook.CreateWorkbook()); HSSFSheet sh = (HSSFSheet)wb.CreateSheet("Sheet1"); // column header var headerFont = wb.CreateFont(); headerFont.Boldweight = (short)FontBoldWeight.Bold; var headerStyle = wb.CreateCellStyle(); headerStyle.SetFont(headerFont); bool hasHeader = false; if (spreadsheet.HasCustomLabeledColumnHeader()) { var r = sh.CreateRow(0); for (int col = 0; col < spreadsheet.columnDatas.Count; ++col) { var cell = r.CreateCell(col); cell.CellStyle = headerStyle; cell.SetCellValue(spreadsheet.columnDatas[col].HasCustomLabel() ? spreadsheet.columnDatas[col].label : SpreadsheetComponentData.GetDefaultColumnName(col)); } hasHeader = true; } for (int row = 0; row < spreadsheet.rowDatas.Count; ++row) { var r = sh.CreateRow(row + (hasHeader ? 1 : 0)); for (int col = 0; col < spreadsheet.columnDatas.Count; ++col) { var cell = r.CreateCell(col); SetCellValue(cell, spreadsheet.cells[row][col]); } } try { using (var fs = new FileStream(path, FileMode.Create, FileAccess.Write)) { wb.Write(fs); } } catch (Exception e) { throw new Exception("Cannot write to Excel file. Another program might be using it."); } finally { Thread.CurrentThread.CurrentCulture = cc; Thread.CurrentThread.CurrentUICulture = cuc; } }
public void Run(ExcelSheet <ExcelTransaction> excelData, string filePath, bool isCreateExtendedHeader = true) { this.watch.StartAll(); this.watch.PrintTime($"Started writing to excel."); using (var fs = new FileStream(filePath, FileMode.Create, FileAccess.Write)) { IWorkbook workbook; if (Path.GetExtension(filePath) == ".xlsx") { workbook = new XSSFWorkbook(); } else { workbook = HSSFWorkbook.Create(InternalWorkbook.CreateWorkbook()); } this.watch.PrintDiff($"File created."); ISheet sheet = workbook.CreateSheet("Könyvelt tételek"); this.CreateHeader(sheet, excelData.Header, isCreateExtendedHeader); IDataFormat newDataFormat = workbook.CreateDataFormat(); ICellStyle dateStyle = workbook.CreateCellStyle(); dateStyle.DataFormat = newDataFormat.GetFormat("yyyy.MM.dd"); ICellStyle numberStyle = workbook.CreateCellStyle(); numberStyle.DataFormat = newDataFormat.GetFormat("0.00"); int rowIndex = 1; foreach (var item in excelData.Transactions) { IRow row = sheet.CreateRow(rowIndex); var dateCell = this.CreateStyledCell(row, 0, dateStyle); var numberCell = this.CreateStyledCell(row, 7, numberStyle); dateCell.SetCellValue(item.AccountingDate); row.CreateCell(1).SetCellValue(item.TransactionId); row.CreateCell(2).SetCellValue(item.Type); row.CreateCell(3).SetCellValue(item.Account); row.CreateCell(4).SetCellValue(item.AccountName); row.CreateCell(5).SetCellValue(item.PartnerAccount); row.CreateCell(6).SetCellValue(item.PartnerName); numberCell.SetCellValue(item.Sum); row.CreateCell(8).SetCellValue(item.Currency); row.CreateCell(9).SetCellValue(item.Message); this.watch.PrintDiff($"{rowIndex}/{excelData.Transactions.Count} line{(rowIndex == 1 ? "" : "s")} created."); rowIndex++; } workbook.Write(fs); this.watch.PrintDiff($"File saved."); } this.watch.PrintTime($"Finished writing the excel file.\n"); this.watch.StopAll(); }
/// <summary> /// Gets the stub HSSF workbook. /// </summary> /// <returns></returns> public HSSFWorkbook GetStubHSSFWorkbook() { // Create a base workbook HSSFWorkbook wb = HSSFWorkbook.Create(GetStubWorkbook()); // Stub the sheets, so sheet name lookups work foreach (BoundSheetRecord bsr in boundSheetRecords) { wb.CreateSheet(bsr.Sheetname); } // Ready for Formula use! return(wb); }
private void WriteToXls() { wb = HSSFWorkbook.Create(InternalWorkbook.CreateWorkbook()); // create sheet sh = (HSSFSheet)wb.CreateSheet("Main"); sh.CreateRow(0); sh.GetRow(0).CreateCell(0); sh.GetRow(0).CreateCell(1); sh.GetRow(0).CreateCell(2); sh.GetRow(0).GetCell(0).SetCellValue("Order"); sh.GetRow(0).GetCell(1).SetCellValue("Barcode"); sh.GetRow(0).GetCell(2).SetCellValue("MICR Control"); for (int i = 0; i < dgwValidateScreen.RowCount; i++) { if (sh.GetRow(i + 1) == null) { sh.CreateRow(i + 1); } //TODO: else for (int j = 0; j < dgwValidateScreen.ColumnCount; j++) { if (sh.GetRow(i + 1).GetCell(j) == null) { sh.GetRow(i + 1).CreateCell(j); } if (dgwValidateScreen[j, i].Value != null) { sh.GetRow(i + 1).GetCell(j).SetCellValue(dgwValidateScreen[j, i].Value.ToString()); } } } //Write Statistics for (int i = 2; i < 6; i++) { if (sh.GetRow(i) == null) { sh.CreateRow(i); } sh.GetRow(i).CreateCell(4); sh.GetRow(i).CreateCell(5); } sh.GetRow(2).GetCell(4).SetCellValue("Processed:"); sh.GetRow(2).GetCell(5).SetCellValue(SP.imgCnt); sh.GetRow(3).GetCell(4).SetCellValue("Valid:"); sh.GetRow(3).GetCell(5).SetCellValue(SP.validCnt); sh.GetRow(4).GetCell(4).SetCellValue("Invalid:"); sh.GetRow(4).GetCell(5).SetCellValue(SP.invCnt); sh.GetRow(5).GetCell(4).SetCellValue("Reused:"); sh.GetRow(5).GetCell(5).SetCellValue(SP.reusedCnt); try { using (var fs = new FileStream(xlsPath, FileMode.Create, FileAccess.Write)) { wb.Write(fs); } } catch (Exception ex) { MessageBox.Show("XLS writing error:\n " + ex.ToString(), Settings.Default.messageBoxTitle, MessageBoxButtons.OK, MessageBoxIcon.Stop); } }
public static ActionResult Go(int companyId) { var res = ActionResult.NoAction; var dictionary = HttpContext.Current.Session["Dictionary"] as Dictionary <string, string>; var company = new Company(companyId); string fileName = string.Format( CultureInfo.InvariantCulture, @"{0}_{1}_{2:yyyyMMddhhmmss}.xls", dictionary["Item_Backup"], company.Name, DateTime.Now); var wb = HSSFWorkbook.Create(InternalWorkbook.CreateWorkbook()); var items = new List <ItemConfiguration> { new ItemConfiguration { ItemName = "User", ItemTable = "ApplicationUser" }, new ItemConfiguration { ItemName = "Process", ItemTable = "Proceso" }, new ItemConfiguration { ItemName = "Department", ItemTable = "Department" }, new ItemConfiguration { ItemName = "Employee", ItemTable = "Employee" }, new ItemConfiguration { ItemName = "EmployeeSkills", ItemTable = "EmployeeSkills" }, new ItemConfiguration { ItemName = "Learning", ItemTable = "Learning" }, new ItemConfiguration { ItemName = "LearningAssistant", ItemTable = "LearningAssistant" }, new ItemConfiguration { ItemName = "Customer", ItemTable = "Customer" }, new ItemConfiguration { ItemName = "Provider", ItemTable = "Provider" }, new ItemConfiguration { ItemName = "Unidad", ItemTable = "Unidad" }, new ItemConfiguration { ItemName = "CostDefinition", ItemTable = "CostDefinition" }, new ItemConfiguration { ItemName = "Equipment", ItemTable = "Equipment" }, new ItemConfiguration { ItemName = "EquipmentScaleDivision", ItemTable = "EquipmentScaleDivision" }, new ItemConfiguration { ItemName = "EquipmentCalibrationDefinition", ItemTable = "EquipmentCalibrationDefinition" }, new ItemConfiguration { ItemName = "EquipmentCalibrationAct", ItemTable = "EquipmentCalibrationAct" }, new ItemConfiguration { ItemName = "EquipmentVerificationDefinition", ItemTable = "EquipmentVerificationDefinition" }, new ItemConfiguration { ItemName = "EquipmentVerificationAct", ItemTable = "EquipmentVerificationAct" }, new ItemConfiguration { ItemName = "EquipmentMaintenanceDefinition", ItemTable = "EquipmentMaintenanceDefinition" }, new ItemConfiguration { ItemName = "EquipmentMaintenanceAct", ItemTable = "EquipmentMaintenanceAct" }, new ItemConfiguration { ItemName = "EquipmentRepair", ItemTable = "EquipmentRepair" }, new ItemConfiguration { ItemName = "JobPosition", ItemTable = "Cargos" }, new ItemConfiguration { ItemName = "Document", ItemTable = "Document" }, new ItemConfiguration { ItemName = "Document_Category", ItemTable = "Document_Category" }, new ItemConfiguration { ItemName = "Incident", ItemTable = "Incident" }, new ItemConfiguration { ItemName = "IncidentCost", ItemTable = "IncidentCost" }, new ItemConfiguration { ItemName = "IncidentAction", ItemTable = "IncidentAction" }, new ItemConfiguration { ItemName = "IncidentActionCost", ItemTable = "IncidentActionCost" }, new ItemConfiguration { ItemName = "BusinessRisk", ItemTable = "BusinessRisk3" }, new ItemConfiguration { ItemName = "Indicador", ItemTable = "Indicador" }, new ItemConfiguration { ItemName = "Objetivo", ItemTable = "Objetivo" } }; foreach (var item in items) { var sh = (HSSFSheet)wb.CreateSheet(dictionary["Item_" + item.ItemName].Replace('/', '-')); string query = string.Format( CultureInfo.InvariantCulture, "SELECT * FROM {0} WHERE CompanyId = {1}", item.ItemTable, companyId); using (var cmd = new SqlCommand(query)) { cmd.CommandType = CommandType.Text; using (var cnn = new SqlConnection(ConfigurationManager.ConnectionStrings["cns"].ConnectionString)) { cmd.Connection = cnn; try { cmd.Connection.Open(); using (var rdr = cmd.ExecuteReader()) { bool first = true; int row = 1; while (rdr.Read()) { if (first) { if (sh.GetRow(0) == null) { sh.CreateRow(0); } for (int x = 0; x < rdr.FieldCount; x++) { if (sh.GetRow(0).GetCell(x) == null) { sh.GetRow(0).CreateCell(x); } sh.GetRow(0).GetCell(x).SetCellValue(rdr.GetName(x)); } first = false; } if (sh.GetRow(row) == null) { sh.CreateRow(row); } for (int x = 0; x < rdr.FieldCount; x++) { if (!rdr.IsDBNull(x)) { if (sh.GetRow(row).GetCell(x) == null) { sh.GetRow(row).CreateCell(x); } sh.GetRow(row).GetCell(x).SetCellValue(rdr[x].ToString()); } } row++; } } } finally { if (cmd.Connection.State != ConnectionState.Closed) { cmd.Connection.Close(); } } } } } string path = HttpContext.Current.Request.PhysicalApplicationPath; if (!path.EndsWith(@"\", StringComparison.OrdinalIgnoreCase)) { path = string.Format(CultureInfo.InvariantCulture, @"{0}\Temp\", path); } else { path = string.Format(CultureInfo.InvariantCulture, @"{0}Temp\", path); } using (var fs = new FileStream(string.Format("{0}{1}", path, fileName), FileMode.Create, FileAccess.Write)) { wb.Write(fs); } res.SetSuccess(string.Format("/Temp/{0}", fileName)); return(res); }
public static ActionResult Excel( long equipmentId, int companyId, bool calibrationInternal, bool calibrationExternal, bool verificationInternal, bool verificationExternal, bool maintenanceInternal, bool maintenanceExternal, bool repairInternal, bool repairExternal, DateTime?dateFrom, DateTime?dateTo, string listOrder) { var res = ActionResult.NoAction; var user = HttpContext.Current.Session["User"] as ApplicationUser; var dictionary = HttpContext.Current.Session["Dictionary"] as Dictionary <string, string>; var equipment = Equipment.ById(equipmentId, companyId); var data = HttpContext.Current.Session["EquipmentRecordsFilter"] as List <EquipmentRecord>; string path = HttpContext.Current.Request.PhysicalApplicationPath; if (!path.EndsWith(@"\", StringComparison.OrdinalIgnoreCase)) { path = string.Format(CultureInfo.InvariantCulture, @"{0}\", path); } var formatedDescription = ToolsPdf.NormalizeFileName(equipment.Description); string fileName = string.Format( CultureInfo.InvariantCulture, @"{0}_{1}_{2:yyyyMMddhhmmss}.xls", dictionary["Item_Equipment"], formatedDescription, DateTime.Now); var wb = HSSFWorkbook.Create(InternalWorkbook.CreateWorkbook()); var sh = (HSSFSheet)wb.CreateSheet(string.Format(CultureInfo.InvariantCulture, "{0} - {1}", dictionary["Item_Equipment"], dictionary["Item_Equipment_Tab_Records"])); var shCriteria = (HSSFSheet)wb.CreateSheet(dictionary["Common_SearchCriteria"]); var moneyCellStyle = wb.CreateCellStyle(); var hssfDataFormat = wb.CreateDataFormat(); moneyCellStyle.DataFormat = hssfDataFormat.GetFormat("#,##0.00"); var headerCellStyle = wb.CreateCellStyle(); var headerFont = wb.CreateFont(); headerFont.Boldweight = (short)FontBoldWeight.Bold; headerCellStyle.SetFont(headerFont); headerCellStyle.BorderBottom = BorderStyle.Double; var totalCellStyle = wb.CreateCellStyle(); var totalFont = wb.CreateFont(); totalFont.Boldweight = (short)FontBoldWeight.Bold; totalCellStyle.SetFont(headerFont); totalCellStyle.BorderTop = BorderStyle.Double; var totalValueCellStyle = wb.CreateCellStyle(); totalFont.Boldweight = (short)FontBoldWeight.Bold; totalValueCellStyle.SetFont(headerFont); totalValueCellStyle.BorderTop = BorderStyle.Double; totalValueCellStyle.BorderBottom = BorderStyle.None; totalValueCellStyle.DataFormat = hssfDataFormat.GetFormat("#,##0.00"); var titleCellStyle = wb.CreateCellStyle(); var titleFont = wb.CreateFont(); titleFont.Boldweight = (short)FontBoldWeight.Bold; titleFont.FontHeight = 400; titleCellStyle.SetFont(titleFont); var decimalFormat = wb.CreateCellStyle(); decimalFormat.DataFormat = wb.CreateDataFormat().GetFormat("#.00"); var integerformat = wb.CreateCellStyle(); integerformat.DataFormat = wb.CreateDataFormat().GetFormat("#0"); var cra = new CellRangeAddress(0, 1, 0, 4); sh.AddMergedRegion(cra); if (sh.GetRow(0) == null) { sh.CreateRow(0); } sh.GetRow(0).CreateCell(0); sh.GetRow(0).GetCell(0).SetCellValue(string.Format(CultureInfo.InvariantCulture, "{0} - {1}", dictionary["Item_Equipment"], dictionary["Item_Equipment_Tab_Records"])); sh.GetRow(0).GetCell(0).CellStyle = titleCellStyle; var dataFormatCustom = wb.CreateDataFormat(); // Condiciones del filtro if (shCriteria.GetRow(1) == null) { shCriteria.CreateRow(1); } if (shCriteria.GetRow(2) == null) { shCriteria.CreateRow(2); } if (shCriteria.GetRow(3) == null) { shCriteria.CreateRow(3); } if (shCriteria.GetRow(4) == null) { shCriteria.CreateRow(4); } if (shCriteria.GetRow(5) == null) { shCriteria.CreateRow(5); } if (shCriteria.GetRow(1).GetCell(1) == null) { shCriteria.GetRow(1).CreateCell(1); } shCriteria.GetRow(1).GetCell(1).SetCellValue(dictionary["Calibration-Int"]); if (shCriteria.GetRow(1).GetCell(3) == null) { shCriteria.GetRow(1).CreateCell(3); } shCriteria.GetRow(1).GetCell(3).SetCellValue(calibrationInternal ? dictionary["Common_Yes"] : dictionary["Common_No"]); if (shCriteria.GetRow(1).GetCell(4) == null) { shCriteria.GetRow(1).CreateCell(4); } shCriteria.GetRow(1).GetCell(4).SetCellValue(dictionary["Calibration-Ext"]); if (shCriteria.GetRow(1).GetCell(6) == null) { shCriteria.GetRow(1).CreateCell(6); } shCriteria.GetRow(1).GetCell(6).SetCellValue(calibrationInternal ? dictionary["Common_Yes"] : dictionary["Common_No"]); if (shCriteria.GetRow(2).GetCell(1) == null) { shCriteria.GetRow(2).CreateCell(1); } shCriteria.GetRow(2).GetCell(1).SetCellValue(dictionary["Verification-Int"]); if (shCriteria.GetRow(2).GetCell(3) == null) { shCriteria.GetRow(2).CreateCell(3); } shCriteria.GetRow(2).GetCell(3).SetCellValue(verificationInternal ? dictionary["Common_Yes"] : dictionary["Common_No"]); if (shCriteria.GetRow(2).GetCell(4) == null) { shCriteria.GetRow(2).CreateCell(4); } shCriteria.GetRow(2).GetCell(4).SetCellValue(dictionary["Verification-Ext"]); if (shCriteria.GetRow(2).GetCell(6) == null) { shCriteria.GetRow(2).CreateCell(6); } shCriteria.GetRow(2).GetCell(6).SetCellValue(verificationExternal ? dictionary["Common_Yes"] : dictionary["Common_No"]); if (shCriteria.GetRow(3).GetCell(1) == null) { shCriteria.GetRow(3).CreateCell(1); } shCriteria.GetRow(3).GetCell(1).SetCellValue(dictionary["Item_Equipment_FilterLabel_MaintenanceInternal"]); if (shCriteria.GetRow(3).GetCell(3) == null) { shCriteria.GetRow(3).CreateCell(3); } shCriteria.GetRow(3).GetCell(3).SetCellValue(maintenanceInternal ? dictionary["Common_Yes"] : dictionary["Common_No"]); if (shCriteria.GetRow(3).GetCell(4) == null) { shCriteria.GetRow(3).CreateCell(4); } shCriteria.GetRow(3).GetCell(4).SetCellValue(dictionary["Item_Equipment_FilterLabel_MaintenanceExternal"]); if (shCriteria.GetRow(3).GetCell(6) == null) { shCriteria.GetRow(3).CreateCell(6); } shCriteria.GetRow(3).GetCell(6).SetCellValue(maintenanceInternal ? dictionary["Common_Yes"] : dictionary["Common_No"]); if (shCriteria.GetRow(4).GetCell(1) == null) { shCriteria.GetRow(4).CreateCell(1); } shCriteria.GetRow(4).GetCell(1).SetCellValue(dictionary["Item_Equipment_FilterLabel_RepairInternal"]); if (shCriteria.GetRow(4).GetCell(3) == null) { shCriteria.GetRow(4).CreateCell(3); } shCriteria.GetRow(4).GetCell(3).SetCellValue(repairInternal ? dictionary["Common_Yes"] : dictionary["Common_No"]); if (shCriteria.GetRow(4).GetCell(4) == null) { shCriteria.GetRow(4).CreateCell(4); } shCriteria.GetRow(4).GetCell(4).SetCellValue(dictionary["Item_Equipment_FilterLabel_RepairExternal"]); if (shCriteria.GetRow(4).GetCell(6) == null) { shCriteria.GetRow(4).CreateCell(6); } shCriteria.GetRow(4).GetCell(6).SetCellValue(repairExternal ? dictionary["Common_Yes"] : dictionary["Common_No"]); if (shCriteria.GetRow(5).GetCell(1) == null) { shCriteria.GetRow(5).CreateCell(1); } shCriteria.GetRow(5).GetCell(1).SetCellValue(dictionary["Common_From"]); if (shCriteria.GetRow(5).GetCell(3) == null) { shCriteria.GetRow(5).CreateCell(3); } string fromValue = "-"; if (dateFrom.HasValue) { fromValue = string.Format(CultureInfo.InvariantCulture, "{0:dd/MM/yyyy}", dateFrom.Value); } shCriteria.GetRow(5).GetCell(3).SetCellValue(fromValue); if (shCriteria.GetRow(5).GetCell(4) == null) { shCriteria.GetRow(5).CreateCell(4); } shCriteria.GetRow(5).GetCell(4).SetCellValue("Hasta"); if (shCriteria.GetRow(5).GetCell(6) == null) { shCriteria.GetRow(5).CreateCell(6); } string toValue = "-"; if (dateTo.HasValue) { toValue = string.Format(CultureInfo.InvariantCulture, "{0:dd/MM/yyyy}", dateTo.Value); } shCriteria.GetRow(5).GetCell(6).SetCellValue(toValue); // Crear Cabecera var headers = new List <string>() { dictionary["Item_EquipmentRepair_HeaderList_Date"], dictionary["Item_EquipmentRepair_HeaderList_Type"], dictionary["Item_EquipmentRepair_HeaderList_Operation"], dictionary["Item_EquipmentRepair_HeaderList_Responsible"], dictionary["Item_EquipmentRepair_HeaderList_Cost"] }; int countColumns = 0; foreach (string headerLabel in headers) { if (sh.GetRow(3) == null) { sh.CreateRow(3); } if (sh.GetRow(3).GetCell(countColumns) == null) { sh.GetRow(3).CreateCell(countColumns); } sh.GetRow(3).GetCell(countColumns).SetCellValue(headerLabel); sh.GetRow(3).GetCell(countColumns).CellStyle = headerCellStyle; countColumns++; } int countRow = 4; decimal total = 0; // Poner el tipo de registro diccionarizado if (data != null) { foreach (var record in data) { record.RecordTypeText = dictionary[record.Item + "-" + (record.RecordType == 0 ? "Int" : "Ext")]; } switch (listOrder.ToUpperInvariant()) { default: case "TH0|ASC": data = data.OrderBy(d => d.Date).ToList(); break; case "TH0|DESC": data = data.OrderByDescending(d => d.Date).ToList(); break; case "TH1|ASC": data = data.OrderBy(d => d.RecordTypeText).ToList(); break; case "TH1|DESC": data = data.OrderByDescending(d => d.RecordTypeText).ToList(); break; case "TH2|ASC": data = data.OrderBy(d => d.Operation).ToList(); break; case "TH2|DESC": data = data.OrderByDescending(d => d.Operation).ToList(); break; case "TH3|ASC": data = data.OrderBy(d => d.Responsible.FullName).ToList(); break; case "TH3|DESC": data = data.OrderByDescending(d => d.Responsible.FullName).ToList(); break; case "TH4|ASC": data = data.OrderBy(d => d.Cost).ToList(); break; case "TH4|DESC": data = data.OrderByDescending(d => d.Cost).ToList(); break; } foreach (var equipmentRecord in data) { if (sh.GetRow(countRow) == null) { sh.CreateRow(countRow); } // Fecha if (sh.GetRow(countRow).GetCell(0) == null) { sh.GetRow(countRow).CreateCell(0); } sh.GetRow(countRow).GetCell(0).CellStyle.DataFormat = dataFormatCustom.GetFormat("dd/MM/yyyy"); sh.GetRow(countRow).GetCell(0).SetCellValue(equipmentRecord.Date); // Tipo if (sh.GetRow(countRow).GetCell(1) == null) { sh.GetRow(countRow).CreateCell(1); } sh.GetRow(countRow).GetCell(1).SetCellValue(equipmentRecord.RecordTypeText); // Operacion if (sh.GetRow(countRow).GetCell(2) == null) { sh.GetRow(countRow).CreateCell(2); } sh.GetRow(countRow).GetCell(2).SetCellValue(equipmentRecord.Operation); // Responsable if (sh.GetRow(countRow).GetCell(3) == null) { sh.GetRow(countRow).CreateCell(3); } sh.GetRow(countRow).GetCell(3).SetCellValue(equipmentRecord.Responsible.FullName); // Coste if (sh.GetRow(countRow).GetCell(4) == null) { sh.GetRow(countRow).CreateCell(4); } if (equipmentRecord.Cost.HasValue) { sh.GetRow(countRow).GetCell(4).SetCellValue(Convert.ToDouble(equipmentRecord.Cost.Value)); sh.GetRow(countRow).GetCell(4).SetCellType(CellType.Numeric); sh.GetRow(countRow).GetCell(4).CellStyle = moneyCellStyle; total += equipmentRecord.Cost.Value; } countRow++; } } if (sh.GetRow(countRow) == null) { sh.CreateRow(countRow); } if (sh.GetRow(countRow).GetCell(0) == null) { sh.GetRow(countRow).CreateCell(0); } if (sh.GetRow(countRow).GetCell(1) == null) { sh.GetRow(countRow).CreateCell(1); } if (sh.GetRow(countRow).GetCell(2) == null) { sh.GetRow(countRow).CreateCell(2); } if (sh.GetRow(countRow).GetCell(3) == null) { sh.GetRow(countRow).CreateCell(3); } sh.GetRow(countRow).GetCell(0).SetCellValue(string.Empty); sh.GetRow(countRow).GetCell(0).CellStyle = totalCellStyle; sh.GetRow(countRow).GetCell(1).SetCellValue(string.Empty); sh.GetRow(countRow).GetCell(1).CellStyle = totalCellStyle; sh.GetRow(countRow).GetCell(2).SetCellValue(string.Empty); sh.GetRow(countRow).GetCell(2).CellStyle = totalCellStyle; sh.GetRow(countRow).GetCell(3).SetCellValue(dictionary["Common_Total"]); sh.GetRow(countRow).GetCell(3).CellStyle = totalCellStyle; if (sh.GetRow(countRow).GetCell(4) == null) { sh.GetRow(countRow).CreateCell(4); } sh.GetRow(countRow).GetCell(4).SetCellValue(Convert.ToDouble(total)); sh.GetRow(countRow).GetCell(4).SetCellType(CellType.Numeric); sh.GetRow(countRow).GetCell(4).CellStyle = totalValueCellStyle; sh.SetColumnWidth(0, 4000); sh.SetColumnWidth(1, 8400); sh.SetColumnWidth(1, 8400); sh.SetColumnWidth(2, 8400); sh.SetColumnWidth(3, 8400); sh.SetColumnWidth(4, 2800); // INsertar logo /*System.Drawing.Image image = System.Drawing.Image.FromFile(string.Format("{0}\\images\\Logos\\{1}.jpg", path, companyId)); * MemoryStream ms = new MemoryStream(); * //pull the memory stream from the image (I need this for the byte array later) * image.Save(ms, System.Drawing.Imaging.ImageFormat.Png); * //the drawing patriarch will hold the anchor and the master information * HSSFPatriarch patriarch = (HSSFPatriarch)sh.CreateDrawingPatriarch(); * //store the coordinates of which cell and where in the cell the image goes * HSSFClientAnchor anchor = new HSSFClientAnchor(0, 0, 0, 0, 0, 0, 1, 2); * //types are 0, 2, and 3. 0 resizes within the cell, 2 doesn't * anchor.AnchorType = 0;// AnchorType.MoveDontResize; * //add the byte array and encode it for the excel file * int index = wb.AddPicture(ms.ToArray(), PictureType.PNG); * HSSFPicture signaturePicture = (HSSFPicture)patriarch.CreatePicture(anchor, index);*/ if (!path.EndsWith("\\")) { path += "\\Temp\\"; } else { path += "Temp\\"; } using (var fs = new FileStream(string.Format("{0}{1}", path, fileName), FileMode.Create, FileAccess.Write)) { wb.Write(fs); } res.SetSuccess(string.Format("/Temp/{0}", fileName)); return(res); }
public static void ExportToExcel1 <T>(string excelName, List <Tuple <string, string, double, Type> > headers, ObservableCollection <T> RecordCollection) { string excelPath = ConfigurationManager.AppSettings["ExcelPath"]; if (excelPath.Last() != System.IO.Path.DirectorySeparatorChar) { excelPath += System.IO.Path.DirectorySeparatorChar; } string excelFile = string.Format("{0}{1}", excelPath, excelName); string sheetName = "Sheet1"; if (File.Exists(excelFile)) { File.Delete(excelFile); } HSSFWorkbook wb = HSSFWorkbook.Create(InternalWorkbook.CreateWorkbook()); HSSFSheet sh = (HSSFSheet)wb.CreateSheet(sheetName); for (int i = 1; i <= RecordCollection.Count; i++) { var item = RecordCollection[i - 1]; if (sh.GetRow(i) == null) { sh.CreateRow(i); } for (int j = 0; j < headers.Count; j++) { if (sh.GetRow(i).GetCell(j) == null) { sh.GetRow(i).CreateCell(j); } string sValue = ""; var y = typeof(T).InvokeMember(headers[j].Item2.ToString(), BindingFlags.GetProperty, null, item, null); sValue = (y == null) ? "" : y.ToString(); sh.GetRow(i).GetCell(j).SetCellValue(sValue); } } var r = sh.CreateRow(0); for (int j = 0; j < headers.Count; j++) { if (sh.GetRow(0).GetCell(j) == null) { sh.GetRow(0).CreateCell(j); } sh.GetRow(0).GetCell(j).SetCellValue(headers[j].Item1); sh.AutoSizeColumn(j); } using (var fs = new FileStream(excelFile, FileMode.Create, FileAccess.Write)) { wb.Write(fs); } ExploreFile(excelFile); }
public static ActionResult SinPresupuestoExcel() { var res = ActionResult.NoAction; var dictionary = HttpContext.Current.Session["Dictionary"] as Dictionary <string, string>; string path = HttpContext.Current.Request.PhysicalApplicationPath; if (!path.EndsWith(@"\", StringComparison.OrdinalIgnoreCase)) { path = string.Format(CultureInfo.InvariantCulture, @"{0}\", path); } string fileName = string.Format( CultureInfo.InvariantCulture, @"SinPresupuesto_{0:yyyyMMdd-hhmmss}.xls", DateTime.Now); var wb = HSSFWorkbook.Create(InternalWorkbook.CreateWorkbook()); var sh = (HSSFSheet)wb.CreateSheet("Presupuestos descartados"); var moneyCellStyle = wb.CreateCellStyle(); var hssfDataFormat = wb.CreateDataFormat(); moneyCellStyle.DataFormat = hssfDataFormat.GetFormat("#,##0.00"); var headerCellStyle = wb.CreateCellStyle(); var headerFont = wb.CreateFont(); headerFont.Boldweight = (short)FontBoldWeight.Bold; headerCellStyle.SetFont(headerFont); headerCellStyle.BorderBottom = BorderStyle.Double; var totalCellStyle = wb.CreateCellStyle(); var totalFont = wb.CreateFont(); totalFont.Boldweight = (short)FontBoldWeight.Bold; totalCellStyle.SetFont(headerFont); totalCellStyle.BorderTop = BorderStyle.Double; var totalValueCellStyle = wb.CreateCellStyle(); totalFont.Boldweight = (short)FontBoldWeight.Bold; totalValueCellStyle.SetFont(headerFont); totalValueCellStyle.BorderTop = BorderStyle.Double; totalValueCellStyle.BorderBottom = BorderStyle.None; totalValueCellStyle.DataFormat = hssfDataFormat.GetFormat("#,##0.00"); var titleCellStyle = wb.CreateCellStyle(); var titleFont = wb.CreateFont(); titleFont.Boldweight = (short)FontBoldWeight.Bold; titleFont.FontHeight = 400; titleCellStyle.SetFont(titleFont); var decimalFormat = wb.CreateCellStyle(); decimalFormat.DataFormat = wb.CreateDataFormat().GetFormat("#.00"); var integerformat = wb.CreateCellStyle(); integerformat.DataFormat = wb.CreateDataFormat().GetFormat("#0"); var cra = new CellRangeAddress(0, 1, 0, 4); sh.AddMergedRegion(cra); if (sh.GetRow(0) == null) { sh.CreateRow(0); } sh.GetRow(0).CreateCell(0); sh.GetRow(0).GetCell(0).SetCellValue("Busquedas sin presupuestos"); sh.GetRow(0).GetCell(0).CellStyle = titleCellStyle; var dataFormatCustom = wb.CreateDataFormat(); // Crear Cabecera var headers = new List <string>() { "Centro", "Fecha", "Búsqueda", "Colectivo" }; int countColumns = 0; foreach (string headerLabel in headers) { if (sh.GetRow(3) == null) { sh.CreateRow(3); } if (sh.GetRow(3).GetCell(countColumns) == null) { sh.GetRow(3).CreateCell(countColumns); } sh.GetRow(3).GetCell(countColumns).SetCellValue(headerLabel); sh.GetRow(3).GetCell(countColumns).CellStyle = headerCellStyle; countColumns++; } int countRow = 4; var query = @" SELECT DISTINCT A.NumberOfEmployees, ISNULL(A.Name,''), DATEADD(dd, 0, DATEDIFF(dd, 0, B.[Date])) AS Fecha, C.Name, ISNULL(B.Busqueda,'') FROM AspadLand_Traces B WITH(NOLOCK) LEFT JOIN AspadLand_Traces P WITH(NOLOCK) ON B.Type = 9 AND P.Type = 5 AND P.CentroId = B.CentroId AND P.Date >= B.Date LEFT JOIN AspadLandPresuspuesto PR WITH(NOLOCK) ON PR.PresupuestoId = P.PresupuestoId LEFT JOIN qes_poliza POL WITH(NOLOCK) ON POL.qes_polizaId = PR.PolizaId AND POL.qes_dni = B.Busqueda INNER JOIN Account A WITH(NOLOCK) ON A.AccountId = b.CentroId INNER JOIN Account C WITH(NOLOCK) ON C.AccountId= B.ColectivoId WHERE P.CentroId is null AND B.Type = 9 AND A.Name <> 'CENTRO VETERINARIO PILOTO ASPAD' ORDER BY DATEADD(dd, 0, DATEDIFF(dd, 0, B.[Date])) DESC"; using (var cmd = new SqlCommand(query)) { using (var cnn = new SqlConnection(ConfigurationManager.ConnectionStrings["cns"].ConnectionString)) { cmd.Connection = cnn; cmd.CommandType = CommandType.Text; try { cmd.Connection.Open(); var acutalCentro = Guid.Empty; var actualMascota = Guid.Empty; var fecha = DateTime.Now; using (var rdr = cmd.ExecuteReader()) { while (rdr.Read()) { if (sh.GetRow(countRow) == null) { sh.CreateRow(countRow); } // Centro if (sh.GetRow(countRow).GetCell(0) == null) { sh.GetRow(countRow).CreateCell(0); } sh.GetRow(countRow).GetCell(0).SetCellValue(rdr[0].ToString() + " - " + rdr.GetString(1)); // Fecha if (sh.GetRow(countRow).GetCell(1) == null) { sh.GetRow(countRow).CreateCell(1); } sh.GetRow(countRow).GetCell(1).CellStyle.DataFormat = dataFormatCustom.GetFormat("dd/MM/yyyy"); sh.GetRow(countRow).GetCell(1).SetCellValue(rdr.GetDateTime(2)); // Colectivo if (sh.GetRow(countRow).GetCell(2) == null) { sh.GetRow(countRow).CreateCell(2); } sh.GetRow(countRow).GetCell(2).SetCellValue(rdr.GetString(3)); // Búsqueda if (sh.GetRow(countRow).GetCell(3) == null) { sh.GetRow(countRow).CreateCell(3); } sh.GetRow(countRow).GetCell(3).SetCellValue(rdr.GetString(4)); countRow++; } } } finally { if (cmd.Connection.State != ConnectionState.Closed) { cmd.Connection.Close(); } } } } sh.SetColumnWidth(0, 12000); sh.SetColumnWidth(1, 3000); sh.SetColumnWidth(2, 8000); if (!path.EndsWith("\\")) { path += "\\Temp\\"; } else { path += "Temp\\"; } using (var fs = new FileStream(string.Format("{0}{1}", path, fileName), FileMode.Create, FileAccess.Write)) { wb.Write(fs); } res.SetSuccess(string.Format("/Temp/{0}", fileName)); return(res); }
public string CreateReportMassive(string path, string idComunicazioneMax, List <String> lData) { NameValueCollection oResult = new NameValueCollection(); string pathfilename = ""; try { Guid filename = new Guid(); // Create and display the value of two GUIDs. filename = Guid.NewGuid(); pathfilename = path + @"\" + filename.ToString() + ".xls"; HSSFWorkbook wb; HSSFSheet sh; wb = HSSFWorkbook.Create(InternalWorkbook.CreateWorkbook()); // create sheet sh = (HSSFSheet)wb.CreateSheet(idComunicazioneMax.Replace("/", "_")); string[] arrData = null; int iRowExc = 0; var headerCellStyle = wb.CreateCellStyle(); var detailSubtotalFont = wb.CreateFont(); detailSubtotalFont.Boldweight = (short)NPOI.SS.UserModel.FontBoldWeight.Bold; //headerCellStyle.FillForegroundColor= NPOI.SS.Util.HSSFColor.AQUA.index; headerCellStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center; headerCellStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.Medium; headerCellStyle.BorderLeft = NPOI.SS.UserModel.BorderStyle.Medium; headerCellStyle.BorderRight = NPOI.SS.UserModel.BorderStyle.Medium; headerCellStyle.SetFont(detailSubtotalFont); var detailSubtotalFontLight = wb.CreateFont(); var DataCellStyle = wb.CreateCellStyle(); detailSubtotalFontLight.Boldweight = (short)NPOI.SS.UserModel.FontBoldWeight.Normal; // DataCellStyle.FillForegroundColor = NPOI.SS.Util.HSSFColor.AQUA.index; DataCellStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Left; DataCellStyle.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin; DataCellStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin; DataCellStyle.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin; DataCellStyle.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin; DataCellStyle.SetFont(detailSubtotalFontLight); for (int iRow = 0; iRow < lData.Count; iRow++) { var r = sh.CreateRow(iRowExc); string sData = lData[iRow]; arrData = sData.Split('|'); int iRowListValue = 1; if (iRow == 0) { int iHeader = 0; for (int iColumn = 0; iColumn < arrData.Length / 2; iColumn++) { var newCell = r.CreateCell(iColumn); newCell.SetCellValue(arrData[iHeader]); newCell.SetCellType(NPOI.SS.UserModel.CellType.String); newCell.CellStyle = headerCellStyle; iHeader = iHeader + 2; } iRowExc++; r = sh.CreateRow(iRowExc); } for (int iColumn = 0; iColumn < arrData.Length / 2; iColumn++) { var newCell = r.CreateCell(iColumn); newCell.SetCellValue(arrData[iRowListValue]); newCell.SetCellType(NPOI.SS.UserModel.CellType.String); newCell.CellStyle = DataCellStyle; iRowListValue = iRowListValue + 2; } iRowExc++; } using (var fs = new FileStream(pathfilename, FileMode.Create, FileAccess.Write)) { for (int iColumn = 0; iColumn < arrData.Length / 2; iColumn++) { sh.AutoSizeColumn(iColumn); } wb.Write(fs); fs.Close(); } } catch (Exception ex) { throw new ArgumentException(ex.Message); } finally { } return(pathfilename); }
public static ActionResult PendientesExcel() { var res = ActionResult.NoAction; var dictionary = HttpContext.Current.Session["Dictionary"] as Dictionary <string, string>; string path = HttpContext.Current.Request.PhysicalApplicationPath; if (!path.EndsWith(@"\", StringComparison.OrdinalIgnoreCase)) { path = string.Format(CultureInfo.InvariantCulture, @"{0}\", path); } string fileName = string.Format( CultureInfo.InvariantCulture, @"Pendientes_{0:yyyyMMdd-hhmmss}.xls", DateTime.Now); var wb = HSSFWorkbook.Create(InternalWorkbook.CreateWorkbook()); var sh = (HSSFSheet)wb.CreateSheet("Presupuestos pendientes"); var moneyCellStyle = wb.CreateCellStyle(); var hssfDataFormat = wb.CreateDataFormat(); moneyCellStyle.DataFormat = hssfDataFormat.GetFormat("#,##0.00"); var headerCellStyle = wb.CreateCellStyle(); var headerFont = wb.CreateFont(); headerFont.Boldweight = (short)FontBoldWeight.Bold; headerCellStyle.SetFont(headerFont); headerCellStyle.BorderBottom = BorderStyle.Double; var totalCellStyle = wb.CreateCellStyle(); var totalFont = wb.CreateFont(); totalFont.Boldweight = (short)FontBoldWeight.Bold; totalCellStyle.SetFont(headerFont); totalCellStyle.BorderTop = BorderStyle.Double; var totalValueCellStyle = wb.CreateCellStyle(); totalFont.Boldweight = (short)FontBoldWeight.Bold; totalValueCellStyle.SetFont(headerFont); totalValueCellStyle.BorderTop = BorderStyle.Double; totalValueCellStyle.BorderBottom = BorderStyle.None; totalValueCellStyle.DataFormat = hssfDataFormat.GetFormat("#,##0.00"); var titleCellStyle = wb.CreateCellStyle(); var titleFont = wb.CreateFont(); titleFont.Boldweight = (short)FontBoldWeight.Bold; titleFont.FontHeight = 400; titleCellStyle.SetFont(titleFont); var decimalFormat = wb.CreateCellStyle(); decimalFormat.DataFormat = wb.CreateDataFormat().GetFormat("#.00"); var integerformat = wb.CreateCellStyle(); integerformat.DataFormat = wb.CreateDataFormat().GetFormat("#0"); var cra = new CellRangeAddress(0, 1, 0, 4); sh.AddMergedRegion(cra); if (sh.GetRow(0) == null) { sh.CreateRow(0); } sh.GetRow(0).CreateCell(0); sh.GetRow(0).GetCell(0).SetCellValue("Presupuestos pendientes"); sh.GetRow(0).GetCell(0).CellStyle = titleCellStyle; var dataFormatCustom = wb.CreateDataFormat(); // Crear Cabecera var headers = new List <string>() { "Centro", "Fecha", "Presupuesto", "Asegurado", "DNI", "Colectivo", "Poliza", "Mascota", "Chip", "Tipo", "Sexo" }; int countColumns = 0; foreach (string headerLabel in headers) { if (sh.GetRow(3) == null) { sh.CreateRow(3); } if (sh.GetRow(3).GetCell(countColumns) == null) { sh.GetRow(3).CreateCell(countColumns); } sh.GetRow(3).GetCell(countColumns).SetCellValue(headerLabel); sh.GetRow(3).GetCell(countColumns).CellStyle = headerCellStyle; countColumns++; } var trazas = Traza.AllPresupuestos; var actualDate = DateTime.Now; var actualMascota = Guid.Empty; var final = new List <Traza>(); foreach (var traza in trazas) { if (traza.Fecha.Value.Date != actualDate && traza.MascotaId != actualMascota && traza.Tipo != 5) { final.Add(traza); } if (traza.Tipo == 4) { final.Add(traza); } actualDate = traza.Fecha.Value.Date; actualMascota = traza.MascotaId; } int countRow = 4; foreach (var t in final) { if (sh.GetRow(countRow) == null) { sh.CreateRow(countRow); } // Centro if (sh.GetRow(countRow).GetCell(0) == null) { sh.GetRow(countRow).CreateCell(0); } sh.GetRow(countRow).GetCell(0).SetCellValue(t.CentroName); // Fecha if (sh.GetRow(countRow).GetCell(1) == null) { sh.GetRow(countRow).CreateCell(1); } sh.GetRow(countRow).GetCell(1).CellStyle.DataFormat = dataFormatCustom.GetFormat("dd/MM/yyyy"); sh.GetRow(countRow).GetCell(1).SetCellValue(t.Fecha.Value); // Presupuesto if (sh.GetRow(countRow).GetCell(2) == null) { sh.GetRow(countRow).CreateCell(2); } sh.GetRow(countRow).GetCell(2).SetCellValue(t.PresupuestoCode); // Asegurado if (sh.GetRow(countRow).GetCell(3) == null) { sh.GetRow(countRow).CreateCell(3); } sh.GetRow(countRow).GetCell(3).SetCellValue(t.asegurado); // DNI if (sh.GetRow(countRow).GetCell(4) == null) { sh.GetRow(countRow).CreateCell(4); } sh.GetRow(countRow).GetCell(4).SetCellValue(t.DNI); // Colectivo if (sh.GetRow(countRow).GetCell(5) == null) { sh.GetRow(countRow).CreateCell(5); } sh.GetRow(countRow).GetCell(5).SetCellValue(t.colectivo); // Poliza if (sh.GetRow(countRow).GetCell(6) == null) { sh.GetRow(countRow).CreateCell(6); } sh.GetRow(countRow).GetCell(6).SetCellValue(t.poliza); // Mascota if (sh.GetRow(countRow).GetCell(7) == null) { sh.GetRow(countRow).CreateCell(7); } sh.GetRow(countRow).GetCell(7).SetCellValue(t.mascotaName); // Sexo var sexo = ""; if (t.sexo == "100000000") { sexo = "macho"; } if (t.sexo == "100000001") { sexo = "hembra"; } if (sh.GetRow(countRow).GetCell(8) == null) { sh.GetRow(countRow).CreateCell(8); } sh.GetRow(countRow).GetCell(8).SetCellValue(sexo); // Tipo var tipo = ""; if (t.tipo == "100000000") { tipo = "perro"; } if (t.tipo == "100000001") { tipo = "gato"; } if (sh.GetRow(countRow).GetCell(9) == null) { sh.GetRow(countRow).CreateCell(9); } sh.GetRow(countRow).GetCell(9).SetCellValue(tipo); // Microchip if (sh.GetRow(countRow).GetCell(10) == null) { sh.GetRow(countRow).CreateCell(10); } sh.GetRow(countRow).GetCell(10).SetCellValue(t.chip); countRow++; } sh.SetColumnWidth(0, 12000); sh.SetColumnWidth(1, 3000); sh.SetColumnWidth(2, 4000); sh.SetColumnWidth(3, 20000); sh.SetColumnWidth(4, 4000); sh.SetColumnWidth(5, 6000); sh.SetColumnWidth(6, 6000); sh.SetColumnWidth(7, 6000); sh.SetColumnWidth(8, 4000); sh.SetColumnWidth(9, 4000); if (!path.EndsWith("\\")) { path += "\\Temp\\"; } else { path += "Temp\\"; } using (var fs = new FileStream(string.Format("{0}{1}", path, fileName), FileMode.Create, FileAccess.Write)) { wb.Write(fs); } res.SetSuccess(string.Format("/Temp/{0}", fileName)); return(res); }
public string CreateReportMassive(string path, string sNameSheet, string sNameFile, List <Dictionary <string, string> > lData) { NameValueCollection oResult = new NameValueCollection(); string pathfilename = ""; try { pathfilename = path + @"\" + sNameFile + ".xls"; HSSFWorkbook wb; HSSFSheet sh; wb = HSSFWorkbook.Create(InternalWorkbook.CreateWorkbook()); // create sheet sh = (HSSFSheet)wb.CreateSheet(sNameSheet.Replace("/", "_")); //string[] arrData = null; int iRowExc = 0; var headerCellStyle = wb.CreateCellStyle(); var detailSubtotalFont = wb.CreateFont(); detailSubtotalFont.Boldweight = (short)NPOI.SS.UserModel.FontBoldWeight.Bold; //headerCellStyle.FillForegroundColor= NPOI.SS.Util.HSSFColor.AQUA.index; headerCellStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center; headerCellStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.Medium; headerCellStyle.BorderLeft = NPOI.SS.UserModel.BorderStyle.Medium; headerCellStyle.BorderRight = NPOI.SS.UserModel.BorderStyle.Medium; headerCellStyle.SetFont(detailSubtotalFont); var detailSubtotalFontLight = wb.CreateFont(); var DataCellStyle = wb.CreateCellStyle(); detailSubtotalFontLight.Boldweight = (short)NPOI.SS.UserModel.FontBoldWeight.Normal; // DataCellStyle.FillForegroundColor = NPOI.SS.Util.HSSFColor.AQUA.index; DataCellStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Left; DataCellStyle.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin; DataCellStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin; DataCellStyle.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin; DataCellStyle.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin; DataCellStyle.SetFont(detailSubtotalFontLight); List <string> listKeys = new List <string>(lData[0].Keys); for (int iRow = 0; iRow < lData.Count; iRow++) { var r = sh.CreateRow(iRowExc); //string sData = lData[iRow]; //arrData = sData.Split('|'); if (iRow == 0) { int iHeader = 0; // Loop through list. foreach (string k in listKeys) { var newCell = r.CreateCell(iHeader); newCell.SetCellValue(k); newCell.SetCellType(NPOI.SS.UserModel.CellType.String); newCell.CellStyle = headerCellStyle; iHeader++; } iRowExc++; r = sh.CreateRow(iRowExc); } for (int iColumn = 0; iColumn < listKeys.Count; iColumn++) { List <string> listValue = new List <string>(lData[iRowExc - 1].Values); var newCell = r.CreateCell(iColumn); newCell.SetCellValue(listValue[iColumn]); newCell.SetCellType(NPOI.SS.UserModel.CellType.String); newCell.CellStyle = DataCellStyle; } iRowExc++; } using (var fs = new FileStream(pathfilename, FileMode.Create, FileAccess.Write)) { for (int iColumn = 0; iColumn < listKeys.Count; iColumn++) { sh.AutoSizeColumn(iColumn); } wb.Write(fs); fs.Close(); } } catch (Exception ex) { throw new ArgumentException(ex.Message); } finally { } return(pathfilename); }
private void XuatExcelChung(string TenFile) { HSSFWorkbook wb; HSSFSheet sh; wb = HSSFWorkbook.Create(InternalWorkbook.CreateWorkbook()); // create sheet sh = (HSSFSheet)wb.CreateSheet(DateTime.Now.ToString("dd_MM_yyyy HH_mm_ss")); // Tieu De IFont font1 = wb.CreateFont(); font1.Boldweight = 100; ICellStyle style = wb.CreateCellStyle(); style.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center; style.SetFont(font1); if (sh.GetRow(0) == null) { sh.CreateRow(0); } int j; int k = 0; for (j = 0; j < grdDuLieu.ColumnCount; j++) { if (grdDuLieu.Columns[j].Visible == true) { if (sh.GetRow(0).GetCell(k) == null) { sh.GetRow(0).CreateCell(k); } sh.GetRow(0).GetCell(k).CellStyle = style; sh.GetRow(0).GetCell(k).SetCellValue(grdDuLieu.Columns[j].HeaderText); k = k + 1; } } //Du lieu Double GiaTri; string _gt; for (int i = 0; i < grdDuLieu.RowCount; i++) { sh.CreateRow(i + 1); k = 0; for (j = 0; j < grdDuLieu.ColumnCount; j++) { if (grdDuLieu.Rows[i].Cells[j].Visible == true) { if (sh.GetRow(i + 1).GetCell(k) == null) { sh.GetRow(i + 1).CreateCell(k); } if (grdDuLieu.Rows[i].Cells[j].Value == null) { sh.GetRow(i + 1).GetCell(k).SetCellValue(""); } else { _gt = grdDuLieu.Rows[i].Cells[j].Value.ToString(); _gt = _gt.Replace(".", ""); if (Double.TryParse(_gt, out GiaTri)) { sh.GetRow(i + 1).GetCell(k).SetCellValue(GiaTri); } else { sh.GetRow(i + 1).GetCell(k).SetCellValue(_gt); } } k = k + 1; } } Xuat(i, null); } //Ghi ra file using (var fs = new FileStream(TenFile, FileMode.Create, FileAccess.Write)) { wb.Write(fs); } XuatXong(null, null); }
static async void Getstockdataasync() { XmlDocument xml = new XmlDocument(); String exepath = AppDomain.CurrentDomain.BaseDirectory; xml.Load(exepath + @"config.xml"); XmlNode tokenst = xml.SelectSingleNode("/configuration/token"); string token = tokenst.InnerText; StreamReader stocklist = new StreamReader("stocklist.txt", Encoding.Default); StreamWriter connect5 = new StreamWriter(@"stockdata_daily.txt", true, Encoding.Default); StreamWriter connect6 = new StreamWriter(@"summary.txt", true, Encoding.Default); string stockl = null; while ((stockl = stocklist.ReadLine()) != null) { var symbol = stockl; StreamWriter connect5s = new StreamWriter(@symbol + "_daily.txt", true, Encoding.Default); var dailyprices = new List <AlphaVantageData>(); await Task.Run(() => { dailyprices = $"https://www.alphavantage.co/query?function=TIME_SERIES_DAILY&symbol={symbol}&outputsize=full&apikey={token}&datatype=csv" .GetStringFromUrl().FromCsv <List <AlphaVantageData> >(); }); List <string> bm = new List <string>(); HSSFWorkbook wb; HSSFSheet sh; if (!File.Exists(@symbol + "_daily.txt" + "test.xls")) { wb = HSSFWorkbook.Create(InternalWorkbook.CreateWorkbook()); // create sheet sh = (HSSFSheet)wb.CreateSheet("Sheet1"); IDataFormat dataFormatCustom = wb.CreateDataFormat(); ICellStyle style1 = wb.CreateCellStyle(); style1.DataFormat = dataFormatCustom.GetFormat("MM/dd/yyyy HH:mm:ss AM/PM"); for (int x = 0; x < dailyprices.Count; x++) { var r = sh.CreateRow(x); for (int j = 0; j < 7; j++) { IRow row = sh.GetRow(x); if (j == 0) { r.CreateCell(j); ICell cell1 = row.GetCell(j, MissingCellPolicy.CREATE_NULL_AS_BLANK); cell1.SetCellValue(symbol); } else if (j == 1) { r.CreateCell(j).CellStyle = style1; ICell cell1 = row.GetCell(j, MissingCellPolicy.CREATE_NULL_AS_BLANK); cell1.SetCellValue(dailyprices[x].Timestamp); } else if (j == 2) { r.CreateCell(j); ICell cell1 = row.GetCell(j, MissingCellPolicy.CREATE_NULL_AS_BLANK); cell1.SetCellValue((double)dailyprices[x].Volume); } else if (j == 3) { r.CreateCell(j); ICell cell1 = row.GetCell(j, MissingCellPolicy.CREATE_NULL_AS_BLANK); cell1.SetCellValue((double)dailyprices[x].Open); } else if (j == 4) { r.CreateCell(j); ICell cell1 = row.GetCell(j, MissingCellPolicy.CREATE_NULL_AS_BLANK); cell1.SetCellValue((double)dailyprices[x].High); } else if (j == 5) { r.CreateCell(j); ICell cell1 = row.GetCell(j, MissingCellPolicy.CREATE_NULL_AS_BLANK); cell1.SetCellValue((double)dailyprices[x].Low); } else if (j == 6) { r.CreateCell(j); ICell cell1 = row.GetCell(j, MissingCellPolicy.CREATE_NULL_AS_BLANK); cell1.SetCellValue((double)dailyprices[x].Close); } } } using (var fs = new FileStream(@symbol + "_daily.txt" + "test.xls", FileMode.Create, FileAccess.Write)) { wb.Write(fs); } } for (int x = 0; x < dailyprices.Count; x++) { connect5.WriteLine(symbol + "\t" + dailyprices[x].Timestamp + "\t" + dailyprices[x].Volume + "\t" + dailyprices[x].Open + "\t" + dailyprices[x].High + "\t" + dailyprices[x].Low + "\t" + dailyprices[x].Close); connect5s.WriteLine(symbol + "\t" + dailyprices[x].Timestamp + "\t" + dailyprices[x].Volume + "\t" + dailyprices[x].Open + "\t" + dailyprices[x].High + "\t" + dailyprices[x].Low + "\t" + dailyprices[x].Close); } decimal ten = ((dailyprices[0].Close - dailyprices[9].Close) / (dailyprices[9].Close)) * 100; decimal thirty = ((dailyprices[0].Close - dailyprices[29].Close) / (dailyprices[29].Close)) * 100; decimal sixty = ((dailyprices[0].Close - dailyprices[59].Close) / (dailyprices[59].Close)) * 100; decimal ninety = ((dailyprices[0].Close - dailyprices[89].Close) / (dailyprices[89].Close)) * 100; decimal N120 = ((dailyprices[0].Close - dailyprices[119].Close) / (dailyprices[119].Close)) * 100; decimal N240 = ((dailyprices[0].Close - dailyprices[239].Close) / (dailyprices[239].Close)) * 100; decimal N360 = ((dailyprices[0].Close - dailyprices[359].Close) / (dailyprices[359].Close)) * 100; connect6.WriteLine(symbol + " 10 day " + ten); connect6.WriteLine(symbol + " 30 day " + thirty); connect6.WriteLine(symbol + " 60 day " + sixty); connect6.WriteLine(symbol + " 90 day " + ninety); connect6.WriteLine(symbol + " 120 day " + N120); connect6.WriteLine(symbol + " 240 day " + N240); connect6.WriteLine(symbol + " 360 day " + N360); connect5s.Close(); } connect5.Close(); connect6.Close(); stocklist.Close(); }
static void Getstockdata() { //main1 //main2 //main3 try { XmlDocument xml = new XmlDocument(); String exepath = AppDomain.CurrentDomain.BaseDirectory; xml.Load(exepath + @"config.xml"); XmlNode tokenst = xml.SelectSingleNode("/configuration/token"); string token = tokenst.InnerText; XmlNode functionst = xml.SelectSingleNode("/configuration/function"); string function = functionst.InnerText; XmlNode outputsizest = xml.SelectSingleNode("/configuration/outputsize"); string outputsize = outputsizest.InnerText; XmlNode intervalst = xml.SelectSingleNode("/configuration/interval"); string interval = intervalst.InnerText; StreamReader stocklist = new StreamReader(@AppDomain.CurrentDomain.BaseDirectory + "stocklist.txt", Encoding.Default); string stockl = null; while ((stockl = stocklist.ReadLine()) != null) { var symbol = stockl; //StreamWriter connect5s = new StreamWriter(@symbol + "_daily.txt", true, Encoding.Default); StreamWriter connect5s = new StreamWriter(@symbol + "_" + function + "_" + interval + "_" + DateTime.Now.ToString().Replace("/", "_").Replace(" ", "_").Replace(":", "_") + ".txt", false, Encoding.Default); //var dailyprices = $"https://www.alphavantage.co/query?function=TIME_SERIES_DAILY&symbol={symbol}&outputsize=full&apikey={token}&datatype=csv" // .GetStringFromUrl().FromCsv<List<AlphaVantageData>>(); var prices = $"https://www.alphavantage.co/query?function={function}&symbol={symbol}&outputsize={outputsize}&interval={interval}&apikey={token}&datatype=csv" .GetStringFromUrl().FromCsv <List <Program.AlphaVantageData> >(); HSSFWorkbook wb; HSSFSheet sh; wb = HSSFWorkbook.Create(InternalWorkbook.CreateWorkbook()); // create sheet sh = (HSSFSheet)wb.CreateSheet("Sheet1"); IDataFormat dataFormatCustom = wb.CreateDataFormat(); ICellStyle style1 = wb.CreateCellStyle(); style1.DataFormat = dataFormatCustom.GetFormat("MM/dd/yyyy HH:mm:ss AM/PM"); XSSFWorkbook wbx; XSSFSheet shx; for (int x = 0; x < prices.Count; x++) { var r = sh.CreateRow(x); for (int j = 0; j < 7; j++) { IRow row = sh.GetRow(x); if (j == 0) { r.CreateCell(j); ICell cell1 = row.GetCell(j, MissingCellPolicy.CREATE_NULL_AS_BLANK); cell1.SetCellValue(symbol); } else if (j == 1) { r.CreateCell(j).CellStyle = style1; ICell cell1 = row.GetCell(j, MissingCellPolicy.CREATE_NULL_AS_BLANK); cell1.SetCellValue(prices[x].Timestamp); } else if (j == 2) { r.CreateCell(j); ICell cell1 = row.GetCell(j, MissingCellPolicy.CREATE_NULL_AS_BLANK); cell1.SetCellValue((double)prices[x].Volume); } else if (j == 3) { r.CreateCell(j); ICell cell1 = row.GetCell(j, MissingCellPolicy.CREATE_NULL_AS_BLANK); cell1.SetCellValue((double)prices[x].Open); } else if (j == 4) { r.CreateCell(j); ICell cell1 = row.GetCell(j, MissingCellPolicy.CREATE_NULL_AS_BLANK); cell1.SetCellValue((double)prices[x].High); } else if (j == 5) { r.CreateCell(j); ICell cell1 = row.GetCell(j, MissingCellPolicy.CREATE_NULL_AS_BLANK); cell1.SetCellValue((double)prices[x].Low); } else if (j == 6) { r.CreateCell(j); ICell cell1 = row.GetCell(j, MissingCellPolicy.CREATE_NULL_AS_BLANK); cell1.SetCellValue((double)prices[x].Close); } } } using (var fs = new FileStream(@symbol + "_" + function + "_" + interval + "_" + DateTime.Now.ToString().Replace("/", "_").Replace(" ", "_").Replace(":", "_") + ".xls", FileMode.Create, FileAccess.Write)) { wb.Write(fs); } //} for (int x = 0; x < prices.Count; x++) { //connect5.WriteLine(symbol + "\t" + prices[x].Timestamp + "\t" + prices[x].Volume + "\t" + prices[x].Open + "\t" + prices[x].High + "\t" + prices[x].Low + "\t" + prices[x].Close); connect5s.WriteLine(symbol + "\t" + prices[x].Timestamp + "\t" + prices[x].Volume + "\t" + prices[x].Open + "\t" + prices[x].High + "\t" + prices[x].Low + "\t" + prices[x].Close); } decimal ten = ((prices[0].Close - prices[9].Close) / (prices[9].Close)) * 100; decimal thirty = ((prices[0].Close - prices[29].Close) / (prices[29].Close)) * 100; decimal sixty = ((prices[0].Close - prices[59].Close) / (prices[59].Close)) * 100; decimal ninety = ((prices[0].Close - prices[89].Close) / (prices[89].Close)) * 100; decimal N120 = ((prices[0].Close - prices[119].Close) / (prices[119].Close)) * 100; decimal N240 = ((prices[0].Close - prices[239].Close) / (prices[239].Close)) * 100; decimal N360 = ((prices[0].Close - prices[359].Close) / (prices[359].Close)) * 100; //connect6.WriteLine(symbol + " 10 day " + ten); //connect6.WriteLine(symbol + " 30 day " + thirty); //connect6.WriteLine(symbol + " 60 day " + sixty); //connect6.WriteLine(symbol + " 90 day " + ninety); //connect6.WriteLine(symbol + " 120 day " + N120); //connect6.WriteLine(symbol + " 240 day " + N240); //connect6.WriteLine(symbol + " 360 day " + N360); connect5s.Close(); } //connect5.Close(); //connect6.Close(); stocklist.Close(); } catch (Exception e) { Console.WriteLine($"Generic Exception Handler: {e}"); } finally { } }
public static ActionResult DescartadosExcel() { var res = ActionResult.NoAction; var dictionary = HttpContext.Current.Session["Dictionary"] as Dictionary <string, string>; string path = HttpContext.Current.Request.PhysicalApplicationPath; if (!path.EndsWith(@"\", StringComparison.OrdinalIgnoreCase)) { path = string.Format(CultureInfo.InvariantCulture, @"{0}\", path); } string fileName = string.Format( CultureInfo.InvariantCulture, @"Descartados_{0:yyyyMMdd-hhmmss}.xls", DateTime.Now); var wb = HSSFWorkbook.Create(InternalWorkbook.CreateWorkbook()); var sh = (HSSFSheet)wb.CreateSheet("Presupuestos descartados"); var moneyCellStyle = wb.CreateCellStyle(); var hssfDataFormat = wb.CreateDataFormat(); moneyCellStyle.DataFormat = hssfDataFormat.GetFormat("#,##0.00"); var headerCellStyle = wb.CreateCellStyle(); var headerFont = wb.CreateFont(); headerFont.Boldweight = (short)FontBoldWeight.Bold; headerCellStyle.SetFont(headerFont); headerCellStyle.BorderBottom = BorderStyle.Double; var totalCellStyle = wb.CreateCellStyle(); var totalFont = wb.CreateFont(); totalFont.Boldweight = (short)FontBoldWeight.Bold; totalCellStyle.SetFont(headerFont); totalCellStyle.BorderTop = BorderStyle.Double; var totalValueCellStyle = wb.CreateCellStyle(); totalFont.Boldweight = (short)FontBoldWeight.Bold; totalValueCellStyle.SetFont(headerFont); totalValueCellStyle.BorderTop = BorderStyle.Double; totalValueCellStyle.BorderBottom = BorderStyle.None; totalValueCellStyle.DataFormat = hssfDataFormat.GetFormat("#,##0.00"); var titleCellStyle = wb.CreateCellStyle(); var titleFont = wb.CreateFont(); titleFont.Boldweight = (short)FontBoldWeight.Bold; titleFont.FontHeight = 400; titleCellStyle.SetFont(titleFont); var decimalFormat = wb.CreateCellStyle(); decimalFormat.DataFormat = wb.CreateDataFormat().GetFormat("#.00"); var integerformat = wb.CreateCellStyle(); integerformat.DataFormat = wb.CreateDataFormat().GetFormat("#0"); var cra = new CellRangeAddress(0, 1, 0, 4); sh.AddMergedRegion(cra); if (sh.GetRow(0) == null) { sh.CreateRow(0); } sh.GetRow(0).CreateCell(0); sh.GetRow(0).GetCell(0).SetCellValue("Presupuestos descartados"); sh.GetRow(0).GetCell(0).CellStyle = titleCellStyle; var dataFormatCustom = wb.CreateDataFormat(); // Crear Cabecera var headers = new List <string>() { "Centro", "Fecha", "Presupuesto", "Asegurado", "DNI/NIF", "Poliza", "Colectivo", "Mascota", "Chip", "Tipo", "Sexo" }; int countColumns = 0; foreach (string headerLabel in headers) { if (sh.GetRow(3) == null) { sh.CreateRow(3); } if (sh.GetRow(3).GetCell(countColumns) == null) { sh.GetRow(3).CreateCell(countColumns); } sh.GetRow(3).GetCell(countColumns).SetCellValue(headerLabel); sh.GetRow(3).GetCell(countColumns).CellStyle = headerCellStyle; countColumns++; } int countRow = 4; using (var cmd = new SqlCommand("ASPADLand_Admin_TracesPresupuestos_Descartados")) { using (var cnn = new SqlConnection(ConfigurationManager.ConnectionStrings["cns"].ConnectionString)) { cmd.Connection = cnn; cmd.CommandType = CommandType.StoredProcedure; try { cmd.Connection.Open(); var acutalCentro = Guid.Empty; var actualMascota = Guid.Empty; var fecha = DateTime.Now; using (var rdr = cmd.ExecuteReader()) { while (rdr.Read()) { if (actualMascota == rdr.GetGuid(6) && acutalCentro == rdr.GetGuid(10) && fecha.Date == rdr.GetDateTime(2).Date) { continue; } actualMascota = rdr.GetGuid(6); acutalCentro = rdr.GetGuid(10); fecha = rdr.GetDateTime(2).Date; if (sh.GetRow(countRow) == null) { sh.CreateRow(countRow); } // Centro if (sh.GetRow(countRow).GetCell(0) == null) { sh.GetRow(countRow).CreateCell(0); } sh.GetRow(countRow).GetCell(0).SetCellValue(rdr[0].ToString() + " - " + rdr.GetString(1)); // Fecha if (sh.GetRow(countRow).GetCell(1) == null) { sh.GetRow(countRow).CreateCell(1); } sh.GetRow(countRow).GetCell(1).CellStyle.DataFormat = dataFormatCustom.GetFormat("dd/MM/yyyy"); sh.GetRow(countRow).GetCell(1).SetCellValue(rdr.GetDateTime(2)); // Presupuesto if (sh.GetRow(countRow).GetCell(2) == null) { sh.GetRow(countRow).CreateCell(2); } sh.GetRow(countRow).GetCell(2).SetCellValue(rdr.GetString(5)); // Asegurado if (sh.GetRow(countRow).GetCell(3) == null) { sh.GetRow(countRow).CreateCell(3); } sh.GetRow(countRow).GetCell(3).SetCellValue(rdr.GetString(15)); // DNI if (sh.GetRow(countRow).GetCell(4) == null) { sh.GetRow(countRow).CreateCell(4); } sh.GetRow(countRow).GetCell(4).SetCellValue(rdr.GetString(9)); // Poliza if (sh.GetRow(countRow).GetCell(5) == null) { sh.GetRow(countRow).CreateCell(5); } sh.GetRow(countRow).GetCell(5).SetCellValue(rdr.GetString(7)); // Poliza if (sh.GetRow(countRow).GetCell(6) == null) { sh.GetRow(countRow).CreateCell(6); } sh.GetRow(countRow).GetCell(6).SetCellValue(rdr.GetString(8)); // Poliza if (sh.GetRow(countRow).GetCell(7) == null) { sh.GetRow(countRow).CreateCell(7); } sh.GetRow(countRow).GetCell(7).SetCellValue(rdr.GetString(11)); // Poliza var sexo = ""; if (rdr.GetString(12) == "100000000") { sexo = "macho"; } if (rdr.GetString(12) == "100000001") { sexo = "hembra"; } if (sh.GetRow(countRow).GetCell(8) == null) { sh.GetRow(countRow).CreateCell(8); } sh.GetRow(countRow).GetCell(8).SetCellValue(sexo); // Poliza var tipo = ""; if (rdr.GetString(13) == "100000000") { tipo = "perro"; } if (rdr.GetString(13) == "100000001") { tipo = "gato"; } if (sh.GetRow(countRow).GetCell(9) == null) { sh.GetRow(countRow).CreateCell(9); } sh.GetRow(countRow).GetCell(9).SetCellValue(tipo); // Poliza if (sh.GetRow(countRow).GetCell(10) == null) { sh.GetRow(countRow).CreateCell(10); } sh.GetRow(countRow).GetCell(10).SetCellValue(rdr.GetString(14)); countRow++; } } } finally { if (cmd.Connection.State != ConnectionState.Closed) { cmd.Connection.Close(); } } } } sh.SetColumnWidth(0, 12000); sh.SetColumnWidth(1, 3000); sh.SetColumnWidth(2, 4000); sh.SetColumnWidth(3, 20000); sh.SetColumnWidth(4, 4000); sh.SetColumnWidth(5, 6000); sh.SetColumnWidth(6, 6000); sh.SetColumnWidth(7, 6000); sh.SetColumnWidth(8, 4000); sh.SetColumnWidth(9, 4000); if (!path.EndsWith("\\")) { path += "\\Temp\\"; } else { path += "Temp\\"; } using (var fs = new FileStream(string.Format("{0}{1}", path, fileName), FileMode.Create, FileAccess.Write)) { wb.Write(fs); } res.SetSuccess(string.Format("/Temp/{0}", fileName)); return(res); }
private void btn_ExportGoodssalesReport_Click(object sender, EventArgs e) { FolderBrowserDialog folderBrowserDialog = new FolderBrowserDialog(); if (folderBrowserDialog.ShowDialog() != DialogResult.OK) { return; } string selectedPath = folderBrowserDialog.SelectedPath; string text = selectedPath + str_file_location + str_file_name + _strFromDate.Replace("-", "") + "-" + _strToDate.Replace("-", "") + str_file_type; FileInfo file = new FileInfo(text); int num = 2; if (!File.Exists(text)) { wb = HSSFWorkbook.Create(InternalWorkbook.CreateWorkbook()); sh = (HSSFSheet)wb.CreateSheet("Sheet1"); for (int i = 0; i < dataGridView2.RowCount + num; i++) { IRow row = sh.CreateRow(i); for (int j = 0; j < dataGridView2.ColumnCount; j++) { row.CreateCell(j); } } using (FileStream @out = new FileStream(text, FileMode.Create, FileAccess.Write)) { wb.Write(@out); } } if (!IsFileLocked(file)) { using (FileStream s = new FileStream(text, FileMode.Open, FileAccess.Read)) { wb = new HSSFWorkbook(s); for (int k = 0; k < wb.Count; k++) { lst_Sheet.Add(wb.GetSheetAt(k).SheetName); } } } if (!IsFileLocked(file)) { sh = (HSSFSheet)wb.GetSheet(lst_Sheet[0]); int l = 0; int num2 = 1; string[] array = new string[8] { "日期:", _strFromDate + "~" + _strToDate, "資料類型:", _data_type, "商品類型:", _goods_type, "商品狀態:", _goods_status }; string[] array2 = new string[8] { "商品條碼", "商品名稱", "商品銷售總額", "銷售數量", "退貨數量", "總客次", "", "" }; num2 = ((array.Length > array2.Length) ? array.Length : array2.Length); for (; l < num; l++) { for (int m = 0; m < num2; m++) { if (sh.GetRow(l).GetCell(m) == null) { sh.GetRow(l).CreateCell(m); } if (l == 0) { sh.GetRow(l).GetCell(m).SetCellValue(array[m]); } if (l == 1) { sh.GetRow(l).GetCell(m).SetCellValue(array2[m]); } } } for (int n = 0; n < dataGridView2.RowCount; n++) { if (sh.GetRow(l) == null) { sh.CreateRow(l); } for (int num3 = 0; num3 < dataGridView2.ColumnCount; num3++) { if (sh.GetRow(l).GetCell(num3) == null) { sh.GetRow(l).CreateCell(num3); } if (dataGridView2[num3, n].Value != null) { sh.GetRow(l).GetCell(num3).SetCellValue(dataGridView2[num3, n].Value.ToString()); } } l++; } using (FileStream out2 = new FileStream(text, FileMode.Open, FileAccess.Write)) { wb.Write(out2); AutoClosingMessageBox.Show("匯出報表於" + text); } } else { AutoClosingMessageBox.Show(text + "檔案使用中,請確認檔案是在未開啟的狀態下"); } }
public static ActionResult Excel(int companyId, DateTime?dateFrom, DateTime?dateTo, string indicadorName, int indicadorId, string listOrder) { var res = ActionResult.NoAction; var user = HttpContext.Current.Session["User"] as ApplicationUser; dictionary = HttpContext.Current.Session["Dictionary"] as Dictionary <string, string>; var company = new Company(companyId); var registros = IndicadorRegistro.ByIndicadorId(indicadorId, companyId).ToList(); string path = HttpContext.Current.Request.PhysicalApplicationPath; if (!path.EndsWith(@"\", StringComparison.OrdinalIgnoreCase)) { path = string.Format(CultureInfo.InvariantCulture, @"{0}\", path); } var formatedDescription = ToolsPdf.NormalizeFileName(indicadorName); string fileName = string.Format( CultureInfo.InvariantCulture, @"{0}_{1}_{2:yyyyMMddhhmmss}.xls", dictionary["Item_Indicador_RecordsReportTitle"], formatedDescription, DateTime.Now); fileName = fileName.Replace("%", ""); var wb = HSSFWorkbook.Create(InternalWorkbook.CreateWorkbook()); var sh = (HSSFSheet)wb.CreateSheet(dictionary["Item_Indicador_RecordsReportTitle"]); var shCriteria = (HSSFSheet)wb.CreateSheet(dictionary["Common_SearchCriteria"]); var moneyCellStyle = wb.CreateCellStyle(); var hssfDataFormat = wb.CreateDataFormat(); moneyCellStyle.DataFormat = hssfDataFormat.GetFormat("#,##0.00"); var headerCellStyle = wb.CreateCellStyle(); var headerFont = wb.CreateFont(); headerFont.Boldweight = (short)FontBoldWeight.Bold; headerCellStyle.SetFont(headerFont); headerCellStyle.BorderBottom = BorderStyle.Double; var totalCellStyle = wb.CreateCellStyle(); var totalFont = wb.CreateFont(); totalFont.Boldweight = (short)FontBoldWeight.Bold; totalCellStyle.SetFont(headerFont); totalCellStyle.BorderTop = BorderStyle.Double; var totalValueCellStyle = wb.CreateCellStyle(); totalFont.Boldweight = (short)FontBoldWeight.Bold; totalValueCellStyle.SetFont(headerFont); totalValueCellStyle.BorderTop = BorderStyle.Double; totalValueCellStyle.BorderBottom = BorderStyle.None; totalValueCellStyle.DataFormat = hssfDataFormat.GetFormat("#,##0.00"); var titleCellStyle = wb.CreateCellStyle(); var titleFont = wb.CreateFont(); titleFont.Boldweight = (short)FontBoldWeight.Bold; titleFont.FontHeight = 400; titleCellStyle.SetFont(titleFont); var decimalFormat = wb.CreateCellStyle(); decimalFormat.DataFormat = wb.CreateDataFormat().GetFormat("#.00"); var integerformat = wb.CreateCellStyle(); integerformat.DataFormat = wb.CreateDataFormat().GetFormat("#0"); var cra = new CellRangeAddress(0, 1, 0, 4); sh.AddMergedRegion(cra); if (sh.GetRow(0) == null) { sh.CreateRow(0); } sh.GetRow(0).CreateCell(0); sh.GetRow(0).GetCell(0).SetCellValue(string.Format(CultureInfo.InvariantCulture, "{0} - {1}", dictionary["Item_Indicador_RecordsReportTitle"], indicadorName)); sh.GetRow(0).GetCell(0).CellStyle = titleCellStyle; var dataFormatCustom = wb.CreateDataFormat(); // Condiciones del filtro if (shCriteria.GetRow(1) == null) { shCriteria.CreateRow(1); } if (shCriteria.GetRow(2) == null) { shCriteria.CreateRow(2); } if (shCriteria.GetRow(3) == null) { shCriteria.CreateRow(3); } if (shCriteria.GetRow(1).GetCell(1) == null) { shCriteria.GetRow(1).CreateCell(1); } shCriteria.GetRow(1).GetCell(1).SetCellValue(dictionary["Item_Indicador_Field_Name"]); if (shCriteria.GetRow(1).GetCell(2) == null) { shCriteria.GetRow(1).CreateCell(2); } shCriteria.GetRow(1).GetCell(2).SetCellValue(indicadorName); if (shCriteria.GetRow(2).GetCell(1) == null) { shCriteria.GetRow(2).CreateCell(1); } shCriteria.GetRow(2).GetCell(1).SetCellValue(dictionary["Common_From"]); if (shCriteria.GetRow(2).GetCell(2) == null) { shCriteria.GetRow(2).CreateCell(2); } string fromValue = "-"; if (dateFrom.HasValue) { fromValue = string.Format(CultureInfo.InvariantCulture, "{0:dd/MM/yyyy}", dateFrom.Value); } shCriteria.GetRow(2).GetCell(2).SetCellValue(fromValue); if (shCriteria.GetRow(3).GetCell(1) == null) { shCriteria.GetRow(3).CreateCell(1); } shCriteria.GetRow(3).GetCell(1).SetCellValue(dictionary["Common_To"]); if (shCriteria.GetRow(3).GetCell(2) == null) { shCriteria.GetRow(3).CreateCell(2); } string toValue = "-"; if (dateTo.HasValue) { toValue = string.Format(CultureInfo.InvariantCulture, "{0:dd/MM/yyyy}", dateTo.Value); } shCriteria.GetRow(3).GetCell(2).SetCellValue(toValue); // Crear Cabecera var headers = new List <string>() { dictionary["Item_Indicador_TableRecords_Header_Status"].ToUpperInvariant(), dictionary["Item_Indicador_TableRecords_Header_Value"].ToUpperInvariant(), dictionary["Item_Indicador_TableRecords_Header_Date"].ToUpperInvariant(), dictionary["Item_Indicador_TableRecords_Header_Comments"].ToUpperInvariant(), dictionary["Item_Indicador_TableRecords_Header_Meta"].ToUpperInvariant(), dictionary["Item_Indicador_TableRecords_Header_Alarm"].ToUpperInvariant(), dictionary["Item_Indicador_TableRecords_Header_Responsible"].ToUpperInvariant() }; int countColumns = 0; foreach (string headerLabel in headers) { if (sh.GetRow(3) == null) { sh.CreateRow(3); } if (sh.GetRow(3).GetCell(countColumns) == null) { sh.GetRow(3).CreateCell(countColumns); } sh.GetRow(3).GetCell(countColumns).SetCellValue(headerLabel); sh.GetRow(3).GetCell(countColumns).CellStyle = headerCellStyle; countColumns++; } int countRow = 4; if (dateFrom.HasValue) { registros = registros.Where(r => r.Date >= dateFrom).ToList(); } if (dateTo.HasValue) { registros = registros.Where(r => r.Date <= dateTo).ToList(); } switch (listOrder.ToUpperInvariant()) { default: case "TH1|ASC": registros = registros.OrderBy(d => d.Value).ToList(); break; case "TH1|DESC": registros = registros.OrderByDescending(d => d.Value).ToList(); break; case "TH2|ASC": registros = registros.OrderBy(d => d.Date).ToList(); break; case "TH2|DESC": registros = registros.OrderByDescending(d => d.Date).ToList(); break; case "TH4|ASC": registros = registros.OrderBy(d => d.Meta).ToList(); break; case "TH4|DESC": registros = registros.OrderByDescending(d => d.Meta).ToList(); break; case "TH5|ASC": registros = registros.OrderBy(d => d.Alarma).ToList(); break; case "TH5|DESC": registros = registros.OrderByDescending(d => d.Alarma).ToList(); break; case "TH6|ASC": registros = registros.OrderBy(d => d.Responsible.FullName).ToList(); break; case "TH6|DESC": registros = registros.OrderByDescending(d => d.Responsible.FullName).ToList(); break; } foreach (var registro in registros) { if (sh.GetRow(countRow) == null) { sh.CreateRow(countRow); } string metaText = IndicadorRegistro.ComparerLabelSign(registro.MetaComparer, dictionary); string alarmText = IndicadorRegistro.ComparerLabelSign(registro.AlarmaComparer, dictionary); string statusLabel = dictionary["Item_Objetivo_StatusLabelWithoutMeta"]; if (!string.IsNullOrEmpty(metaText)) { metaText = string.Format(CultureInfo.InvariantCulture, "{0} {1:#,##0.00}", metaText, registro.Meta); if (metaText == "=" && registro.Value == registro.Meta) { statusLabel = dictionary["Item_Objetivo_StatusLabelMeta"]; } else if (metaText == ">" && registro.Value > registro.Meta) { statusLabel = dictionary["Item_Objetivo_StatusLabelMeta"]; } else if (metaText == ">=" && registro.Value >= registro.Meta) { statusLabel = dictionary["Item_Objetivo_StatusLabelMeta"]; } else if (metaText == "<" && registro.Value < registro.Meta) { statusLabel = dictionary["Item_Objetivo_StatusLabelMeta"]; } else if (metaText == "<=" && registro.Value <= registro.Meta) { statusLabel = dictionary["Item_Objetivo_StatusLabelMeta"]; } else if (!string.IsNullOrEmpty(alarmText)) { if (alarmText == ">" && registro.Value > registro.Alarma) { statusLabel = dictionary["Item_Objetivo_StatusLabelWarning"]; } else if (alarmText == ">=" && registro.Value >= registro.Alarma) { statusLabel = dictionary["Item_Objetivo_StatusLabelWarning"]; } else if (alarmText == "<" && registro.Value < registro.Alarma) { statusLabel = dictionary["Item_Objetivo_StatusLabelWarning"]; } else if (alarmText == "<=" && registro.Value <= registro.Alarma) { statusLabel = dictionary["Item_Objetivo_StatusLabelWarning"]; } else { statusLabel = dictionary["Item_Objetivo_StatusLabelNoMeta"]; } } else { statusLabel = dictionary["Item_Objetivo_StatusLabelNoMeta"]; } } // Status if (sh.GetRow(countRow).GetCell(0) == null) { sh.GetRow(countRow).CreateCell(0); } sh.GetRow(countRow).GetCell(0).SetCellValue(statusLabel); // Value if (sh.GetRow(countRow).GetCell(1) == null) { sh.GetRow(countRow).CreateCell(1); } sh.GetRow(countRow).GetCell(1).SetCellType(CellType.Numeric); sh.GetRow(countRow).GetCell(1).CellStyle = moneyCellStyle; sh.GetRow(countRow).GetCell(1).SetCellValue(Convert.ToDouble(registro.Value)); // Date if (sh.GetRow(countRow).GetCell(2) == null) { sh.GetRow(countRow).CreateCell(2); } sh.GetRow(countRow).GetCell(2).CellStyle.DataFormat = dataFormatCustom.GetFormat("dd/MM/yyyy"); sh.GetRow(countRow).GetCell(2).SetCellValue(registro.Date); // Comments if (sh.GetRow(countRow).GetCell(3) == null) { sh.GetRow(countRow).CreateCell(3); } sh.GetRow(countRow).GetCell(3).SetCellValue(registro.Comments); // Meta if (sh.GetRow(countRow).GetCell(4) == null) { sh.GetRow(countRow).CreateCell(4); } sh.GetRow(countRow).GetCell(4).SetCellValue(metaText); // Alarm if (!registro.Alarma.HasValue) { alarmText = string.Empty; } else { alarmText = string.Format(CultureInfo.InvariantCulture, "{0} {1:#,##0.00}", alarmText, registro.Alarma); } if (sh.GetRow(countRow).GetCell(5) == null) { sh.GetRow(countRow).CreateCell(5); } sh.GetRow(countRow).GetCell(5).SetCellValue(alarmText); // Responsible if (sh.GetRow(countRow).GetCell(6) == null) { sh.GetRow(countRow).CreateCell(6); } sh.GetRow(countRow).GetCell(6).SetCellValue(registro.Responsible.FullName); countRow++; } sh.SetColumnWidth(0, 4000); sh.SetColumnWidth(1, 4000); sh.SetColumnWidth(2, 4000); sh.SetColumnWidth(3, 10000); sh.SetColumnWidth(4, 8400); sh.SetColumnWidth(5, 8400); sh.SetColumnWidth(6, 8400); if (!path.EndsWith("\\")) { path += "\\Temp\\"; } else { path += "Temp\\"; } using (var fs = new FileStream(string.Format("{0}{1}", path, fileName), FileMode.Create, FileAccess.Write)) { wb.Write(fs); } res.SetSuccess(string.Format("/Temp/{0}", fileName)); return(res); }