public class SheetHelper
{
private Xls.Worksheet sheet;
private int startRowIndex;
private int startColIndex;
public SheetHelper(Xls.Worksheet sheet)
: this(sheet, 0, 0){
}
public SheetHelper(Xls.Worksheet xlsSheet, int startRowIndex, int startColIndex)
{
this.sheet = xlsSheet;
this.startRowIndex = startRowIndex;
this.startColIndex = startColIndex;
}
#region 行列操作
public void CopyRow(int r1, int r2)
{
Xls.Range rg1 = sheet.Rows[r1 + startRowIndex, Missing.Value];
Xls.Range rg2 = sheet.Rows[r2 + startRowIndex, Missing.Value];
rg1.EntireRow.Copy(rg2.EntireRow);
}
public void CopyCol(int c1, int c2)
{
Xls.Range rg1 = sheet.Cells[Missing.Value, c1 + startColIndex];
Xls.Range rg2 = sheet.Cells[Missing.Value, c2 + startColIndex];
rg1.EntireColumn.Copy(rg2.EntireColumn);
}
public void DeleteRow(int rowIndex, int amount)
{
for (int i = 0; i < amount; i++)
{
Xls.Range rg = sheet.Rows[rowIndex + startRowIndex, Missing.Value];
rg.EntireRow.Delete();
}
}
public void DeleteCol(int colIndex, int amount)
{
for (int i = 0; i < amount; i++)
{
Xls.Range rg = sheet.Cells[Missing.Value, colIndex + startColIndex];
rg.EntireColumn.Delete();
}
}
public void InsertRow(int rowIndex, int amount)
{
InsertRow(rowIndex, amount, false);
}
public void InsertRow(int rowIndex, int amount, bool isCopy)
{
Xls.Range rg = sheet.Rows[rowIndex + startRowIndex, Missing.Value];
for (int i = 0; i < amount; i++)
{
rg.EntireRow.Insert();
if (isCopy)
{
CopyRow(rowIndex + i + 1, rowIndex + i);
}
}
}
public void InsertCol(int colIndex, int amount)
{
InsertCol(colIndex, amount, false);
}
public void InsertCol(int colIndex, int amount, bool isCopy)
{
Xls.Range rg = sheet.Cells[Missing.Value, colIndex + startColIndex];
for (int i = 0; i < amount; i++)
{
rg.EntireColumn.Insert();
if (isCopy)
{
CopyCol(colIndex + i + 1, colIndex + i);
}
}
}
public void SetRowHeight(int rowIndex, float height)
{
Xls.Range rg = sheet.Rows[rowIndex + startRowIndex, Missing.Value];
rg.RowHeight = height;
}
public void SetColWidth(int colIndex, float width)
{
Xls.Range rg = sheet.Cells[Missing.Value, colIndex + startColIndex];
rg.ColumnWidth = width;
}
public void AutoFitRow(int rowIndex)
{
Xls.Range rg = sheet.Rows[rowIndex + startRowIndex, Missing.Value];
rg.EntireRow.AutoFit();
}
public void AutoFitCol(int colIndex)
{
Xls.Range rg = sheet.Cells[Missing.Value, colIndex + startColIndex];
rg.EntireColumn.AutoFit();
}
public static string GetColName(int colIndex)
{
if (colIndex < 1) colIndex = 1;
else if (colIndex > 256) colIndex = 256;
colIndex -= 1; // 十进制数是从0开始索引的
int i = colIndex / 26;
int j = colIndex % 26;
if (i == 0)
{
char c = (char)(j + 65);
return c.ToString();
}
else
{
char c1 = (char)(j + 65);
char c2 = (char)(i + 64);
return c2.ToString() + c1.ToString();
}
}
#endregion
#region 单元格操作
// 获取单个单元格区域对象
public Xls.Range GetRange(int rowIndex, int colIndex)
{
return sheet.Cells[rowIndex + startRowIndex, colIndex + startColIndex];
}
// 获取单元格区域
public Xls.Range GetRange(int r1, int c1, int r2, int c2)
{
string colName1 = GetColName(c1 + startColIndex);
string colName2 = GetColName(c2 + startColIndex);
string cell1 = colName1 + (r1 + startRowIndex).ToString();
string cell2 = colName2 + (r2 + startRowIndex).ToString();
return sheet.get_Range(cell1, cell2);
}
// 给单元格填充内容
public void SetValue(int r, int c, object value)
{
sheet.Cells[r + startRowIndex, c + startColIndex].Value = value;
}
// 给单元格插入缩进
public void InsertIndent(int rowIndex, int colIndex, int amount)
{
Xls.Range rg = sheet.Cells[rowIndex + startRowIndex, colIndex + startColIndex];
rg.InsertIndent(amount);
}
// 单元格合并
public void Marge(int r1, int c1, int r2, int c2)
{
Xls.Range rg = GetRange(r1, c1, r2, c2);
rg.Merge();
}
// 单元格合并及居中
public void MargeToCenter(int r1, int c1, int r2, int c2)
{
Xls.Range rg = GetRange(r1, c1, r2, c2);
rg.Merge();
SetHAlign(rg, Xls.XlHAlign.xlHAlignCenter);
SetVAlign(rg, Xls.XlVAlign.xlVAlignCenter);
}
// 单元格合并解除
public void UnMarge(int r1, int c1, int r2, int c2)
{
Xls.Range rg = GetRange(r1, c1, r2, c2);
rg.UnMerge();
}
#endregion
#region 样式设置
// 设置单元格边框
public void SetBorders(Xls.Range rg, Color color)
{
SetBorders(rg, color, Xls.XlLineStyle.xlContinuous, 1);
}
public void SetBorders(Xls.Range rg, Color color, Xls.XlLineStyle lineStyle, decimal weight)
{
rg.Borders.Color = color;
rg.Borders.Weight = weight;
rg.Borders.LineStyle = lineStyle;
}
public void SetBorders(Xls.Range rg, Color color, Xls.XlLineStyle lineStyle, decimal weight, Xls.XlBordersIndex bordersIndex)
{
rg.Borders.Item[bordersIndex].Color = color;
rg.Borders.Item[bordersIndex].Weight = weight;
rg.Borders.Item[bordersIndex].LineStyle = lineStyle;
}
public void SetBordersToInside(Xls.Range rg, Color color, Xls.XlLineStyle lineStyle, decimal weight)
{
SetBorders(rg, color, lineStyle, weight, Xls.XlBordersIndex.xlInsideHorizontal);
SetBorders(rg, color, lineStyle, weight, Xls.XlBordersIndex.xlInsideVertical);
}
public void SetBordersToOutside(Xls.Range rg, Color color, Xls.XlLineStyle lineStyle, decimal weight)
{
SetBorders(rg, color, lineStyle, weight, Xls.XlBordersIndex.xlEdgeBottom);
SetBorders(rg, color, lineStyle, weight, Xls.XlBordersIndex.xlEdgeLeft);
SetBorders(rg, color, lineStyle, weight, Xls.XlBordersIndex.xlEdgeRight);
SetBorders(rg, color, lineStyle, weight, Xls.XlBordersIndex.xlEdgeTop);
}
public void SetBordersToDiagonal(Xls.Range rg, Color color, Xls.XlLineStyle lineStyle, decimal weight)
{
SetBorders(rg, color, lineStyle, weight, Xls.XlBordersIndex.xlDiagonalDown);
SetBorders(rg, color, lineStyle, weight, Xls.XlBordersIndex.xlDiagonalUp);
}
// 设置区域背景色
public void SetBackColor(Xls.Range rg, Color color)
{
rg.Interior.Color = color;
}
// 设置区域字体
public void SetFont(Xls.Range rg, float size, bool bold, bool underline)
{
SetFont(rg, size, bold, underline, SystemColors.WindowText);
}
// 设置区域字体
public void SetFont(Xls.Range rg, float size, bool bold, bool underline, Color color)
{
rg.Font.Size = size;
rg.Font.Bold = bold;
rg.Font.Underline = underline;
rg.Font.Color = color;
}
// 设置文本水平对齐方式
public void SetHAlign(Xls.Range rg, Xls.XlHAlign align)
{
rg.HorizontalAlignment = align;
}
// 设置文本垂直对齐方式
public void SetVAlign(Xls.Range rg, Xls.XlVAlign align)
{
rg.VerticalAlignment = align;
}
// 设置自动换行
public void SetWrapText(Xls.Range rg, bool wrapText)
{
rg.WrapText = wrapText;
}
#endregion
/// <summary>
/// 将DataTable中的数据导出到当前Sheet中
/// </summary>
/// <param name="dt">数据表</param>
/// <param name="startRowIndex">起始行号</param>
/// <param name="startColIndex">起始列号</param>
public void Export(DataTable dt)
{
if (startRowIndex < 1) startRowIndex = 1;
if (startColIndex < 1) startColIndex = 1;
for (int i = 0; i < dt.Columns.Count; i++)
{
string colName = string.IsNullOrEmpty(dt.Columns[i].Caption) ?
dt.Columns[i].ColumnName : dt.Columns[i].Caption;
SetValue(startRowIndex, i + startColIndex, colName);
}
for (int i = 0; i < dt.Rows.Count; i++)
{
DataRow r = dt.Rows[i];
for (int j = 0; j < dt.Columns.Count; j++)
{
SetValue(i + startRowIndex + 1, j + startColIndex, r[j]);
}
}
}
public void Export(DataGridView dgv)
{
if (startRowIndex < 1) startRowIndex = 1;
if (startColIndex < 1) startColIndex = 1;
for (int i = 0; i < dgv.Columns.Count; i++)
{
SetValue(startRowIndex, i + startColIndex, dgv.Columns[i].HeaderText);
SetColWidth(i + startColIndex, (float)(dgv.Columns[i].Width / 6.5));
}
for (int i = 0; i < dgv.Rows.Count; i++)
{
DataGridViewRow r = dgv.Rows[i];
for (int j = 0; j < dgv.Columns.Count; j++)
{
SetValue(i + startRowIndex + 1, j + startColIndex, r.Cells[j].Value);
}
}
}
/// <summary>
/// 打印页面设置
/// </summary>
/// <param name="centerHeader">页面头部标题,可以包含字体信息,如:&"黑体"&18 发票联</param>
/// <param name="paperSize">纸张大小</param>
/// <param name="pageOrientation">打印方向(横向或纵向)</param>
/// <param name="printGridlines">是否打印网格线</param>
public void PrintSetUp(string centerHeader, Xls.XlPaperSize paperSize, Xls.XlPageOrientation pageOrientation, bool printGridlines)
{
sheet.PageSetup.CenterHeader = centerHeader; // "&\"黑体\"&18 发票联";
sheet.PageSetup.CenterFooter = "第 &p 页";
sheet.PageSetup.PrintGridlines = printGridlines;
sheet.PageSetup.CenterHorizontally = true;
sheet.PageSetup.LeftMargin = sheet.Application.InchesToPoints(0.5);
sheet.PageSetup.RightMargin = sheet.Application.InchesToPoints(0.5);
sheet.PageSetup.TopMargin = sheet.Application.InchesToPoints(1.3);
sheet.PageSetup.HeaderMargin = sheet.Application.InchesToPoints(0.9);
sheet.PageSetup.BottomMargin = sheet.Application.InchesToPoints(1.0);
sheet.PageSetup.FooterMargin = sheet.Application.InchesToPoints(0.6);
sheet.PageSetup.PaperSize = paperSize;
sheet.PageSetup.Orientation = pageOrientation;
}
}