/// <summary>
/// Export to one sheet excel
/// Trả về :
/// "err": lỗi
/// đường dẫn: thành công
/// </summary>
/// <param name="dt"></param>
/// <param name="fileName"></param>
/// <returns></returns>
public static string ExportDatatableToExcelMultiSheet(DataSet ds)
{
try
{
string _fileName = "CP_Thông tin phản hồi_" + DateTime.Now.ToString("yyyyMMddhhmmss") + ".xlsx";
using (ClosedXML.Excel.XLWorkbook wb = new ClosedXML.Excel.XLWorkbook())
{
for (int i = 0; i < ds.Tables.Count; i++)
{
wb.Worksheets.Add(ds.Tables[i].Copy(), ds.Tables[i].TableName);
}
wb.Style.Alignment.Horizontal = ClosedXML.Excel.XLAlignmentHorizontalValues.Center;
wb.Style.Font.Bold = true;
string pathStr = System.Web.HttpContext.Current.Server.MapPath("/export/") + _fileName;
using (var outStream = new System.IO.MemoryStream())
{
using (var fileStream = new System.IO.FileStream(pathStr, System.IO.FileMode.CreateNew, System.IO.FileAccess.ReadWrite))
{
wb.SaveAs(fileStream, false);
}
}
return(_fileName);
}
}
catch (Exception)
{
return("err");
}
}
private async void m_exportBtn_Click(object sender, EventArgs e)
{
try
{
SaveFileDialog dlg = new SaveFileDialog();
dlg.Filter = "Alarm files (*.xlsx)|*.xlsx";
if (dlg.ShowDialog() != System.Windows.Forms.DialogResult.OK || bcf.Common.BCFUtility.isEmpty(dlg.FileName))
{
return;
}
string filename = dlg.FileName;
//建立 xlxs 轉換物件
Common.XSLXHelper helper = new Common.XSLXHelper();
//取得轉為 xlsx 的物件
ClosedXML.Excel.XLWorkbook xlsx = null;
await Task.Run(() => xlsx = helper.Export(alarmShowList));
if (xlsx != null)
{
xlsx.SaveAs(filename);
}
}
catch (Exception ex)
{
NLog.LogManager.GetCurrentClassLogger().Warn(ex, "Exception");
}
}
protected void btnGenerarExcel2_Click(object sender, EventArgs e)
{
ClosedXML.Excel.XLWorkbook wrkbook = new ClosedXML.Excel.XLWorkbook();
var worksheet = wrkbook.Worksheets.Add("Bitacora");
var currentRow = 1;
worksheet.Cell(currentRow, 1).Value = "CareMonitor";
currentRow = 3;
worksheet.Cell(currentRow, 1).Value = "Fecha: " + DateTime.Now.ToShortDateString();
currentRow = 5;
worksheet.Cell(currentRow, 1).Value = "Servicios Cerrados";
currentRow = 7;
worksheet.Cell(currentRow, 1).Value = "Usuario";
worksheet.Cell(currentRow, 2).Value = "Nombre";
worksheet.Cell(currentRow, 3).Value = "Fecha";
worksheet.Cell(currentRow, 4).Value = "Fecha Cierre";
worksheet.Cell(currentRow, 5).Value = "Dirección";
worksheet.Cell(currentRow, 6).Value = "Tiempo Estimado";
worksheet.Cell(currentRow, 7).Value = "Tiempo de Servicio";
List <BE.Servicio_Cerrado> listaServicio = (List <BE.Servicio_Cerrado>)Session["ListaServiciosCerrados"];
foreach (BE.Servicio_Cerrado bit in listaServicio)
{
currentRow++;
worksheet.Cell(currentRow, 1).Value = bit.Usuario.Nombre + " " + bit.Usuario.Apellido;
worksheet.Cell(currentRow, 2).Value = bit.TipoServicio.Nombre;
worksheet.Cell(currentRow, 3).Value = bit.FechaServicio.ToShortDateString();
worksheet.Cell(currentRow, 4).Value = bit.FechaCierre.ToShortDateString();
worksheet.Cell(currentRow, 5).Value = bit.Usuario.Direccion;
worksheet.Cell(currentRow, 6).Value = bit.TipoServicio.TiempoMedio;
worksheet.Cell(currentRow, 7).Value = bit.TiempoServicio;
if (bit.TiempoServicio > bit.TipoServicio.TiempoMedio)
{
worksheet.Cell(currentRow, 7).Style.Fill.BackgroundColor = ClosedXML.Excel.XLColor.Red;
}
}
MemoryStream stream = new MemoryStream();
wrkbook.SaveAs(stream);
var content = stream.ToArray();
byte[] bytesStream = stream.ToArray();
stream = new MemoryStream();
stream.Write(bytesStream, 0, bytesStream.Length);
stream.Position = 0;
Response.AddHeader("content-disposition", "inline;filename=Reporte.xlsx");
Response.ContentType = "application/octectstream";
Response.BinaryWrite(bytesStream);
Response.End();
}
private void SalvaFileApri()
{
var newfile = Path.Combine(System.IO.Path.GetTempPath(), DateTime.Now.Ticks.ToString() + "_Fatt.xlsx");
_excel.SaveAs(newfile);
if (_excel.Worksheets.Count() > 1)
{
var totRowPage = _excel.Worksheet(1).PageSetup.PrintAreas.First().RowCount();
for (int i = 2; i <= _excel.Worksheets.Count(); i++)
{
var range = _excel.Worksheet(i).Range("A1", "I" + totRowPage.ToString());
var sheetBase = _excel.Worksheet(1);
range.CopyTo(_excel.Worksheet(1).Cell(totRowPage * (i - 1) + 1, 1));
}
_excel.Worksheet(1).PageSetup.PrintAreas.Clear();
_excel.Worksheet(1).PageSetup.PrintAreas.Add(1, 1, totRowPage * _excel.Worksheets.Count(), 9);
for (int i = 2; i <= _excel.Worksheets.Count(); i++)
{
_excel.Worksheet(1).PageSetup.AddHorizontalPageBreak(totRowPage * (i - 1));
}
_excel.Save();
}
Process.Start(newfile);
}
public void exporttoexcel(DataGridView dgv, string Name, string date)
{
try
{
DataTable dt = _objCommon.DataGridView2DataTable(dgv, "", 0);
Name = Name + date;
ClosedXML.Excel.XLWorkbook wbook = new ClosedXML.Excel.XLWorkbook();
wbook.Worksheets.Add(dt);
wbook.Worksheets.Add(dt, Name);
try
{
string folder = Environment.CurrentDirectory + "/ExcelReport/";
// If directory does not exist, don't even try
if (!Directory.Exists(folder))
{
Directory.CreateDirectory(folder);
}
wbook.SaveAs(Environment.CurrentDirectory + "/ExcelReport/" + Name + ".xlsx");
}
catch (Exception ex)
{ MessageBox.Show(ex.Message); }
MessageBox.Show("Excelsheet saved");
}
catch (Exception ex)
{
MessageBox.Show("Please Check Spire.Office.dll if missing");
int line = _error.GetLineNumber(ex);
_error.AddException(ex, "ExportToExcel");
}
}
/// <summary>
/// Generate an Excel file from a list of SMBScanResult.
/// </summary>
/// <param name="hosts">A list of SMBScanResult objects</param>
/// <param name="filename">The name to use for the report (filename_SharesScanResult.xls)</param>
public static void GenerateSMBHostsReport(Dictionary <string, SMBHost> hosts, string filename)
{
int row = 1;
int col = 1;
var workbook = new ClosedXML.Excel.XLWorkbook();
var worksheet = workbook.Worksheets.Add("Scan Result");
row = WriteHeader(worksheet, row, col);
foreach (SMBHost host in hosts.Values)
{
if (host.hostSharesScanResult.Count > 0)
{
foreach (SMBScanResult scanResult in host.hostSharesScanResult.Values)
{
row = WriteShare(worksheet, scanResult.shareACL, row);
if (scanResult.shareSubDirectories.Count > 0)
{
row = WriteShareDirectories(worksheet, scanResult.shareSubDirectories, row, 1, scanResult.shareACL.share.shareInfo.shi1_remark);
}
}
}
else
{
//TODO : write host without shares
}
}
workbook.SaveAs(filename + "_ShareScanResult.xlsx");
}
/// <summary>
/// Export to one sheet excel
/// Trả về :
/// "err": lỗi
/// đường dẫn: thành công
/// </summary>
/// <param name="dt"></param>
/// <param name="fileName"></param>
/// <returns></returns>
public static string ExportDatatableToExcel(System.Data.DataTable dt, string fileName, string sheetName = "SheetBC")
{
try
{
string _fileName = "CP_" + fileName + "_" + DateTime.Now.ToString("yyyyMMddhhmmss") + ".xlsx";
using (ClosedXML.Excel.XLWorkbook wb = new ClosedXML.Excel.XLWorkbook())
{
wb.Worksheets.Add(dt.Copy(), sheetName);
wb.Style.Alignment.Horizontal = ClosedXML.Excel.XLAlignmentHorizontalValues.Center;
wb.Style.Font.Bold = true;
if (!Directory.Exists(System.Web.HttpContext.Current.Server.MapPath("/export/")))
{
Directory.CreateDirectory(System.Web.HttpContext.Current.Server.MapPath("/export/"));
}
string pathStr = System.Web.HttpContext.Current.Server.MapPath("/export/") + _fileName;
using (var outStream = new System.IO.MemoryStream())
{
using (var fileStream = new System.IO.FileStream(pathStr, System.IO.FileMode.CreateNew, System.IO.FileAccess.ReadWrite))
{
wb.SaveAs(fileStream, false);
}
}
return(_fileName);
}
}
catch (Exception)
{
return("err");
}
}
private void XuatExcel(DataTable tb1, DataTable tb2)
{
string tenPhanExcel = "ThucDonSo_" + cbThucDon.Text;
string path = GetPath(tenPhanExcel);
if (path == null)
{
return;
}
ClosedXML.Excel.XLWorkbook workbook = new ClosedXML.Excel.XLWorkbook();
workbook.Worksheets.Add(tenPhanExcel);
workbook.Worksheet(tenPhanExcel).FirstCell().InsertData(tb1.Rows);
workbook.Worksheet(tenPhanExcel).Cell(tb1.Rows.Count + 2, 1).InsertTable(tb2);
FileStream file = new FileStream(path, FileMode.Create);
MemoryStream ms = new MemoryStream();
workbook.SaveAs(ms);
ms.WriteTo(file);
ms.Close();
MessageBox.Show("Da xuat thanh cong");
}
private void buttonX1_Click(object sender, EventArgs e)
{
DataTable dt = new DataTable();
foreach (DataGridViewColumn column in dgv_all.Columns)
{
if (column.Index <= 5)
{
dt.Columns.Add(column.HeaderText);
}
}
foreach (DataGridViewRow rows in dgv_all.Rows)
{
dt.Rows.Add();
foreach (DataGridViewCell cell in rows.Cells)
{
if (cell.ColumnIndex <= 5)
{
dt.Rows[dt.Rows.Count - 1][cell.ColumnIndex] = cell.Value.ToString();
}
}
}
string folderPath = Environment.GetFolderPath(Environment.SpecialFolder.MyDocuments) + "\\POS" + "\\Sales Delete Record Excel\\Cash Drop\\";
if (!Directory.Exists(folderPath))
{
Directory.CreateDirectory(folderPath);
}
using (ClosedXML.Excel.XLWorkbook wb = new ClosedXML.Excel.XLWorkbook())
{
wb.Worksheets.Add(dt, "Cash Drop");
wb.SaveAs(folderPath + DateTime.Now.ToString("yyyy-MM-dd hh") + "CashDrop.xlsx");
}
}
public static void ClosedXmlWriter(string inFile, string outFile)
{
var workbook = new ClosedXML.Excel.XLWorkbook();
ClosedXML.Excel.IXLWorksheet sheet = workbook.Worksheets.Add("Sheet1");
using (var sr = new StreamReader(File.OpenRead(inFile)))
{
int r = 1;
string line = "";
string s;
bool ifInner = false;
while ((s = sr.ReadLine()) != null)
{
if (ifInner)
{
line += "\n" + s;
if (s.Contains("\"\t"))
{
ifInner = false;
}
else
{
continue;
}
}
else
{
line = s;
}
int pos;
if ((pos = s.LastIndexOf('\t')) > -1 && pos != s.Length - 1)
{
if (s[pos + 1] == '"' && s[s.Length - 1] != '"')
{
ifInner = true;
continue;
}
}
var row = sheet.Row(r);
string[] columns = line.Split('\t');
for (var c = 0; c < columns.Length; c++)
{
if (double.TryParse(columns[c], out double value))
{
row.Cell(c + 1).Value = value;
}
else
{
row.Cell(c + 1).Value = columns[c].Trim('"');
}
}
r++;
}
}
workbook.SaveAs(outFile);
}
protected void btnGenerarExcel_Click(object sender, EventArgs e)
{
if (listAlarmas.SelectedIndex != -1)
{
BE.Alarma alarma = ListaAlarma[listAlarmas.SelectedIndex];
ClosedXML.Excel.XLWorkbook wrkbook = new ClosedXML.Excel.XLWorkbook();
var worksheet = wrkbook.Worksheets.Add("Reporte");
var currentRow = 1;
worksheet.Cell(currentRow, 1).Value = "CareMonitor";
currentRow = 3;
worksheet.Cell(currentRow, 1).Value = "Fecha: " + DateTime.Now.ToShortDateString();
currentRow = 5;
worksheet.Cell(currentRow, 1).Value = "Medición de " + alarma.Usuario.Nombre + " " + alarma.Usuario.Apellido + " del " + alarma.Fecha;
currentRow = 7;
worksheet.Cell(currentRow, 1).Value = "Medición";
worksheet.Cell(currentRow, 2).Value = "Valor";
worksheet.Cell(currentRow, 3).Value = "Máximo";
worksheet.Cell(currentRow, 4).Value = "Mínimo";
List <BE.Medicion> ListaMediciones = ListaAlarma[listAlarmas.SelectedIndex].Mediciones;
foreach (BE.Medicion bit in ListaMediciones)
{
currentRow++;
worksheet.Cell(currentRow, 1).Value = bit.Tipo.Nombre;
worksheet.Cell(currentRow, 2).Value = bit.Valor;
if (bit.Valor < bit.Tipo.MinimoMasculino || bit.Valor > bit.Tipo.MaximoMasculino)
{
worksheet.Cell(currentRow, 2).Style.Fill.BackgroundColor = ClosedXML.Excel.XLColor.Red;
}
worksheet.Cell(currentRow, 3).Value = bit.Tipo.MaximoMasculino;
worksheet.Cell(currentRow, 4).Value = bit.Tipo.MinimoMasculino;
}
MemoryStream stream = new MemoryStream();
wrkbook.SaveAs(stream);
var content = stream.ToArray();
byte[] bytesStream = stream.ToArray();
stream = new MemoryStream();
stream.Write(bytesStream, 0, bytesStream.Length);
stream.Position = 0;
Response.AddHeader("content-disposition", "inline;filename=Reporte.xlsx");
Response.ContentType = "application/octectstream";
Response.BinaryWrite(bytesStream);
Response.End();
}
}
public ExcelDocumentResult(List <dynamic> results, Dictionary <string, string> header = null,
string filePath = null)
{
if (results == null)
{
throw new ArgumentNullException("results");
}
this._filePath = filePath;
this._header = header;
using (var doc = new ClosedXML.Excel.XLWorkbook())
{
var first = results.FirstOrDefault() as JObject;
if (first != null)
{
var sheet = doc.AddWorksheet("Results");
var row = 1;
var col = 1;
if (this._header == null)
{
this._header = new Dictionary <string, string>();
foreach (var field in first)
{
this._header[field.Key] = field.Key;
col++;
}
}
col = 1;
foreach (var field in this._header)
{
sheet.Cell(row, col).Value = field.Value;
col++;
}
row++;
foreach (JObject item in results)
{
col = 1;
foreach (var field in this._header)
{
var sb = new StringBuilder((string)item[field.Key].ToObject(typeof(string)));
if (sb.Length < 32000)
{
sheet.Cell(row, col).Value = sb.ToString();
}
else
{
sheet.Cell(row, col).Value = sb.ToString().Substring(0, 32000) + "[.....]";
}
col++;
}
row++;
}
}
doc.SaveAs(_filePath);
}
}
protected void ExportDataFromServer()
{
DataTable table = new DataTable();
using (var db = DBConfig.Connection)
{
MySqlCommand cmd = new MySqlCommand("GetAllAdoptionsProc", db);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add(new MySqlParameter("p_start_date", MySqlDbType.Date));
cmd.Parameters[0].Direction = System.Data.ParameterDirection.Input;
cmd.Parameters.Add(new MySqlParameter("p_end_date", MySqlDbType.Date));
cmd.Parameters[1].Direction = System.Data.ParameterDirection.Input;
cmd.Parameters[0].Value = startDate.SelectedDate;
cmd.Parameters[1].Value = endDate.SelectedDate;
db.Open();
MySqlDataAdapter dataAdapter = new MySqlDataAdapter(cmd);
dataAdapter.Fill(table);
ClosedXML.Excel.XLWorkbook wbook = new ClosedXML.Excel.XLWorkbook();
var ws = wbook.Worksheets.Add(table, "Осиновявания");
ws.Row(1).InsertRowsAbove(1);
if (startDate.ToString() == null && endDate.ToString() == null)
{
ws.Cell(1, 1).Value = "Период:";
ws.Cell(1, 2).Value = startDate.ToString();
ws.Cell(1, 3).Value = endDate.ToString();
}
else
{
ws.Cell(1, 1).Value = "Всички осиновявания";
}
var saveFileDialog = new SaveFileDialog
{
Filter = "Excel files|*.xlsx",
Title = "Save an Excel File"
};
saveFileDialog.ShowDialog();
if (!String.IsNullOrWhiteSpace(saveFileDialog.FileName))
{
wbook.SaveAs(saveFileDialog.FileName);
}
wbook.Dispose();
}
}
public void SaveToTxt(DataSet ds, String fileName)
{
try
{
//System.Text.StringBuilder sb = new StringBuilder();
DataTable dt = new DataTable();
string[] columns = { "VersionDocumentoSAT", "RFCcontribuyente", "MesPeriodo", "AnioPeriodo", "TipoEnvio", "FechaModBal", "NumCuenta", "SaldoInicial", "Cargos", "Creditos", "SaldoFinal" };
foreach (string col in columns)
{
dt.Columns.Add(col, typeof(string));
}
string _Name = "";
foreach (DataRow rowenc in ds.Tables[0].Rows)
{
foreach (DataRow rowdet in ds.Tables[1].Rows)
{
DataRow row = dt.NewRow();
row["VersionDocumentoSAT"] = rowenc["Version"];
row["RFCcontribuyente"] = rowenc["RFC"];
row["MesPeriodo"] = Convert.ToInt32(rowenc["Mes"]);
row["AnioPeriodo"] = rowenc["Anio"];
row["TipoEnvio"] = rowenc["TipoEnvio"];
row["NumCuenta"] = rowdet["NumCta"];
row["SaldoInicial"] = rowdet["SaldoIni"];
row["Cargos"] = rowdet["Debe"];
row["Creditos"] = rowdet["Haber"];
row["SaldoFinal"] = rowdet["SaldoFin"];
dt.Rows.Add(row);
//sb.AppendLine(String.Join("|", row.ItemArray));
}
_Name = $"_{ rowenc["Mes"]}_{rowenc["Anio"]}.xlsx";
}
ClosedXML.Excel.XLWorkbook wb = new ClosedXML.Excel.XLWorkbook();
wb.Worksheets.Add(dt, "Balanza");
wb.SaveAs($"{fileName.Replace(".XML", "").Replace(".xml", "")}{_Name}");
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
}
public void SaveToTxtCatalogos(DataSet ds, String fileName)
{
try
{
//Version RFC Mes Anio CodAgrup NumCta Desc Nivel Natur
DataTable dt = new DataTable();
string[] columns = { "VersionDocumentoSAT", "RFCcontribuyente", "MesPeriodo", "AnioPeriodo", "CodigoAgrupSAT", "NumCuenta", "DescripciónCuenta", "NivelCuenta", "NaturalezaCuenta" };
foreach (string col in columns)
{
dt.Columns.Add(col, typeof(string));
}
string _Name = "";
foreach (DataRow rowenc in ds.Tables[0].Rows)
{
foreach (DataRow rowdet in ds.Tables[1].Rows)
{
DataRow row = dt.NewRow();
row["VersionDocumentoSAT"] = rowenc["Version"];
row["RFCcontribuyente"] = rowenc["RFC"];
row["MesPeriodo"] = Convert.ToInt32(rowenc["Mes"]);
row["AnioPeriodo"] = rowenc["Anio"];
row["CodigoAgrupSAT"] = rowdet["CodAgrup"];
row["NumCuenta"] = rowdet["NumCta"];
row["DescripciónCuenta"] = rowdet["Desc"];
row["NivelCuenta"] = rowdet["Nivel"];
row["NaturalezaCuenta"] = rowdet["Natur"];
dt.Rows.Add(row);
//sb.AppendLine(String.Join("|", row.ItemArray));
}
_Name = $"_{ rowenc["Mes"]}_{rowenc["Anio"]}.xlsx";
}
ClosedXML.Excel.XLWorkbook wb = new ClosedXML.Excel.XLWorkbook();
wb.Worksheets.Add(dt, "Balanza");
wb.SaveAs($"{fileName.Replace(".XML", "").Replace(".xml", "")}{_Name}");
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
}
public async Task <byte[]> ConvertToXlsx(IDictionary <string, IList <string> > data, CancellationToken cancellationToken)
{
return(await Task.Factory.StartNew(() =>
{
_logger.Information("trying to convert lists of data to xlsx file");
_logger.Information("trying to open Excel Work Book.");
ClosedXML.Excel.XLWorkbook wbook = new ClosedXML.Excel.XLWorkbook(ClosedXML.Excel.XLEventTracking.Disabled);
_logger.Information("trying to add Excel Work Sheet.");
var sheet = wbook.Worksheets.Add("sheet1");
int row = 1;
int col = 1;
int rowsCount = data[data.Keys.First()].Count;
int columnsCount = data.Keys.Count;
var keys = data.Keys.ToArray();
_logger.Information($"start printing data to excel file, ColumnsCount={columnsCount} . RowsCount={rowsCount}");
for (row = 1; row <= rowsCount + 1; row++)
{
for (col = 1; col <= columnsCount; col++)
{
string key = keys[col - 1];
var values = data[key];
if (row == 1)
{
//write the column header
sheet.Cell(1, col).Value = key;
}
else
{
sheet.Cell(row, col).Value = values[row - 2];
}
}
if (cancellationToken != null && cancellationToken.IsCancellationRequested)
{
_logger.Information($"the operation has broken, a cancellation request has been sent.");
break;
}
}
_logger.Information($"all data has been printed successfully to the excel file.");
sheet.ExpandColumns();
setHeaderStyle(sheet, 1, 1, 1, columnsCount);
setBorder(sheet, 2, 1, rowsCount + 1, columnsCount);
wbook.Author = "Orphanage Service V" + System.Reflection.Assembly.GetExecutingAssembly().GetName().Version.ToString();
using (MemoryStream memoryStream = new MemoryStream())
{
wbook.SaveAs(memoryStream);
_logger.Information($"the excel file has been saved successfully, an array of bytes will be returned.");
return memoryStream.ToArray();
}
}));
}
protected void btnGenerarExcel_Click(object sender, EventArgs e)
{
ClosedXML.Excel.XLWorkbook wrkbook = new ClosedXML.Excel.XLWorkbook();
var worksheet = wrkbook.Worksheets.Add("Bitacora");
var currentRow = 1;
worksheet.Cell(currentRow, 1).Value = "CareMonitor";
currentRow = 3;
worksheet.Cell(currentRow, 1).Value = "Fecha: " + DateTime.Now.ToShortDateString();
currentRow = 5;
worksheet.Cell(currentRow, 1).Value = "Reporte de Bitácora";
currentRow = 7;
worksheet.Cell(currentRow, 1).Value = "Fecha";
worksheet.Cell(currentRow, 2).Value = "Usuario";
worksheet.Cell(currentRow, 3).Value = "Tipo";
worksheet.Cell(currentRow, 4).Value = "Accion";
List <BE.Bitacora> listaBitacora = (List <BE.Bitacora>)Session["ListaBitacora"];
foreach (BE.Bitacora bit in listaBitacora)
{
currentRow++;
worksheet.Cell(currentRow, 1).Value = bit.Fecha.ToShortDateString();
worksheet.Cell(currentRow, 2).Value = bit.Usuario;
worksheet.Cell(currentRow, 3).Value = bit.Tipo;
worksheet.Cell(currentRow, 4).Value = bit.Accion;
}
MemoryStream stream = new MemoryStream();
wrkbook.SaveAs(stream);
var content = stream.ToArray();
byte[] bytesStream = stream.ToArray();
stream = new MemoryStream();
stream.Write(bytesStream, 0, bytesStream.Length);
stream.Position = 0;
Response.AddHeader("content-disposition", "inline;filename=Reporte.xlsx");
Response.ContentType = "application/octectstream";
Response.BinaryWrite(bytesStream);
Response.End();
}
protected void btnGenerarExcel2_Click(object sender, EventArgs e)
{
ClosedXML.Excel.XLWorkbook wrkbook = new ClosedXML.Excel.XLWorkbook();
var worksheet = wrkbook.Worksheets.Add("Mediciones");
var currentRow = 1;
worksheet.Cell(currentRow, 1).Value = "CareMonitor";
currentRow = 3;
worksheet.Cell(currentRow, 1).Value = "Fecha: " + DateTime.Now.ToShortDateString();
BE.Usuario usu = (BE.Usuario)Session["UsuarioEnSesion"];
currentRow = 5;
worksheet.Cell(currentRow, 1).Value = "Alarmas Cerradas de " + usu.Nombre + " " + usu.Apellido;
currentRow = 7;
worksheet.Cell(currentRow, 1).Value = "Fecha";
worksheet.Cell(currentRow, 2).Value = "Evolución";
foreach (BE.Alarma bit in ListaAlarmas)
{
currentRow++;
worksheet.Cell(currentRow, 1).Value = bit.Fecha.ToShortDateString();
worksheet.Cell(currentRow, 2).Value = bit.Evolucion;
}
MemoryStream stream = new MemoryStream();
wrkbook.SaveAs(stream);
var content = stream.ToArray();
byte[] bytesStream = stream.ToArray();
stream = new MemoryStream();
stream.Write(bytesStream, 0, bytesStream.Length);
stream.Position = 0;
Response.AddHeader("content-disposition", "inline;filename=Reporte.xlsx");
Response.ContentType = "application/octectstream";
Response.BinaryWrite(bytesStream);
Response.End();
}
public void ExportExcelDataSet(DataSet ds, string input_file_path, string template_file_path)
{
try
{
ClosedXML.Excel.XLWorkbook wb = new ClosedXML.Excel.XLWorkbook(template_file_path);
wb.Worksheets.Add(ds);
wb.SaveAs(input_file_path);
}
catch (Exception ex)
{
Logger.Log.error("ExportExcelDataSet with exception: " + ex.ToString());
}
}
/// <summary>
/// Write ACLs of a directory and his subdirectories into an Excel file.
/// </summary>
/// <param name="scanDirectoryResult">Object returned by an ACL scan on a UNC path</param>
/// <param name="filename">The name to use for the report (filename_ShareScanResult.xls)</param>
public static void GenerateSMBDirectoryScanResultReport(ScanDirectoryResult scanDirectoryResult, string filename)
{
int row = 1;
int col = 1;
var workbook = new ClosedXML.Excel.XLWorkbook();
var worksheet = workbook.Worksheets.Add("Scan Result");
row = WriteHeader(worksheet, row, col);
WriteShareDirectories(worksheet, new Dictionary <string, ScanDirectoryResult> {
{ scanDirectoryResult.shareDirectoryACL.shareDirectory, scanDirectoryResult }
}, row);
workbook.SaveAs(filename + "_ShareDirectoryScan.xlsx");
}
static async Task SeasonalImport(string path, int year)
{
using var seasonalRetriever = new MaddenImporter.Core.SeasonalRetriever();
var workbook = new ClosedXML.Excel.XLWorkbook();
var players = (await seasonalRetriever.GetAllPlayers(year)).ToList();
ExcelExtensions.WritePlayerSheet <PassingPlayer>(workbook, Extensions.GetPlayersOfType <PassingPlayer>(players));
ExcelExtensions.WritePlayerSheet <RushingPlayer>(workbook, Extensions.GetPlayersOfType <RushingPlayer>(players));
ExcelExtensions.WritePlayerSheet <DefensePlayer>(workbook, Extensions.GetPlayersOfType <DefensePlayer>(players));
ExcelExtensions.WritePlayerSheet <ReturningPlayer>(workbook, Extensions.GetPlayersOfType <ReturningPlayer>(players));
ExcelExtensions.WritePlayerSheet <KickingPlayer>(workbook, Extensions.GetPlayersOfType <KickingPlayer>(players));
ExcelExtensions.WritePlayerSheet <ReceivingPlayer>(workbook, Extensions.GetPlayersOfType <ReceivingPlayer>(players));
workbook.SaveAs(path + "/players.xlsx");
workbook.Dispose();
}
private async void ErrorExport_Click(object sender, EventArgs e)
{
OpenFileDialog fdlg = new OpenFileDialog();
fdlg.Title = "Select your log file";
fdlg.InitialDirectory = location;
fdlg.Filter = "All files (*.*)|*.*|All files (*.*)|*.*";
fdlg.FilterIndex = 2;
fdlg.RestoreDirectory = true;
if (fdlg.ShowDialog() == DialogResult.OK)
{
fileLocated = fdlg.FileName;
}
else
{
return;
}
statusLabel.Text = "Busy... Please Wait";
string[] lines = File.ReadAllLines(fileLocated);
IEnumerable <string> selectLines = lines.Where(line => line.Contains(": Error:"));
List <String> lista = new List <String>();
foreach (var item in selectLines)
{
lista.Add(item);
}
using (var workBook = new ClosedXML.Excel.XLWorkbook())
{
var worksheet = workBook.Worksheets.Add("Error list");
for (int i = 1; i < lista.Count; i++)
{
worksheet.Cell("A" + i).Value = lista[i - 1];
}
workBook.SaveAs(location + "Error Report.xlsx");
}
await Task.Delay(1000);
statusLabel.Text = "Done!";
await Task.Delay(1000);
statusLabel.Text = "Idle";
}
protected virtual void Response()
{
HttpResponse _response = HttpContext.Current.Response;
_response.ClearContent();
_response.Buffer = true;
_response.AddHeader("content-disposition", string.Format("attachment; filename={0}.xlsx", this._filename ?? (new Guid()).ToString()));
_response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
_response.Charset = "";
using (MemoryStream MyMemoryStream = new MemoryStream())
{
workbook.SaveAs(MyMemoryStream);
MyMemoryStream.WriteTo(_response.OutputStream);
_response.Flush();
_response.End();
}
}
public async Task <IActionResult> GerarRelatorio([FromServices] EvolucionalContext context)
{
var alunos = await context.Alunos.ToListAsync();
var disciplinas = await context.Disciplinas.ToListAsync();
using (var workbook = new ClosedXML.Excel.XLWorkbook())
{
var worksheet = workbook.Worksheets.Add("Aluno");
var currentRow = 1;
var coluna = 1;
worksheet.Cell(currentRow, 1).Value = "Aluno";
worksheet.Cell(currentRow, 2).Value = "Matemática";
worksheet.Cell(currentRow, 3).Value = "Português";
worksheet.Cell(currentRow, 4).Value = "História";
worksheet.Cell(currentRow, 5).Value = "Geografica";
worksheet.Cell(currentRow, 6).Value = "Inglês";
worksheet.Cell(currentRow, 7).Value = "Biologia";
worksheet.Cell(currentRow, 8).Value = "FIlosofia";
worksheet.Cell(currentRow, 9).Value = "Física";
worksheet.Cell(currentRow, 10).Value = "Química";
worksheet.Cell(currentRow, 11).Value = "Media";
foreach (var aluno in alunos.OrderBy(el => el.Nome))
{
currentRow++;
worksheet.Cell(currentRow, 1).Value = aluno.Nome;
worksheet.Cell(currentRow, 2).Value = aluno.Notas.Where(_ => _.Disciplina.Nome == "Matemática").FirstOrDefault().NotaAluno;
worksheet.Cell(currentRow, 3).Value = aluno.Notas.Where(_ => _.Disciplina.Nome == "Português").FirstOrDefault().NotaAluno;
worksheet.Cell(currentRow, 4).Value = aluno.Notas.Where(_ => _.Disciplina.Nome == "História").FirstOrDefault().NotaAluno;
worksheet.Cell(currentRow, 5).Value = aluno.Notas.Where(_ => _.Disciplina.Nome == "Geografica").FirstOrDefault().NotaAluno;
worksheet.Cell(currentRow, 6).Value = aluno.Notas.Where(_ => _.Disciplina.Nome == "Inglês").FirstOrDefault().NotaAluno;
worksheet.Cell(currentRow, 7).Value = aluno.Notas.Where(_ => _.Disciplina.Nome == "Biologia").FirstOrDefault().NotaAluno;
worksheet.Cell(currentRow, 8).Value = aluno.Notas.Where(_ => _.Disciplina.Nome == "FIlosofia").FirstOrDefault().NotaAluno;
worksheet.Cell(currentRow, 9).Value = aluno.Notas.Where(_ => _.Disciplina.Nome == "Física").FirstOrDefault().NotaAluno;
worksheet.Cell(currentRow, 10).Value = aluno.Notas.Where(_ => _.Disciplina.Nome == "Química").FirstOrDefault().NotaAluno;
worksheet.Cell(currentRow, 11).Value = (aluno.Notas.Sum(_ => _.NotaAluno) / disciplinas.Count());
}
var stream = new System.IO.MemoryStream();
workbook.SaveAs(stream);
return(File(stream.ToArray(), "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet", "Faturamento.xls"));
}
}
// сохранение. путь к папке, лист с данными
public void SaveExcel(string fileName, ObservableCollection <Raspisanie> raspisanie)
{
using (ClosedXML.Excel.XLWorkbook workbook = new ClosedXML.Excel.XLWorkbook())
{
workbook.AddWorksheet("Расписание");
var ws = workbook.Worksheet("Расписание");
int row = 1;
foreach (var c in raspisanie)
{
ws.Cell("A" + row.ToString()).Value = c.Party;
ws.Cell("B" + row.ToString()).Value = c.Equipment;
ws.Cell("C" + row.ToString()).Value = c.TStart;
ws.Cell("D" + row.ToString()).Value = c.TStop;
row++;
}
workbook.SaveAs(fileName + "\\" + "Raspisanie.xlsx");
}
}
string AsExcel(DataTable dataTable, string fileName)
{
using (ClosedXML.Excel.XLWorkbook wbook = new ClosedXML.Excel.XLWorkbook())
{
wbook.Worksheets.Add(dataTable, fileName);
var memoryStream = new MemoryStream();
wbook.SaveAs(memoryStream);
var strPdfPath = Path.Combine(
Directory.GetCurrentDirectory(), "wwwroot/Documents",
fileName);
var file = File.Create(strPdfPath);
memoryStream.Position = 0;
memoryStream.WriteTo(file);
memoryStream.Close();
file.Close();
return(fileName);
}
}
static void Main(string[] args)
{
if (args.Count() < 1)
{
Console.WriteLine("Please enter a file path:");
args = new string[1];
args[0] = Console.ReadLine();
}
var path = args[0];
if (!File.Exists(path) || !path.ToUpper().Contains("CSV"))
{
Console.WriteLine("Must provide a valid CSV");
System.Threading.Thread.Sleep(500);
return;
}
List <dynamic> issues;
using (var reader = new StreamReader(path))
{
using (var csv = new CsvReader(reader))
{
issues = csv.GetRecords <dynamic>().ToList();
}
}
using (var wb = new ClosedXML.Excel.XLWorkbook())
{
DataTable table = ToDataTable(issues);
wb.AddWorksheet(table, "Sheet1");
foreach (var ws in wb.Worksheets)
{
ws.Columns().AdjustToContents();
}
var output = path.Substring(0, path.Length - 3) + "xlsx";
wb.SaveAs(output);
Console.WriteLine("wrote to : " + output);
System.Threading.Thread.Sleep(500);
return;
}
}
public static void WriteExcel(string templateName, string fileName, ExcelDelegate xlsd)
{
string templatePath = Path.Combine(Util.GetAppPath(), templateName);
ClosedXML.Excel.XLWorkbook workbook = new ClosedXML.Excel.XLWorkbook(templatePath);
xlsd(workbook);
SaveFileDialog saveFileDialog = new SaveFileDialog();
//saveFileDialog.InitialDirectory = "%USERPROFILE%";
saveFileDialog.Filter = "Excel|*.xlsx";
saveFileDialog.RestoreDirectory = true;
saveFileDialog.ShowHelp = true;
saveFileDialog.FileName = fileName;
if (saveFileDialog.ShowDialog().Equals(DialogResult.OK))
{
workbook.SaveAs(saveFileDialog.FileName);
}
workbook.Dispose();
}
static void PrintCountries()
{
string contentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
string fileName = "authors.xlsx";
Console.WriteLine("Input Address city");
var city = Console.ReadLine().ToString();
var AddressDAL = new AddressDAL(_iconfiguration);
var listAddressModel = AddressDAL.GetList(city);
Console.WriteLine("Your city adress is:");
listAddressModel.ForEach(item =>
{
Console.WriteLine($"Your address is: {item.AddressLine1}");
});
using (var wb = new ClosedXML.Excel.XLWorkbook())
{
var ws = wb.AddWorksheet("Duy");
var currentRow = 1;
ws.Cell(currentRow, 1).SetValue("Id");
ws.Cell(currentRow, 2).SetValue("Address");
ws.Cell(currentRow, 3).SetValue("City");
foreach (var item in listAddressModel)
{
currentRow++;
ws.Cell(currentRow, 1).SetValue(item.AddressId);
ws.Cell(currentRow, 2).SetValue(item.AddressLine1);
ws.Cell(currentRow, 3).SetValue(item.City);
}
string desktopPath = Environment.GetFolderPath(Environment.SpecialFolder.Desktop);
string savePath = Path.Combine(desktopPath, "test.xlsx");
wb.SaveAs(savePath, false);
}
Console.WriteLine("Press any key to stop.");
Console.ReadKey();
}
public ClosedXML.Excel.XLWorkbook exporttoexcel(DataGridView dgv, DataTable Details, string Name, string date)
{
ClosedXML.Excel.XLWorkbook wbook = new ClosedXML.Excel.XLWorkbook();
try
{
DataTable dt = _objCommon.DataGridView2DataTable(dgv, "", 0);
Name = Name + date;
wbook.Worksheets.Add(dt, "Items");
//wbook.Worksheets.Add("Details");
wbook.Worksheets.Add(Details, "Details");
try
{
string folder = Environment.CurrentDirectory + "/ExcelReport/";
// If directory does not exist, don't even try
if (!Directory.Exists(folder))
{
Directory.CreateDirectory(folder);
}
wbook.Protect(true);
wbook.SaveAs(Environment.CurrentDirectory + "/ExcelReport/" + Name + ".xlsx");
}
catch (Exception ex)
{ MessageBox.Show(ex.Message); }
MessageBox.Show("Excelsheet saved");
}
catch (Exception ex)
{
MessageBox.Show("Please Check Closed.XML if missing");
int line = _error.GetLineNumber(ex);
_error.AddException(ex, "ExportToExcel");
}
return(wbook);
}
public static void WriteDataTable(DataTable Data2Write,string FileName,string SheetName)
{
ClosedXML.Excel.XLWorkbook wb = new ClosedXML.Excel.XLWorkbook();
wb.Worksheets.Add(Data2Write, SheetName);
wb.SaveAs(FileName);
}