316 lines
11 KiB
C#
316 lines
11 KiB
C#
using System;
|
|
using NPOI.SS.UserModel;
|
|
using NPOI.SS.Util;
|
|
using NPOI.HSSF.UserModel;
|
|
using NPOI.HSSF.Util;
|
|
using System.IO;
|
|
|
|
public class Excel
|
|
{
|
|
public HSSFWorkbook WorkBook
|
|
{
|
|
get;
|
|
set;
|
|
}
|
|
|
|
public HSSFSheet Sheet
|
|
{
|
|
get;
|
|
set;
|
|
}
|
|
|
|
/// <summary>
|
|
/// 建立或取得指定行列的儲存格
|
|
/// </summary>
|
|
/// <param name="row">列</param>
|
|
/// <param name="col">行</param>
|
|
/// <returns></returns>
|
|
public ICell Cell(int row, int col)
|
|
{
|
|
if (Sheet.GetRow(row) == null)
|
|
Sheet.CreateRow(row);
|
|
if (Sheet.GetRow(row).GetCell(col) == null)
|
|
Sheet.GetRow(row).CreateCell(col);
|
|
return Sheet.GetRow(row).GetCell(col);
|
|
}
|
|
|
|
public string RCtoA1(int row, int col)
|
|
{
|
|
string colX;
|
|
|
|
if (col <= 25)
|
|
colX = Char.ConvertFromUtf32(65 + col);
|
|
else
|
|
{
|
|
col = col - 26;
|
|
colX = Char.ConvertFromUtf32(65 + (int)(col / 26)) + Char.ConvertFromUtf32(65 + (col % 26));
|
|
}
|
|
|
|
return String.Format("{0}{1}", colX, row + 1);
|
|
}
|
|
|
|
/// <summary>
|
|
/// 建立新字體
|
|
/// </summary>
|
|
public IFont NewFont(string fontName)
|
|
{
|
|
return NewFont(fontName, 9, FontBoldWeight.Normal, HSSFColor.COLOR_NORMAL);
|
|
}
|
|
|
|
/// <summary>
|
|
/// 建立新字體
|
|
/// </summary>
|
|
public IFont NewFont(string fontName, short fontSize)
|
|
{
|
|
return NewFont(fontName, fontSize, FontBoldWeight.Normal, HSSFColor.COLOR_NORMAL);
|
|
}
|
|
|
|
/// <summary>
|
|
/// 建立新字體
|
|
/// </summary>
|
|
public IFont NewFont(string fontName, short fontSize, FontBoldWeight fontWeight)
|
|
{
|
|
return NewFont(fontName, fontSize, fontWeight, HSSFColor.COLOR_NORMAL);
|
|
}
|
|
|
|
/// <summary>
|
|
/// 建立新字體
|
|
/// </summary>
|
|
public IFont NewFont(string fontName, short fontSize, FontBoldWeight fontWeight, short color)
|
|
{
|
|
IFont result = WorkBook.CreateFont(); // 抬頭字體
|
|
result.FontName = fontName;
|
|
result.FontHeightInPoints = fontSize;
|
|
result.Boldweight = (short)fontWeight;
|
|
result.Color = color;
|
|
return result;
|
|
}
|
|
|
|
/// <summary>
|
|
/// 建立新樣式
|
|
/// </summary>
|
|
public ICellStyle NewCellStyle(IFont font, HorizontalAlignment align, VerticalAlignment valign, bool warpText)
|
|
{
|
|
ICellStyle style = WorkBook.CreateCellStyle();
|
|
style.SetFont(font);
|
|
style.Alignment = align;
|
|
style.VerticalAlignment = valign;
|
|
style.WrapText = warpText;
|
|
return style;
|
|
}
|
|
|
|
/// <summary>
|
|
/// 合併儲存格
|
|
/// </summary>
|
|
public void MergeCell(int row1, int col1, int row2, int col2)
|
|
{
|
|
// 建立範圍內所有儲存格
|
|
for (int row = row1; row <= row2; row++)
|
|
for (int col = col1; col <= col2; col++)
|
|
Cell(row, col);
|
|
|
|
CellRangeAddress region = new CellRangeAddress(row1, row2, col1, col2); // Region.ConvertToCellRangeAddress(new Region(row1, col1, row2, col2));
|
|
Sheet.AddMergedRegion(region);
|
|
}
|
|
|
|
/// <summary>
|
|
/// 合併儲存格，並設定合併後的邊框樣式
|
|
/// </summary>
|
|
public void MergeCell(int row1, int col1, int row2, int col2, BorderStyle bordertype)
|
|
{
|
|
// 建立範圍內所有儲存格
|
|
for (int row = row1; row <= row2; row++)
|
|
for (int col = col1; col <= col2; col++)
|
|
Cell(row, col);
|
|
|
|
CellRangeAddress region = new CellRangeAddress(row1, row2, col1, col2); //Region.ConvertToCellRangeAddress(new Region(row1, col1, row2, col2));
|
|
Sheet.AddMergedRegion(region);
|
|
SetRangeOuterBorder(region, bordertype);
|
|
}
|
|
|
|
/// <summary>
|
|
/// 設定單一儲存格的邊框樣式
|
|
/// </summary>
|
|
public void SetCellBorder(int row, int col, BorderStyle bordertype)
|
|
{
|
|
CellRangeAddress region = new CellRangeAddress(row, row, col, col);
|
|
HSSFRegionUtil.SetBorderTop(bordertype, region, Sheet as HSSFSheet, WorkBook);
|
|
HSSFRegionUtil.SetBorderLeft(bordertype, region, Sheet as HSSFSheet, WorkBook);
|
|
HSSFRegionUtil.SetBorderBottom(bordertype, region, Sheet as HSSFSheet, WorkBook);
|
|
HSSFRegionUtil.SetBorderRight(bordertype, region, Sheet as HSSFSheet, WorkBook);
|
|
}
|
|
|
|
/// <summary>
|
|
/// 設定一個儲存格範圍的外圍邊框樣式
|
|
/// </summary>
|
|
public void SetRangeOuterBorder(int row1, int col1, int row2, int col2, BorderStyle bordertype)
|
|
{
|
|
CellRangeAddress region = new CellRangeAddress(row1, row2, col1, col2);
|
|
HSSFRegionUtil.SetBorderTop(bordertype, region, Sheet as HSSFSheet, WorkBook);
|
|
HSSFRegionUtil.SetBorderLeft(bordertype, region, Sheet as HSSFSheet, WorkBook);
|
|
HSSFRegionUtil.SetBorderBottom(bordertype, region, Sheet as HSSFSheet, WorkBook);
|
|
HSSFRegionUtil.SetBorderRight(bordertype, region, Sheet as HSSFSheet, WorkBook);
|
|
}
|
|
|
|
/// <summary>
|
|
/// 設定一個儲存格範圍的外圍邊框樣式
|
|
/// </summary>
|
|
public void SetRangeOuterBorder(int row1, int col1, int row2, int col2, BorderStyle bordertypeTop, BorderStyle bordertypeLeft, BorderStyle bordertypeBottom, BorderStyle bordertypeRight)
|
|
{
|
|
CellRangeAddress region = new CellRangeAddress(row1, row2, col1, col2);
|
|
HSSFRegionUtil.SetBorderTop(bordertypeTop, region, Sheet as HSSFSheet, WorkBook);
|
|
HSSFRegionUtil.SetBorderLeft(bordertypeLeft, region, Sheet as HSSFSheet, WorkBook);
|
|
HSSFRegionUtil.SetBorderBottom(bordertypeBottom, region, Sheet as HSSFSheet, WorkBook);
|
|
HSSFRegionUtil.SetBorderRight(bordertypeRight, region, Sheet as HSSFSheet, WorkBook);
|
|
}
|
|
|
|
/// <summary>
|
|
/// 設定一個儲存格範圍的外圍邊框樣式
|
|
/// </summary>
|
|
public void SetRangeOuterBorder(CellRangeAddress region, BorderStyle bordertype)
|
|
{
|
|
HSSFRegionUtil.SetBorderTop(bordertype, region, Sheet as HSSFSheet, WorkBook);
|
|
HSSFRegionUtil.SetBorderLeft(bordertype, region, Sheet as HSSFSheet, WorkBook);
|
|
HSSFRegionUtil.SetBorderBottom(bordertype, region, Sheet as HSSFSheet, WorkBook);
|
|
HSSFRegionUtil.SetBorderRight(bordertype, region, Sheet as HSSFSheet, WorkBook);
|
|
}
|
|
|
|
/// <summary>
|
|
/// 設定一個儲存格範圍的外圍邊框樣式
|
|
/// </summary>
|
|
public void SetRangeOuterBorder(CellRangeAddress region, BorderStyle bordertypeTop, BorderStyle bordertypeLeft, BorderStyle bordertypeBottom, BorderStyle bordertypeRight)
|
|
{
|
|
HSSFRegionUtil.SetBorderTop(bordertypeTop, region, Sheet as HSSFSheet, WorkBook);
|
|
HSSFRegionUtil.SetBorderLeft(bordertypeLeft, region, Sheet as HSSFSheet, WorkBook);
|
|
HSSFRegionUtil.SetBorderBottom(bordertypeBottom, region, Sheet as HSSFSheet, WorkBook);
|
|
HSSFRegionUtil.SetBorderRight(bordertypeRight, region, Sheet as HSSFSheet, WorkBook);
|
|
}
|
|
|
|
/// <summary>
|
|
/// 設定一個儲存格範圍的內部格線樣式 (慢，建議僅使用於小範圍！)
|
|
/// </summary>
|
|
public void SetRangeInnerGrid(int row1, int col1, int row2, int col2, BorderStyle bordertype)
|
|
{
|
|
/*
|
|
CellRangeAddress region = new CellRangeAddress(row1, row2, col1, col2);
|
|
Sheet.SetEnclosedBorderOfRegion(region, bordertype, HSSFColor.COLOR_NORMAL);
|
|
*/
|
|
|
|
for (int col = col1; col <= col2; col++)
|
|
{
|
|
SetRangeOuterBorder(row1, col, row2, col, bordertype);
|
|
}
|
|
for (int row = row1; row <= row2; row++)
|
|
{
|
|
SetRangeOuterBorder(row, col1, row, col2, bordertype);
|
|
}
|
|
}
|
|
|
|
public void SetCellStyle(int row, int col, ICellStyle style)
|
|
{
|
|
Cell(row, col).CellStyle = style;
|
|
}
|
|
|
|
public void SetCellValue(int row, int col, bool value, ICellStyle style)
|
|
{
|
|
Cell(row, col).SetCellValue(value);
|
|
Cell(row, col).CellStyle = style;
|
|
}
|
|
|
|
public void SetCellValue(int row, int col, DateTime value, ICellStyle style)
|
|
{
|
|
Cell(row, col).SetCellValue(value);
|
|
Cell(row, col).CellStyle = style;
|
|
}
|
|
|
|
public void SetCellValue(int row, int col, double value, ICellStyle style)
|
|
{
|
|
Cell(row, col).SetCellValue(value);
|
|
Cell(row, col).CellStyle = style;
|
|
}
|
|
|
|
public void SetCellValue(int row, int col, IRichTextString value, ICellStyle style)
|
|
{
|
|
Cell(row, col).SetCellValue(value);
|
|
Cell(row, col).CellStyle = style;
|
|
}
|
|
|
|
public void SetCellValue(int row, int col, string value, ICellStyle style)
|
|
{
|
|
Cell(row, col).SetCellValue(value);
|
|
Cell(row, col).CellStyle = style;
|
|
}
|
|
|
|
public void SetCellValue(int row, int col, bool value)
|
|
{
|
|
Cell(row, col).SetCellValue(value);
|
|
}
|
|
|
|
public void SetCellValue(int row, int col, DateTime value)
|
|
{
|
|
Cell(row, col).SetCellValue(value);
|
|
}
|
|
|
|
public void SetCellValue(int row, int col, double value)
|
|
{
|
|
Cell(row, col).SetCellValue(value);
|
|
}
|
|
|
|
public void SetCellValue(int row, int col, IRichTextString value)
|
|
{
|
|
Cell(row, col).SetCellValue(value);
|
|
}
|
|
|
|
public void SetCellValue(int row, int col, string value)
|
|
{
|
|
Cell(row, col).SetCellValue(value);
|
|
}
|
|
|
|
public void SetCellFormula(int row, int col, string formula)
|
|
{
|
|
Cell(row, col).CellFormula = formula;
|
|
}
|
|
|
|
public void SetCellFormula(int row, int col, string formula, ICellStyle style)
|
|
{
|
|
Cell(row, col).CellFormula = formula;
|
|
Cell(row, col).CellStyle = style;
|
|
}
|
|
|
|
private HSSFPatriarch patriarch = null;
|
|
public void DrawLine(int col1, int row1, int dx1, int dy1, int col2, int row2, int dx2, int dy2, LineStyle lineStyle, int lineWidth)
|
|
{
|
|
if (patriarch == null)
|
|
patriarch = Sheet.CreateDrawingPatriarch() as HSSFPatriarch;
|
|
|
|
HSSFClientAnchor a = new HSSFClientAnchor(dx1, dy1, dx2, dy2, col1, row1, col2, row2);
|
|
HSSFSimpleShape s = patriarch.CreateSimpleShape(a);
|
|
s.ShapeType = HSSFSimpleShape.OBJECT_TYPE_LINE; // 設定線條類型為線條
|
|
s.LineStyle = lineStyle; // 設定為實線
|
|
s.LineWidth = lineWidth;
|
|
}
|
|
|
|
public void DrawPicture(int col1, int row1, int dx1, int dy1, int col2, int row2, int dx2, int dy2, string pictureFileName)
|
|
{
|
|
if (patriarch == null)
|
|
patriarch = Sheet.CreateDrawingPatriarch() as HSSFPatriarch;
|
|
|
|
byte[] picture = File.ReadAllBytes(pictureFileName);
|
|
int pictureIdx = WorkBook.AddPicture(picture, PictureType.JPEG);
|
|
|
|
HSSFClientAnchor a = new HSSFClientAnchor(dx1, dy1, dx2, dy2, col1, row1, col2, row2);
|
|
HSSFPicture p = patriarch.CreatePicture(a, pictureIdx) as HSSFPicture;
|
|
//p.Resize();
|
|
}
|
|
|
|
public void SetColumnWidth(int column, int width)
|
|
{
|
|
Sheet.SetColumnWidth(column, width * 256 + 182);
|
|
}
|
|
|
|
public void SetRowHeight(int row, int height)
|
|
{
|
|
Sheet.GetRow(row).HeightInPoints = height;
|
|
}
|
|
}
|