/*****************************************************
* 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);
}
