private void ExportChart(string fileName, ISymbolicDataAnalysisSolution solution, string formula) {
FileInfo newFile = new FileInfo(fileName);
if (newFile.Exists) {
newFile.Delete();
newFile = new FileInfo(fileName);
}
var formulaParts = formula.Split(new string[] { Environment.NewLine }, StringSplitOptions.None);
using (ExcelPackage package = new ExcelPackage(newFile)) {
ExcelWorksheet modelWorksheet = package.Workbook.Worksheets.Add("Model");
FormatModelSheet(modelWorksheet, solution, formulaParts);
ExcelWorksheet datasetWorksheet = package.Workbook.Worksheets.Add("Dataset");
WriteDatasetToExcel(datasetWorksheet, solution.ProblemData);
ExcelWorksheet inputsWorksheet = package.Workbook.Worksheets.Add("Inputs");
WriteInputSheet(inputsWorksheet, datasetWorksheet, formulaParts.Skip(2), solution.ProblemData.Dataset);
if (solution is IRegressionSolution) {
ExcelWorksheet estimatedWorksheet = package.Workbook.Worksheets.Add("Estimated Values");
WriteEstimatedWorksheet(estimatedWorksheet, datasetWorksheet, formulaParts, solution as IRegressionSolution);
ExcelWorksheet chartsWorksheet = package.Workbook.Worksheets.Add("Charts");
AddCharts(chartsWorksheet);
}
package.Workbook.Properties.Title = "Excel Export";
package.Workbook.Properties.Author = "HEAL";
package.Workbook.Properties.Comments = "Excel export of a symbolic data analysis solution from HeuristicLab";
package.Save();
}
}
/// <summary>
/// 保存excel文件，覆盖相同文件名的文件
/// </summary>
public static void SaveExcel(string FileName, string sql, string SheetName)
{
FileInfo newFile = new FileInfo(FileName);
if (newFile.Exists)
{
newFile.Delete();
newFile = new FileInfo(FileName);
}
using (ExcelPackage package = new ExcelPackage(newFile))
{
try
{
ExcelWorksheet ws = package.Workbook.Worksheets.Add(SheetName);
IDataReader reader = DBConfig.db.DBProvider.ExecuteReader(sql);
ws.Cells["A1"].LoadFromDataReader(reader, true);
}
catch (Exception ex)
{
throw ex;
}
package.Save();
}
}
/// <summary>
/// Экспортирует массив данных в XLSX формат с учетом выбранной локали
/// </summary>
/// <param name="path">Путь к файлу, в который нужно сохранить данные</param>
/// <param name="localisation">Локализация</param>
/// <returns>Успешное завершение операции</returns>
public override bool Export(String path, Localisation localisation)
{
try
{
if (!path.EndsWith(".xlsx"))
path += ".xlsx";
log.Info(String.Format("Export to .xlsx file to: {0}", path));
var timer = new Stopwatch();
timer.Start();
var file = new FileInfo(path);
using (var pck = new ExcelPackage(file))
{
ExcelWorksheet ws = pck.Workbook.Worksheets.Add("Sheet1");
ws.Cells["A1"].LoadFromDataTable(dataTable, true);
ws.Cells.AutoFitColumns();
pck.Save();
}
timer.Stop();
log.Info(String.Format("Export complete! Elapsed time: {0} ms", timer.Elapsed.Milliseconds));
return true;
}
catch (Exception ex)
{
log.Error("Can't export to .xlsx file!", ex);
return false;
}
}
public System.IO.Stream Serialize(T data)
{
var ms = new System.IO.MemoryStream();
using (var pkg = new OfficeOpenXml.ExcelPackage(ms))
{
if (settings.Template != null)
{
using (var tmp = new OfficeOpenXml.ExcelPackage(settings.Template.Open()))
{
foreach (var sheet in tmp.Workbook.Worksheets)
{
pkg.Workbook.Worksheets.Add(sheet.Name, sheet);
}
}
}
if (!pkg.Workbook.Worksheets.Any())
{
pkg.Workbook.Worksheets.Add("Export");
}
foreach (var p in settings.Processes)
{
p.Run(pkg, data);
}
pkg.Save();
}
ms.Position = 0;
return(ms);
}
void Access_Excel(ITestCaseCollection testcases)
{
try
{
FileInfo new_file = new FileInfo(File_Name.Text);
// FileInfo template = new FileInfo(System.Windows.Forms.Application.StartupPath + "\\Resources\\Test_Case_Template.xlsx");
FileInfo template = new FileInfo(Directory.GetCurrentDirectory() + "\\Test_Case_Template.xlsx");
using (ExcelPackage xlpackage = new ExcelPackage(new_file,template))
{
ExcelWorksheet worksheet = xlpackage.Workbook.Worksheets["Test Case"];
GetTestCases(testcases, worksheet);
xlpackage.Save();
MessageBox.Show("File has been saved at " + File_Name.Text);
}
}
catch (Exception theException)
{
String errorMessage;
errorMessage = "Error: ";
errorMessage = String.Concat(errorMessage, theException.Message);
errorMessage = String.Concat(errorMessage, " Line: ");
errorMessage = String.Concat(errorMessage, theException.Source);
MessageBox.Show(errorMessage, "Error");
}
}
public bool InsertNewRow(string FolderPath, string DestinationFileName,int AddNewRowAfter, int NumberOfRowsToAdd)
{
try
{
var File = new FileInfo(FolderPath + "\\" + DestinationFileName);
using (ExcelPackage package = new ExcelPackage(File))
{
ExcelWorkbook workBook = package.Workbook;
if (workBook != null)
{
if (workBook.Worksheets.Count > 0)
{
ExcelWorksheet cSheet = workBook.Worksheets.First();
cSheet.InsertRow(AddNewRowAfter, NumberOfRowsToAdd);
}
}
package.Save();
}
return true;
}
catch
{
return false;
}
}
public bool UpdateExcelTemplate(string FolderPath, string SourceFileName, string DestinationFileName, bool DeleteExistingCopy, string CellName, string CellValue)
{
if (System.IO.File.Exists(FolderPath + "\\" + DestinationFileName) && DeleteExistingCopy)
{
System.IO.File.Delete(FolderPath + "\\" + DestinationFileName);
System.IO.File.Copy(FolderPath + "\\" + SourceFileName, FolderPath + "\\" + DestinationFileName);
}
else if (!System.IO.File.Exists(FolderPath + "\\" + DestinationFileName))
{
System.IO.File.Copy(FolderPath + "\\" + SourceFileName, FolderPath + "\\" + DestinationFileName);
}
var File = new FileInfo(FolderPath + "\\" + DestinationFileName);
using (ExcelPackage package = new ExcelPackage(File))
{
ExcelWorkbook workBook = package.Workbook;
if (workBook != null)
{
if (workBook.Worksheets.Count > 0)
{
ExcelWorksheet cSheet = workBook.Worksheets.First();
cSheet.Cells[CellName].Value = CellValue;
}
}
package.Save();
}
return false;
}
private void writeHeader(string filename)
{
ExcelPackage ep;
ep = new ExcelPackage(new FileInfo(filename));
var workbook = ep.Workbook;
ExcelWorksheet akWorksheet = null;
foreach (ExcelWorksheet worksheet in workbook.Worksheets)
{
if (worksheet.Name == TableName)
akWorksheet = worksheet;
}
if (akWorksheet == null)
{
akWorksheet = workbook.Worksheets.Add(TableName);
}
int n = 1;
foreach (DatasetConfigRow myFeld in datasetConfig.DatasetConfigRows)
{
akWorksheet.Cells[1, n].Value = myFeld.DatabaseField;
n++;
}
ep.Save();
ep.Dispose();
}
public ActionResult Import1()
{
SqlConnection Con = new SqlConnection();
string Path = ConfigurationManager.ConnectionStrings["DefaultConnection"].ConnectionString;
Con.ConnectionString = Path;
DataTable DtNew = new DataTable();
SqlDataAdapter Adp = new SqlDataAdapter("select * from OrganizationCurators", Con);
Adp.Fill(DtNew);
if (DtNew.Rows.Count > 0)
{
string filepath = Server.MapPath("~/Content/ExcelExportfile1.xlsx");
FileInfo Files = new FileInfo(filepath);
OfficeOpenXml.ExcelPackage excel = new OfficeOpenXml.ExcelPackage(Files);
var sheetcreate = excel.Workbook.Worksheets.Add("OrganizationCuratorDat");
for (int i = 0; i < DtNew.Columns.Count; i++)
{
sheetcreate.Cells[1, i + 1].Value = DtNew.Columns[i].ColumnName.ToString();
}
for (int i = 0; i < DtNew.Rows.Count; i++)
{
for (int j = 0; j < DtNew.Columns.Count; j++)
{
sheetcreate.Cells[i + 2, j + 1].Value = DtNew.Rows[i][j].ToString();
}
}
excel.Save();
}
return(View());
}
protected void btn_Excel_Click(object sender, EventArgs e)
{
GetRecoredForExcelfile();
string newFilePath = Server.MapPath("ExcelFile/ErrorList.xlsx");
string templateFilePath = Server.MapPath("ExcelFile/ErrorListtemplate.xlsx");
FileInfo newFile = new FileInfo(newFilePath);
FileInfo template = new FileInfo(templateFilePath);
using (ExcelPackage xlPackage = new ExcelPackage(newFile, template))
{
foreach (ExcelWorksheet aworksheet in xlPackage.Workbook.Worksheets)
{
aworksheet.Cell(1, 1).Value = aworksheet.Cell(1, 1).Value;
}
ExcelWorksheet worksheet = xlPackage.Workbook.Worksheets["Sheet1"];
int startrow = 5;
int row = 0;
int col = 0;
for (int j = 0; j < Dt.Columns.Count; j++)
{
// col = col + j;
col++;
for (int i = 0; i < Dt.Rows.Count; i++)
{
row = startrow + i;
ExcelCell cell = worksheet.Cell(row, col);
cell.Value = Dt.Rows[i][j].ToString();
xlPackage.Save();
}
}
}
}
/// <summary>
/// 单表格导出到多excel工作簿，分页版本
/// </summary>
public static int ExportExcel(string TabelName, int PageSize, string filename)
{
if (filename != null)
{
Stopwatch watch = Stopwatch.StartNew();
watch.Start();
int RecordCount = DBConfig.db.DBProvider.ReturnTbCount(TabelName);
string sql = "select * from [" + TabelName + "]";
int WorkBookCount = (RecordCount - 1) / PageSize + 1;
FileInfo newFile = new FileInfo(filename);
for (int i = 1; i <= WorkBookCount; i++)
{
string s = filename.Substring(0, filename.LastIndexOf("."));
StringBuilder newfileName = new StringBuilder(s);
newfileName.Append(i + ".xlsx");
newFile = new FileInfo(newfileName.ToString());
if (newFile.Exists)
{
newFile.Delete();
newFile = new FileInfo(newfileName.ToString());
}
using (ExcelPackage package = new ExcelPackage(newFile))
{
DataTable dt = DBConfig.db.DBProvider.ReturnDataTable(sql, PageSize * (i - 1), PageSize);
SaveExcel(TabelName, dt, package);
package.Save();
}
}
watch.Stop();
return Convert.ToInt32(watch.ElapsedMilliseconds / 1000);
}
return -1;
}
private static void CreateDeletePackage(int Sheets, int rows)
{
List<object> row = new List<object>();
row.Add(1);
row.Add("Some text");
row.Add(12.0);
row.Add("Some larger text that has completely no meaning. How much wood can a wood chuck chuck if a wood chuck could chuck wood. A wood chuck could chuck as much wood as a wood chuck could chuck wood.");
FileInfo LocalFullFileName = new FileInfo(Path.GetTempFileName());
LocalFullFileName.Delete();
package = new ExcelPackage(LocalFullFileName);
try
{
for (int ca = 0; ca < Sheets; ca++)
{
CreateWorksheet("Sheet" + (ca+1), row, rows);
}
package.Save();
}
finally
{
LocalFullFileName.Refresh();
if (LocalFullFileName.Exists)
{
LocalFullFileName.Delete();
}
package.Dispose();
package = null;
GC.Collect();
}
}
public static void CreateReport()
{
// Set the file name and get the output directory
var fileName = "Report-" + DateTime.Now.ToString("yyyy-MM-dd--hh-mm-ss") + ".xlsx";
var outputDir = "../../../Reports/ExcelReports/";
// Create the file
var file = new FileInfo(outputDir + fileName);
using (var package = new ExcelPackage(file))
{
// add a new worksheet to the empty workbook
ExcelWorksheet worksheet = package.Workbook.Worksheets.Add("Profit per month");
// Set headers here
worksheet.Cells[1, 1].Value = "Month";
worksheet.Cells[1, 2].Value = "Income";
worksheet.Cells[1, 3].Value = "Outcome";
worksheet.Cells[1, 4].Value = "Profit";
int numberOfColumns = 4;
//Headers styling
FormatHeaders(worksheet, numberOfColumns);
FillData(worksheet, numberOfColumns);
package.Save();
}
}
protected void btn_download_Click(object sender, EventArgs e)
{
DataTable dt = new DataTable();
dt.TableName = "All_Contacts";
using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["dlgf"].ConnectionString))
{
SqlCommand cmd = new SqlCommand("[CTX].[sp_Select_All_List]", conn);
conn.Open();
SqlDataAdapter adapter = new SqlDataAdapter(cmd);
adapter.Fill(dt);
conn.Close();
}
string dir = Server.MapPath("All_List_output");
string milli = DateTime.Now.Millisecond.ToString();
FileInfo newFile = new FileInfo(dir + @"\CTX_"+ milli + ".xlsx");
if (newFile.Exists)
{
newFile.Delete(); // ensures we create a new workbook
newFile = new FileInfo(dir + @"\CTX_" + milli + ".xlsx");
}
using (ExcelPackage pack = new ExcelPackage(newFile))
{
ExcelWorksheet ws = pack.Workbook.Worksheets.Add(dt.TableName.ToString());
ws.Cells["A1"].LoadFromDataTable(dt, true);
pack.Save();
}
string path = dir + @"\" + newFile.Name;
Response.ContentType = "application/excel";
Response.AppendHeader("Content-Disposition", "attachment; filename=\"" + newFile.Name + "\""); //file name must be in double quotes to allow spaces in the filename
Response.TransmitFile(path);
Response.End();
}
public void Write(int rows, int cols)
{
String file = "testWs.xlsx";
if (File.Exists(file)) { File.Delete(file); }
TimeSpan start;
// Write
Console.WriteLine();
Console.WriteLine("Test: " + rows + " x " + cols);
Console.WriteLine("=======");
Console.WriteLine("Writing");
start = Process.GetCurrentProcess().TotalProcessorTime;
using (ExcelPackage package = new ExcelPackage(new FileInfo(file)))
{
int div = Math.Max(1, rows / 20);
ExcelWorksheet ws = package.Workbook.Worksheets.Add("Stress", rows, cols);
TimeRestart("Create", ref start);
for (int row = 0; row < rows; row++)
{
for (int col = 0; col < cols; col++)
{
ws.Cell(row + 1, col + 1).Value = GetVal(row, col);
}
if (row % div == 0) { Console.Write("*"); }
}
Console.WriteLine("done");
TimeRestart("Write", ref start);
package.Save();
}
TimeRestart("Save", ref start);
// Read
Console.WriteLine("Reading");
start = Process.GetCurrentProcess().TotalProcessorTime;
using (ExcelPackage package = new ExcelPackage(new FileInfo(file)))
{
int div = Math.Max(1, rows / 20);
ExcelWorksheet ws = package.Workbook.Worksheets["Stress"];
TimeRestart("Open", ref start);
for (int row = 0; row < rows; row++)
{
for (int col = 0; col < cols; col++)
{
ExcelCell cell = ws.Cell(row + 1, col + 1);
String val = cell.Value;
Assert.AreEqual(GetVal(row, col), cell.Value, "@" + (row + 1) + ", " + (col + 1));
}
if (row % div == 0) { Console.Write("*"); }
}
Console.WriteLine("done");
TimeRestart("Read", ref start);
}
Console.WriteLine(" "); // skip
}
private void CreateExcelFile(string fileName, IList<SqliteExcelFormat> data)
{
if (File.Exists(fileName)) File.Delete(fileName);
using (var excel = new ExcelPackage(new FileInfo(fileName)))
{
var ws = excel.Workbook.Worksheets.Add("Sheet1");
ws.Cells[1, 1].Value = "Vendor";
ws.Cells[1, 2].Value = "Incomes";
ws.Cells[1, 3].Value = "Expenses";
ws.Cells[1, 4].Value = "Taxes";
ws.Cells[1, 5].Value = "Financial Result";
for (int i = 0; i < data.Count; i++)
{
var tax = (data[i].TaxPercentage / 100) * data[i].Incomes;
ws.Cells[i + 2, 1].Value = data[i].Vendor;
ws.Cells[i + 2, 2].Value = data[i].Incomes;
ws.Cells[i + 2, 3].Value = data[i].Expenses;
ws.Cells[i + 2, 4].Value = Math.Round((decimal)tax, 2);
ws.Cells[i + 2, 5].Value = Math.Round((decimal)(data[i].Incomes - data[i].Expenses - tax), 2);
}
ws.Column(1).AutoFit();
ws.Column(2).AutoFit();
ws.Column(3).AutoFit();
ws.Column(4).AutoFit();
ws.Column(5).AutoFit();
excel.Save();
}
}
public void ExportExcel(IList<dynamic> items, string fileName)
{
using (var excelPackage = new ExcelPackage(new FileStream(fileName, FileMode.CreateNew)))
{
var currentIndex = 0;
var sheetIndex = 1;
do
{
var take = Math.Min(1000000, items.Count - currentIndex);
var currentSet = items.Skip(currentIndex).Take(take).ToList();
currentIndex += currentSet.Count;
var sheet = excelPackage.Workbook.Worksheets.Add("Sheet " + sheetIndex);
var arrayData = new List<object[]>();
foreach (var item in currentSet)
{
arrayData.Add(((IDictionary<string, object>)item).Values.ToArray());
}
sheet.Cells["A1"].LoadFromArrays(arrayData);
sheetIndex++;
excelPackage.Save();
}
while (currentIndex < items.Count);
}
}
public void Schedule(int number)
{
// Sprawdzenie czy poprzedni tydzień był już wykonany w całości
// Jeżeli nie to sprawdzamy czy stworzenie nowego harmonogramu miałobyć wymuszone
// Jeżeli nie to wyświetlamy komunikat, że nie można utworzyć nowego tygdnia bo poprzedni nie był zrobiony
//
// Tworzymy nowy harmonogram z nr tygodnia
//
var directoryPath = Path.Combine(ConfigurationManager.AppSettings["Scheduling"], number + " Week");
if (Directory.Exists(directoryPath))
throw new InvalidOperationException(string.Format("Can't add new week because directory ({0}) exist",directoryPath));
Directory.CreateDirectory(directoryPath);
var path = Path.Combine(directoryPath, "Raport.xlsx");
var file = new FileInfo(path);
using (var package = new ExcelPackage(file))
{
var worksheet = package.Workbook.Worksheets.Add("Zadania do wykonania");
var tasks = _taskGenerator.GenerateWeekTasks();
var actualRow = 0;
foreach (var task in tasks)
{
worksheet.Cells[++actualRow, 1].Value = task;
}
worksheet.Column(1).AutoFit();
package.Save();
}
}
public void GenerateCommonRep(DataTable table)
{
if (table.Rows.Count > 0)
{
string fname = string.Format(_rep_fname, DateTime.Now.ToShortDateString());
if (File.Exists(fname))
File.Delete(fname);
excel = new ExcelPackage(new FileInfo(fname), new FileInfo(_template_file));
int rownum = 2;
ws = excel.Workbook.Worksheets[1];
foreach (DataRow dr in table.Rows)
{
ws.Cells[rownum, 1].Value = dr[1].ToString();
ws.Cells[rownum, 2].Value = dr[2].ToString();
ws.Cells[rownum, 3].Value = dr[3].ToString();
ws.Cells[rownum, 4].Value = dr[4].ToString();
ws.Cells[rownum, 5].Value = dr[5].ToString();
ws.Cells[rownum, 6].Value = dr[6].ToString();
ws.Cells[rownum, 7].Value = dr[7].ToString();
ws.Cells[rownum, 9].Value = dr[8].ToString();
ws.Cells[rownum, 11].Value = dr[9].ToString();
//ws.Cells[rownum, 10].Value = dr[10].ToString();
ws.Cells[rownum, 10].Value = dr[11].ToString();
ws.Cells[rownum, 12].Value = dr[12].ToString();
ws.Cells[rownum, 13].Value = GetStringAnswer((bool)dr[13]);
ws.Cells[rownum, 14].Value = dr[14].ToString();
ws.Cells[rownum, 15].Value = dr[15].ToString();
ws.Cells[rownum, 16].Value = dr[16].ToString();
ws.Cells[rownum, 17].Value = dr[17].ToString();
ws.Cells[rownum, 18].Value = dr[18].ToString();
ws.Cells[rownum, 19].Value = dr[19].ToString();
ws.Cells[rownum, 20].Value = dr[20].ToString();
ws.Cells[rownum, 21].Value = dr[21].ToString();
ws.Cells[rownum, 22].Value = GetStringAnswer((bool)dr[24]);
ws.Cells[rownum, 23].Value = GetStringAnswer((bool)dr[25]);
ws.Cells[rownum, 24].Value = dr[28].ToString();
ws.Cells[rownum, 25].Value = dr[27].ToString();
ws.Cells[rownum, 26].Value = dr[26].ToString();
ws.Cells[rownum, 27].Value = dr[22].ToString();
ws.Cells[rownum, 8].Value = dr[29].ToString();
rownum++;
}
ws.Cells["A1:V1"].AutoFilter = true;
excel.Save();
anvlib_reports.Classes.ExcelWriter.OpenFile(anvlib.Utilites.IOUtils.GetBaseDirPath() + fname, false);
}
}
public void ExecuteQueries(
string servername,
string username,
string password,
string scriptLocation,
string outputFolder,
IList<string> databases,
bool useTrusted,
bool autoFitColumns,
int queryTimeoutSeconds
)
{
_dictWorksheet.Clear();
string dateString = DateTime.Now.ToString("yyyyMMdd_hhmmss_");
List<SqlQuery> queries = new List<SqlQuery>();
foreach (var file in Directory.EnumerateFiles(scriptLocation, "*.sql"))
{
var parser = new QueryFileParser(file);
queries.AddRange(parser.Load());
}
string outputFilepath = GetOutputFilepath(outputFolder, servername, dateString);
using (var fs = new FileStream(outputFilepath, FileMode.Create))
{
using (var pck = new ExcelPackage(fs))
{
string connectionString = GetConnectionStringTemplate(servername, "master", username, password, useTrusted);
var serverQueries = queries.Where(q => q.ServerScope).ToList();
ExecuteQueriesAndSaveToExcel(pck, connectionString, serverQueries, "", "", autoFitColumns, queryTimeoutSeconds);
if (databases.Count > 0)
{
int databaseNo = 1;
var dbQueries = queries.Where(q => !q.ServerScope).ToList();
foreach (var db in databases)
{
connectionString = GetConnectionStringTemplate(servername, db, username, password, useTrusted);
ExecuteQueriesAndSaveToExcel(pck, connectionString, dbQueries, db.Trim(),
databaseNo.ToString(CultureInfo.InvariantCulture),
autoFitColumns, queryTimeoutSeconds);
databaseNo++;
}
}
foreach (var file in Directory.EnumerateFiles(scriptLocation, "*.ps1"))
{
ExecutePoShAndSaveToExcel(pck, file, autoFitColumns);
}
pck.Save();
}
}
}
public static void RunExcelPackageExport(FileInfo file, List<PortData> portlist)
{
using (var excelPackage = new ExcelPackage(file))
{
var workSheet = excelPackage.Workbook.Worksheets.Add("DZG");
ExcelExport.CreateExcelFileHeader(workSheet);
ExcelExport.CreateExcelFileContent(workSheet, portlist);
excelPackage.Save();
}
}
protected override bool SaveInternal()
{
if (_obj == null)
{
return(false);
}
_obj.Save();
return(true);
}
public static FileInfo ToExcelFile(this IEnumerable<BsonDocument> data, string worksheetName, IEnumerable<string> headings)
{
var tempPath = Path.GetTempPath() + "Report-" + Guid.NewGuid() + ".xlsx";
var newFile = new FileInfo(tempPath);
var package = new ExcelPackage(newFile);
AddWorksheet(data, worksheetName, headings, package);
package.Save();
return newFile;
}
private Boolean addToLastCell(ExcelPackage pck)
{
System.Timers.Timer aTimer = new System.Timers.Timer(600000);
aTimer.Interval = 60000000;
aTimer.Stop();
aTimer.Start();
pck.Save();
return true;
}
public async Task DeleteTerm(int id)
{
using (var excelPackage =
new ExcelPackage(new FileInfo(_providerSettings.TermFilePath)))
{
var workSheet = await GetTerminologyWorksheet(excelPackage);
workSheet.DeleteRow(id);
excelPackage.Save();
}
}
public void Issue15159()
{
var fs = new FileStream(@"C:\temp\bug\DeleteColFormula\FormulasIssue\demo.xlsx", FileMode.OpenOrCreate);
using (var package = new OfficeOpenXml.ExcelPackage(fs))
{
package.Save();
}
fs.Seek(0, SeekOrigin.Begin);
var fs2 = fs;
}
public void CopyAndDeleteWorksheetWithImage()
{
using (var pck = new ExcelPackage(new MemoryStream()))
{
var ws = pck.Workbook.Worksheets.Add("original");
ws.Drawings.AddPicture("Pic1", Properties.Resources.Test1);
pck.Workbook.Worksheets.Copy("original", "copy");
pck.Workbook.Worksheets.Delete(ws);
pck.Save();
}
}
public void Issue13492()
{
using (var package = new OfficeOpenXml.ExcelPackage(new FileInfo(@"c:\temp\bug\Bug13492.xlsx")))
{
ExcelWorkbook workBook = package.Workbook;
var worksheet = workBook.Worksheets[1];
var rt = worksheet.Cells["K31"].RichText.Text;
package.Save();
}
}
public void Issue13492()
{
using (var package = new OfficeOpenXml.ExcelPackage(new FileInfo(@"c:\temp\bug\Bug13492.xlsx")))
{
ExcelWorkbook workBook = package.Workbook;
var worksheet = workBook.Worksheets[1];
var rt = worksheet.Cells["K31"].RichText.Text;
package.Save();
}
}
public void Issue14788()
{
using (var package = new OfficeOpenXml.ExcelPackage(new FileInfo(@"c:\temp\bug\i15195_Save.xlsx"), new FileInfo(@"c:\temp\bug\GetWorkSheetXmlBad.xlsx")))
{
ExcelWorkbook workBook = package.Workbook;
var worksheet = workBook.Worksheets[1];
worksheet.InsertColumn(8, 2);
package.Save();
}
}
public void SaveAsOfficeOpenXml(string fileName)
{
System.IO.FileInfo fileInfo = new System.IO.FileInfo(fileName);
if (fileInfo.Exists)
fileInfo.Delete();
ExcelPackage excelPackage = new ExcelPackage(fileInfo);
ExcelWorksheet sheet1 = excelPackage.Workbook.Worksheets.Add("Sheet1");
for (int rowInd= 0; rowInd <rows.Count; rowInd++)
for (int colInd= 0; colInd<rows[rowInd].Cells.Count; colInd++)
sheet1.Cells[rowInd+1, colInd+1].Value = rows[rowInd].Cells[colInd].Value;
excelPackage.Save();
}
public void Issue14788()
{
using (var package = new OfficeOpenXml.ExcelPackage(new FileInfo(@"c:\temp\bug\i15195_Save.xlsx"), new FileInfo(@"c:\temp\bug\GetWorkSheetXmlBad.xlsx")))
{
ExcelWorkbook workBook = package.Workbook;
var worksheet = workBook.Worksheets[1];
worksheet.InsertColumn(8, 2);
package.Save();
}
}
public static void WriteSpreadsheet(string filename, Dictionary<string, IEnumerable<ComparisonAgregator>> comparisonsByName)
{
// Example at http://epplus.codeplex.com/wikipage?title=ContentSheetExample
var fileInfo = new FileInfo(filename);
if (fileInfo.Exists)
fileInfo.Delete();
var pck = new ExcelPackage(fileInfo);
foreach (var kvp in comparisonsByName)
{
var name = kvp.Key;
var comparisons = kvp.Value;
var worksheet = pck.Workbook.Worksheets.Add(name);
worksheet.Cells[1,2].Value = "Worst Error";
worksheet.Cells[1,3].Value = "Worst Error (16 bit)";
worksheet.Cells[1,4].Value = "Worst bits per sample";
worksheet.Cells[1,5].Value = "Worst Equilization";
worksheet.Cells[1,6].Value = "Average Error";
worksheet.Cells[1,7].Value = "Average Error (16 bit)";
worksheet.Cells[1,8].Value = "Average bits per sample";
worksheet.Cells[1,9].Value = "Equilization";
var rowCtr = 2;
foreach (var comparison in comparisons.OrderBy(c => c.SortOrder))
{
worksheet.Cells[rowCtr, 1].Value = comparison.DisplayTitle;
worksheet.Cells[rowCtr, 2].Value = comparison.LargestError;
worksheet.Cells[rowCtr, 3].Value = comparison.LargestError * short.MaxValue;
worksheet.Cells[rowCtr, 4].Value = comparison.WorstBits;
worksheet.Cells[rowCtr, 5].Value = comparison.LargestEquilization * 100;
worksheet.Cells[rowCtr, 6].Value = comparison.AverageError;
worksheet.Cells[rowCtr, 7].Value = comparison.AverageError * short.MaxValue;
worksheet.Cells[rowCtr, 8].Value = comparison.AverageBits;
worksheet.Cells[rowCtr, 9].Value = comparison.Equilization * 100;
rowCtr++;
}
for (var ctr = 1; ctr <= 9; ctr++)
{
worksheet.Column(ctr).AutoFit();
}
//CreateChart(worksheet);
}
pck.Save();
}
public void Issue15198()
{
using (var package = new OfficeOpenXml.ExcelPackage(new FileInfo(@"c:\temp\bug\Output.xlsx"), new FileInfo(@"c:\temp\bug\demo.xlsx")))
{
ExcelWorkbook workBook = package.Workbook;
var worksheet = workBook.Worksheets[1];
worksheet.DeleteRow(12);
package.Save();
}
}
public static void SaveIncomeStatements( string fileName, CompanyInfo ci )
{
fileName = fileName.Contains( ".xslx" ) ? fileName : fileName + ".xlsx";
System.IO.FileInfo fi = new System.IO.FileInfo( fileName );
pck = new ExcelPackage( fi );
foreach ( IncomeStatement IS in ci.IncomeStatements )
{
CreateIndividualIncomeStatement( IS );
}
pck.Save( );
pck = null;
}
public static void SaveBalanceSheets( string fileName, CompanyInfo ci )
{
fileName = fileName.Contains( ".xslx" ) ? fileName : fileName + ".xlsx";
System.IO.FileInfo fi = new System.IO.FileInfo( fileName );
pck = new ExcelPackage( fi );
foreach ( BalanceSheet bs in ci.BalanceSheets )
{
CreateIndividualBalanceSheet( bs );
}
pck.Save( );
pck = null;
}
public static void SaveCashFlowStatements( string fileName, CompanyInfo ci )
{
fileName = fileName.Contains( ".xslx" ) ? fileName : fileName + ".xlsx";
System.IO.FileInfo fi = new System.IO.FileInfo( fileName );
pck = new ExcelPackage( fi );
foreach ( CashFlowStatement cfs in ci.CashFlowStatements )
{
SaveCashFlowStatement( cfs );
}
pck.Save( );
pck = null;
}
public static void MakeDocument(IEnumerable <ExcelBasedCourse> courses)
{
if (courses == null)
{
throw new ArgumentNullException(nameof(courses));
}
string fileName = "report.xlsx";
if (File.Exists(fileName))
{
File.Delete(fileName);
}
using (var package = new OfficeOpenXml.ExcelPackage(new System.IO.FileInfo(fileName)))
{
var sheet = package.Workbook.Worksheets.Add("Exported Courses");
var row = 1;
int col = 1;
sheet.Cells[row, col++].Value = "Area";
sheet.Cells[row, col++].Value = "Number";
sheet.Cells[row, col++].Value = "Name";
sheet.Cells[row, col++].Value = "Title";
sheet.Cells[row, col++].Value = "Content";
sheet.Cells[row, col++].Value = "Semester";
sheet.Cells[row, col++].Value = "Prereqs";
for (int i = 1; i <= 12; i++)
{
sheet.Cells[row, col++].Value = $"Outcome {i}";
}
foreach (var course in courses)
{
col = 1;
row++;
sheet.Cells[row, col++].Value = course.Area;
sheet.Cells[row, col++].Value = course.Number;
sheet.Cells[row, col++].Value = course.Name;
sheet.Cells[row, col++].Value = course.Title;
sheet.Cells[row, col++].Value = course.Content;
sheet.Cells[row, col++].Value = course.Semester;
sheet.Cells[row, col++].Value = String.Join(", ", course.Prerequisites);
foreach (var outcome in course.Outcomes)
{
sheet.Cells[row, col++].Value = outcome;
}
}
package.Save();
}
Process.Start(fileName);
}
public void Issue15194()
{
using (var package = new OfficeOpenXml.ExcelPackage(new FileInfo(@"c:\temp\bug\i15194-Save.xlsx"), new FileInfo(@"c:\temp\bug\I15194.xlsx")))
{
ExcelWorkbook workBook = package.Workbook;
var worksheet = workBook.Worksheets[1];
worksheet.Cells["E3:F3"].Merge = false;
worksheet.DeleteRow(2, 6);
package.Save();
}
}
public void ValidateCaseInsensitiveCustomProperties_Loading()
{
var p = new OfficeOpenXml.ExcelPackage();
p.Workbook.Worksheets.Add("CustomProperties");
p.Workbook.Properties.SetCustomPropertyValue("fOO", "bAR");
p.Workbook.Properties.SetCustomPropertyValue("Foo", "Bar");
p.Save();
var p2 = new OfficeOpenXml.ExcelPackage(p.Stream);
Assert.AreEqual("Bar", p2.Workbook.Properties.GetCustomPropertyValue("fOo"));
p.Dispose();
p2.Dispose();
}
public void Issue15158()
{
using (var package = new OfficeOpenXml.ExcelPackage(new FileInfo(@"c:\temp\Output.xlsx"), new FileInfo(@"C:\temp\bug\DeleteColFormula\FormulasIssue\demo.xlsx")))
{
ExcelWorkbook workBook = package.Workbook;
ExcelWorksheet worksheet = workBook.Worksheets[1];
//string column = ColumnIndexToColumnLetter(28);
worksheet.DeleteColumn(28);
if (worksheet.Cells["AA19"].Formula != "")
{
throw new Exception("this cell should not have formula");
}
package.Save();
}
}
public void Issue15118()
{
using (var package = new OfficeOpenXml.ExcelPackage(new FileInfo(@"c:\temp\bugOutput.xlsx"), new FileInfo(@"c:\temp\bug\DeleteRowIssue\Template.xlsx")))
{
ExcelWorkbook workBook = package.Workbook;
var worksheet = workBook.Worksheets[1];
worksheet.Cells[9, 6, 9, 7].Merge = true;
worksheet.Cells[9, 8].Merge = false;
worksheet.DeleteRow(5);
worksheet.DeleteColumn(5);
worksheet.DeleteColumn(5);
worksheet.DeleteColumn(5);
worksheet.DeleteColumn(5);
package.Save();
}
}
public void SaveAndCloseFile()
{
if (app == null)
{
return;
}
if (worksheet == null)
{
return;
}
// save the application
try
{
app.Save();
}
catch (Exception e)
{
throw e;
}
}
private void SurasytiIExcel(DataTable table)
{
string path = System.IO.Path.GetDirectoryName(Assembly.GetExecutingAssembly().Location);
path = System.IO.Path.Combine(path, "Leaderboard.xlsx");
FileInfo ExistingFile = new FileInfo(path);
using (var pck = new OfficeOpenXml.ExcelPackage(ExistingFile))
{
using (var stream = File.OpenRead(path))
{
pck.Load(stream);
}
var ws = pck.Workbook.Worksheets[1];
ws.Column(1).Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
ws.Column(1).Style.VerticalAlignment = ExcelVerticalAlignment.Center;
ws.Column(2).Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
ws.Column(2).Style.VerticalAlignment = ExcelVerticalAlignment.Center;
ws.Column(3).Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
ws.Column(3).Style.VerticalAlignment = ExcelVerticalAlignment.Center;
ws.Cells["A1"].LoadFromDataTable(table, true);
pck.Save();
}
}
public string DescargaExcel(string fechaAsignacion, int TipoServicio)
{
int _fila = 2;
string _ruta;
string nombreArchivo = "";
string ruta_descarga = ConfigurationManager.AppSettings["Archivos"];
var usuario = ((Sesion)Session["Session_Usuario_Acceso"]).usuario.usu_id;
try
{
List <Cls_Entidad_Export_trabajos_lectura> _lista = new List <Cls_Entidad_Export_trabajos_lectura>();
Cls_Negocio_Export_trabajos_lectura obj_negocio = new DSIGE.Negocio.Cls_Negocio_Export_trabajos_lectura();
_lista = obj_negocio.Capa_Negocio_Get_ListaLecturas_Excel(fechaAsignacion, TipoServicio);
if (_lista.Count == 0)
{
return(_Serialize("0|No hay informacion para mostrar.", true));
}
if (TipoServicio == 1)
{
nombreArchivo = "LECTURAS_EXPORTADO_" + usuario + ".xls";
}
else if (TipoServicio == 2)
{
nombreArchivo = "RELECTURAS_EXPORTADO_" + usuario + ".xls";
}
else if (TipoServicio == 9)
{
nombreArchivo = "RECLAMOS_EXPORTADO_" + usuario + ".xls";
}
_ruta = Path.Combine(Server.MapPath("~/Temp") + "\\" + nombreArchivo);
FileInfo _file = new FileInfo(_ruta);
if (_file.Exists)
{
_file.Delete();
_file = new FileInfo(_ruta);
}
using (Excel.ExcelPackage oEx = new Excel.ExcelPackage(_file))
{
Excel.ExcelWorksheet oWs = oEx.Workbook.Worksheets.Add("Importar");
oWs.Cells.Style.Font.SetFromFont(new Font("Tahoma", 8));
for (int i = 1; i <= 21; i++)
{
oWs.Cells[1, i].Style.Border.BorderAround(OfficeOpenXml.Style.ExcelBorderStyle.Thin);
oWs.Cells[1, i].Style.Font.Size = 9; //letra tamaño
oWs.Cells[1, i].Style.Font.Bold = true; //Letra negrita
}
oWs.Cells[1, 1].Value = "ITEM";
oWs.Cells[1, 2].Value = "NOBORAR";
oWs.Cells[1, 3].Value = "INSTALACIÓN";
oWs.Cells[1, 4].Value = "APARATO";
oWs.Cells[1, 5].Value = "TIPO CALLE";
oWs.Cells[1, 6].Value = "NOMBRE DE CALLE";
oWs.Cells[1, 7].Value = "ALTURA DE CALLE";
oWs.Cells[1, 8].Value = "NÚMERO DE EDIFICIO";
oWs.Cells[1, 9].Value = "NÚMERO DE DEPARTAMENTO";
oWs.Cells[1, 10].Value = "DETALLE CONSTRUCCIÓN (OBJETO DE CONEXIÓN)";
oWs.Cells[1, 11].Value = "CONJUNTO DE VIVIENDA (OBJETO DE CONEXIÓN)";
oWs.Cells[1, 12].Value = "MANZANA/LOTE";
oWs.Cells[1, 13].Value = "DISTRITO";
oWs.Cells[1, 14].Value = "CUENTA CONTRATO";
oWs.Cells[1, 15].Value = "SECUENCIA DE LECTURA";
oWs.Cells[1, 16].Value = "UNIDAD DE LECTURA";
oWs.Cells[1, 17].Value = "NÚMERO DE LECTURAS ESTIMADAS CONSECUTIVAS";
oWs.Cells[1, 18].Value = "EMPRESA LECTORA";
oWs.Cells[1, 19].Value = "NOTA 2 DE LA UBICACIÓN DEL APARATO";
oWs.Cells[1, 20].Value = "TECNICO";
oWs.Cells[1, 21].Value = "SECUENCIA";
int acu = 0;
foreach (Cls_Entidad_Export_trabajos_lectura oBj in _lista)
{
acu = acu + 1;
for (int i = 1; i <= 21; i++)
{
oWs.Cells[_fila, i].Style.Border.BorderAround(OfficeOpenXml.Style.ExcelBorderStyle.Thin);
}
oWs.Cells[_fila, 1].Value = acu;
oWs.Cells[_fila, 2].Value = oBj.id_Lectura;
oWs.Cells[_fila, 3].Value = oBj.Instalacion;
//oWs.Cells[_fila, 4].Style.Numberformat.Format = "#,##0";
//oWs.Cells[_fila, 4].Value = Convert.ToDouble(oBj.Aparato);
oWs.Cells[_fila, 4].Value = oBj.Aparato;
oWs.Cells[_fila, 5].Value = oBj.Tipo_calle;
oWs.Cells[_fila, 6].Value = oBj.Nombre_Calle;
oWs.Cells[_fila, 7].Value = oBj.Altura_Calle;
oWs.Cells[_fila, 8].Value = oBj.Numero_Edificio;
oWs.Cells[_fila, 9].Value = oBj.Numero_Departamento;
oWs.Cells[_fila, 10].Value = oBj.Detalle_Construccion;
oWs.Cells[_fila, 11].Value = oBj.Conjunto_Vivienda;
oWs.Cells[_fila, 12].Value = oBj.Manzana_Lote;
oWs.Cells[_fila, 13].Value = oBj.Distrito;
oWs.Cells[_fila, 14].Value = oBj.Cuenta_contrato;
oWs.Cells[_fila, 15].Value = oBj.Secuencia_lectura;
oWs.Cells[_fila, 16].Value = oBj.Unidad_lectura;
oWs.Cells[_fila, 17].Value = oBj.Numero_lecturas_estimadas_consecutivas;
oWs.Cells[_fila, 18].Value = oBj.Empresa_Lectora;
oWs.Cells[_fila, 19].Value = oBj.Nota_2_ubicacion_aparato;
oWs.Cells[_fila, 20].Value = oBj.Tecnico;
oWs.Cells[_fila, 21].Value = oBj.Secuencia;
_fila++;
}
oWs.Row(1).Style.Font.Bold = true;
oWs.Row(1).Style.HorizontalAlignment = Style.ExcelHorizontalAlignment.Center;
oWs.Row(1).Style.VerticalAlignment = Style.ExcelVerticalAlignment.Center;
oWs.Column(1).Style.Font.Bold = true;
for (int i = 1; i <= 21; i++)
{
oWs.Column(i).AutoFit();
}
oEx.Save();
}
return(_Serialize("1|" + ruta_descarga + nombreArchivo, true));
}
catch (Exception ex)
{
return(_Serialize("0|" + ex.Message, true));
}
}
public string generarExcelResumenOperario(DataTable listReporteGeneral, string fechaini, string imgBase)
{
string Res = "";
string _servidor;
string FileRuta = "";
string FileExcel = "";
try
{
_servidor = String.Format("{0:ddMMyyyy_hhmmss}.xlsx", DateTime.Now);
FileRuta = System.Web.Hosting.HostingEnvironment.MapPath("~/ArchivosExcel/" + "RESUMEN_OPERARIO" + _servidor);
string rutaServer = ConfigurationManager.AppSettings["servidor_archivos"];
FileExcel = rutaServer + "RESUMEN_OPERARIO" + _servidor;
FileInfo _file = new FileInfo(FileRuta);
if (_file.Exists)
{
_file.Delete();
_file = new FileInfo(FileRuta);
}
if (listReporteGeneral.Rows.Count <= 0)
{
Res = "0|No hay informacion disponible";
return(Res);
}
using (Excel.ExcelPackage oEx = new Excel.ExcelPackage(_file))
{
Excel.ExcelWorksheet oWs = oEx.Workbook.Worksheets.Add("reporte");
oWs.Cells[1, 1].Value = "RESUMEN POR OPERARIO: ";
oWs.Cells[1, 1].Style.HorizontalAlignment = Style.ExcelHorizontalAlignment.Center; // alinear texto
oWs.Cells[1, 1, 1, 18].Merge = true; // combinar celdaS
oWs.Cells[1, 1].Style.Font.Size = 15; //letra tamaño
int _ultimaRow = 6;
int count = 0;
foreach (DataRow item in listReporteGeneral.Rows)
{
count = 0;
foreach (DataColumn col in listReporteGeneral.Columns)
{
count++;
string field = col.ToString();
oWs.Cells[_ultimaRow, count].Value = item[field];
oWs.Cells[_ultimaRow, count].Style.Font.Size = 8; //letra tamaño
oWs.Cells[_ultimaRow, count].Style.HorizontalAlignment = Style.ExcelHorizontalAlignment.Center;
}
_ultimaRow++;
}
oEx.Save();
Res = "1|" + FileExcel;
}
}
catch (Exception ex)
{
Res = "0|" + ex.Message;
}
return(Res);
}
public string Reporte_DetalleLecturas(int id_local, int id_servicio, string fechaini, string fechaFin, int ipOperario, int categoriaLectura)
{
string Res = "";
string _servidor;
string _ruta;
string resultado = "";
int _fila = 2;
string FileRuta = "";
string FileExcel = "";
List <ResumenLecturas> loDatos = new List <ResumenLecturas>();
try
{
NLectura obj_negocio = new NLectura();
loDatos = obj_negocio.NListaLecturaDetalladoEstado(id_local, id_servicio, fechaini, fechaFin, ipOperario, categoriaLectura);
if (loDatos.Count == 0)
{
return(_Serialize("0|No hay informacion para mostrar.", true));
}
_servidor = String.Format("{0:ddMMyyyy_hhmmss}.xlsx", DateTime.Now);
FileRuta = System.Web.Hosting.HostingEnvironment.MapPath("~/Upload/Detalle_Resumen_Lecturas_" + _servidor);
string rutaServer = ConfigurationManager.AppSettings["servidor-archivos"];
FileExcel = rutaServer + "Detalle_Resumen_Lecturas_" + _servidor;
FileInfo _file = new FileInfo(FileRuta);
if (_file.Exists)
{
_file.Delete();
_file = new FileInfo(FileRuta);
}
using (Excel.ExcelPackage oEx = new Excel.ExcelPackage(_file))
{
Excel.ExcelWorksheet oWs = oEx.Workbook.Worksheets.Add("ControlProcesos");
oWs.Cells.Style.Font.SetFromFont(new Font("Tahoma", 9));
oWs.Cells[1, 1].Value = "Lecturista";
oWs.Cells[1, 2].Value = "Direccion";
oWs.Cells[1, 3].Value = "Suministro";
oWs.Cells[1, 4].Value = "Medidor";
oWs.Cells[1, 5].Value = "Zona";
oWs.Cells[1, 6].Value = "Lectura";
oWs.Cells[1, 7].Value = "Confirmacion Lectura";
oWs.Cells[1, 8].Value = "Observacion";
oWs.Cells[1, 9].Value = "Estado";
//marco detalle CABECERA
for (int i = 1; i <= 11; i++)
{
oWs.Cells[1, i].Style.Font.Size = 9;
oWs.Cells[1, i].Style.Font.Bold = true;
oWs.Cells[1, i].Style.Border.BorderAround(OfficeOpenXml.Style.ExcelBorderStyle.Thin); //marco
oWs.Cells[1, i].Style.VerticalAlignment = OfficeOpenXml.Style.ExcelVerticalAlignment.Center;
}
foreach (ResumenLecturas obj in loDatos)
{
//marco detalle
for (int i = 1; i <= 9; i++)
{
oWs.Cells[_fila, i].Style.Border.BorderAround(OfficeOpenXml.Style.ExcelBorderStyle.Thin); //marco
}
oWs.Cells[_fila, 1].Value = obj.id_Operario_Lectura;
oWs.Cells[_fila, 2].Value = obj.direccion_lectura;
oWs.Cells[_fila, 3].Value = obj.suministro_lectura;
oWs.Cells[_fila, 4].Value = obj.medidor_lectura;
oWs.Cells[_fila, 5].Value = obj.Zona_lectura;
oWs.Cells[_fila, 6].Value = obj.LecturaMovil_Lectura;
oWs.Cells[_fila, 7].Value = obj.confirmacion_Lectura;
oWs.Cells[_fila, 8].Value = obj.abreviatura_observacion;
oWs.Cells[_fila, 9].Value = obj.descripcion_estado;
_fila++;
}
oWs.Cells.Style.Font.Size = 8; //letra tamaño
oWs.Row(1).Style.Font.Bold = true;
oWs.Row(1).Style.HorizontalAlignment = Style.ExcelHorizontalAlignment.Center;
oWs.Row(1).Style.VerticalAlignment = Style.ExcelVerticalAlignment.Center;
for (int i = 1; i <= 9; i++)
{
oWs.Column(i).AutoFit();
}
oEx.Save();
}
return(_Serialize("1|" + FileExcel, true));
}
catch (Exception ex)
{
return(_Serialize("0|" + ex.Message, true));
}
}
public string Descarga_Excel(string __a, string __g, string __c, string __d, string __e, string __f, int __o, string sector, string zona)
{
Int32 _fila = 2;
string _servidor;
String _ruta;
string resultado = "";
string nombreServicio = "";
string FileExcel = "";
try
{
List <string> listaVar = new List <string>();
listaVar = new List <string>();
listaVar.Add(__a);
listaVar.Add(__g);
listaVar.Add(__c);
listaVar.Add(__d);
listaVar.Add(__e);
listaVar.Add(__f);
listaVar.Add(Convert.ToString(__o));
Session["listaVal"] = listaVar;
List <Lecturas_Tomadas> _lista = new List <Lecturas_Tomadas>();
NLectura obj_negocio = new DSIGE.Negocio.NLectura();
_lista = obj_negocio.NListaLecturasTomadas(
new Request_Lectura_Tomadas()
{
local = Convert.ToInt32(__a),
lista = Convert.ToString(__g),
f_ini = Convert.ToString(__c),
f_fin = Convert.ToString(__d),
suministro = Convert.ToString(__e),
medidor = Convert.ToString(__f),
operario = Convert.ToInt32(__o)
}
);
if (_lista.Count == 0)
{
return(_Serialize("0|No hay informacion para mostrar.", true));
}
nombreServicio = "";
if (Convert.ToInt32(__g) == 1)
{
nombreServicio = "LECTURAS_";
}
else if (Convert.ToInt32(__g) == 2)
{
nombreServicio = "RELECTURA_";
}
_servidor = String.Format("{0:ddMMyyyy_hhmmss}.xlsx", DateTime.Now);
_ruta = Path.Combine(Server.MapPath("~/Temp/") + "\\" + nombreServicio + _servidor);
string rutaServer = ConfigurationManager.AppSettings["Archivos"];
FileExcel = rutaServer + nombreServicio + _servidor;
FileInfo _file = new FileInfo(_ruta);
if (_file.Exists)
{
_file.Delete();
_file = new FileInfo(_ruta);
}
using (Excel.ExcelPackage oEx = new Excel.ExcelPackage(_file))
{
Excel.ExcelWorksheet oWs = oEx.Workbook.Worksheets.Add("Lecturas_tomadas");
oWs.Cells.Style.Font.SetFromFont(new Font("Tahoma", 9));
//marco detalle CABECERA
for (int i = 1; i <= 10; i++)
{
oWs.Cells[1, i].Style.Font.Bold = true;
oWs.Cells[1, i].Style.Border.BorderAround(OfficeOpenXml.Style.ExcelBorderStyle.Thin); //marco
oWs.Cells[1, i].Style.VerticalAlignment = OfficeOpenXml.Style.ExcelVerticalAlignment.Center;
}
oWs.Cells[1, 1].Value = "SUMINISTRO";
oWs.Cells[1, 2].Value = "MEDIDOR";
oWs.Cells[1, 3].Value = "LECTURA ACTUAL";
oWs.Cells[1, 4].Value = "LECTURA ANTERIOR";
oWs.Cells[1, 5].Value = "PROMEDIO DE CONSUMO";
oWs.Cells[1, 6].Value = "LECTURISTA";
oWs.Cells[1, 7].Value = "COD. OBSERVACION";
oWs.Cells[1, 8].Value = "DESC. OBSERVACION";
oWs.Cells[1, 9].Value = "FECHA LECTURA";
oWs.Cells[1, 10].Value = "TIENE FOTO";
int acu = 0;
foreach (Lecturas_Tomadas oBj in _lista)
{
//marco detalle
for (int i = 1; i <= 10; i++)
{
oWs.Cells[_fila, i].Style.Border.BorderAround(OfficeOpenXml.Style.ExcelBorderStyle.Thin); //marco
}
oWs.Cells[_fila, 1].Value = oBj.Suministro_lectura;
oWs.Cells[_fila, 2].Value = oBj.Medidor_lectura;
oWs.Cells[_fila, 3].Value = oBj.Confirmacion_lectura;
oWs.Cells[_fila, 3].Style.VerticalAlignment = OfficeOpenXml.Style.ExcelVerticalAlignment.Center;
oWs.Cells[_fila, 3].Style.HorizontalAlignment = Style.ExcelHorizontalAlignment.Right; // alinear tex
oWs.Cells[_fila, 4].Value = oBj.lectura_Anterior;
oWs.Cells[_fila, 4].Style.VerticalAlignment = OfficeOpenXml.Style.ExcelVerticalAlignment.Center;
oWs.Cells[_fila, 4].Style.HorizontalAlignment = Style.ExcelHorizontalAlignment.Right; // alinear tex
oWs.Cells[_fila, 5].Value = oBj.promedioConsumo_Lectura;
oWs.Cells[_fila, 5].Style.VerticalAlignment = OfficeOpenXml.Style.ExcelVerticalAlignment.Center;
oWs.Cells[_fila, 5].Style.HorizontalAlignment = Style.ExcelHorizontalAlignment.Right; // alinear tex
oWs.Cells[_fila, 6].Value = oBj.Operario;
oWs.Cells[_fila, 7].Value = oBj.observacion_lectura;
oWs.Cells[_fila, 8].Value = oBj.descripcion_observacion;
oWs.Cells[_fila, 9].Value = oBj.fechaLecturaMovil_Lectura;
oWs.Cells[_fila, 10].Value = oBj.tieneFoto_lectura;
_fila++;
}
oWs.Cells.Style.Font.Size = 8; //letra tamaño
oWs.Row(1).Style.Font.Bold = true;
oWs.Row(1).Style.HorizontalAlignment = Style.ExcelHorizontalAlignment.Center;
oWs.Row(1).Style.VerticalAlignment = Style.ExcelVerticalAlignment.Center;
oWs.Column(1).Style.Font.Bold = true;
for (int i = 1; i <= 10; i++)
{
oWs.Column(i).AutoFit();
}
oEx.Save();
}
return(_Serialize("1|" + FileExcel, true));
}
catch (Exception ex)
{
return(_Serialize("0|" + ex.Message, true));
}
}
public string ExportOrderToInvoiceExcelFile(int orderID)
{
var order = _orderService.GetOrderById(orderID);
if (order.Products.Count <= 0 || order.Products.First().CampaignProductRecord == null)
{
throw new Exception("Order Record is not Valid!");
}
var campaign = _campaignServiec.GetCampaignById(order.Products.First().CampaignProductRecord.CampaignRecord_Id);
///
///TODO: Chnage later to read from setting.
///
//string templateAddress = Path.Combine(Server.MapPath());
string template = System.Web.HttpContext.Current.Server.MapPath("/Media/Excel/Templates/Invoice.xlsx");
string newFileName =
string.Format("TYT-{0}-{1}-{2}-{3}-{4}-{5}-{6}{7}.xlsx",
order.Created.ToShortDateString().Replace('.', '-').Replace("/", "-").Replace("\\", "-"),
order.Id,
order.OrderPublicId,
order.Email.Replace("@", "--"),
order.Products.First().CampaignProductRecord.CampaignRecord_Id,
campaign.Alias,
order.CurrencyRecord.Name,
order.TotalPrice);
newFileName = System.Web.HttpContext.Current.Server.MapPath("/Media/Excel/tmp/" + newFileName);
using (OfficeOpenXml.ExcelPackage package = new OfficeOpenXml.ExcelPackage(new FileInfo(newFileName), new FileInfo(template)))
{
ExcelWorkbook myWorkbook = package.Workbook;
ExcelWorksheet myWorksheet = myWorkbook.Worksheets[myWorkbook.Worksheets.First().Name];
myWorksheet.Cells[11, 1].Value = order.Created.ToShortDateString();
myWorksheet.Cells[11, 2].Value = order.OrderPublicId;
myWorksheet.Cells[11, 3].Value = campaign.Alias;
myWorksheet.Cells[11, 4].Value = campaign.Id;
myWorksheet.Cells[11, 5].Value = order.Email;
myWorksheet.Cells[13, 5].Value = order.PaymentMethod; //(order.Paid == null) ? "COD" : "Online Banking";
myWorksheet.Cells[15, 5].Value = (order.CurrencyRecord != null) ? order.CurrencyRecord.Name : "";
myWorksheet.Cells[13, 2].Value = order.FirstName;
myWorksheet.Cells[14, 2].Value = order.LastName;
myWorksheet.Cells[15, 2].Value = order.StreetAddress;
myWorksheet.Cells[16, 2].Value = order.City;
myWorksheet.Cells[17, 2].Value = order.PostalCode;
myWorksheet.Cells[18, 2].Value = order.State;
myWorksheet.Cells[19, 2].Value = order.Country;
myWorksheet.Cells[20, 2].Value = order.PhoneNumber;
var totalprice = order.TotalPriceWithPromo;
double realprice = 0;
double ratio = 1;
if (totalprice != 0)
{
foreach (var product in order.Products)
{
realprice += (product.Count * product.CampaignProductRecord.Price);
}
ratio = totalprice / realprice;
}
int i = 23;
var DC = order.Delivery;
var nMethod = false;
if (order.Created.Year > 2015 && order.Created.Month >= 2 && order.Created.Day > 18)
{
DC = order.Delivery / (1 + (order.TotalSold - 1) / 2);
nMethod = true;
}
foreach (var product in order.Products)
{
//"Style Name", "Colour", "Size", "Unit Price", "Delivery Cost
myWorksheet.Cells[i, 1].Value = product.CampaignProductRecord.ProductRecord.Name; //"Style Name
myWorksheet.Cells[i, 2].Value = product.ProductColorRecord.Name != null ? product.ProductColorRecord.Name : ""; //"Colour
myWorksheet.Cells[i, 3].Value = (product.ProductSizeRecord != null) ? product.ProductSizeRecord.SizeCodeRecord.Name : ""; //"Size
myWorksheet.Cells[i, 4].Value = product.Count; //count
myWorksheet.Cells[i, 5].Value = product.CampaignProductRecord.Price; //"Unit Price;
if (i == 23)
{
if (nMethod)
{
myWorksheet.Cells[i, 6].Value = order.Delivery + ((product.Count - 1) * order.Delivery / 2); //"Unit Price;
}
else
{
myWorksheet.Cells[i, 6].Value = order.Delivery;
}
}
else
{
if (nMethod)
{
myWorksheet.Cells[i, 6].Value = order.Delivery / 2 * product.Count;
}
else
{
myWorksheet.Cells[i, 6].Value = 0; /// order.Delivery / 2 * product.Count;
}
}
//if (product.ProductColorRecord != null && product.ProductSizeRecord != null)
//{
// if (i == 23)
// {
// myWorksheet.Cells[i, 5].Value = ratio * product.CampaignProductRecord.Price + order.Delivery;
// }
// else
// {
// myWorksheet.Cells[i, 5].Value = ratio * product.CampaignProductRecord.Price;
// }
//}
i++;
}
package.Save();
}
return(newFileName);
}
public string GenerarArchivoExcel_data_detallado(DataTable dt_detalles, string nombreFile, string nombreExcel)
{
string _ruta = "";
string Res = "";
int _fila = 2;
string ruta_descarga = ConfigurationManager.AppSettings["Archivos"];
try
{
_ruta = HttpContext.Current.Server.MapPath("~/Temp/" + nombreFile);
FileInfo _file = new FileInfo(_ruta);
if (_file.Exists)
{
_file.Delete();
_file = new FileInfo(_ruta);
}
using (Excel.ExcelPackage oEx = new Excel.ExcelPackage(_file))
{
Excel.ExcelWorksheet oWs = oEx.Workbook.Worksheets.Add(nombreExcel);
oWs.Cells.Style.Font.SetFromFont(new Font("Tahoma", 9));
//for (int i = 1; i <= 8; i++)
//{
// oWs.Cells[1, i].Style.Border.BorderAround(OfficeOpenXml.Style.ExcelBorderStyle.Thin);
//}
oWs.Cells[1, 1].Value = "Año";
oWs.Cells[1, 2].Value = "Mes";
oWs.Cells[1, 3].Value = "Dia";
oWs.Cells[1, 4].Value = "Codigo";
oWs.Cells[1, 5].Value = "Operario";
oWs.Cells[1, 6].Value = "Total";
int ac = 0;
foreach (DataRow oBj in dt_detalles.Rows)
{
ac += 1;
//for (int j = 1; j <= 6; j++)
//{
// oWs.Cells[_fila, j].Style.Border.BorderAround(OfficeOpenXml.Style.ExcelBorderStyle.Thin);
//}
oWs.Cells[_fila, 1].Value = oBj["anio"].ToString();
oWs.Cells[_fila, 2].Value = oBj["mes"].ToString();
oWs.Cells[_fila, 3].Value = oBj["dia"].ToString();
oWs.Cells[_fila, 4].Value = oBj["id_Operario_Lectura"].ToString();
oWs.Cells[_fila, 5].Value = oBj["operario"].ToString();
oWs.Cells[_fila, 6].Style.VerticalAlignment = OfficeOpenXml.Style.ExcelVerticalAlignment.Center;
oWs.Cells[_fila, 6].Style.HorizontalAlignment = Style.ExcelHorizontalAlignment.Right; // alinear texto
oWs.Cells[_fila, 6].Style.Numberformat.Format = "#,##0.00";
oWs.Cells[_fila, 6].Value = Convert.ToDecimal(oBj["cant_reg"]);
_fila++;
}
oWs.Row(1).Style.Font.Bold = true;
oWs.Row(1).Style.HorizontalAlignment = Style.ExcelHorizontalAlignment.Center;
oWs.Row(1).Style.VerticalAlignment = Style.ExcelVerticalAlignment.Center;
oWs.Column(1).Style.Font.Bold = true;
for (int k = 1; k <= 6; k++)
{
oWs.Column(k).AutoFit();
}
oEx.Save();
}
Res = "1|" + ruta_descarga + nombreFile;
}
catch (Exception ex)
{
Res = "0|" + ex.Message;
}
return(Res);
}
public string Descarga_ResultadosReclamosExcel(string fechaini, string fechafin, int servicio)
{
int _fila = 2;
string _ruta;
string nombreServicio = "";
string FileExcel = "";
int usuario = 0;
try
{
List <Lecturas_Tomadas> _lista = new List <Lecturas_Tomadas>();
NLectura obj_negocio = new DSIGE.Negocio.NLectura();
_lista = obj_negocio.N_Descarga_ResultadosReclamosExcel(fechaini, fechafin, servicio);
if (_lista.Count == 0)
{
return(_Serialize("0|No hay informacion para mostrar.", true));
}
usuario = ((Sesion)Session["Session_Usuario_Acceso"]).usuario.usu_id;
nombreServicio = usuario + "RECLAMOS_REPORTE.xlsx";
_ruta = Path.Combine(Server.MapPath("~/Temp/") + "\\" + nombreServicio);
string rutaServer = ConfigurationManager.AppSettings["Archivos"];
FileExcel = rutaServer + nombreServicio;
FileInfo _file = new FileInfo(_ruta);
if (_file.Exists)
{
_file.Delete();
_file = new FileInfo(_ruta);
}
using (Excel.ExcelPackage oEx = new Excel.ExcelPackage(_file))
{
Excel.ExcelWorksheet oWs = oEx.Workbook.Worksheets.Add("Reclamos");
oWs.Cells.Style.Font.SetFromFont(new Font("Tahoma", 9));
oWs.Cells[1, 1].Value = "MEDIDOR";
oWs.Cells[1, 2].Value = "CUENTA CONTRATO";
oWs.Cells[1, 3].Value = "FECHA PLAN LECTURA";
oWs.Cells[1, 4].Value = "MES";
oWs.Cells[1, 5].Value = "LECTURA";
foreach (Lecturas_Tomadas oBj in _lista)
{
oWs.Cells[_fila, 1].Value = oBj.MEDIDOR;
oWs.Cells[_fila, 2].Value = oBj.CTA_CTO;
oWs.Cells[_fila, 3].Value = oBj.FECHA_PLAN_LECTURA;
oWs.Cells[_fila, 4].Value = oBj.MES;
oWs.Cells[_fila, 5].Value = oBj.LECTURA;
_fila++;
}
//------definir el tamaño de todo el documento
oWs.Cells.Style.Font.Size = 8;
oWs.Row(1).Style.Font.Bold = true;
oWs.Row(1).Style.HorizontalAlignment = Style.ExcelHorizontalAlignment.Center;
oWs.Row(1).Style.VerticalAlignment = Style.ExcelVerticalAlignment.Center;
for (int i = 1; i <= 5; i++)
{
oWs.Column(i).AutoFit();
}
oEx.Save();
}
return(_Serialize("1|" + FileExcel, true));
}
catch (Exception ex)
{
return(_Serialize("0|" + ex.Message, true));
}
}
public ActionResult DescargaExcel(string __a)
{
Int32 _fila = 2;
String _servidor;
String _ruta;
if (__a.Length == 0)
{
return(View());
}
List <LecturaHistorico> _lista = MvcApplication._Deserialize <List <LecturaHistorico> >(__a);
_servidor = String.Format("{0:ddMMyyyy_hhmmss}.xlsx", DateTime.Now);
//_ruta = Path.Combine(Server.MapPath("/Lecturas/Temp"), _servidor);
_ruta = Path.Combine(Server.MapPath("/Temp"), _servidor);
FileInfo _file = new FileInfo(_ruta);
if (_file.Exists)
{
_file.Delete();
_file = new FileInfo(_ruta);
}
using (Excel.ExcelPackage oEx = new Excel.ExcelPackage(_file))
{
Excel.ExcelWorksheet oWs = oEx.Workbook.Worksheets.Add("Resumen de Lecturas");
oWs.Cells[1, 1].Value = "OPERADOR";
oWs.Cells[1, 2].Value = "TOTAL";
oWs.Cells[1, 3].Value = "REALIZADOS";
oWs.Cells[1, 4].Value = "CON FOTO";
oWs.Cells[1, 5].Value = "PENDIENTES";
oWs.Cells[1, 6].Value = "% AVANCE";
oWs.Cells[1, 7].Value = "HORA INICIO DE TRABAJO";
oWs.Cells[1, 8].Value = "HORA TERMINO DE TRABAJO";
oWs.Cells[1, 9].Value = "HORAS TRABAJADAS";
foreach (LecturaHistorico oBj in _lista)
{
oWs.Cells[_fila, 1].Value = oBj.des_ope;
oWs.Cells[_fila, 2].Value = oBj.total;
oWs.Cells[_fila, 3].Value = (oBj.realizado);
oWs.Cells[_fila, 4].Value = (oBj.conFoto);
oWs.Cells[_fila, 5].Value = (oBj.pendiente);
oWs.Cells[_fila, 6].Value = (oBj.avance);
oWs.Cells[_fila, 7].Value = (oBj.f_ini);
oWs.Cells[_fila, 8].Value = (oBj.f_fin);
oWs.Cells[_fila, 9].Value = (oBj.horas);
_fila++;
}
oWs.Row(1).Style.WrapText = true;
oWs.Row(1).Style.Font.Bold = true;
oWs.Row(1).Style.HorizontalAlignment = Style.ExcelHorizontalAlignment.Center;
oWs.Row(1).Style.VerticalAlignment = Style.ExcelVerticalAlignment.Center;
oWs.Column(1).Style.Font.Bold = true;
oWs.Column(1).AutoFit();
oWs.Column(2).AutoFit();
oWs.Column(3).AutoFit();
oWs.Column(4).AutoFit();
oWs.Column(5).AutoFit();
oWs.Column(6).AutoFit();
oWs.Column(7).AutoFit();
oWs.Column(8).AutoFit();
oWs.Column(9).AutoFit();
oEx.Save();
}
return(new ContentResult
{
Content = "{ \"__a\": \"" + _servidor + "\" }",
ContentType = "application/json"
});
}
public string GenerarArchivoExcel_data(DataTable dt_detalles, string nombreFile, string nombreExcel)
{
string _ruta = "";
string Res = "";
int _fila = 2;
try
{
_ruta = HttpContext.Current.Server.MapPath("~/Temp/" + nombreFile);
string ruta_descarga = ConfigurationManager.AppSettings["Archivos"];
ruta_descarga = ruta_descarga + nombreFile;
FileInfo _file = new FileInfo(_ruta);
if (_file.Exists)
{
_file.Delete();
_file = new FileInfo(_ruta);
}
using (Excel.ExcelPackage oEx = new Excel.ExcelPackage(_file))
{
Excel.ExcelWorksheet oWs = oEx.Workbook.Worksheets.Add(nombreExcel);
oWs.Cells.Style.Font.SetFromFont(new Font("Tahoma", 9));
for (int i = 1; i <= 12; i++)
{
oWs.Cells[1, i].Style.Border.BorderAround(OfficeOpenXml.Style.ExcelBorderStyle.Thin);
}
oWs.Cells[1, 1].Value = "Unidad Lectura";
oWs.Cells[1, 2].Value = "Cuenta Contrato";
oWs.Cells[1, 3].Value = "Cliente";
oWs.Cells[1, 4].Value = "Direccion";
oWs.Cells[1, 5].Value = "Urbanización";
oWs.Cells[1, 6].Value = "Distrito";
oWs.Cells[1, 7].Value = "Latitud";
oWs.Cells[1, 8].Value = "Longitud";
oWs.Cells[1, 9].Value = "Fecha";
oWs.Cells[1, 10].Value = "Hora";
oWs.Cells[1, 11].Value = "Porcion";
oWs.Cells[1, 12].Value = "Mes";
foreach (DataRow oBj in dt_detalles.Rows)
{
oWs.Cells[_fila, 1].Value = oBj["Cod_UnidadLectura"].ToString();
oWs.Cells[_fila, 2].Value = oBj["CuentaContrato_Reparto"].ToString();
oWs.Cells[_fila, 3].Value = oBj["nombreCliente_Reparto"].ToString();
oWs.Cells[_fila, 4].Value = oBj["Direccion_Reparto"].ToString();
oWs.Cells[_fila, 5].Value = oBj["urbanizacion_Reparto"].ToString();
oWs.Cells[_fila, 6].Value = oBj["distrito_Reparto"].ToString();
oWs.Cells[_fila, 7].Value = oBj["latitud_lectura"].ToString();
oWs.Cells[_fila, 8].Value = oBj["longitud_lectura"].ToString();
oWs.Cells[_fila, 9].Value = oBj["fechaRecepcion_Reparto"].ToString();
oWs.Cells[_fila, 10].Value = oBj["hora"].ToString();
oWs.Cells[_fila, 11].Value = oBj["porcion"].ToString();
oWs.Cells[_fila, 12].Value = oBj["mes"].ToString();
_fila++;
}
oWs.Row(1).Style.Font.Bold = true;
oWs.Row(1).Style.HorizontalAlignment = Style.ExcelHorizontalAlignment.Center;
oWs.Row(1).Style.VerticalAlignment = Style.ExcelVerticalAlignment.Center;
for (int k = 1; k <= 12; k++)
{
oWs.Column(k).AutoFit();
}
oEx.Save();
}
Res = "1|" + ruta_descarga;
}
catch (Exception ex)
{
Res = "0|" + ex.Message;
}
return(Res);
}
public string ExportCampaign2(int campaignID)
{
var _campaign = _campaignServiec.GetCampaignById(campaignID);
var _orders = _orderService.GetOrdersByCampaignID(campaignID);
var max_k = 0;
string template = System.Web.HttpContext.Current.Server.MapPath("/Media/Excel/Templates/campaign2.xlsx");
string newFileName =
string.Format("TYT-{0}-{1}{2}-Items-" + Guid.NewGuid().ToString() + ".xlsx",
DateTime.Now.ToShortDateString().Replace('.', '-').Replace("/", "-").Replace("\\", "-"),
campaignID,
_campaign.Alias);
newFileName = System.Web.HttpContext.Current.Server.MapPath("/Media/Excel/tmp/" + newFileName);
using (OfficeOpenXml.ExcelPackage package = new OfficeOpenXml.ExcelPackage(new FileInfo(newFileName)))
{
ExcelWorkbook myWorkbook = package.Workbook;
ExcelWorksheet myWorksheet = myWorkbook.Worksheets.Add("Total to purchase");//myWorkbook.Worksheets[myWorkbook.Worksheets.First().Name];
myWorksheet.Cells[2, 1].Value = "Campaign Name";
myWorksheet.Cells[3, 2].Value = "Campaign ID";
myWorksheet.Cells[2, 2].Value = _campaign.Title;
myWorksheet.Cells[3, 2].Value = campaignID;
int left = 1;
foreach (var product in _campaign.Products)
{
var product_count = product.ProductRecord.ColorsAvailable.Count();
//myWorksheet.Cells[6, left, 6, left + product_count + 1].Merge = false;
//myWorksheet.Cells[6, left, 6, left + product_count + 1].Merge = true;
myWorksheet.Cells[6, left, 6, left + product_count].Value = product.ProductRecord.Name;
int j = 1;
myWorksheet.Cells[7, left].Value = "Sizes";
foreach (var productColor in product.ProductRecord.ColorsAvailable)
{
myWorksheet.Cells[7, j + left].Value = productColor.ProductColorRecord.Name;
j++;
}
int k = 8;
foreach (var productSize in product.ProductRecord.SizesAvailable)
{
myWorksheet.Cells[k, left].Value = productSize.ProductSizeRecord.SizeCodeRecord.Name;
k++;
}
myWorksheet.Cells[k, left].Value = "Total";
if (max_k < k)
{
max_k = k + 1;
}
int j1 = 1;
var alltottal = 0;
foreach (var product_color in product.ProductRecord.ColorsAvailable)
{
int k1 = 8;
var total1 = 0;
foreach (var product_size in product.ProductRecord.SizesAvailable)
{
//var orders =
// _orders.Where(bb => (bb.OrderStatusRecord.Name.ToLower() == "approved" ||
// bb.OrderStatusRecord.Name.ToLower() == "printing" ||
// bb.OrderStatusRecord.Name.ToLower() == "shipped" ||
// bb.OrderStatusRecord.Name.ToLower() == "delivered"));
//orders = orders.Where(aa => aa.Products.Any(bb => bb.CampaignProductRecord.Id == product.Id));
//orders = orders.Where(aa => aa.Email != null && aa.IsActive == true);
var allProduct = 0;
if (_orders.Count() > 0)
{
foreach (var order in _orders)
{
if (" approved, printing, shipped, delivered".IndexOf(order.OrderStatusRecord.Name.ToLower()) >= 0)
{
foreach (var p in order.Products)
{
if (p.ProductColorRecord != null)
{
if (p.CampaignProductRecord.Id == product.Id && p.ProductColorRecord.Id == product_color.ProductColorRecord.Id && p.ProductSizeRecord.Id == product_size.ProductSizeRecord.Id)
{
allProduct += p.Count;
}
}
}
}
}
}
myWorksheet.Cells[k1, j1 + left].Value = allProduct;
total1 += allProduct;
k1++;
}
myWorksheet.Cells[k1, j1 + left].Value = total1;
alltottal += total1;
j1++;
}
myWorksheet.Cells[max_k + 1, left].Value = "Grand Total";
myWorksheet.Cells[max_k + 1, left + 1].Value = alltottal;
left += (product_count + 1);
}
package.Save();
}
return(newFileName);
}
public string Descarga_repartoExcel(int servicio, string tipoRecibo, string cicloFacturacion, int Estado, string fecha_ini, string fecha_fin, string suministro, string medidor, int operario)
{
int _fila = 2;
string _ruta;
string nombreServicio = "";
string FileExcel = "";
int usuario = 0;
DataTable _lista = new DataTable();
try
{
NLectura obj_negocio = new DSIGE.Negocio.NLectura();
_lista = obj_negocio.N_ListandoReparto_Tomadas(servicio, tipoRecibo, cicloFacturacion, Estado, fecha_ini, fecha_fin, suministro, medidor, operario);
if (_lista.Rows.Count == 0)
{
return(_Serialize("0|No hay informacion para mostrar.", true));
}
usuario = ((Sesion)Session["Session_Usuario_Acceso"]).usuario.usu_id;
nombreServicio = usuario + "LISTADO_REPARTO.xlsx";
_ruta = Path.Combine(Server.MapPath("~/Temp/") + "\\" + nombreServicio);
string rutaServer = ConfigurationManager.AppSettings["Archivos"];
FileExcel = rutaServer + nombreServicio;
FileInfo _file = new FileInfo(_ruta);
if (_file.Exists)
{
_file.Delete();
_file = new FileInfo(_ruta);
}
using (Excel.ExcelPackage oEx = new Excel.ExcelPackage(_file))
{
Excel.ExcelWorksheet oWs = oEx.Workbook.Worksheets.Add("listado_reparto");
oWs.Cells.Style.Font.SetFromFont(new Font("Tahoma", 9));
oWs.Cells[1, 1].Value = "Suministro";
oWs.Cells[1, 2].Value = "Ciclo";
oWs.Cells[1, 3].Value = "Fecha Recojo";
oWs.Cells[1, 4].Value = "Fecha Entrega";
oWs.Cells[1, 5].Value = "Unidad Lectura";
oWs.Cells[1, 6].Value = "Operario";
oWs.Cells[1, 7].Value = "Estado";
foreach (DataRow oBj in _lista.Rows)
{
oWs.Cells[_fila, 1].Value = oBj["suministro"].ToString();
oWs.Cells[_fila, 2].Value = oBj["ciclo"].ToString();
oWs.Cells[_fila, 3].Value = oBj["fecha_recojo"].ToString();
oWs.Cells[_fila, 4].Value = oBj["fecha_entrega"].ToString();
oWs.Cells[_fila, 5].Value = oBj["unidad_lectura"].ToString();
oWs.Cells[_fila, 6].Value = oBj["operario"].ToString();
oWs.Cells[_fila, 7].Value = oBj["estado"].ToString();
_fila++;
}
//------definir el tamaño de todo el documento
oWs.Cells.Style.Font.Size = 8;
oWs.Row(1).Style.Font.Bold = true;
oWs.Row(1).Style.HorizontalAlignment = Style.ExcelHorizontalAlignment.Center;
oWs.Row(1).Style.VerticalAlignment = Style.ExcelVerticalAlignment.Center;
for (int i = 1; i <= 5; i++)
{
oWs.Column(i).AutoFit();
}
oEx.Save();
}
return(_Serialize("1|" + FileExcel, true));
}
catch (Exception ex)
{
return(_Serialize("0|" + ex.Message, true));
}
}
private void Import_To_Grid(string FilePath, string Extension, string isHDR)
{
if (Extension == ".xls")
{
//string notxlsx = ("This is not an xlsx file");
//ClientScript.RegisterStartupScript(this.GetType(), "myalert", "alert('" + notxlsx + "');", true);
HSSFWorkbook hssfworkbook;
using (FileStream file = new FileStream(FilePath, FileMode.Open, FileAccess.Read))
hssfworkbook = new HSSFWorkbook(file);
ISheet sheet = hssfworkbook.GetSheetAt(0);
System.Collections.IEnumerator rows = sheet.GetRowEnumerator();
DataTable dt = new DataTable();
//Counts the number of cells in a row and determines the columns from that.
int counter = sheet.GetRow(0).Cells.Count;
// J < number of columns needs to be exact at this moment
for (int j = 0; j < counter; j++)
{
// set each column to a - ** letters
// dt.Columns.Add(Convert.ToChar(((int)'A') + j).ToString());
//Get first row and set the headers for each cell
//dt.Columns.Add(Convert.ToString((string)sheet.GetRow(0).GetCell(+j).StringCellValue).ToString());
//Get each cell value in row 0 and return its string for a column name.
dt.Columns.Add(sheet.GetRow(0).GetCell(+j).StringCellValue);
}
while (rows.MoveNext())
{
HSSFRow row = (HSSFRow)rows.Current;
DataRow dr = dt.NewRow();
for (int i = 0; i < row.LastCellNum; i++)
{
ICell cell = row.GetCell(i);
if (cell == null)
{
dr[i] = null;
}
else
{
dr[i] = cell.ToString();
}
}
dt.Rows.Add(dr);
}
//Hackish way to remove the bad first row made by getting column names
dt.Rows.RemoveAt(0);
GridView1.Caption = Path.GetFileName(FilePath);
GridView1.DataSource = dt;
//Bind the data
GridView1.DataBind();
sheet.Dispose();
hssfworkbook.Dispose();
}
else
{
//Create a new epplus package using openxml
var pck = new OfficeOpenXml.ExcelPackage();
//load the package with the filepath I got from my fileuploader above on the button
//pck.Load(new System.IO.FileInfo(FilePath).OpenRead());
//stream the package
FileStream stream = new FileStream(FilePath, FileMode.Open);
pck.Load(stream);
//So.. I am basicly telling it that there is 1 worksheet or to just look at the first one. Not really sure what kind of mayham placing 2 in there would cause.
//Don't put 0 in the box it will likely cause it to break since it won't have a worksheet page at all.
var ws = pck.Workbook.Worksheets[1];
//This will add a sheet1 if your doing pck.workbook.worksheets["Sheet1"];
if (ws == null)
{
ws = pck.Workbook.Worksheets.Add("Sheet1");
// Obiviously I didn't add anything to the sheet so probably can count on it being blank.
}
//I created this datatable for below.
DataTable tbl = new DataTable();
//My sad attempt at changing a radio button value into a bool value to check if there is a header on the xlsx
var hdr = bool.Parse(isHDR);
Console.WriteLine(hdr);
//Set the bool value for from above.
var hasHeader = hdr;
//Setup the table based on the value from my bool
foreach (var firstRowCell in ws.Cells[1, 1, 1, ws.Dimension.End.Column])
{
tbl.Columns.Add(hasHeader ? firstRowCell.Text : string.Format("Column {0}", firstRowCell.Start.Column));
}
var startRow = hasHeader ? 2 : 1;
for (var rowNum = startRow; rowNum <= ws.Dimension.End.Row; rowNum++)
{
var wsRow = ws.Cells[rowNum, 1, rowNum, ws.Dimension.End.Column];
var row = tbl.NewRow();
foreach (var cell in wsRow)
{
row[cell.Start.Column - 1] = cell.Text;
}
tbl.Rows.Add(row);
}
//Bind Data to GridView
//I have all my info in the tbl dataTable so the datasource for the Gridview1 is set to tbl
GridView1.Caption = Path.GetFileName(FilePath);
GridView1.DataSource = tbl;
//Bind the data
GridView1.DataBind();
pck.Save();
pck.Dispose();
stream.Close();
// string pathD = FilePath;
FilePath = null;
stream = null;
// var fileToDelete = new FileInfo(pathD);
// fileToDelete.Delete();
}
}
public ActionResult DescargaHistorico(string __a)
{
Int32 _fila = 2;
String _servidor;
String _ruta;
if (__a.Length == 0)
{
return(View());
}
List <AsignaLecturaReLectura> _lista = MvcApplication._Deserialize <List <AsignaLecturaReLectura> >(__a);
_servidor = String.Format("{0:ddMMyyyy_hhmmss}.xlsx", DateTime.Now);
_ruta = Path.Combine(Server.MapPath("/Lecturas/Temp"), _servidor);
FileInfo _file = new FileInfo(_ruta);
if (_file.Exists)
{
_file.Delete();
_file = new FileInfo(_ruta);
}
using (Excel.ExcelPackage oEx = new Excel.ExcelPackage(_file))
{
Excel.ExcelWorksheet oWs = oEx.Workbook.Worksheets.Add("Historico de Lecturas");
oWs.Cells[1, 1].Value = "MEDIDOR";
oWs.Cells[1, 2].Value = "FECHA DE LECTURA";
oWs.Cells[1, 3].Value = "LECTURA";
oWs.Cells[1, 4].Value = "OPERARIO";
oWs.Cells[1, 5].Value = "OBSERVACION";
oWs.Cells[1, 6].Value = "ESTADO";
foreach (AsignaLecturaReLectura oBj in _lista)
{
oWs.Cells[_fila, 1].Value = oBj.medidorLectura;
oWs.Cells[_fila, 2].Value = oBj.fecLectura;
oWs.Cells[_fila, 3].Value = (oBj.lectura);
oWs.Cells[_fila, 4].Value = (oBj.ope_nombre);
oWs.Cells[_fila, 5].Value = (oBj.obsLectura);
oWs.Cells[_fila, 6].Value = (oBj.Estado);
_fila++;
}
oWs.Row(1).Style.WrapText = true;
oWs.Row(1).Style.Font.Bold = true;
oWs.Row(1).Style.HorizontalAlignment = Style.ExcelHorizontalAlignment.Center;
oWs.Row(1).Style.VerticalAlignment = Style.ExcelVerticalAlignment.Center;
oWs.Column(1).Style.Font.Bold = true;
oWs.Column(1).AutoFit();
oWs.Column(2).AutoFit();
oWs.Column(3).AutoFit();
oWs.Column(4).AutoFit();
oWs.Column(5).AutoFit();
oWs.Column(6).AutoFit();
oEx.Save();
}
return(new ContentResult
{
Content = "{ \"__a\": \"" + _servidor + "\" }",
ContentType = "application/json"
});
}
public string GenerarArchivoExcel_solicitud_cab(List <Solicitud_E> listDetalle, int id_usuario)
{
string Res = "";
int _fila = 4;
string FileRuta = "";
string FileExcel = "";
try
{
FileRuta = System.Web.Hosting.HostingEnvironment.MapPath("~/Archivos/Excel/" + id_usuario + "_SolicitudResguardo.xlsx");
string rutaServer = ConfigurationManager.AppSettings["Archivos"];
FileExcel = rutaServer + "Excel/" + id_usuario + "_SolicitudResguardo.xlsx";
FileInfo _file = new FileInfo(FileRuta);
if (_file.Exists)
{
_file.Delete();
_file = new FileInfo(FileRuta);
}
Thread.Sleep(1);
using (Excel.ExcelPackage oEx = new Excel.ExcelPackage(_file))
{
Excel.ExcelWorksheet oWs = oEx.Workbook.Worksheets.Add("solicitudResguardo");
oWs.Cells.Style.Font.SetFromFont(new Font("Tahoma", 8));
oWs.Cells[1, 1].Style.Font.Size = 24; //letra tamaño 2
oWs.Cells[1, 1].Value = "SOLICITUDES DE RESGUARDO";
oWs.Cells[1, 1, 1, 7].Merge = true; // combinar celdaS
oWs.Cells[3, 1].Value = "NRO SOLICITUD";
oWs.Cells[3, 2].Value = "AREA ";
oWs.Cells[3, 3].Value = "SOLICITANTE";
oWs.Cells[3, 4].Value = "JEFE CUADRILLA";
oWs.Cells[3, 5].Value = "FECHA ASIGNACION";
oWs.Cells[3, 6].Value = "CANT. EFECTIVOS";
oWs.Cells[3, 7].Value = "ESTADOS";
foreach (var item in listDetalle)
{
oWs.Cells[_fila, 1].Value = item.nroSolicitud.ToString();
oWs.Cells[_fila, 2].Value = item.descripcionArea.ToString();
oWs.Cells[_fila, 3].Value = item.descripcionSolicitante.ToString();
oWs.Cells[_fila, 4].Value = item.descripcionJefeCuadrilla.ToString();
oWs.Cells[_fila, 5].Value = item.fechaAtencion.ToString();
oWs.Cells[_fila, 6].Value = item.cantidadEfectivos.ToString();
oWs.Cells[_fila, 7].Value = item.descripcionEstado.ToString();
_fila++;
}
oWs.Row(1).Style.Font.Bold = true;
oWs.Row(1).Style.HorizontalAlignment = Style.ExcelHorizontalAlignment.Center;
oWs.Row(1).Style.VerticalAlignment = Style.ExcelVerticalAlignment.Center;
oWs.Row(3).Style.Font.Bold = true;
oWs.Row(3).Style.HorizontalAlignment = Style.ExcelHorizontalAlignment.Center;
oWs.Row(3).Style.VerticalAlignment = Style.ExcelVerticalAlignment.Center;
for (int k = 1; k <= 7; k++)
{
oWs.Column(k).AutoFit();
}
oEx.Save();
}
Res = FileExcel;
}
catch (Exception)
{
throw;
}
return(Res);
}
/// <summary>
/// FUNCTION FOR EXPORT TO EXCEL
/// </summary>
/// <param name="dataTable"></param>
/// <param name="worksheetName"></param>
/// <param name="saveAsLocation"></param>
/// <returns></returns>
public bool WriteDataTableToExcel(System.Data.DataTable dataTable, string worksheetName, string saveAsLocation, string ReporType)
{
ExcelRange excelCellrange;
try
{
var saveFile = new FileInfo(saveAsLocation);
if (saveFile.Exists)
{
saveFile.Delete();
}
using (var package = new OfficeOpenXml.ExcelPackage(saveFile))
{
var excelSheet = package.Workbook.Worksheets.Add(worksheetName);
for (int i = 1; i <= dataTable.Columns.Count; i++)
{
excelSheet.Cells[3, i].Value = dataTable.Columns[i - 1].ColumnName;
excelSheet.Cells[3, i].Style.Font.Color.SetColor(System.Drawing.ColorTranslator.FromHtml("#666"));
//excelSheet.Cells[1, i].Style.Font.Color.SetColor(System.Drawing.Color.White);
}
// loop through each row and add values to our sheet
int rowcount = 4;
foreach (DataRow datarow in dataTable.Rows)
{
for (int i = 1; i <= dataTable.Columns.Count; i++)
{
excelSheet.Cells[rowcount, i].Value = datarow[i - 1].ToString();
excelCellrange = excelSheet.Cells[rowcount, 1, rowcount, dataTable.Columns.Count];
excelCellrange.Style.Fill.PatternType = ExcelFillStyle.Solid;
string hexA = Convert.ToString(ConfigurationSettings.AppSettings["PARAM_UPLOAD_XL_COLOR"]);
excelCellrange.Style.Fill.BackgroundColor.SetColor(System.Drawing.ColorTranslator.FromHtml(hexA));
excelCellrange.Style.Border.Top.Style = ExcelBorderStyle.Thin;
excelCellrange.Style.Border.Left.Style = ExcelBorderStyle.Thin;
excelCellrange.Style.Border.Right.Style = ExcelBorderStyle.Thin;
excelCellrange.Style.Border.Bottom.Style = ExcelBorderStyle.Thin;
//for alternate rows
if (rowcount > 3)
{
if (i == dataTable.Columns.Count)
{
if (rowcount % 2 == 0)
{
excelCellrange = excelSheet.Cells[rowcount, 1, rowcount, dataTable.Columns.Count];
excelCellrange.Style.Fill.PatternType = ExcelFillStyle.Solid;
string hex = Convert.ToString(ConfigurationSettings.AppSettings["PARAM_UPLOAD_XL_COLOR"]);
excelCellrange.Style.Fill.BackgroundColor.SetColor(System.Drawing.ColorTranslator.FromHtml(hex));
excelCellrange.Style.Border.Top.Style = ExcelBorderStyle.Thin;
excelCellrange.Style.Border.Left.Style = ExcelBorderStyle.Thin;
excelCellrange.Style.Border.Right.Style = ExcelBorderStyle.Thin;
excelCellrange.Style.Border.Bottom.Style = ExcelBorderStyle.Thin;
}
else
{
excelCellrange = excelSheet.Cells[rowcount, 1, rowcount, dataTable.Columns.Count];
excelCellrange.Style.Fill.PatternType = ExcelFillStyle.Solid;
excelCellrange.Style.Fill.BackgroundColor.SetColor(Color.White);
excelCellrange.Style.Border.Top.Style = ExcelBorderStyle.Thin;
excelCellrange.Style.Border.Left.Style = ExcelBorderStyle.Thin;
excelCellrange.Style.Border.Right.Style = ExcelBorderStyle.Thin;
excelCellrange.Style.Border.Bottom.Style = ExcelBorderStyle.Thin;
}
}
}
}
rowcount += 1;
}
package.Save();
}
return(true);
}
catch (Exception ex)
{
//tracerMailException.SendMail(System.Reflection.MethodInfo.GetCurrentMethod().Name, ex);
return(false);
}
finally
{
/*excelSheet = null;
* excelCellrange = null;
* excelworkBook = null;*/
}
}
public string DescargaExcel_New(int Local, string fechaAsignacion, int TipoServicio)
{
int _fila = 5;
string _ruta;
string nombreArchivo = "";
string ruta_descarga = ConfigurationManager.AppSettings["Archivos"];
var usuario = ((Sesion)Session["Session_Usuario_Acceso"]).usuario.usu_id;
try
{
DataTable dt_detalles = new DataTable();
Cls_Negocio_Export_trabajos_lectura obj_negocio = new DSIGE.Negocio.Cls_Negocio_Export_trabajos_lectura();
dt_detalles = obj_negocio.Capa_Negocio_Get_ListaCortesReconexion_Excel(Local, fechaAsignacion, TipoServicio);
if (dt_detalles.Rows.Count <= 0)
{
return(_Serialize("0|No hay informacion para mostrar.", true));
}
if (TipoServicio == 3)
{
nombreArchivo = "CORTES_EXPORTADO" + usuario + ".xls";
}
else if (TipoServicio == 4)
{
nombreArchivo = "RECONEXION_EXPORTADO" + usuario + ".xls";
}
_ruta = Path.Combine(Server.MapPath("~/Temp") + "\\" + nombreArchivo);
FileInfo _file = new FileInfo(_ruta);
if (_file.Exists)
{
_file.Delete();
_file = new FileInfo(_ruta);
}
using (Excel.ExcelPackage oEx = new Excel.ExcelPackage(_file))
{
Excel.ExcelWorksheet oWs = oEx.Workbook.Worksheets.Add("Estructura");
oWs.Cells.Style.Font.SetFromFont(new Font("Tahoma", 8));
oWs.Cells[1, 1].Value = "ESTRUCTURA LEGALIZACIÓN MASIVA DE ORDENES DE CORTES";
oWs.Cells[1, 1].Style.Font.Size = 18; //letra tamaño
oWs.Cells[1, 1, 1, 31].Merge = true; // combinar celdaS dt
oWs.Cells[1, 1].Style.HorizontalAlignment = Style.ExcelHorizontalAlignment.Center;
oWs.Cells[1, 1].Style.VerticalAlignment = Style.ExcelVerticalAlignment.Center;
oWs.Cells[1, 1].Style.Font.Bold = true; //Letra negrita
oWs.Cells[3, 5].Value = "Actividades";
oWs.Cells[3, 5].Style.Font.Size = 10; //letra tamaño
oWs.Cells[3, 5, 3, 19].Merge = true; // combinar celdaS dt
oWs.Cells[3, 5].Style.HorizontalAlignment = Style.ExcelHorizontalAlignment.Center;
oWs.Cells[3, 5].Style.VerticalAlignment = Style.ExcelVerticalAlignment.Center;
oWs.Cells[3, 5].Style.Font.Bold = true; //Letra negrita
oWs.Cells[3, 21].Value = "Lecturas_Elementos";
oWs.Cells[3, 21].Style.Font.Size = 10; //letra tamaño
oWs.Cells[3, 21, 3, 27].Merge = true; // combinar celdaS dt
oWs.Cells[3, 21].Style.HorizontalAlignment = Style.ExcelHorizontalAlignment.Center;
oWs.Cells[3, 21].Style.VerticalAlignment = Style.ExcelVerticalAlignment.Center;
oWs.Cells[3, 21].Style.Font.Bold = true; //Letra negrita
oWs.Cells[3, 28].Value = "Tipo Comentario";
oWs.Cells[3, 28].Style.Font.Size = 10; //letra tamaño
oWs.Cells[3, 28, 3, 29].Merge = true; // combinar celdaS dt
oWs.Cells[3, 28].Style.HorizontalAlignment = Style.ExcelHorizontalAlignment.Center;
oWs.Cells[3, 28].Style.VerticalAlignment = Style.ExcelVerticalAlignment.Center;
oWs.Cells[3, 28].Style.Font.Bold = true; //Letra negrita
oWs.Cells[3, 30].Value = "Fechas Legalización";
oWs.Cells[3, 30].Style.Font.Size = 10; //letra tamaño
oWs.Cells[3, 30, 3, 31].Merge = true; // combinar celdaS dt
oWs.Cells[3, 30].Style.HorizontalAlignment = Style.ExcelHorizontalAlignment.Center;
oWs.Cells[3, 30].Style.VerticalAlignment = Style.ExcelVerticalAlignment.Center;
oWs.Cells[3, 30].Style.Font.Bold = true; //Letra negrita
oWs.Cells[4, 1].Value = "Orden";
oWs.Cells[4, 2].Value = "Causal";
oWs.Cells[4, 3].Value = "Personal";
oWs.Cells[4, 4].Value = "Datos_Adicionales";
oWs.Cells[4, 5].Value = "Or_Activity_id>";
oWs.Cells[4, 6].Value = "Cant Legal;";
oWs.Cells[4, 7].Value = "Nombre Atributo 1>";
oWs.Cells[4, 8].Value = "Medidor>";
oWs.Cells[4, 9].Value = "Id Componente>";
oWs.Cells[4, 10].Value = "Sello1=";
oWs.Cells[4, 11].Value = "Codigo Ubicación Sello=";
oWs.Cells[4, 12].Value = "Acción=";
oWs.Cells[4, 13].Value = "Manipulado S/N=";
oWs.Cells[4, 14].Value = "Medidor!";
oWs.Cells[4, 15].Value = "Sello2=";
oWs.Cells[4, 16].Value = "Ubicación=";
oWs.Cells[4, 17].Value = "Acción=";
oWs.Cells[4, 18].Value = "Manipulado S/N=";
oWs.Cells[4, 19].Value = "Medidor;;;";
oWs.Cells[4, 20].Value = "Items_Elementos";
oWs.Cells[4, 21].Value = "Medidor;";
oWs.Cells[4, 22].Value = "Consumo=";
oWs.Cells[4, 23].Value = "Lectura=";
oWs.Cells[4, 24].Value = "Causa=";
oWs.Cells[4, 25].Value = "Observación1=";
oWs.Cells[4, 26].Value = "Observación2=";
oWs.Cells[4, 27].Value = "Observación3";
oWs.Cells[4, 28].Value = "Código Comentario;";
oWs.Cells[4, 29].Value = "Comentario";
oWs.Cells[4, 30].Value = "Fecha Ini Ejec;";
oWs.Cells[4, 31].Value = "Fecha Fin Ejec";
int acu = 0;
foreach (DataRow oBj in dt_detalles.Rows)
{
acu = acu + 1;
oWs.Cells[_fila, 1].Value = oBj["Orden"].ToString();
oWs.Cells[_fila, 2].Value = oBj["Causal"].ToString();
oWs.Cells[_fila, 3].Value = oBj["Personal"].ToString();
oWs.Cells[_fila, 4].Value = oBj["Datos_Adicionales"].ToString();
oWs.Cells[_fila, 5].Value = oBj["Or_Activity_id"].ToString();
oWs.Cells[_fila, 6].Value = oBj["CantLegal"].ToString();
oWs.Cells[_fila, 7].Value = oBj["NombreAtributo1"].ToString();
oWs.Cells[_fila, 8].Value = oBj["Medidor1"].ToString();
oWs.Cells[_fila, 9].Value = oBj["IdComponente"].ToString();
oWs.Cells[_fila, 10].Value = oBj["Sello1"].ToString();
oWs.Cells[_fila, 11].Value = oBj["CodigoUbicacionSello"].ToString();
oWs.Cells[_fila, 12].Value = oBj["Accion1"].ToString();
oWs.Cells[_fila, 13].Value = oBj["ManipuladoS_N1"].ToString();
oWs.Cells[_fila, 14].Value = oBj["Medidor2"].ToString();
oWs.Cells[_fila, 15].Value = oBj["Sello2"].ToString();
oWs.Cells[_fila, 16].Value = oBj["Ubicacion"].ToString();
oWs.Cells[_fila, 17].Value = oBj["Accion2"].ToString();
oWs.Cells[_fila, 18].Value = oBj["ManipuladoS_N2"].ToString();
oWs.Cells[_fila, 19].Value = oBj["Medidor3"].ToString();
oWs.Cells[_fila, 20].Value = oBj["Items_Elementos"].ToString();
oWs.Cells[_fila, 21].Value = oBj["Medidor4"].ToString();
oWs.Cells[_fila, 22].Value = oBj["Consumo"].ToString();
oWs.Cells[_fila, 23].Value = oBj["Lectura"].ToString();
oWs.Cells[_fila, 24].Value = oBj["Causa"].ToString();
oWs.Cells[_fila, 25].Value = oBj["Observacion1"].ToString();
oWs.Cells[_fila, 26].Value = oBj["Observacion2"].ToString();
oWs.Cells[_fila, 27].Value = oBj["Observacion3"].ToString();
oWs.Cells[_fila, 28].Value = oBj["CodigoComentario"].ToString();
oWs.Cells[_fila, 29].Value = oBj["Comentario"].ToString();
oWs.Cells[_fila, 30].Value = oBj["FechaIniEjec"].ToString();
oWs.Cells[_fila, 31].Value = oBj["FechaFinEjec"].ToString();
_fila++;
}
oWs.Row(4).Style.Font.Bold = true;
oWs.Row(4).Style.HorizontalAlignment = Style.ExcelHorizontalAlignment.Center;
oWs.Row(4).Style.VerticalAlignment = Style.ExcelVerticalAlignment.Center;
for (int i = 1; i <= 31; i++)
{
oWs.Column(i).AutoFit();
}
oEx.Save();
}
return(_Serialize("1|" + ruta_descarga + nombreArchivo, true));
}
catch (Exception ex)
{
return(_Serialize("0|" + ex.Message, true));
}
}