private bool movedBuiltins = false; // Flag to see if need to
// Check the built in list
// or if the regular list
// has all entries.
/// <summary>
/// Construncts a new data formatter. It takes a workbook to have
/// access to the workbooks format records.
/// </summary>
/// <param name="workbook">the workbook the formats are tied to.</param>
public HSSFDataFormat(InternalWorkbook workbook)
{
this.workbook = workbook;
IEnumerator i = workbook.Formats.GetEnumerator();
while (i.MoveNext())
{
FormatRecord r = (FormatRecord)i.Current;
for (int j = formats.Count; formats.Count <= r.IndexCode; j++)
{
formats.Add(null);
}
formats[r.IndexCode] = r.FormatString;
}
}
/// <summary>
/// This must be called to Setup the internal work book references whenever
/// a RichTextString Is Added to a cell
/// </summary>
/// <param name="book">The workbook.</param>
/// <param name="record">The record.</param>
public void SetWorkbookReferences(InternalWorkbook book, LabelSSTRecord record)
{
this._book = book;
this._record = record;
}
/// <summary>
/// Wraps up your stub Workbook as a stub HSSFWorkbook, ready for passing to HSSFFormulaParser
/// </summary>
/// <param name="workbook">The stub workbook.</param>
/// <returns></returns>
public static HSSFWorkbook CreateStubHSSFWorkbook(InternalWorkbook workbook)
{
return(new StubHSSFWorkbook(workbook));
}
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 void TestFontStuff()
{
HSSFWorkbook hwb = new HSSFWorkbook();
InternalWorkbook wb = TestHSSFWorkbook.GetInternalWorkbook(hwb);
Assert.AreEqual(4, wb.NumberOfFontRecords);
Assert.AreEqual(68, wb.Records.Count);
FontRecord f1 = wb.GetFontRecordAt(0);
FontRecord f4 = wb.GetFontRecordAt(3);
Assert.AreEqual(0, wb.GetFontIndex(f1));
Assert.AreEqual(3, wb.GetFontIndex(f4));
Assert.AreEqual(f1, wb.GetFontRecordAt(0));
Assert.AreEqual(f4, wb.GetFontRecordAt(3));
// There is no 4! new ones go in at 5
FontRecord n = wb.CreateNewFont();
Assert.AreEqual(69, wb.Records.Count);
Assert.AreEqual(5, wb.NumberOfFontRecords);
Assert.AreEqual(5, wb.GetFontIndex(n));
Assert.AreEqual(n, wb.GetFontRecordAt(5));
// And another
FontRecord n6 = wb.CreateNewFont();
Assert.AreEqual(70, wb.Records.Count);
Assert.AreEqual(6, wb.NumberOfFontRecords);
Assert.AreEqual(6, wb.GetFontIndex(n6));
Assert.AreEqual(n6, wb.GetFontRecordAt(6));
// Now remove the one formerly at 5
Assert.AreEqual(70, wb.Records.Count);
wb.RemoveFontRecord(n);
// Check that 6 has gone to 5
Assert.AreEqual(69, wb.Records.Count);
Assert.AreEqual(5, wb.NumberOfFontRecords);
Assert.AreEqual(5, wb.GetFontIndex(n6));
Assert.AreEqual(n6, wb.GetFontRecordAt(5));
// Check that the earlier ones are unChanged
Assert.AreEqual(0, wb.GetFontIndex(f1));
Assert.AreEqual(3, wb.GetFontIndex(f4));
Assert.AreEqual(f1, wb.GetFontRecordAt(0));
Assert.AreEqual(f4, wb.GetFontRecordAt(3));
// Finally, add another one
FontRecord n7 = wb.CreateNewFont();
Assert.AreEqual(70, wb.Records.Count);
Assert.AreEqual(6, wb.NumberOfFontRecords);
Assert.AreEqual(6, wb.GetFontIndex(n7));
Assert.AreEqual(n7, wb.GetFontRecordAt(6));
hwb.Close();
}
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 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 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);
}
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 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 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);
}
/**
* used internally to Set the workbook properties.
*/
private void SetPropertiesFromWorkbook(InternalWorkbook book)
{
this.workbook = book;
// none currently
}
/**
* given a POI POIFSFileSystem object, and a specific directory
* within it, read in its Workbook and populate the high and
* low level models. If you're reading in a workbook...start here.
*
* @param directory the POI filesystem directory to process from
* @param preserveNodes whether to preseve other nodes, such as
* macros. This takes more memory, so only say yes if you
* need to. If set, will store all of the POIFSFileSystem
* in memory
* @see org.apache.poi.poifs.filesystem.POIFSFileSystem
* @exception IOException if the stream cannot be read
*/
public HSSFWorkbook(DirectoryNode directory, bool preserveNodes):base(directory)
{
String workbookName = GetWorkbookDirEntryName(directory);
this.preserveNodes = preserveNodes;
// If we're not preserving nodes, don't track the
// POIFS any more
if (!preserveNodes)
{
this.directory = null;
}
_sheets = new List<HSSFSheet>(INITIAL_CAPACITY);
names = new List<HSSFName>(INITIAL_CAPACITY);
// Grab the data from the workbook stream, however
// it happens to be spelled.
Stream stream = directory.CreatePOIFSDocumentReader(workbookName);
List<Record> records = RecordFactory.CreateRecords(stream);
workbook = InternalWorkbook.CreateWorkbook(records);
SetPropertiesFromWorkbook(workbook);
int recOffset = workbook.NumRecords;
// Convert all LabelRecord records to LabelSSTRecord
ConvertLabelRecords(records, recOffset);
RecordStream rs = new RecordStream(records, recOffset);
while (rs.HasNext())
{
InternalSheet sheet = InternalSheet.CreateSheet(rs);
_sheets.Add(new HSSFSheet(this, sheet));
}
for (int i = 0; i < workbook.NumNames; ++i)
{
NameRecord nameRecord = workbook.GetNameRecord(i);
HSSFName name = new HSSFName(this, workbook.GetNameRecord(i), workbook.GetNameCommentRecord(nameRecord));
names.Add(name);
}
}
public HSSFWorkbook(InternalWorkbook book)
: base((DirectoryNode)null)
{
workbook = book;
_sheets = new List<HSSFSheet>(INITIAL_CAPACITY);
names = new List<HSSFName>(INITIAL_CAPACITY);
}
public static HSSFWorkbook Create(InternalWorkbook book)
{
return new HSSFWorkbook(book);
}
/// <summary>
/// Creates an HSSFSheet representing the given Sheet object. Should only be
/// called by HSSFWorkbook when reading in an exisiting file.
/// </summary>
/// <param name="workbook">The HSSF Workbook object associated with the _sheet.</param>
/// <param name="sheet">lowlevel Sheet object this _sheet will represent</param>
/// <see cref="LF.Utils.NPOI.HSSF.UserModel.HSSFWorkbook(LF.Utils.NPOI.POIFS.FileSystem.DirectoryNode, bool)"/>
public HSSFSheet(HSSFWorkbook workbook, InternalSheet sheet)
{
this._sheet = sheet;
rows = new Dictionary<int, LF.Utils.NPOI.SS.UserModel.IRow>();
this._workbook = workbook;
this.book = _workbook.Workbook;
SetPropertiesFromSheet(_sheet);
}
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);
}
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();
}
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 StubHSSFWorkbook(InternalWorkbook wb)
: base(wb)
{
}
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);
}
/// <summary>
/// Wraps up your stub Workbook as a stub
/// HSSFWorkbook, ready for passing to
/// HSSFFormulaParser
/// </summary>
/// <param name="workbook">The stub workbook.</param>
/// <returns></returns>
public static HSSFWorkbook CreateStubHSSFWorkbook(InternalWorkbook workbook)
{
return new StubHSSFWorkbook(workbook);
}
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 void TestReadFeatRecord()
{
HSSFWorkbook hssf =
HSSFTestDataSamples.OpenSampleWorkbook("46136-NoWarnings.xls");
InternalWorkbook wb = HSSFTestHelper.GetWorkbookForTest(hssf);
FeatRecord fr = null;
FeatHdrRecord fhr = null;
Assert.AreEqual(1, hssf.NumberOfSheets);
// First check it isn't on the Workbook
int countFR = 0;
int countFRH = 0;
foreach (Record r in wb.Records)
{
if (r is FeatRecord)
{
fr = (FeatRecord)r;
countFR++;
}
else if (r.Sid == FeatRecord.sid)
{
Assert.Fail("FeatRecord SID found but not Created correctly!");
}
if (r is FeatHdrRecord)
{
countFRH++;
}
else if (r.Sid == FeatHdrRecord.sid)
{
Assert.Fail("FeatHdrRecord SID found but not Created correctly!");
}
}
Assert.AreEqual(0, countFR);
Assert.AreEqual(0, countFRH);
// Now find it on our sheet
HSSFSheet s = (HSSFSheet)hssf.GetSheetAt(0);
InternalSheet sheet = HSSFTestHelper.GetSheetForTest(s);
foreach (RecordBase rb in sheet.Records)
{
if (rb is Record)
{
Record r = (Record)rb;
if (r is FeatRecord)
{
fr = (FeatRecord)r;
countFR++;
}
else if (r.Sid == FeatRecord.sid)
{
countFR++;
}
if (r is FeatHdrRecord)
{
fhr = (FeatHdrRecord)r;
countFRH++;
}
else if (r.Sid == FeatHdrRecord.sid)
{
countFRH++;
}
}
}
Assert.AreEqual(1, countFR);
Assert.AreEqual(1, countFRH);
Assert.IsNotNull(fr);
Assert.IsNotNull(fhr);
// Now check the contents are as expected
Assert.AreEqual(
FeatHdrRecord.SHAREDFEATURES_ISFFEC2,
fr.Isf_sharedFeatureType
);
// Applies to one cell only
Assert.AreEqual(1, fr.CellRefs.Length);
Assert.AreEqual(0, fr.CellRefs[0].FirstRow);
Assert.AreEqual(0, fr.CellRefs[0].LastRow);
Assert.AreEqual(0, fr.CellRefs[0].FirstColumn);
Assert.AreEqual(0, fr.CellRefs[0].LastColumn);
// More Checking of shared features stuff
Assert.AreEqual(4, fr.CbFeatData);
Assert.AreEqual(4, fr.SharedFeature.DataSize);
Assert.AreEqual(typeof(FeatFormulaErr2), fr.SharedFeature.GetType());
FeatFormulaErr2 fferr2 = (FeatFormulaErr2)fr.SharedFeature;
Assert.AreEqual(0x04, fferr2.RawErrorCheckValue);
Assert.IsFalse(fferr2.CheckCalculationErrors);
Assert.IsFalse(fferr2.CheckDateTimeFormats);
Assert.IsFalse(fferr2.CheckEmptyCellRef);
Assert.IsFalse(fferr2.CheckInconsistentFormulas);
Assert.IsFalse(fferr2.CheckInconsistentRanges);
Assert.IsTrue(fferr2.CheckNumbersAsText);
Assert.IsFalse(fferr2.CheckUnprotectedFormulas);
Assert.IsFalse(fferr2.PerformDataValidation);
}
public void Run(ExcelSheet <ExcelTransactionExtended> 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);
row.CreateCell(10).SetCellValue(item.IsOmitted ? "1" : "");
row.CreateCell(11).SetCellValue(item.GroupId);
row.CreateCell(12).SetCellValue(string.Join(",", item.TagNames));
row.CreateCell(13).SetCellValue(item.TagGroupId);
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();
}
public void TestWithoutFeatRecord()
{
HSSFWorkbook hssf =
HSSFTestDataSamples.OpenSampleWorkbook("46136-WithWarnings.xls");
InternalWorkbook wb = HSSFTestHelper.GetWorkbookForTest(hssf);
Assert.AreEqual(1, hssf.NumberOfSheets);
int countFR = 0;
int countFRH = 0;
// Check on the workbook, but shouldn't be there!
foreach (Record r in wb.Records)
{
if (r is FeatRecord)
{
countFR++;
}
else if (r.Sid == FeatRecord.sid)
{
countFR++;
}
if (r is FeatHdrRecord)
{
countFRH++;
}
else if (r.Sid == FeatHdrRecord.sid)
{
countFRH++;
}
}
Assert.AreEqual(0, countFR);
Assert.AreEqual(0, countFRH);
// Now check on the sheet
HSSFSheet s = (HSSFSheet)hssf.GetSheetAt(0);
InternalSheet sheet = HSSFTestHelper.GetSheetForTest(s);
foreach (RecordBase rb in sheet.Records)
{
if (rb is Record)
{
Record r = (Record)rb;
if (r is FeatRecord)
{
countFR++;
}
else if (r.Sid == FeatRecord.sid)
{
countFR++;
}
if (r is FeatHdrRecord)
{
countFRH++;
}
else if (r.Sid == FeatHdrRecord.sid)
{
countFRH++;
}
}
}
Assert.AreEqual(0, countFR);
Assert.AreEqual(0, countFRH);
}
public StubHSSFWorkbook(InternalWorkbook wb)
: base(wb)
{
}
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);
}
/// <summary>
/// Initializes a new instance of the <see cref="HSSFRichTextString"/> class.
/// </summary>
/// <param name="book">The workbook.</param>
/// <param name="record">The record.</param>
public HSSFRichTextString(InternalWorkbook book, LabelSSTRecord record)
{
SetWorkbookReferences(book, record);
this._string = book.GetSSTString(record.SSTIndex);
}
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);
}
public void TestFormulas()
{
FormulaRecord[] fRecs = mockListen.GetFormulaRecords();
// Check our formula records
Assert.AreEqual(6, fRecs.Length);
InternalWorkbook stubWB = listener.GetStubWorkbook();
Assert.IsNotNull(stubWB);
HSSFWorkbook stubHSSF = listener.GetStubHSSFWorkbook();
Assert.IsNotNull(stubHSSF);
// Check these stubs have the right stuff on them
Assert.AreEqual("Sheet1", stubWB.GetSheetName(0));
Assert.AreEqual("S2", stubWB.GetSheetName(1));
Assert.AreEqual("Sh3", stubWB.GetSheetName(2));
// Check we can Get the formula without breaking
for (int i = 0; i < fRecs.Length; i++)
{
HSSFFormulaParser.ToFormulaString(stubHSSF, fRecs[i].ParsedExpression);
}
// Peer into just one formula, and check that
// all the ptgs give back the right things
Ptg[] ptgs = fRecs[0].ParsedExpression;
Assert.AreEqual(1, ptgs.Length);
Assert.IsTrue(ptgs[0] is Ref3DPtg);
Ref3DPtg ptg = (Ref3DPtg)ptgs[0];
HSSFEvaluationWorkbook book = HSSFEvaluationWorkbook.Create(stubHSSF);
Assert.AreEqual("Sheet1!A1", ptg.ToFormulaString(book));
// Now check we Get the right formula back for
// a few sample ones
FormulaRecord fr;
// Sheet 1 A2 is on same sheet
fr = fRecs[0];
Assert.AreEqual(1, fr.Row);
Assert.AreEqual(0, fr.Column);
Assert.AreEqual("Sheet1!A1", HSSFFormulaParser.ToFormulaString(stubHSSF, fr.ParsedExpression));
// Sheet 1 A5 is to another sheet
fr = fRecs[3];
Assert.AreEqual(4, fr.Row);
Assert.AreEqual(0, fr.Column);
Assert.AreEqual("'S2'!A1", HSSFFormulaParser.ToFormulaString(stubHSSF, fr.ParsedExpression));
// Sheet 1 A7 is to another sheet, range
fr = fRecs[5];
Assert.AreEqual(6, fr.Row);
Assert.AreEqual(0, fr.Column);
Assert.AreEqual("SUM(Sh3!A1:A4)", HSSFFormulaParser.ToFormulaString(stubHSSF, fr.ParsedExpression));
// Now, load via Usermodel and re-check
HSSFWorkbook wb = HSSFTestDataSamples.OpenSampleWorkbook("3dFormulas.xls");
Assert.AreEqual("Sheet1!A1", wb.GetSheetAt(0).GetRow(1).GetCell(0).CellFormula);
Assert.AreEqual("SUM(Sh3!A1:A4)", wb.GetSheetAt(0).GetRow(6).GetCell(0).CellFormula);
}
//private static POILogger log = POILogFactory.GetLogger(typeof(HSSFSheet));
/// <summary>
/// Creates new HSSFSheet - called by HSSFWorkbook to create a _sheet from
/// scratch. You should not be calling this from application code (its protected anyhow).
/// </summary>
/// <param name="workbook">The HSSF Workbook object associated with the _sheet.</param>
/// <see cref="LF.Utils.NPOI.HSSF.UserModel.HSSFWorkbook.CreateSheet()"/>
public HSSFSheet(HSSFWorkbook workbook)
{
_sheet = InternalSheet.CreateSheet();
rows = new Dictionary<int, LF.Utils.NPOI.SS.UserModel.IRow>();
this._workbook = workbook;
this.book = workbook.Workbook;
}
