Unity uses the NPOI library to operate Excel files
1. Import the DLL of NPOI library
Link: https://pan.baidu.com/s/1O29nSZdBsNM8hP_f6-bH1A?pwd=6666
Extraction code: 6666
– Sharing from Baidu Netdisk super member V4
2. Import the class library file into the Plugins folder. The class library file must be placed in the Plugins folder before it can be referenced in C#
3. Set the .NET version. If it is not 4.x, there may be incompatibility issues. This is not necessarily true. It depends on your own situation
4. The code creates an Excel document, writes text, and then opens the Excel document
using System.IO;
using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;
using UnityEngine;
public class ExcelExporter : MonoBehaviour
{<!-- -->
private void Start()
{<!-- -->
//Create a new workbook
IWorkbook workbook = new XSSFWorkbook();
//Create a new worksheet
ISheet sheet = workbook.CreateSheet("site sheet");
// Create a row and add some data in the first column
IRow row = sheet.CreateRow(0);
row.CreateCell(0).SetCellValue("Hello");
// Create another row and add some data in the second column
row = sheet.CreateRow(1);
row.CreateCell(1).SetCellValue("World");
//Save workbook to file
string filePath = Path.Combine("C:\Users\Administrator\Desktop", "Course List Table.xlsx");
using (FileStream fileStream = new FileStream(filePath, FileMode.Create))
{<!-- -->
workbook.Write(fileStream);
}
Debug.Log("Excel file saved at: " + filePath);
System.Diagnostics.Process.Start(filePath);
}
}
5. The results are as follows:
6. The following are examples of some properties and methods commonly used to operate Excel in the NPOI library:
6.1. Create workbooks, worksheets and cells:
//Create a new workbook
IWorkbook workbook = new XSSFWorkbook();
//Create a new worksheet
ISheet sheet = workbook.CreateSheet("Sheet1");
// Create new row (on the first row)
IRow row = sheet.CreateRow(0);
// Create a new cell in this row (in the first column)
ICell cell = row.CreateCell(0);
cell.SetCellValue("Hello");
6.2. Set cell style:
//Create a new style ICellStyle style = workbook.CreateCellStyle(); //Create font IFont font = workbook.CreateFont(); font.FontName = "Arial"; font.FontHeightInPoints = 12; font.IsBold = true; //Apply font to style style.SetFont(font); //Apply styles to cells cell.CellStyle = style;
6.3. Read data in Excel files:
//Load existing Excel file
IWorkbook workbook = new XSSFWorkbook(File.OpenRead("example.xlsx"));
// Get the first worksheet
ISheet sheet = workbook.GetSheetAt(0);
//Read the value of the cell in the first row and first column
string value = sheet.GetRow(0).GetCell(0).ToString();
6.4. Save the workbook to file:
//Save workbook to file
string filePath = "output.xlsx";
using (FileStream fileStream = new FileStream(filePath, FileMode.Create))
{<!-- -->
workbook.Write(fileStream);
}
Of course, in addition to the basic operations mentioned in the above reply, the NPOI library also provides many other functions. Here are some examples of more advanced operations:
6.5. Write data in batches:
If you need to batch write data to an Excel file, you can use NPOI’s loop to create rows and cells. Here’s a simple example:
//Assume there is a two-dimensional array data containing the data to be written
object[,] data = new object[10, 5];
for (int i = 0; i < data.GetLength(0); i + + )
{<!-- -->
IRow row = sheet.CreateRow(i);
for (int j = 0; j < data.GetLength(1); j + + )
{<!-- -->
ICell cell = row.CreateCell(j);
cell.SetCellValue(data[i, j].ToString());
}
}
6.6. Read cells of different data types:
When reading Excel files, cells may contain different types of data, such as strings, numbers, dates, etc. You can use the NPOI library methods to read these different types of data. Here’s an example:
ICell cell = sheet.GetRow(0).GetCell(0);
switch(cell.CellType)
{<!-- -->
case CellType.String:
string strValue = cell.StringCellValue;
break;
case CellType.Numeric:
double numValue = cell.NumericCellValue;
break;
case CellType.Formula:
string formula = cell.CellFormula;
break;
// Other types...
}
6.7. Set the width and height of the cell:
You can use the NPOI library to set the cell width and height. Here’s an example:
//Set the width of the first column to 20 characters wide sheet.SetColumnWidth(0, 20 * 256); //Set the height of the first row to 20 points sheet.GetRow(0).HeightInPoints = 20;
Of course, there are some other NPOI-related operations and examples involving more complex functionality:
6.8. Merge cells:
In Excel, you may need to merge multiple cells to create one large cell. Here is an example of how to use NPOI for cell merging:
// Merge the first column to the third column of the first row sheet.AddMergedRegion(new CellRangeAddress(0, 0, 0, 2));
6.9. Use formula:
You can add formulas in cells, such as sum, average, etc. Here’s an example of how to add a formula:
//Add a summation formula in the first column of the second row to calculate the first two values of the first column
ICell cell = sheet.GetRow(1).CreateCell(0);
cell.SetCellFormula("SUM(A1:A2)");
6.10. Data verification:
You can add data validation to Excel cells to ensure that user-entered data meets specific conditions. Here is an example of how to add data validation using NPOI:
//Create an integer validation that limits input values to between 1 and 10 var dvHelper = new HSSFDataValidationHelper((HSSFWorkbook)workbook); var constraint = dvHelper.CreateIntegerConstraint(ValidationConstraint.OperatorType.Between, "1", "10"); var region = new CellRangeAddressList(0, 0, 0, 0); var validation = dvHelper.CreateValidation(constraint, region); sheet.AddValidationData(validation);
6.11. Reading pictures:
If the Excel worksheet contains pictures, you can also use NPOI to read these pictures:
var pictures = sheet.GetPictures();
foreach (var picture in pictures)
{<!-- -->
var pictureIndex = picture.PictureIndex;
var pictureData = workbook.GetPictureData(pictureIndex);
// You can convert pictureData to image and save or display
}
These examples demonstrate some of the advanced features of the NPOI library. Depending on your needs, you may also want to explore more features and details. Please note that as the Excel format is updated and changed, the NPOI library may also be updated, so it is recommended to regularly check NPOI’s official documentation and source code repository to ensure that you are using the latest and most stable functionality.
7. Call it a day.