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);
Install-Package IronXL.Excel
エクセルワークシート
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");
Install-Package IronXL.Excel
エクセルファイルを作成する
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");
Install-Package IronXL.Excel
スプレッドシートファイルタイプを変換する
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();
Install-Package IronXL.Excel
System.Data.DataSetを介したExcelからSQLへの変換
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]);
}
}
}
Install-Package IronXL.Excel
ExcelからSQLおよびDataGridへのデータ変換はDataTableを介して行います。
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]);
}
}
Install-Package IronXL.Excel
エクセル条件付き書式設定
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");
Install-Package IronXL.Excel
エクセルファイルを保護
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();
Install-Package IronXL.Excel
C#でのExcel数式
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();
Install-Package IronXL.Excel
C#でExcelメタデータを編集する
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");
Install-Package IronXL.Excel
Excelデータベースレコードの更新
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);
}
Install-Package IronXL.Excel
Excel セルのボーダーとフォントをスタイル設定
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");
Install-Package IronXL.Excel
C#でExcel範囲を並び替え
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");
Install-Package IronXL.Excel
エクセルの行および列の繰り返し
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");
Install-Package IronXL.Excel
Excel 印刷設定
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");
Install-Package IronXL.Excel
セルデータ表示形式
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");
Install-Package IronXL.Excel
SQLデータベースからExcelを読み込む
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);
}
Install-Package IronXL.Excel
Excel範囲の結合
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);
}
Install-Package IronXL.Excel
Excel 範囲を選択
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($"");
}
Install-Package IronXL.Excel
エクセルをHTMLに変換
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);
Install-Package IronXL.Excel
集計Excel関数
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();
Install-Package IronXL.Excel
Excelの折れ線グラフを作成
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");
Install-Package IronXL.Excel
Excelのウィンドウ枠の固定
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();
Install-Package IronXL.Excel
Excelワークシートのコピー
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");