Pruebas en un entorno real
Pruebe en producción sin marcas de agua.
Funciona donde lo necesites.
Biblioteca Excel en C
using IronXL;
using System;
using System.Linq;
// Supported for XLSX, XLS, XLSM, XLTX, CSV and TSV
WorkBook workBook = WorkBook.Load("sample.xlsx");
// Select worksheet at index 0
WorkSheet workSheet = workBook.WorkSheets[0];
// Get any existing worksheet
WorkSheet firstSheet = workBook.DefaultWorkSheet;
// Select a cell and return the converted value
int cellValue = workSheet["A2"].IntValue;
// Read from ranges of cells elegantly.
foreach (var cell in workSheet["A2:A10"])
{
Console.WriteLine("Cell {0} has value '{1}'", cell.AddressString, cell.Text);
}
// Calculate aggregate values such as Min, Max and Sum
decimal sum = workSheet["A2:A10"].Sum();
// Linq compatible
decimal max = workSheet["A2:A10"].Max(c => c.DecimalValue);
using IronXL;
// Create new Excel spreadsheet
WorkBook workBook = WorkBook.Create(ExcelFileFormat.XLSX);
// Create worksheets (workSheet1, workSheet2, workSheet3)
WorkSheet workSheet1 = workBook.CreateWorkSheet("workSheet1");
WorkSheet workSheet2 = workBook.CreateWorkSheet("workSheet2");
WorkSheet workSheet3 = workBook.CreateWorkSheet("workSheet3");
// Set worksheet position (workSheet2, workSheet1, workSheet3)
workBook.SetSheetPosition("workSheet2", 0);
// Set active for workSheet3
workBook.SetActiveTab(2);
// Remove workSheet1
workBook.RemoveWorkSheet(1);
workBook.SaveAs("manageWorkSheet.xlsx");
using IronXL;
// Create new Excel WorkBook document
WorkBook workBook = WorkBook.Create();
// Convert XLSX to XLS
WorkBook xlsWorkBook = WorkBook.Create(ExcelFileFormat.XLS);
// Create a blank WorkSheet
WorkSheet workSheet = workBook.CreateWorkSheet("new_sheet");
// Add data and styles to the new worksheet
workSheet["A1"].Value = "Hello World";
workSheet["A1"].Style.WrapText = true;
workSheet["A2"].BoolValue = true;
workSheet["A2"].Style.BottomBorder.Type = IronXL.Styles.BorderType.Double;
// Save the excel file as XLS, XLSX, CSV, TSV, JSON, XML, HTML and streams
workBook.SaveAs("sample.xlsx");
using IronXL;
using System.IO;
// Import any XLSX, XLS, XLSM, XLTX, CSV and TSV
WorkBook workBook = WorkBook.Load("sample.xlsx");
// Export the excel file as XLS, XLSX, XLSM, CSV, TSV, JSON, XML
workBook.SaveAs("sample.xls");
workBook.SaveAs("sample.xlsx");
workBook.SaveAs("sample.tsv");
workBook.SaveAsCsv("sample.csv");
workBook.SaveAsJson("sample.json");
workBook.SaveAsXml("sample.xml");
// Export the excel file as Html, Html string
workBook.ExportToHtml("sample.html");
string htmlString = workBook.ExportToHtmlString();
// Export the excel file as Binary, Byte array, Data set, Stream
byte[] binary = workBook.ToBinary();
byte[] byteArray = workBook.ToByteArray();
System.Data.DataSet dataSet = workBook.ToDataSet(); // Allow easy integration with DataGrids, SQL and EF
Stream stream = workBook.ToStream();
using IronXL;
using System;
using System.Data;
// Supported for XLSX, XLS, XLSM, XLTX, CSV and TSV
WorkBook workBook = WorkBook.Load("sample.xlsx");
// Convert the whole Excel WorkBook to a DataSet
DataSet dataSet = workBook.ToDataSet();
foreach (DataTable table in dataSet.Tables)
{
Console.WriteLine(table.TableName);
// Enumerate by rows or columns first at your preference
foreach (DataRow row in table.Rows)
{
for (int i = 0 ; i < table.Columns.Count ; i++)
{
Console.Write(row[i]);
}
}
}
using IronXL;
using System;
using System.Data;
// Supported for XLSX, XLS, XLSM, XLTX, CSV and TSV
WorkBook workBook = WorkBook.Load("sample.xlsx");
// Select default sheet
WorkSheet workSheet = workBook.DefaultWorkSheet;
// Convert the worksheet to DataTable
DataTable dataTable = workSheet.ToDataTable(true);
// Enumerate by rows or columns first at your preference
foreach (DataRow row in dataTable.Rows)
{
for (int i = 0 ; i < dataTable.Columns.Count ; i++)
{
Console.Write(row[i]);
}
}
using IronXL;
using IronXL.Formatting.Enums;
using IronXL.Styles;
WorkBook workBook = WorkBook.Load("sample.xlsx");
WorkSheet workSheet = workBook.DefaultWorkSheet;
// Create conditional formatting rule
var rule = workSheet.ConditionalFormatting.CreateConditionalFormattingRule(ComparisonOperator.LessThan, "8");
// Set style options
rule.FontFormatting.IsBold = true;
rule.FontFormatting.FontColor = "#123456";
rule.BorderFormatting.RightBorderColor = "#ffffff";
rule.BorderFormatting.RightBorderType = BorderType.Thick;
rule.PatternFormatting.BackgroundColor = "#54bdd9";
rule.PatternFormatting.FillPattern = FillPattern.Diamonds;
// Apply formatting on specified region
workSheet.ConditionalFormatting.AddConditionalFormatting("A3:A8", rule);
// Create conditional formatting rule
var rule1 = workSheet.ConditionalFormatting.CreateConditionalFormattingRule(ComparisonOperator.Between, "7", "10");
// Set style options
rule1.FontFormatting.IsItalic = true;
rule1.FontFormatting.UnderlineType = FontUnderlineType.Single;
// Apply formatting on specified region
workSheet.ConditionalFormatting.AddConditionalFormatting("A3:A9", rule1);
workBook.SaveAs("applyConditionalFormatting.xlsx");
using IronXL;
WorkBook workBook = WorkBook.Load("sample.xlsx");
WorkSheet workSheet = workBook.DefaultWorkSheet;
// Open protected spreadsheet file
WorkBook protectedWorkBook = WorkBook.Load("sample.xlsx", "IronSoftware");
// Spreadsheet protection
// Set protection for spreadsheet file
workBook.Encrypt("IronSoftware");
// Remove protection for spreadsheet file. Original password is required.
workBook.Password = null;
workBook.Save();
// Worksheet protection
// Set protection for individual worksheet
workSheet.ProtectSheet("IronXL");
// Remove protection for particular worksheet. It works without password!
workSheet.UnprotectSheet();
workBook.Save();
using IronXL;
using System.Linq;
WorkBook workBook = WorkBook.Load("sample.xlsx");
WorkSheet workSheet = workBook.DefaultWorkSheet;
// Set Formulas
workSheet["A1"].Formula = "Sum(B8:C12)";
workSheet["B8"].Formula = "=C9/C11";
workSheet["G30"].Formula = "Max(C3:C7)";
// Force recalculate all formula values in all sheets.
workBook.EvaluateAll();
// Get the formula's calculated value. e.g. "52"
var formulaValue = workSheet["G30"].First().FormattedCellValue;
// Get the formula as a string. e.g. "Max(C3:C7)"
string formulaString = workSheet["G30"].Formula;
// Save changes with updated formulas and calculated values.
workBook.Save();
using IronXL;
using System;
WorkBook workBook = WorkBook.Load("sample.xlsx");
// Set author
workBook.Metadata.Author = "Your Name";
// Set comments
workBook.Metadata.Comments = "Monthly report";
// Set title
workBook.Metadata.Title = "July";
// Set keywords
workBook.Metadata.Keywords = "Report";
// Read the creation date of the excel file
DateTime? creationDate = workBook.Metadata.Created;
// Read the last printed date of the excel file
DateTime? printDate = workBook.Metadata.LastPrinted;
workBook.SaveAs("editedMetadata.xlsx");
using IronXL;
using System.Data;
using System.Data.SqlClient;
// Supported for XLSX, XLS, XLSM, XLTX, CSV and TSV
WorkBook workBook = WorkBook.Load("sample.xlsx");
// Convert the workbook to ToDataSet
DataSet dataSet = workBook.ToDataSet();
// Your sql query
string sql = "SELECT * FROM Users";
// Your connection string
string connectionString = @"Data Source=.\SQLEXPRESS;Initial Catalog=usersdb;Integrated Security=True";
using (SqlConnection connection = new SqlConnection(connectionString))
{
// Open connections to the database
connection.Open();
SqlDataAdapter adapter = new SqlDataAdapter(sql, connection);
// Update the values in database using the values in Excel
adapter.Update(dataSet);
}
using IronSoftware.Drawing;
using IronXL;
using IronXL.Styles;
using System.Linq;
WorkBook workBook = WorkBook.Load("sample.xls");
WorkSheet workSheet = workBook.WorkSheets.First();
var range = workSheet["A1:H10"];
var cell = range.First();
// Set background color of the cell with an rgb string
cell.Style.SetBackgroundColor("#428D65");
// Apply styling to the whole range.
// Set underline property to the font
// FontUnderlineType is enum that stands for different types of font underlying
range.Style.Font.Underline = FontUnderlineType.SingleAccounting;
// Define whether to use horizontal line through the text or not
range.Style.Font.Strikeout = false;
// Define whether the font is bold or not
range.Style.Font.Bold = true;
// Define whether the font is italic or not
range.Style.Font.Italic = false;
// Get or set script property of a font
// Font script enum stands for available options
range.Style.Font.FontScript = FontScript.Super;
// Set the type of the border line
// There are also TopBorder,LeftBorder,RightBorder,DiagonalBorder properties
// BorderType enum indicates the line style of a border in a cell
range.Style.BottomBorder.Type = BorderType.MediumDashed;
// Indicate whether the cell should be auto-sized
range.Style.ShrinkToFit = true;
// Set alignment of the cell
range.Style.VerticalAlignment = VerticalAlignment.Bottom;
// Set border color
range.Style.DiagonalBorder.SetColor("#20C96F");
// Define border type and border direction as well
range.Style.DiagonalBorder.Type = BorderType.Thick;
// DiagonalBorderDirection enum stands for direction of diagonal border inside cell
range.Style.DiagonalBorderDirection = DiagonalBorderDirection.Forward;
// Set background color of cells
range.Style.SetBackgroundColor(Color.Aquamarine);
// Set fill pattern of the cell
// FillPattern enum indicates the style of fill pattern
range.Style.FillPattern = FillPattern.Diamonds;
// Set the number of spaces to intend the text
range.Style.Indention = 5;
// Indicate if the text is wrapped
range.Style.WrapText = true;
workBook.SaveAs("stylingOptions.xls");
using IronXL;
WorkBook workBook = WorkBook.Load("sample.xlsx");
WorkSheet workSheet = workBook.DefaultWorkSheet;
// Select a range
var range = workSheet["A1:D20"];
// Select a column(B)
var column = workSheet.GetColumn(1);
// Sort the range in ascending order (A to Z)
range.SortAscending();
// Sort the range by column(C) in ascending order
range.SortByColumn("C", SortOrder.Ascending);
// Sort the column(B) in descending order (Z to A)
column.SortDescending();
workBook.SaveAs("sortExcelRange.xlsx");
using IronXL;
WorkBook workBook = WorkBook.Load("sample.xlsx");
WorkSheet workSheet = workBook.DefaultWorkSheet;
// Set repeating rows for row(2-4)
workSheet.SetRepeatingRows(1, 3);
// Set repeating columns for column(C-D)
workSheet.SetRepeatingColumns(2, 3);
// Set column break after column(H). Hence, the first page will only contain column(A-G)
workSheet.SetColumnBreak(7);
workBook.SaveAs("repeatingRows.xlsx");
using IronXL;
using IronXL.Printing;
WorkBook workBook = WorkBook.Load("sample.xlsx");
WorkSheet workSheet = workBook.DefaultWorkSheet;
// Set the print header and footer of the worksheet
workSheet.Header.Center = "My document";
workSheet.Footer.Center = "Page &P of &N";
// Set the header margin
workSheet.PrintSetup.HeaderMargin = 2.33;
// Set the size of the paper
// Paper size enum represents different sizes of paper
workSheet.PrintSetup.PaperSize = PaperSize.B4;
// Set the print orientation of the worksheet
workSheet.PrintSetup.PrintOrientation = PrintOrientation.Portrait;
// Set black and white printing
workSheet.PrintSetup.NoColor = true;
workBook.SaveAs("PrintSetup.xlsx");
using IronXL;
using System;
using System.Linq;
// Load an existing WorkSheet
WorkBook workBook = WorkBook.Load("sample.xls");
WorkSheet workSheet = workBook.WorkSheets.First();
// Set data display format to cell
// The cell value will look like 12300%
workSheet["A2"].Value = 123;
workSheet["A2"].FormatString = "0.0%";
// The cell value will look like 123.0000
workSheet["A2"].First().FormatString = "0.0000";
// Set data display format to range
DateTime dateValue = new DateTime(2020, 1, 1, 12, 12, 12);
workSheet["A3"].Value = dateValue;
workSheet["A4"].First().Value = new DateTime(2022, 3, 3, 10, 10, 10);
workSheet["A5"].First().Value = new DateTime(2021, 2, 2, 11, 11, 11);
var range = workSheet["A3:A5"];
// The cell(A3) value will look like 1/1/2020 12:12:12 PM
range.FormatString = "MM/dd/yy h:mm:ss";
workBook.SaveAs("numberFormats.xls");
using IronXL;
using System.Data;
using System.Data.SqlClient;
// Your sql query
string sql = "SELECT * FROM Users";
// Your connection string
string connectionString = @"Data Source=.\SQLEXPRESS;Initial Catalog=usersdb;Integrated Security=True";
using (SqlConnection connection = new SqlConnection(connectionString))
{
// Open connections to the database
connection.Open();
SqlDataAdapter adapter = new SqlDataAdapter(sql, connection);
DataSet ds = new DataSet();
// Fill DataSet with data
adapter.Fill(ds);
// Create an Excel workbook from the SQL DataSet
WorkBook workBook = WorkBook.Load(ds);
}
using IronXL;
using System;
using System.Linq;
WorkBook workBook = WorkBook.Load("sample.xls");
WorkSheet workSheet = workBook.WorkSheets.First();
// Get a range from an Excel worksheet
var range = workSheet["A2:A8"];
// Combine two ranges
var combinedRange = range + workSheet["A9:A10"];
// Iterate over combined range
foreach (var cell in combinedRange)
{
Console.WriteLine(cell.Value);
}
using IronXL;
using System;
using System.Linq;
WorkBook workBook = WorkBook.Load("sample.xls");
WorkSheet workSheet = workBook.WorkSheets.First();
// Get range from worksheet
var range = workSheet["A2:A8"];
// Get column from worksheet
var columnA = workSheet.GetColumn(0);
// Get row from worksheet
var row1 = workSheet.GetRow(0);
// Iterate over the range
foreach (var cell in range)
{
Console.WriteLine($"{cell.Value}");
}
// Select and print every row
var rows = workSheet.Rows;
foreach (var eachRow in rows)
{
foreach (var cell in eachRow)
{
Console.Write($" {cell.Value} |");
}
Console.WriteLine($"");
}
using IronXL;
using IronXL.Options;
WorkBook workBook = WorkBook.Load("sample.xlsx");
var options = new HtmlExportOptions()
{
// Set row/column numbers visible in html document
OutputRowNumbers = true,
OutputColumnHeaders = true,
// Set hidden rows/columns visible in html document
OutputHiddenRows = true,
OutputHiddenColumns = true,
// Set leading spaces as non-breaking
OutputLeadingSpacesAsNonBreaking = true
};
// Export workbook to the HTML file
workBook.ExportToHtml("workBook.html", options);
using IronXL;
using System.Linq;
WorkBook workBook = WorkBook.Load("sample.xls");
WorkSheet workSheet = workBook.WorkSheets.First();
// Get range from worksheet
var range = workSheet["A1:A8"];
// Apply sum of all numeric cells within the range
decimal sum = range.Sum();
// Apply average value of all numeric cells within the range
decimal avg = range.Avg();
// Identify maximum value of all numeric cells within the range
decimal max = range.Max();
// Identify minimum value of all numeric cells within the range
decimal min = range.Min();
using IronXL;
using IronXL.Drawing.Charts;
WorkBook workBook = WorkBook.Load("test.xlsx");
WorkSheet workSheet = workBook.DefaultWorkSheet;
// Set the chart type and it's position on the worksheet.
var chart = workSheet.CreateChart(ChartType.Line, 10, 10, 18, 20);
// Add the series to the chart
// The first parameter represents the address of the range for horizontal(category) axis.
// The second parameter represents the address of the range for vertical(value) axis.
var series = chart.AddSeries("B3:B8", "A3:A8");
// Set the chart title.
series.Title = "Line Chart";
// Set the legend position.
// Can be removed by setting it to None.
chart.SetLegendPosition(LegendPosition.Bottom);
// We can change the position of the chart.
chart.Position.LeftColumnIndex = 2;
chart.Position.RightColumnIndex = chart.Position.LeftColumnIndex + 3;
// Plot all the data that was added to the chart before.
// Multiple call of this method leads to plotting multiple charts instead of modifying the existing chart.
// Yet there is no possibility to remove chart or edit it's series/position.
// We can just create new one.
chart.Plot();
workBook.SaveAs("CreateLineChart.xlsx");
using IronXL;
using System.Linq;
WorkBook workBook = WorkBook.Load("sample.xls");
WorkSheet workSheet = workBook.WorkSheets.First();
// Create freeze pane from column(A-B) and row(1-3)
workSheet.CreateFreezePane(2, 3);
// Overwriting freeze or split pane to column(A-E) and row(1-5) as well as applying prescroll
// The column will show E,G,... and the row will show 5,8,...
workSheet.CreateFreezePane(5, 5, 6, 7);
workBook.SaveAs("createFreezePanes.xls");
// Remove all existing freeze or split pane
workSheet.RemovePane();
using IronXL;
WorkBook firstBook = WorkBook.Load("sample.xlsx");
WorkBook secondBook = WorkBook.Create();
// Select first worksheet in the workbook
WorkSheet workSheet = firstBook.DefaultWorkSheet;
// Duplicate the worksheet to the same workbook
workSheet.CopySheet("Copied Sheet");
// Duplicate the worksheet to another workbook with the specified name
workSheet.CopyTo(secondBook, "Copied Sheet");
firstBook.Save();
secondBook.SaveAs("copyExcelWorksheet.xlsx");
Es sencillo crear un analizador CSV. Con sólo dos líneas de código puede cargar un documento CSV, convertirlo y exportarlo a Excel.
Antes de mostrarte cómo crear un analizador CSV, primero instalaremos IronXL en tu proyecto (gratis en el entorno de desarrollo y depuración).
Puede descargarlo directamente desde el sitio web de Iron Software utilizando el siguiente enlace: https://ironsoftware.com/csharp/excel/docs/
o
Haga clic en Instalar
Simplemente, también puede utilizar el símbolo del sistema para desarrolladores:
Install-Package IronXL.Excel
Una vez que haya instalado IronXL, cree un nuevo proyecto y añada el espacio de nombres IronXL
using IronXL;
using IronXL;
Imports IronXL
El siguiente código utiliza el método Load
del objeto Workbook para cargar un archivo CSV en Excel. A continuación, se analiza este archivo. Por último, utiliza el método SaveAs
para guardar el archivo en formato csv.
private void button4_Click(object sender, EventArgs e)
{
// Import XLSX, XLS, XLSM, XLTX, CSV and TSV
WorkBook workBook = WorkBook.Load("Normal_Excel_File.xlsx");
//Exported as : Parsed_CSV.Sheet1.csv
workBook.SaveAs("Parsed_CSV.csv");
}
private void button4_Click(object sender, EventArgs e)
{
// Import XLSX, XLS, XLSM, XLTX, CSV and TSV
WorkBook workBook = WorkBook.Load("Normal_Excel_File.xlsx");
//Exported as : Parsed_CSV.Sheet1.csv
workBook.SaveAs("Parsed_CSV.csv");
}
Private Sub button4_Click(ByVal sender As Object, ByVal e As EventArgs)
' Import XLSX, XLS, XLSM, XLTX, CSV and TSV
Dim workBook As WorkBook = WorkBook.Load("Normal_Excel_File.xlsx")
'Exported as : Parsed_CSV.Sheet1.csv
workBook.SaveAs("Parsed_CSV.csv")
End Sub
No olvides crear un Libro de Excel llamado Archivo_Excel_Normal.xlsx que contenga los siguientes datos:
Curiosamente, el archivo CSV exportado se guardará como Parsed_CSV.Sheet1.csv porque los datos están en la Hoja1 dentro del Libro de Excel. A continuación se muestra el aspecto que tendría el archivo en el Explorador de archivos una vez seleccionado.
Learn more and share how to merge, unmerge, and work with cells in Excel spreadsheets using the handy Documentación de referencia de la API IronXL.
Documentación de referencia de la API IronXLPM > Install-Package IronXL.Excel
Clave de prueba de 30 días al instante.
Clave de prueba de 15 días al instante.
Tu clave de prueba debería estar en el correo electrónico.
Si no es así, póngase en contacto con
support@ironsoftware.com
Install-Package IronXL.Excel
¿Tiene alguna pregunta? Póngase en contacto con nuestro equipo de desarrollo.
¿Quieres desplegar IronXL en un proyecto vivo GRATIS?
Tu clave de prueba debería estar en el correo electrónico.El formulario de prueba se presentó
con éxito.
Si no es así, póngase en contacto con
support@ironsoftware.com
¿Quieres desplegar IronXL en un proyecto vivo GRATIS?
Tu clave de prueba debería estar en el correo electrónico.El formulario de prueba se presentó
con éxito.
Si no es así, póngase en contacto con
support@ironsoftware.com
¿Quieres desplegar IronXL en un proyecto vivo GRATIS?
Tu clave de prueba debería estar en el correo electrónico.El formulario de prueba se presentó
con éxito.
Si no es así, póngase en contacto con
support@ironsoftware.com
¿Quieres desplegar IronXL en un proyecto vivo GRATIS?
Tu clave de prueba debería estar en el correo electrónico.El formulario de prueba se presentó
con éxito.
Si no es así, póngase en contacto con
support@ironsoftware.com
Empezar GRATIS
No se necesita tarjeta de crédito
Pruebe en producción sin marcas de agua.
Funciona donde lo necesites.
Consigue 30 días de producto totalmente funcional.
Póngalo en marcha en cuestión de minutos.
Acceso completo a nuestro equipo de asistencia técnica durante la prueba del producto
No se necesita tarjeta de crédito ni crear una cuenta
Tu clave de prueba debería estar en el correo electrónico.
Si no es así, póngase en contacto con
support@ironsoftware.com
Empezar GRATIS
No se necesita tarjeta de crédito
Pruebe en producción sin marcas de agua.
Funciona donde lo necesites.
Consigue 30 días de producto totalmente funcional.
Póngalo en marcha en cuestión de minutos.
Acceso completo a nuestro equipo de asistencia técnica durante la prueba del producto
No se necesita tarjeta de crédito ni crear una cuenta
Tu clave de prueba debería estar en el correo electrónico.
Si no es así, póngase en contacto con
support@ironsoftware.com
Empezar GRATIS
No se necesita tarjeta de crédito
Pruebe en producción sin marcas de agua.
Funciona donde lo necesites.
Consigue 30 días de producto totalmente funcional.
Póngalo en marcha en cuestión de minutos.
Acceso completo a nuestro equipo de asistencia técnica durante la prueba del producto
Licencias de $749. ¿Tiene alguna pregunta? Póngase en contacto.
Gracias.
Su clave de licencia ha sido enviada al correo electrónico proporcionado.Póngase en contacto con nosotros
Oferta de actualización en 24 horas:
Save 50% en un
Professional Actualizar
Vaya a Professional para cubrir a 10 desarrolladores
y proyectos ilimitados.
:
:
Professional
$600 USD
$299 USD
5 productos .NET por el precio de 2
Valor total de la suite:
$7,192 USD
Precio de actualización
HOY
SÓLO
$499 USD
Después de 24 horas
$1,098 USD
Producto totalmente funcional, obtenga la llave al instante
PM > Install-Package IronXL.Excel
9 productos API .NET para sus documentos de oficina