public void SavingLoadingTableWithNewLineInHeader2()
{
using (var wb = new XLWorkbook())
{
IXLWorksheet ws = wb.Worksheets.Add("Test");
var dt = new DataTable();
string columnName = "Line1" + Environment.NewLine + "Line2";
dt.Columns.Add(columnName);
DataRow dr = dt.NewRow();
dr[columnName] = "some text";
dt.Rows.Add(dr);
ws.Cell(1, 1).InsertTable(dt);
IXLTable table1 = ws.Table(0);
string fieldName1 = table1.Field(0).Name;
Assert.AreEqual(columnName, fieldName1);
using (var ms = new MemoryStream())
{
wb.SaveAs(ms, true);
var wb2 = new XLWorkbook(ms);
IXLWorksheet ws2 = wb2.Worksheet(1);
IXLTable table2 = ws2.Table(0);
string fieldName2 = table2.Field(0).Name;
Assert.AreEqual("Line1\nLine2", fieldName2);
}
}
}
public void ExportDataGridView(string NombreArchivo, System.Data.DataTable Tabla)
{
using (XLWorkbook Libro = new XLWorkbook())
{
IXLWorksheet Hoja = Libro.Worksheets.Add(Tabla, "Reporte");
Hoja.Table(0).ShowAutoFilter = false;
Libro.SaveAs(NombreArchivo + ".xlsx");
}
}
private IEnumerable <T> ToList <T>(IXLWorksheet worksheet)
where T : class, new()
{
var props =
GetImportableProperties(typeof(T));
var fields =
worksheet.Table(typeof(T).Name).Fields;
var data =
worksheet.Table(typeof(T).Name).DataRange;
if (fields.All(f => props.Select(p => p.Name).Contains(f.Name)) == false)
{
throw new MissingFieldException();
}
var export =
new List <T>();
foreach (var r in data.Rows())
{
var e =
new T();
foreach (var p in props)
{
Type t =
Nullable.GetUnderlyingType(p.PropertyType) ?? p.PropertyType;
object value =
(r.Field(p.Name).Value == null || r.Field(p.Name).Value.ToString().IsEmpty()) ? null : Convert.ChangeType(r.Field(p.Name).Value, t);
p.SetValue(e, value, null);
}
export.Add(e);
}
return(export);
}
/// <summary>
/// ''' can't touch already created pivot-table
/// ''' https://github.com/ClosedXML/ClosedXML/pull/124
/// ''' </summary>
/// ''' <param name="sheetSource"></param>
/// ''' <param name="sheetDest"></param>
/// ''' <param name="rows"></param>
/// ''' <param name="cols"></param>
/// ''' <returns></returns>
public ExcelHelper DoPivotTable(string sheetSource, string sheetDest, string[] rows = null, string[] cols = null, string[] datafields = null)
{
try
{
IXLWorksheet worksheetSource = null;
IXLTable sourceTable = null;
if (_workbook.Worksheets.Count < 1)
{
_workbook = new XLWorkbook(this._inputFile.FullName);
}
worksheetSource = _workbook.Worksheets.Worksheet(sheetSource);
sourceTable = worksheetSource.Table(0);
// TODO PRIMA CONTROLLARE SE ESISTE E nel caso cancella
this.RemoveSheet(sheetDest); // <<TODO invece di remove che in questo caso crea un errore cercare se è possibile chiamare REFRESH
IXLWorksheet pivotTableSheet = _workbook.Worksheets.Add(sheetDest);
IXLPivotTable pivoTable = pivotTableSheet.PivotTables.Add("PivotTable", pivotTableSheet.Cell(1, 1), sourceTable.AsRange());
foreach (string r in rows)
{
if (r.Trim() != "")
{
pivoTable.RowLabels.Add(r);
}
}
foreach (string c in cols)
{
if (c.Trim() != "")
{
pivoTable.ColumnLabels.Add(c);
}
}
foreach (string d in datafields)
{
if (d.Trim() != "")
{
pivoTable.Values.Add(d);
}
}
}
// i filtri non sono al momento supportati https://github.com/ClosedXML/ClosedXML/issues/218
catch (Exception ex)
{
//SD.Log(ex.Message, SD.LogLevel.Error, ex.StackTrace);
return(this);
}
return(this);
}
public void SavingLoadingTableWithNewLineInHeader()
{
var wb = new XLWorkbook();
IXLWorksheet ws = wb.AddWorksheet("Sheet1");
string columnName = "Line1" + Environment.NewLine + "Line2";
ws.FirstCell().SetValue(columnName)
.CellBelow().SetValue("A");
ws.RangeUsed().CreateTable();
using (var ms = new MemoryStream())
{
wb.SaveAs(ms);
var wb2 = new XLWorkbook(ms);
IXLWorksheet ws2 = wb2.Worksheet(1);
IXLTable table2 = ws2.Table(0);
string fieldName = table2.Field(0).Name;
Assert.AreEqual("Line1\nLine2", fieldName);
}
}
private void btnSave_Click(object sender, EventArgs e)
{
SaveFileDialog dialog = new SaveFileDialog
{
Filter = $"Excel (*.xlsx)|*.xlsx",
Title = "Save result"
,
FileName = $"PatentCitedByResult_{DateTime.Now.ToString("yyyyMMddHHmmss")}.xlsx"
};
if (dialog.ShowDialog() == DialogResult.OK)
{
XLWorkbook wb = new XLWorkbook();
foreach (DataTable dt in dts)
{
IXLWorksheet ws = wb.AddWorksheet(dt);
ws.Table(0).Theme = XLTableTheme.None;
}
wb.SaveAs(dialog.FileName);
MessageBox.Show($"File {dialog.FileName} saved");
}
}
private List <string> ObtenerListaDeClasesDesdeXlsx(IXLWorksheet hoja, out List <ZonaDePosicionamiento> listaZonaDePosicionamientos)
{
var listaDeErrores = new List <string>();
listaZonaDePosicionamientos = new List <ZonaDePosicionamiento>();
try
{
// Los indices siempre son 1, 2, 3 y 4 porque son las columnas del XLSX generado en la descarga de plantilla, la cual es la aceptada para la carga.
const int indiceBodegas = 1;
const int indiceZona = 2;
const int indiceMandatorio = 3;
const int indiceFamilia = 4;
// Almacena los registros de la plantilla en TablaIXL
IXLTable tableClass = hoja.Table("Clases");
var cantidadFilas = tableClass.RowCount();
// Obtiene la fila de los encabezados
var primeraFilaUsada = tableClass.FirstRowUsed();
var fila = primeraFilaUsada.RowUsed();
// Baja una fila para leer la data
fila = fila.RowBelow();
var indice = 2;
// Recorre las filas de la tabla sin contar el encabezado.
for (int i = 1; i < cantidadFilas; i++)
{
// Valida que las columnas tengan data
if (string.IsNullOrEmpty(fila.Cell(indiceBodegas).GetString()) || string.IsNullOrEmpty(fila.Cell(indiceZona).GetString()) || string.IsNullOrEmpty(fila.Cell(indiceMandatorio).GetString()) || string.IsNullOrEmpty(fila.Cell(indiceFamilia).GetString()))
{
var mensaje = $"La fila No.{indice} tiene campos vacios.";
listaDeErrores.Add(mensaje);
}
else
{
var zonaPosicionamiento = new ZonaDePosicionamiento
{
WAREHOUSE_CODE = fila.Cell(indiceBodegas).GetString()
,
ZONE = fila.Cell(indiceZona).GetString()
,
MANDATORY = (fila.Cell(indiceMandatorio).GetString().Equals("Si"))
,
FAMILY = int.Parse(fila.Cell(indiceFamilia).GetString())
};
Console.WriteLine(zonaPosicionamiento);
// Agrega las clases encontradas en el XLSX a la lista de clases
listaZonaDePosicionamientos.Add(zonaPosicionamiento);
}
// Baja una fila para continuar leyendo el XLSX
fila = fila.RowBelow();
indice++;
}
}
catch (Exception e)
{
InteraccionConUsuarioServicio.Alerta($"Error al leer la hoja de Familia: {e.Message}");
}
return(listaDeErrores);
}