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");
Crear y dar salida a un archivo CSV fácilmente con IronXL libre para el desarrollo. El siguiente código muestra cómo hacerlo.
En primer lugar, debes tener IronXL instalado. Hay varias opciones. Puede descargarlo directamente: https://ironsoftware.com/csharp/excel/docs/
O puede importar el paquete NuGet de IronXL siguiendo los siguientes pasos
Install-Package IronXL.Excel
**Paquete NuGet IronXL.Excel
Así de fácil!
Aquí está el código de este pequeño proyecto. Este código carga un Libro de Excel existente, y luego simplemente lo guarda en el formato CSV utilizando el comando Guardar como CSV método.
El libro de Excel Normal contiene los siguientes datos:
****
/**
Save as CSV file
anchor-save-workbook-as-csv
**/
using IronXL;
WorkBook wb = WorkBook.Load("Normal_Excel_File.xlsx"); //Import .xls, .csv, or .tsv file
wb.SaveAsCsv("SaveAsCSV.csv", ","); //Saved as : SaveAsCSV.Sheet1.csv
/**
Save as CSV file
anchor-save-workbook-as-csv
**/
using IronXL;
WorkBook wb = WorkBook.Load("Normal_Excel_File.xlsx"); //Import .xls, .csv, or .tsv file
wb.SaveAsCsv("SaveAsCSV.csv", ","); //Saved as : SaveAsCSV.Sheet1.csv
'''
'''Save as CSV file
'''anchor-save-workbook-as-csv
'''*
Imports IronXL
Private wb As WorkBook = WorkBook.Load("Normal_Excel_File.xlsx") 'Import .xls, .csv, or .tsv file
wb.SaveAsCsv("SaveAsCSV.csv", ",") 'Saved as : SaveAsCSV.Sheet1.csv
Además, la biblioteca IronXL Excel ofrece las siguientes funciones:
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
dfgdsfgsdfgdfsgdsfg¿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.
Thank you!
Your license key has been delivered to the email provided. Contact us
24-Hour Upgrade Offer:
Save 50% on a
Professional Upgrade
Go Professional to cover 10 developers
and unlimited projects.
hours
:
minutes
:
seconds
Professional
$600 USD
$299 USD
5 .NET Products for the Price of 2
Total Suite Value:
$7,192 USD
Upgrade price
TODAY
ONLY
$499 USD
After 24 Hrs
$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