There are many ways to operate Excel using C#. In the past, Office was required to be installed on the computer to use it, but the company did not allow the installation of Office due to copyright issues. Therefore, NPOI is used for Excel operations. Basically, there is no problem with some simple Excel operations, including reading, writing and merging cells.
NPOI library download address
NPOI Baidu Cloud download: https://pan.baidu.com/s/13wiYmZ2txCq79CR-kj9Cvg Extraction code: 5x1v
Namespaces:
using NPOI; using NPOI.XSSF.UserModel; using NPOI.SS.UserModel; using NPOI.HSSF.UserModel; using NPOI.HSSF.Util;
Simply save data:
public void ExcelTest(string path)
{
IWorkbook workbook = new HSSFWorkbook();//Create Workbook
workbook.CreateSheet("sheet1");//Create sheet
using (FileStream fs = File.Create(path))//path=mmm.xls;
{
ISheet sheet = workbook.GetSheetAt(0);//Get sheet
sheet.CreateRow(1).CreateCell(0).SetCellValue("nami");//Create the first row/Create the first cell/Set the content of the first cell [can be created separately, but must be created first row to create a cell, otherwise an error will be reported]
sheet.GetRow(1).CreateCell(1).SetCellValue("robin");//Get the first row/Create the second cell/Set the content of the second cell
sheet.CreateRow(2).CreateCell(0).SetCellValue("saber");//Create the second row/Create the first cell/Set the content of the first cell
sheet.GetRow(2).CreateCell(1).SetCellValue("luffy");//Get the second row/Create the second cell/Set the content of the second cell
sheet.GetRow(1).CreateCell(2).SetCellValue(5);
sheet.GetRow(2).CreateCell(2).SetCellValue(2);
//Add comments
IDrawing draw = sheet.CreateDrawingPatriarch();
IComment comment = draw.CreateCellComment(new HSSFClientAnchor(0, 0, 0, 0, 1, 2, 4, 4));//The parameters inside should indicate the position and size of the comment.
comment.String = new HSSFRichTextString("one-piece");//Add comment content
comment.Author = "Mengqi Xiaosheng";//Add comment author
sheet.GetRow(1).GetCell(1).CellComment = comment;//Give the previously set comment to a cell
//Cell format settings
ICellStyle cellStyle = workbook.CreateCellStyle();
IDataFormat format = workbook.CreateDataFormat();
cellStyle.DataFormat = format.GetFormat("0.00");
sheet.GetRow(2).GetCell(2).CellStyle = cellStyle;
//Merge Cells
sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(0, 0, 0, 2));
sheet.CreateRow(0).CreateCell(0).SetCellValue("Mengqi Xiaosheng");
ICellStyle titleStyle = workbook.CreateCellStyle();
IFont titleFont = workbook.CreateFont();
titleFont.FontHeightInPoints = 15; //Set font size
titleFont.Color = HSSFColor.BLUE.index;//Set font color
titleStyle.SetFont(titleFont);
titleStyle.Alignment = HorizontalAlignment.CENTER;//Centered
sheet.GetRow(0).GetCell(0).CellStyle = titleStyle;
ICellStyle style = workbook.CreateCellStyle();
style.BorderBottom = BorderStyle.THIN;
style.BorderLeft = BorderStyle.THIN;
style.BorderRight = BorderStyle.THIN;
style.BorderTop = BorderStyle.THIN;
sheet.GetRow(1).GetCell(1).CellStyle = style;
//Insert picture
HSSFClientAnchor anchor2 = new HSSFClientAnchor(0, 0, 0, 0, 0, 5, 6, 10);
byte[] bytes = System.IO.File.ReadAllBytes(@"C:\Users\Administrator\Desktop\image\mqxs.png");
int picID = workbook.AddPicture(bytes, PictureType.PNG);
IPicture pic = patriarch.CreatePicture(anchor2, picID);
pic.Resize();
workbook.Write(fs);//Save the file
}
}
Reading Excel returns DataTable:
/// <summary>
/// Read Excel[.xls] (return DataTable)
/// </summary>
/// <param name="path">Excel path</param>
/// <returns></returns>
public static DataTable ReadExcel(string path)
{
try
{
DataTable dt = new DataTable();
using (FileStream fs = new FileStream(path, FileMode.Open))
{
IWorkbook workbook = new HSSFWorkbook(fs);
ISheet sheet = workbook.GetSheetAt(0);
int rfirst = sheet.FirstRowNum;
int rlast = sheet.LastRowNum;
IRow row = sheet.GetRow(rfirst);
int cfirst = row.FirstCellNum;
int clast = row.LastCellNum;
for (int i = cfirst; i < clast; i + + )
{
if (row.GetCell(i) != null)
dt.Columns.Add(row.GetCell(i).StringCellValue, System.Type.GetType("System.String"));
}
row = null;
for (int i = rfirst + 1; i <= rlast; i + + )
{
DataRow r = dt.NewRow();
IRow ir = sheet.GetRow(i);
for (int j = cfirst; j < clast; j + + )
{
if (ir.GetCell(j) != null)
{
r[j] = ir.GetCell(j).ToString();
}
}
dt.Rows.Add(r);
ir = null;
r = null;
}
sheet = null;
workbook = null;
}
return dt;
}
catch
{
System.Windows.Forms.MessageBox.Show("Excel format error or Excel is being accessed by another process");
return null;
}
}
Ok, NPOI has been used for a while…
Let’s talk about some experience… Regarding NPOI’s cell styles, the number of CellStyles is limited to 4000, so when you set the cell style, try not to define it inside the for loop. You can define it outside the for loop.. .Reduce the number of CellStyles, Ok, the main thing I want to talk about is the following one….
((HSSFSheet)sheet).SetEnclosedBorderOfRegion(new NPOI.SS.Util.CellRangeAddress(0, 50, 0, 100), BorderStyle.MEDIUM, HSSFColor.BLACK.index);
The function of this sentence is to add an outer border to the merged cells.
For example, this statement means to draw an overall outer border from cells 0 rows and 0 columns to 50 rows and 100 columns. It’s easy to use… but it takes up a lot of resources. I don’t know why just one sentence like this takes up several CellStyles… and it’s slow…
Therefore, this function can be used when the amount of data is small, and the problem is not big, and the basic impact on speed is not big. However, if the amount of data is large… this will slow down the speed and take up too much resources.
I have suffered a loss here. It is this statement that causes the number of CellStyles to be insufficient. [Of course, it is not a problem with just one sentence… Because many places merge cells and add outer borders… In the end, it is a tragedy. ….]….Xiaosheng thinks this is a flaw of NPOI…
Instructions for using Excel for NPOI operations:
The official website tutorial is quite detailed. If you need further study, please go to the official website to learn http://tonyqus.sinaapp.com/npoi2tutorial
2017/05/05 added:
C# Color to NPOI color:
/// <summary>
/// Get the color value
/// </summary>
/// <param name="color">Color RGB</param>
/// <param name="workbook">Excel canvas</param>
/// <returns></returns>
public static short GetColorIndex(this HSSFWorkbook workbook,Color color)
{
HSSFPalette palette = workbook.GetCustomPalette();
var v = palette.FindSimilarColor(color.R, color.G, color.B);
if (v == null)
{
throw new Exception("Color is not in Palette");
}
else return v.GetIndex();
}
2018/4/8 added:
Get merged cells
/// <summary>
/// Get the position of the merged cell where the current cell is located
/// </summary>
/// <param name="sheet">sheet form</param>
/// <param name="rowIndex">Row index starts from 0</param>
/// <param name="colIndex">Column index starts from 0</param>
/// <param name="start">Merge the upper left corner coordinates of cells</param>
/// <param name="end">Coordinates of the lower right corner of merged cells</param>
/// <returns>Return false to indicate non-merged cells</returns>
private static bool IsMergeCell(ISheet sheet, int rowIndex, int colIndex, out Point start, out Point end)
{
bool result = false;
start = new Point(0, 0);
end = new Point(0, 0);
if ((rowIndex < 0) || (colIndex < 0)) return result;
int regionsCount = sheet.NumMergedRegions;
for (int i = 0; i < regionsCount; i + + )
{
CellRangeAddress range = sheet.GetMergedRegion(i);
//sheet.IsMergedRegion(range);
if (rowIndex >= range.FirstRow & amp; & amp; rowIndex <= range.LastRow & amp; & amp; colIndex >= range.FirstColumn & amp; & amp; colIndex <= range.LastColumn)
{
start = new Point(range.FirstRow, range.FirstColumn);
end = new Point(range.LastRow, range.LastColumn);
result = true;
break;
}
}
return result;
}
2020-09-29 Supplement: (Why do I still add after so many years… Because I used it again when I changed companies, so I will add)
Export DataTable to Excel:
/// <summary>
/// Create Excel
/// </summary>
/// <param name="savefile">Save path</param>
/// <param name="title">Table title</param>
/// <param name="dt">Table data</param>
/// <returns></returns>
public static bool CreateExcel(string savefile, string title, DataTable dt)
{
try
{
if (dt.Columns.Count > 255)
{
throw new Exception("Table data column exceeds the maximum value of 255");
}
IWorkbook workbook = new HSSFWorkbook();
ISheet sheet = workbook.CreateSheet("sheet1");
using (FileStream fs = File.Create(savefile))
{
int dtRowCount = dt.Rows.Count;
int dtcolumnCount = dt.Columns.Count;
#region[table style]
ICellStyle TitleStyle = MCellStyle.GetCellStyle(workbook, -1, "", 15, true, HSSFColor.Black.Index, HorizontalAlignment.Center, VerticalAlignment.Center, HSSFColor.Grey25Percent.Index);
ICellStyle columnStyle = MCellStyle.GetCellStyle(workbook, -1, "", 11, true, HSSFColor.Black.Index, HorizontalAlignment.Center, VerticalAlignment.Center, HSSFColor.Grey25Percent.Index);
ICellStyle limeStyle = MCellStyle.GetCellStyle(workbook, -1, "", 11, false, HSSFColor.Black.Index, HorizontalAlignment.Center, VerticalAlignment.Center, HSSFColor.White.Index);
ICellStyle roseStyle = MCellStyle.GetCellStyle(workbook, -1, "", 11, false, HSSFColor.Black.Index, HorizontalAlignment.Center, VerticalAlignment.Center, HSSFColor.White.Index);
#endregion
#region[set title]
sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(0, 1, 0, dtcolumnCount - 1));
sheet.CreateRow(0); sheet.CreateRow(1);
sheet.GetRow(0).CreateCell(0).SetCellValue(title);
sheet.GetRow(0).GetCell(0).CellStyle = TitleStyle;
#endregion
#region[Set header]
IRow row = sheet.CreateRow(2);
for (int j = 0; j < dtcolumnCount; j + + )
{
row.CreateCell(j).SetCellValue(dt.Columns[j].ToString());
row.GetCell(j).CellStyle = columnStyle;
sheet.SetColumnWidth(j, 20 * 256);
}
row = null;
#endregion
#region[setting data]
for (int i = 0; i < dtRowCount; i + + )
{
IRow rows = sheet.CreateRow(i + 3);
for (int j = 0; j < dtcolumnCount; j + + )
{
rows.CreateCell(j).SetCellValue(dt.Rows[i][j].ToString());
if (i % 2 == 0)
rows.GetCell(j).CellStyle = limeStyle;
else
rows.GetCell(j).CellStyle = roseStyle;
}
rows = null;
}
#endregion
#region[Set main border]
sheet.GetRow(0).CreateCell(dtcolumnCount - 1).CellStyle = TitleStyle;
sheet.GetRow(1).CreateCell(dtcolumnCount - 1).CellStyle = TitleStyle;
//((HSSFSheet)sheet).SetEnclosedBorderOfRegion(new NPOI.SS.Util.CellRangeAddress(0, dtRowCount, 0, dtcolumnCount - 1), BorderStyle.MEDIUM, HSSFColor.Black.Index);
#endregion
#region[Table style anti-setting]
TitleStyle = null;
columnStyle = null;
limeStyle = null;
roseStyle = null;
#endregion
workbook.Write(fs);
}
sheet = null;
workbook = null;
return true;
}
catch
{
return false;
}
}
Create a style: (2020-10-20Add the fill color of the style)
public static class MCellStyle
{
/// <summary>
/// Get the cell style of NPOI
/// </summary>
/// <param name="workbook">Table</param>
/// <param name="cellstyleindex">Cell number. The default is -1, which means the number is ignored</param>
/// <param name="fontname">Font name</param>
/// <param name="fontsize">Font size</param>
/// <param name="IsBold">Whether to bold</param>
/// <param name="fillforecolor">Fill color</param>
/// <param name="halignment">Horizontal alignment</param>
/// <param name="valignment">Vertical alignment</param>
/// <param name="fillbackcolor">Background color</param>
/// <param name="format">Number format"0.00"</param>
/// <returns>Return table cell style CellStyle</returns>
public static ICellStyle GetCellStyle(IWorkbook workbook, short cellstyleindex = -1, string fontname = "", short fontsize = 11, bool IsBold = false, short fillforecolor = HSSFColor.COLOR_NORMAL, HorizontalAlignment halignment = HorizontalAlignment.Left, VerticalAlignment valignment = VerticalAlignment .Center, short fillbackcolor = HSSFColor.COLOR_NORMAL, string format = "")
{
if (cellstyleindex != -1)
{
try { return workbook.GetCellStyleAt(cellstyleindex); }
catch {; }
}
if (cellStyle != null) { cellStyle = null; }
cellStyle = workbook.CreateCellStyle();
IFont font = workbook.CreateFont();
if (fillforecolor != HSSFColor.COLOR_NORMAL)
font.Color = fillforecolor;
else
font.Color = HSSFColor.Black.Index;
if (font size > 0)
font.FontHeightInPoints = fontsize;
if (fontname != "")
font.FontName = fontname;
if(IsBold)
font.Boldweight = (short)NPOI.SS.UserModel.FontBoldWeight.Bold;
cellStyle.SetFont(font);
if (fillbackcolor != HSSFColor.COLOR_NORMAL)
{
cellStyle.FillForegroundColor = fillbackcolor;
cellStyle.FillPattern = FillPattern.SolidForeground;
}
if (format != "")
{
IDataFormat dataformat = workbook.CreateDataFormat();
cellStyle.DataFormat = dataformat.GetFormat(format);
}
cellStyle.Alignment = halignment;
cellStyle.VerticalAlignment = valignment;
cellStyle.BorderLeft = BorderStyle.Thin;
cellStyle.BorderRight = BorderStyle.Thin;
cellStyle.BorderTop = BorderStyle.Thin;
cellStyle.BorderBottom = BorderStyle.Thin;
cellStyle.WrapText = true;
return cellStyle;
}
/// <summary>
/// Get the cell style of NPOI (fill color is accurate)
/// </summary>
/// <param name="workbook">Table</param>
/// <param name="fillforecolor">Fill color</param>
/// <param name="fillbackcolor">Background color</param>
/// <param name="cellstyleindex">Cell number. The default is -1, which means the number is ignored</param>
/// <param name="fontname">Font name</param>
/// <param name="fontsize">Font size</param>
/// <param name="IsBold">Whether to bold</param>
/// <param name="halignment">Horizontal alignment</param>
/// <param name="valignment">Vertical alignment</param>
/// <param name="format">Number format"0.00"</param>
/// <returns>Return table cell style CellStyle</returns>
public static ICellStyle GetCellStyle(XSSFWorkbook workbook, Color fillforecolor, Color fillbackcolor, short cellstyleindex = -1, string fontname = "", short fontsize = 11, bool IsBold = false, HorizontalAlignment halignment = HorizontalAlignment.Left, VerticalAlignment valignment = VerticalAlignment. Center, string format = "")
{
if (cellstyleindex != -1)
{
try { return workbook.GetCellStyleAt(cellstyleindex); }
catch {; }
}
if (xssfcellStyle != null) { xssfcellStyle = null; }
xssfcellStyle = workbook.CreateCellStyle() as XSSFCellStyle;
XSSFFont font = workbook.CreateFont() as XSSFFont;
font.SetColor(new XSSFColor(new byte[] { fillforecolor.R, fillforecolor.G, fillforecolor.B }));
if (font size > 0)
font.FontHeightInPoints = fontsize;
if (fontname != "")
font.FontName = fontname;
if(IsBold)
font.Boldweight = (short)NPOI.SS.UserModel.FontBoldWeight.Bold;
xssfcellStyle.SetFont(font);
xssfcellStyle.FillPattern = FillPattern.SolidForeground;
if (xssfcellStyle.FillForegroundColorColor == null)
xssfcellStyle.FillForegroundColorColor = new XSSFColor(new byte[] { fillbackcolor.R, fillbackcolor.G, fillbackcolor.B });
else
((XSSFColor)xssfcellStyle.FillForegroundColorColor).SetRgb(new byte[] { fillbackcolor.R, fillbackcolor.G, fillbackcolor.B });
if (format != "")
{
IDataFormat dataformat = workbook.CreateDataFormat();
xssfcellStyle.DataFormat = dataformat.GetFormat(format);
}
xssfcellStyle.Alignment = halignment;
xssfcellStyle.VerticalAlignment = valignment;
xssfcellStyle.BorderLeft = BorderStyle.Thin;
xssfcellStyle.BorderRight = BorderStyle.Thin;
xssfcellStyle.BorderTop = BorderStyle.Thin;
xssfcellStyle.BorderBottom = BorderStyle.Thin;
xssfcellStyle.WrapText = true;
return xssfcellStyle;
}
/// <summary>
/// Get the color value (not accurate)
/// </summary>
/// <param name="color">Color RGB</param>
/// <param name="workbook">Excel canvas</param>
/// <returns></returns>
public static short GetColorIndex(this HSSFWorkbook workbook, Color color)
{
HSSFPalette palette = workbook.GetCustomPalette();
var v = palette.FindSimilarColor(color.R, color.G, color.B);
if (v == null)
{
throw new Exception("Color is not in Palette");
}
else return v.Indexed;
}
private static ICellStyle cellStyle;
private static XSSFCellStyle xssfcellStyle;
}
Scan the QR code to follow the WeChat official account
