using System;
using System.Collections.Generic;// List
using System.Data;// DataTable
using System.Data.OleDb;// OleDbConnection,OleDbDataAdapter
using System.IO;// FileStream
using System.Text.RegularExpressions;// Regex
using System.Web;// HttpResponse,HttpUtility
using System.Web.UI;// HtmlTextWriter
using System.Web.UI.WebControls;// GridView
using System.Drawing;// Color
using NPOI.HSSF.UserModel;// HSSFWorkbook,HSSFSheet,HSSFRow,HSSFCell
using NPOI.SS.UserModel;// IWorkbook,ISheet,IRow,ICell
using NPOI.SS.Util;// CellRangeAddress
/*/--------------------------------------------------------------------------------//
// GetExcelNpoi 的摘要说明
// Ver 2.3.1.0125 for NPOI 2.0
//--------------------------------------------------------------------------------/*/
public class GetExcelNpoi : System.Web.UI.Page
{
// 构造函数
public GetExcelNpoi() { }
// GridView 导出 Excel
// 注意: 使用此函数要定义事件 public override void VerifyRenderingInServerForm(Control control){}
public void ExportExcel(HttpResponse response, GridView gridView, string fileName)
{
response.Clear();
response.Buffer = false;
response.Charset = "GB2312";
response.AppendHeader("Content-Disposition", "attachment;filename=" + fileName + ".xls");
response.ContentEncoding = System.Text.Encoding.GetEncoding("GB2312");
response.ContentType = "application/ms-excel";
response.Write("<meta http-equiv=Content-Type content=\"text/html; charset=GB2312\">");
EnableViewState = false;
System.IO.StringWriter oStringWriter = new System.IO.StringWriter();
HtmlTextWriter oHtmlTextWriter = new HtmlTextWriter(oStringWriter);
gridView.RenderControl(oHtmlTextWriter);
response.Write(oStringWriter.ToString());
response.End();
}
// 列名转索引
public static int ColumnNameToIndex(string columnName)
{
int index = 0;
if (!Regex.IsMatch(columnName, @"^[A-Za-z]+$"))
{
return -1;
}
char[] chars = columnName.ToUpper().ToCharArray();
for (int i = 0; i < chars.Length; i++)
{
index += ((int)chars[i] - (int)'A' + 1) * (int)Math.Pow(26, chars.Length - i - 1);
}
return index - 1;
}
// 列索引转名
public static string ColumnIndexToName(int columnIndex)
{
if (columnIndex < 0) return "";
List<string> chars = new List<string>();
do
{
if (chars.Count > 0) columnIndex--;
chars.Insert(0, ((char)(columnIndex % 26 + (int)'A')).ToString());
columnIndex = (int)((columnIndex - columnIndex % 26) / 26);
}
while (columnIndex > 0);
return String.Join(string.Empty, chars.ToArray());
}
// 颜色索引转颜色对象
public Color ColorIndexToObject(short index)
{
Color result = Color.Empty;
switch (index)
{
case 49:
result = Color.Aqua;
break;
case 8:
result = Color.Black;
break;
case 12:
result = Color.Blue;
break;
case 60:
result = Color.Brown;
break;
case 29:
result = Color.Coral;
break;
case 24:
result = Color.CornflowerBlue;
break;
case 18:
result = Color.DarkBlue;
break;
case 58:
result = Color.DarkGreen;
break;
case 16:
result = Color.DarkRed;
break;
case 51:
result = Color.Gold;
break;
case 17:
result = Color.Green;
break;
case 62:
result = Color.Indigo;
break;
case 46:
result = Color.Lavender;
break;
case 26:
result = Color.LemonChiffon;
break;
case 48:
result = Color.LightBlue;
break;
case 42:
result = Color.LightGreen;
break;
case 43:
result = Color.LightYellow;
break;
case 50:
result = Color.Lime;
break;
case 25:
result = Color.Maroon;
break;
case 53:
result = Color.Orange;
break;
case 28:
result = Color.Orchid;
break;
case 14:
result = Color.Pink;
break;
case 61:
result = Color.Plum;
break;
case 10:
result = Color.Red;
break;
case 30:
result = Color.RoyalBlue;
break;
case 57:
result = Color.SeaGreen;
break;
case 40:
result = Color.SkyBlue;
break;
case 47:
result = Color.Tan;
break;
case 21:
result = Color.Teal;
break;
case 15:
result = Color.Turquoise;
break;
case 20:
result = Color.Violet;
break;
case 13:
result = Color.Yellow;
break;
case 9:
case 64:// 自动色
result = Color.White;
break;
}
return result;
}
// 从Excel导出到DataSet
public DataSet GetDataSet(string filePath, string sheetName)
{
DataSet ds = new DataSet();
string strConnect = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filePath + ";Extended Properties='Excel 8.0;HDR=Yes;IMEX=1';";
try
{
OleDbConnection objConn = new OleDbConnection(strConnect);
OleDbDataAdapter objDA = new OleDbDataAdapter("select * from [" + sheetName + "$]", strConnect);
objConn.Open();
objDA.Fill(ds, sheetName + "$");
objConn.Close();
}
catch
{
}
return ds;
}
// 从Excel导出到DataSet
public DataSet GetDataSet(string filePath, int sheetIndex)
{
DataSet ds = new DataSet();
string strConnect = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filePath + ";Extended Properties='Excel 8.0;HDR=Yes;IMEX=1';";
try
{
FileStream fs = File.Open(filePath, FileMode.Open);
HSSFWorkbook wb = new HSSFWorkbook(fs);
fs.Close();
string sheetName = wb.GetSheetName(sheetIndex);
OleDbConnection objConn = new OleDbConnection(strConnect);
OleDbDataAdapter objDA = new OleDbDataAdapter("select * from [" + sheetName + "$]", strConnect);
objConn.Open();
objDA.Fill(ds, sheetName + "$");
objConn.Close();
}
catch
{
}
return ds;
}
// 从Excel导出到DataTable重载
public System.Data.DataTable GetDataTable(string filePath, string sheetName)
{
System.Data.DataTable dt = new System.Data.DataTable();
string strConnect = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filePath + ";Extended Properties='Excel 8.0;HDR=Yes;IMEX=1';";
try
{
OleDbConnection objConn = new OleDbConnection(strConnect);
OleDbDataAdapter objDA = new OleDbDataAdapter("select * from [" + sheetName + "$]", strConnect);
objConn.Open();
objDA.Fill(dt);
objConn.Close();
}
catch
{
}
return dt;
}
// 从Excel导出到DataTable重载
public System.Data.DataTable GetDataTable(string filePath, int sheetIndex)
{
System.Data.DataTable dt = new System.Data.DataTable();
string strConnect = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filePath + ";Extended Properties='Excel 8.0;HDR=Yes;IMEX=1';";
try
{
FileStream fs = File.Open(filePath, FileMode.Open);
HSSFWorkbook wb = new HSSFWorkbook(fs);
fs.Close();
string sheetName = wb.GetSheetName(sheetIndex);
OleDbConnection objConn = new OleDbConnection(strConnect);
OleDbDataAdapter objDA = new OleDbDataAdapter("select * from [" + sheetName + "$]", strConnect);
objConn.Open();
objDA.Fill(dt);
objConn.Close();
}
catch
{
}
return dt;
}
// 创建工作簿
public HSSFWorkbook Create()
{
HSSFWorkbook wb = new HSSFWorkbook();
return wb;
}
// 打开工作簿
public HSSFWorkbook Open(string filePath)
{
FileStream fs = File.Open(filePath, FileMode.Open);
HSSFWorkbook wb = new HSSFWorkbook(fs);
fs.Close();
return wb;
}
// 保存工作簿
public void Save(HSSFWorkbook workbook, string filePath)
{
FileStream fs = File.Open(filePath, FileMode.OpenOrCreate);
workbook.Write(fs);
fs.Close();
}
// 创建表单
public ISheet CreateSheet(HSSFWorkbook workbook, string sheetName)
{
return workbook.CreateSheet(sheetName);
}
// 打开表单重载
public ISheet OpenSheet(HSSFWorkbook workbook, string sheetName)
{
int index = workbook.GetSheetIndex(sheetName);
return index >= 0 ? workbook.GetSheetAt(index) : null;
}
// 打开表单重载
public ISheet OpenSheet(HSSFWorkbook workbook, int sheetIndex)
{
int index = sheetIndex;
return index >= 0 ? workbook.GetSheetAt(index) : null;
}
// 获取表单对象重载
public ISheet GetSheet(string filePath, string sheetName)
{
FileStream fs = File.Open(filePath, FileMode.Open);
HSSFWorkbook wb = new HSSFWorkbook(fs);
fs.Close();
int index = wb.GetSheetIndex(sheetName);
return wb.GetSheetAt(index);
}
// 获取表单对象重载
public ISheet GetSheet(string filePath, int sheetIndex)
{
FileStream fs = File.Open(filePath, FileMode.Open);
HSSFWorkbook wb = new HSSFWorkbook(fs);
fs.Close();
int index = sheetIndex;
return wb.GetSheetAt(index);
}
// 获取指定索引表单名
public string GetSheetName(HSSFWorkbook workbook, int sheetIndex)
{
return workbook.GetSheetName(sheetIndex);
}
// 获取指定表单名索引
public int GetSheetIndex(HSSFWorkbook workbook, string sheetName)
{
return workbook.GetSheetIndex(sheetName);
}
// 统计表单数
public int CountSheet(HSSFWorkbook workbook)
{
return workbook.NumberOfSheets;
}
// 统计行数
public int CountRow(ISheet sheet)
{
int result = sheet.LastRowNum;
return result + 1;
}
// 统计列数
public int CountColumn(ISheet sheet)
{
int result = 0;
int rowCount = sheet.LastRowNum;
for (int i = 0; i <= rowCount; i++)
{
try
{
int colCount = sheet.GetRow(i).LastCellNum;
result = colCount > result ? colCount : result;
}
catch
{
}
}
return result;
}
// 获取单元格数据类型
public string GetCellDataType(ISheet sheet, int rowNum, int colNum)
{
string result = "";
if ((rowNum < 1) || (colNum < 1)) return result;
int rowIndex = rowNum - 1;
int colIndex = colNum - 1;
try
{
ICell cell = sheet.GetRow(rowIndex).GetCell(colIndex);
result = cell.CellType.ToString();
}
catch
{
}
return result;
}
// 读取单元格重载
public string ReadCell(ISheet sheet, int rowNum, int colNum)
{
string result = "";
if ((rowNum < 1) || (colNum < 1)) return result;
int rowIndex = rowNum - 1;
int colIndex = colNum - 1;
try
{
ICell cell = sheet.GetRow(rowIndex).GetCell(colIndex);
if (cell.IsMergedCell)
{
int regionsCount = sheet.NumMergedRegions;
for (int i = 0; i < regionsCount; i++)
{
CellRangeAddress range = sheet.GetMergedRegion(i);
sheet.IsMergedRegion(range);
if (range.FirstRow <= rowIndex && rowIndex <= range.LastRow && range.FirstColumn <= colIndex && colIndex <= range.LastColumn)
{
cell = sheet.GetRow(range.FirstRow).GetCell(range.FirstColumn);
break;
}
}
}
result = cell.ToString();
}
catch
{
}
return result;
}
// 读取单元格重载
public string ReadCell(ISheet sheet, int rowNum, int colNum, string dataFormat)
{
string result = "";
if ((rowNum < 1) || (colNum < 1)) return result;
int rowIndex = rowNum - 1;
int colIndex = colNum - 1;
try
{
ICell cell = sheet.GetRow(rowIndex).GetCell(colIndex);
if (cell.IsMergedCell)
{
int regionsCount = sheet.NumMergedRegions;
for (int i = 0; i < regionsCount; i++)
{
CellRangeAddress range = sheet.GetMergedRegion(i);
sheet.IsMergedRegion(range);
if (range.FirstRow <= rowIndex && rowIndex <= range.LastRow && range.FirstColumn <= colIndex && colIndex <= range.LastColumn)
{
cell = sheet.GetRow(range.FirstRow).GetCell(range.FirstColumn);
break;
}
}
}
short data = cell.CellStyle.DataFormat;
cell.CellStyle.DataFormat = sheet.Workbook.CreateDataFormat().GetFormat(dataFormat);
result = cell.ToString();
cell.CellStyle.DataFormat = data;
}
catch
{
}
return result;
}
// 写入单元格重载
public bool WriteCell(ISheet sheet, int rowNum, int colNum, object value)
{
bool result = false;
if ((rowNum < 1) || (colNum < 1)) return result;
int rowIndex = rowNum - 1;
int colIndex = colNum - 1;
int physicalRowNum = sheet.PhysicalNumberOfRows;
if (rowNum > physicalRowNum)
{
for (int i = physicalRowNum; i <= rowIndex; i++)
{
sheet.CreateRow(i);
}
}
int physicalColNum = sheet.GetRow(rowIndex).PhysicalNumberOfCells;
if (colNum > physicalColNum)
{
for (int i = physicalColNum; i <= colIndex; i++)
{
sheet.GetRow(rowIndex).CreateCell(i);
}
}
try
{
ICell cell = sheet.GetRow(rowIndex).GetCell(colIndex);
if (cell.IsMergedCell)
{
int regionsCount = sheet.NumMergedRegions;
for (int i = 0; i < regionsCount; i++)
{
CellRangeAddress range = sheet.GetMergedRegion(i);
sheet.IsMergedRegion(range);
if (range.FirstRow <= rowIndex && rowIndex <= range.LastRow && range.FirstColumn <= colIndex && colIndex <= range.LastColumn)
{
cell = sheet.GetRow(range.FirstRow).GetCell(range.FirstColumn);
break;
}
}
}
result = true;
switch (value.GetType().ToString())
{
case "System.Boolean":
cell.SetCellValue((bool)value);
break;
case "System.DateTime":
cell.SetCellValue((DateTime)value);
break;
case "System.Double":
cell.SetCellValue((double)value);
break;
case "System.String":
cell.SetCellValue((string)value);
break;
case "System.Int16":
cell.SetCellValue((short)value);
break;
case "System.Int32":
cell.SetCellValue((int)value);
break;
case "NPOI.HSSF.UserModel.HSSFRichTextString":
cell.SetCellValue((IRichTextString)value);
break;
default:
result = false;
break;
}
}
catch
{
}
return result;
}
// 写入单元格重载
public bool WriteCell(ISheet sheet, int rowNum, int colNum, object value, string dataFormat)
{
bool result = false;
if ((rowNum < 1) || (colNum < 1)) return result;
int rowIndex = rowNum - 1;
int colIndex = colNum - 1;
int physicalRowNum = sheet.PhysicalNumberOfRows;
if (rowNum > physicalRowNum)
{
for (int i = physicalRowNum; i <= rowIndex; i++)
{
sheet.CreateRow(i);
}
}
int physicalColNum = sheet.GetRow(rowIndex).PhysicalNumberOfCells;
if (colNum > physicalColNum)
{
for (int i = physicalColNum; i <= colIndex; i++)
{
sheet.GetRow(rowIndex).CreateCell(i);
}
}
try
{
ICell cell = sheet.GetRow(rowIndex).GetCell(colIndex);
if (cell.IsMergedCell)
{
int regionsCount = sheet.NumMergedRegions;
for (int i = 0; i < regionsCount; i++)
{
CellRangeAddress range = sheet.GetMergedRegion(i);
sheet.IsMergedRegion(range);
if (range.FirstRow <= rowIndex && rowIndex <= range.LastRow && range.FirstColumn <= colIndex && colIndex <= range.LastColumn)
{
cell = sheet.GetRow(range.FirstRow).GetCell(range.FirstColumn);
break;
}
}
}
result = true;
switch (value.GetType().ToString())
{
case "System.Boolean":
cell.SetCellValue((bool)value);
break;
case "System.DateTime":
cell.SetCellValue((DateTime)value);
break;
case "System.Double":
cell.SetCellValue((double)value);
break;
case "System.String":
cell.SetCellValue((string)value);
break;
case "System.Int16":
cell.SetCellValue((short)value);
break;
case "System.Int32":
cell.SetCellValue((int)value);
break;
case "NPOI.HSSF.UserModel.HSSFRichTextString":
cell.SetCellValue((IRichTextString)value);
break;
default:
result = false;
break;
}
cell.CellStyle.DataFormat = sheet.Workbook.CreateDataFormat().GetFormat(dataFormat);
cell.SetCellValue(cell.ToString());
}
catch
{
}
return result;
}
// 读取公式
public string ReadFormula(ISheet sheet, int rowNum, int colNum)
{
string result = "";
if ((rowNum < 1) || (colNum < 1)) return result;
int rowIndex = rowNum - 1;
int colIndex = colNum - 1;
int physicalRowNum = sheet.PhysicalNumberOfRows;
if (rowNum > physicalRowNum)
{
for (int i = physicalRowNum; i <= rowIndex; i++)
{
sheet.CreateRow(i);
}
}
int physicalColNum = sheet.GetRow(rowIndex).PhysicalNumberOfCells;
if (colNum > physicalColNum)
{
for (int i = physicalColNum; i <= colIndex; i++)
{
sheet.GetRow(rowIndex).CreateCell(i);
}
}
try
{
ICell cell = sheet.GetRow(rowIndex).GetCell(colIndex);
if (cell.IsMergedCell)
{
int regionsCount = sheet.NumMergedRegions;
for (int i = 0; i < regionsCount; i++)
{
CellRangeAddress range = sheet.GetMergedRegion(i);
sheet.IsMergedRegion(range);
if (range.FirstRow <= rowIndex && rowIndex <= range.LastRow && range.FirstColumn <= colIndex && colIndex <= range.LastColumn)
{
cell = sheet.GetRow(range.FirstRow).GetCell(range.FirstColumn);
break;
}
}
}
result = cell.NumericCellValue.ToString();
}
catch
{
}
return result;
}
// 写入公式
public bool WriteFormula(ISheet sheet, int rowNum, int colNum, string formulaText, ICellStyle cellStyle)
{
bool result = false;
if ((rowNum < 1) || (colNum < 1)) return result;
int rowIndex = rowNum - 1;
int colIndex = colNum - 1;
int physicalRowNum = sheet.PhysicalNumberOfRows;
if (rowNum > physicalRowNum)
{
for (int i = physicalRowNum; i <= rowIndex; i++)
{
sheet.CreateRow(i);
}
}
int physicalColNum = sheet.GetRow(rowIndex).PhysicalNumberOfCells;
if (colNum > physicalColNum)
{
for (int i = physicalColNum; i <= colIndex; i++)
{
sheet.GetRow(rowIndex).CreateCell(i);
}
}
try
{
ICell cell = sheet.GetRow(rowIndex).GetCell(colIndex);
if (cell.IsMergedCell)
{
int regionsCount = sheet.NumMergedRegions;
for (int i = 0; i < regionsCount; i++)
{
CellRangeAddress range = sheet.GetMergedRegion(i);
sheet.IsMergedRegion(range);
if (range.FirstRow <= rowIndex && rowIndex <= range.LastRow && range.FirstColumn <= colIndex && colIndex <= range.LastColumn)
{
((HSSFSheet)sheet).SetBorderTopOfRegion(range, cellStyle.BorderTop, cellStyle.TopBorderColor);
((HSSFSheet)sheet).SetBorderBottomOfRegion(range, cellStyle.BorderBottom, cellStyle.BottomBorderColor);
((HSSFSheet)sheet).SetBorderLeftOfRegion(range, cellStyle.BorderLeft, cellStyle.LeftBorderColor);
((HSSFSheet)sheet).SetBorderRightOfRegion(range, cellStyle.BorderRight, cellStyle.RightBorderColor);
cell = sheet.GetRow(range.FirstRow).GetCell(range.FirstColumn);
break;
}
}
}
cell.SetCellFormula(formulaText);
sheet.ForceFormulaRecalculation = true;
cell.CellStyle = cellStyle;
result = true;
}
catch
{
}
return result;
}
// 写入富文本
// 在文本中上标符号为 ^ 下标符号为 _ 结束符号为 ! ,任何上下标必须以结束符作为结尾,如"x^3!+y_1!^2!=z_3!"
public bool WriteRichText(ISheet sheet, int rowNum, int colNum, string richFormat)
{
bool result = false;
if ((rowNum < 1) || (colNum < 1)) return result;
int rowIndex = rowNum - 1;
int colIndex = colNum - 1;
int physicalRowNum = sheet.PhysicalNumberOfRows;
if (rowNum > physicalRowNum)
{
for (int i = physicalRowNum; i <= rowIndex; i++)
{
sheet.CreateRow(i);
}
}
int physicalColNum = sheet.GetRow(rowIndex).PhysicalNumberOfCells;
if (colNum > physicalColNum)
{
for (int i = physicalColNum; i <= colIndex; i++)
{
sheet.GetRow(rowIndex).CreateCell(i);
}
}
try
{
ICell cell = sheet.GetRow(rowIndex).GetCell(colIndex);
if (cell.IsMergedCell)
{
int regionsCount = sheet.NumMergedRegions;
for (int i = 0; i < regionsCount; i++)
{
CellRangeAddress range = sheet.GetMergedRegion(i);
sheet.IsMergedRegion(range);
if (range.FirstRow <= rowIndex && rowIndex <= range.LastRow && range.FirstColumn <= colIndex && colIndex <= range.LastColumn)
{
cell = sheet.GetRow(range.FirstRow).GetCell(range.FirstColumn);
break;
}
}
}
// 创建上标字体
HSSFFont superFont = (HSSFFont)sheet.Workbook.CreateFont();
superFont.TypeOffset = NPOI.SS.UserModel.FontSuperScript.Super;
// 创建下标字体
HSSFFont subFont = (HSSFFont)sheet.Workbook.CreateFont();
subFont.TypeOffset = NPOI.SS.UserModel.FontSuperScript.Sub;
// 处理文本格式
string trimText = richFormat;
trimText = trimText.Replace("^", "");// 上标符
trimText = trimText.Replace("_", "");// 下标符
trimText = trimText.Replace("!", "");// 结束符
HSSFRichTextString richText = new HSSFRichTextString(trimText);
int offset = 0;
while (richFormat.IndexOf('!') != -1)
{
int superIndex = richFormat.IndexOf('^');
int subIndex = richFormat.IndexOf('_');
int endFlag = richFormat.IndexOf('!');
if (superIndex != -1 && superIndex < endFlag)
{// 处理上标
int start = superIndex + offset;
offset += endFlag - 1;
richText.ApplyFont(start, offset, superFont);
}
else if (subIndex != -1 && subIndex < endFlag)
{// 处理下标
int start = subIndex + offset;
offset += endFlag - 1;
richText.ApplyFont(start, offset, subFont);
}
richFormat = richFormat.Substring(endFlag + 1);
}
cell.SetCellValue(richText);
result = true;
}
catch
{
}
return result;
}
// 判断合并单元格
public bool IsMergeCell(ISheet sheet, int rowNum, int colNum)
{
bool result = false;
if ((rowNum < 1) || (colNum < 1)) return result;
try
{
int rowIndex = rowNum - 1;
int colIndex = colNum - 1;
result = sheet.GetRow(rowIndex).GetCell(colIndex).IsMergedCell;
}
catch
{
}
return result;
}
// 获取单元格信息
// 调用时要在输出变量前加 out
public void GetCellInfo(ISheet sheet, int rowNum, int colNum, out int rowSpan, out int colSpan, out bool isMerged, out bool isHeader)
{
rowSpan = 0;
colSpan = 0;
isMerged = false;
isHeader = false;
if ((rowNum < 1) || (colNum < 1)) return;
try
{
int rowIndex = rowNum - 1;
int colIndex = colNum - 1;
rowSpan = 1;
colSpan = 1;
isMerged = sheet.GetRow(rowIndex).GetCell(colIndex).IsMergedCell;
if (isMerged == false)
{
isHeader = true;
}
else
{
for (int i = 0; i < sheet.NumMergedRegions; i++)
{
CellRangeAddress range = sheet.GetMergedRegion(i);
if (range.FirstRow == rowIndex && range.FirstColumn == colIndex)
{
rowSpan = range.LastRow - range.FirstRow + 1;
colSpan = range.LastColumn - range.FirstColumn + 1;
isHeader = true;
break;
}
}
}
}
catch
{
}
}
// 设置合并单元格
// rowSpan 行跨度,colSpan 列跨度,最小值为1
public bool SetMergeCell(ISheet sheet, int rowNum, int colNum, int rowSpan, int colSpan)
{
bool result = false;
if ((rowNum < 1) || (colNum < 1)) return result;
int rowIndex = rowNum - 1;
int colIndex = colNum - 1;
int rowSpanIndex = (rowSpan < 1 ? 1 : rowSpan) - 1;
int colSpanIndex = (colSpan < 1 ? 1 : colSpan) - 1;
try
{
if (0 <= sheet.AddMergedRegion(new CellRangeAddress(rowIndex, rowIndex + rowSpanIndex, colIndex, colIndex + colSpanIndex)))
{
result = true;
}
}
catch
{
}
return result;
}
// 撤销合并单元格
public bool UndoMergeCell(ISheet sheet, int rowNum, int colNum)
{
bool result = false;
if ((rowNum < 1) || (colNum < 1)) return result;
int rowIndex = rowNum - 1;
int colIndex = colNum - 1;
try
{
ICell cell = sheet.GetRow(rowIndex).GetCell(colIndex);
if (cell.IsMergedCell)
{
int regionsCount = sheet.NumMergedRegions;
for (int i = 0; i < regionsCount; i++)
{
CellRangeAddress range = sheet.GetMergedRegion(i);
sheet.IsMergedRegion(range);
if (range.FirstRow <= rowIndex && rowIndex <= range.LastRow && range.FirstColumn <= colIndex && colIndex <= range.LastColumn)
{
((HSSFSheet)sheet).SetBorderTopOfRegion(range, NPOI.SS.UserModel.BorderStyle.None, NPOI.HSSF.Util.HSSFColor.Automatic.Index);
((HSSFSheet)sheet).SetBorderBottomOfRegion(range, NPOI.SS.UserModel.BorderStyle.None, NPOI.HSSF.Util.HSSFColor.Automatic.Index);
((HSSFSheet)sheet).SetBorderLeftOfRegion(range, NPOI.SS.UserModel.BorderStyle.None, NPOI.HSSF.Util.HSSFColor.Automatic.Index);
((HSSFSheet)sheet).SetBorderRightOfRegion(range, NPOI.SS.UserModel.BorderStyle.None, NPOI.HSSF.Util.HSSFColor.Automatic.Index);
sheet.RemoveMergedRegion(i);
cell.CellStyle.BorderTop = NPOI.SS.UserModel.BorderStyle.None;
cell.CellStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.None;
cell.CellStyle.BorderLeft = NPOI.SS.UserModel.BorderStyle.None;
cell.CellStyle.BorderRight = NPOI.SS.UserModel.BorderStyle.None;
cell.CellStyle.TopBorderColor = NPOI.HSSF.Util.HSSFColor.Automatic.Index;
cell.CellStyle.BottomBorderColor = NPOI.HSSF.Util.HSSFColor.Automatic.Index;
cell.CellStyle.LeftBorderColor = NPOI.HSSF.Util.HSSFColor.Automatic.Index;
cell.CellStyle.RightBorderColor = NPOI.HSSF.Util.HSSFColor.Automatic.Index;
cell.CellStyle.FillPattern = FillPattern.NoFill;
cell.CellStyle.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.Automatic.Index;
cell.CellStyle.FillBackgroundColor = NPOI.HSSF.Util.HSSFColor.Automatic.Index;
result = true;
break;
}
}
}
}
catch
{
}
return result;
}
// 设置行高
public void SetRowHeight(ISheet sheet, int rowNum, int value)
{
if (rowNum < 1) return;
int rowIndex = rowNum - 1;
int physicalRowNum = sheet.PhysicalNumberOfRows;
if (rowNum > physicalRowNum)
{
for (int i = physicalRowNum; i <= rowIndex; i++)
{
sheet.CreateRow(i);
}
}
sheet.GetRow(rowIndex).Height = (short)(value * 20);
}
// 设置列宽
// value = 0 时自动列宽
public void SetColumnWidth(ISheet sheet, int colNum, int value)
{
if (colNum < 1) return;
int colIndex = colNum - 1;
if (1 > sheet.PhysicalNumberOfRows)
{
sheet.CreateRow(0);
}
int physicalColNum = sheet.GetRow(0).PhysicalNumberOfCells;
if (colNum > physicalColNum)
{
for (int i = physicalColNum; i <= colIndex; i++)
{
sheet.GetRow(0).CreateCell(i);
}
}
if (value == 0)
{
sheet.AutoSizeColumn(colNum);
}
else
{
sheet.SetColumnWidth(colIndex, (int)((value + 0.72) * 256));
}
}
// 读取行高
public int GetRowHeight(ISheet sheet, int rowNum)
{
int result = -1;
if (rowNum < 1) return result;
int rowIndex = rowNum - 1;
result = sheet.GetRow(rowIndex).Height;
return (int)result / 20;
}
// 读取列宽
public int GetColumnWidth(ISheet sheet, int colNum)
{
int result = -1;
if (colNum < 1) return result;
int colIndex = colNum - 1;
result = sheet.GetColumnWidth(colIndex);
return (int)(result - 0.72) / 256;
}
// 获取单元格样式
public ICellStyle GetCellStyle(ISheet sheet, int rowNum, int colNum)
{
ICellStyle result = null;
if ((rowNum < 1) || (colNum < 1)) return result;
int rowIndex = rowNum - 1;
int colIndex = colNum - 1;
int physicalRowNum = sheet.PhysicalNumberOfRows;
if (rowNum > physicalRowNum)
{
for (int i = physicalRowNum; i <= rowIndex; i++)
{
sheet.CreateRow(i);
}
}
int physicalColNum = sheet.GetRow(rowIndex).PhysicalNumberOfCells;
if (colNum > physicalColNum)
{
for (int i = physicalColNum; i <= colIndex; i++)
{
sheet.GetRow(rowIndex).CreateCell(i);
}
}
try
{
ICell cell = sheet.GetRow(rowIndex).GetCell(colIndex);
result = cell.CellStyle;
}
catch
{
}
return result;
}
// 设置单元格样式
public ICellStyle SetCellStyle(ISheet sheet, int rowNum, int colNum, ICellStyle cellStyle)
{
ICellStyle result = null;
if ((rowNum < 1) || (colNum < 1)) return result;
int rowIndex = rowNum - 1;
int colIndex = colNum - 1;
int physicalRowNum = sheet.PhysicalNumberOfRows;
if (rowNum > physicalRowNum)
{
for (int i = physicalRowNum; i <= rowIndex; i++)
{
sheet.CreateRow(i);
}
}
int physicalColNum = sheet.GetRow(rowIndex).PhysicalNumberOfCells;
if (colNum > physicalColNum)
{
for (int i = physicalColNum; i <= colIndex; i++)
{
sheet.GetRow(rowIndex).CreateCell(i);
}
}
try
{
ICell cell = sheet.GetRow(rowIndex).GetCell(colIndex);
if (cell.IsMergedCell)
{
int regionsCount = sheet.NumMergedRegions;
for (int i = 0; i < regionsCount; i++)
{
CellRangeAddress range = sheet.GetMergedRegion(i);
sheet.IsMergedRegion(range);
if (range.FirstRow <= rowIndex && rowIndex <= range.LastRow && range.FirstColumn <= colIndex && colIndex <= range.LastColumn)
{
((HSSFSheet)sheet).SetBorderTopOfRegion(range, cellStyle.BorderTop, cellStyle.TopBorderColor);
((HSSFSheet)sheet).SetBorderBottomOfRegion(range, cellStyle.BorderBottom, cellStyle.BottomBorderColor);
((HSSFSheet)sheet).SetBorderLeftOfRegion(range, cellStyle.BorderLeft, cellStyle.LeftBorderColor);
((HSSFSheet)sheet).SetBorderRightOfRegion(range, cellStyle.BorderRight, cellStyle.RightBorderColor);
cell = sheet.GetRow(range.FirstRow).GetCell(range.FirstColumn);
break;
}
}
}
cell.CellStyle = cellStyle;
result = cell.CellStyle;
}
catch
{
}
return result;
}
// 新建样式重载
public ICellStyle NewStyle(HSSFWorkbook workbook)
{
ICellStyle style = workbook.CreateCellStyle();
return style;
}
// 新建样式重载
// 日期: yyyy/MM/dd
// 数字: 0.00
// 百分比: 0.00%
// 科学计数法( + ): 0.00E + 00
// 科学计数法(-): 0.00E-00
// 中文数字大写 [DbNum2][$-804]0
public ICellStyle NewStyle(HSSFWorkbook workbook, string dataFormat)
{
ICellStyle style = workbook.CreateCellStyle();
IDataFormat format = workbook.CreateDataFormat();
style.DataFormat = format.GetFormat(dataFormat);
return style;
}
// 新建字体
// fontFamily = {"宋体"|"黑体"|"楷体"|"Tahoma"|"Times New Roman"|...}
// fontColor = NPOI.HSSF.Util.HSSFColor.{Automatic|Black|Blue|DarkBlue|...}.Index
// underlineType = FontUnderlineType.{None|Single|Double|SingleAccounting|DoubleAccounting}
public IFont NewFont(HSSFWorkbook workbook, string fontFamily, short fontColor, int fontSize, bool isBold, bool isItalic, FontUnderlineType underlineType)
{
IFont font = workbook.CreateFont();
font.FontName = fontFamily;// 字体名称
font.Color = fontColor;// 字体颜色
font.FontHeightInPoints = (short)fontSize;// 字体大小
if (isBold) font.Boldweight = (short)FontBoldWeight.Bold;//加粗
font.IsItalic = isItalic;// 斜体
font.Underline = underlineType;// 下划线
return font;
}
// 设置字体样式
public ICellStyle SetFont(ICellStyle cellStyle, IFont cellFont)
{
cellStyle.SetFont(cellFont);
return cellStyle;
}
// 设置数据格式
public ICellStyle SetFormat(ICellStyle cellStyle, HSSFWorkbook workbook, string dataFormat)
{
IDataFormat format = workbook.CreateDataFormat();
cellStyle.DataFormat = format.GetFormat(dataFormat);
return cellStyle;
}
// 设置对齐样式
// horizPos = 0常规,1靠左,2居中,3靠右,4填充,5两端,6跨列,7分散
// vertiPos = 0靠上,1居中,2靠下,3两端,4分散
public ICellStyle SetAlign(ICellStyle cellStyle, int horizPos, int vertiPos, bool isWrap)
{
horizPos = horizPos < 0 ? 0 : horizPos;
vertiPos = vertiPos < 0 ? 0 : vertiPos;
((HSSFCellStyle)cellStyle).Alignment = (HorizontalAlignment)(horizPos % 8);// 水平对齐
((HSSFCellStyle)cellStyle).VerticalAlignment = (VerticalAlignment)(vertiPos % 5);// 垂直对齐
((HSSFCellStyle)cellStyle).WrapText = isWrap;
return cellStyle;
}
// 设置边框颜色
// topColor|bottomColor|leftColor|rightColor = NPOI.HSSF.Util.HSSFColor.{Automatic|White|Black|Blue|Green|...}.Index
public ICellStyle SetBorderColor(ICellStyle cellStyle, short topColor, short bottomColor, short leftColor, short rightColor)
{
cellStyle.TopBorderColor = topColor;
cellStyle.BottomBorderColor = bottomColor;
cellStyle.LeftBorderColor = leftColor;
cellStyle.RightBorderColor = rightColor;
return cellStyle;
}
// 设置边框样式
// topBorder|bottomBorder|leftBorder|rightBorder = BorderStyle.{None|Thin|Medium|Dashed|Dotted|Thick|Double|Hair|MediumDashed|DashDot|MediumDashDot|DashDotDot|MediumDashDotDot|SlantedDashDot}
public ICellStyle SetBorderStyle(ICellStyle cellStyle, NPOI.SS.UserModel.BorderStyle topBorder, NPOI.SS.UserModel.BorderStyle bottomBorder, NPOI.SS.UserModel.BorderStyle leftBorder, NPOI.SS.UserModel.BorderStyle rightBorder)
{
cellStyle.BorderTop = topBorder;
cellStyle.BorderBottom = bottomBorder;
cellStyle.BorderLeft = leftBorder;
cellStyle.BorderRight = rightBorder;
return cellStyle;
}
// 设置填充样式
// pattern = FillPattern.{NoFill|SolidForeground|FineDots|AltBars|SparseDots|ThickHorizontalBands|ThickVerticalBands|ThickBackwardDiagonals|ThickForwardDiagonals|BigSpots|Bricks|...}
public ICellStyle SetFillStyle(ICellStyle cellStyle, FillPattern pattern, short foregroundColor, short backgroundColor)
{
cellStyle.FillPattern = pattern;// 图案样式
cellStyle.FillForegroundColor = foregroundColor;// 图案颜色
cellStyle.FillBackgroundColor = backgroundColor;// 背景颜色
return cellStyle;
}
// 绘制表单
public string DrawSheet(ISheet sheet)
{
int maxColNum = 0;
string html;
html = "<table border=\"1\" style=\"text-align:center;vertical-align:middle;border:2px solid darkgray;border-collapse:collapse;width:100%;\">";
for (int rowIndex = 0; rowIndex <= sheet.LastRowNum; rowIndex++)
{// 表单行
if (sheet.GetRow(rowIndex) == null)
{// 处理空行并跳过当前循环
if (maxColNum == 0)
{
html += "<tr><td><br/></td></tr>";
}
else
{
html += "<tr><td colspan=" + maxColNum + "><br/></td></tr>";
}
continue;
}
else
{// 保存最后非空行单元列数
maxColNum = sheet.GetRow(rowIndex).LastCellNum;
}
// 绘制非空行数据
html += "<tr height=" + sheet.GetRow(rowIndex).Height / 20 + ">";
for (int colIndex = 0; colIndex < sheet.GetRow(rowIndex).LastCellNum; colIndex++)
{// 表单列
ICell cell = sheet.GetRow(rowIndex).GetCell(colIndex);
if (cell == null)
{// 处理空单元格并跳过当前循环
html += "<td />";
continue;
}
int rowSpan = 1;
int colSpan = 1;
bool isHeader = false;
if (cell.IsMergedCell == false)
{
isHeader = true;
}
else
{
for (int i = 0; i < sheet.NumMergedRegions; i++)
{
CellRangeAddress range = sheet.GetMergedRegion(i);
if (range.FirstRow == rowIndex && range.FirstColumn == colIndex)
{
rowSpan = range.LastRow - range.FirstRow + 1;
colSpan = range.LastColumn - range.FirstColumn + 1;
isHeader = true;
break;
}
}
}
if (isHeader)
{// 仅头表单绘制内容
string colorName = "White";
switch (cell.CellStyle.FillForegroundColor)
{// 转换颜色索引为颜色名称
case 49:
colorName = "Aqua";
break;
case 8:
colorName = "Black";
break;
case 12:
colorName = "Blue";
break;
case 60:
colorName = "Brown";
break;
case 29:
colorName = "Coral";
break;
case 24:
colorName = "CornflowerBlue";
break;
case 18:
colorName = "DarkBlue";
break;
case 58:
colorName = "DarkGreen";
break;
case 16:
colorName = "DarkRed";
break;
case 51:
colorName = "Gold";
break;
case 17:
colorName = "Green";
break;
case 62:
colorName = "Indigo";
break;
case 46:
colorName = "Lavender";
break;
case 26:
colorName = "LemonChiffon";
break;
case 48:
colorName = "LightBlue";
break;
case 42:
colorName = "LightGreen";
break;
case 43:
colorName = "LightYellow";
break;
case 50:
colorName = "Lime";
break;
case 25:
colorName = "Maroon";
break;
case 53:
colorName = "Orange";
break;
case 28:
colorName = "Orchid";
break;
case 14:
colorName = "Pink";
break;
case 61:
colorName = "Plum";
break;
case 10:
colorName = "Red";
break;
case 30:
colorName = "RoyalBlue";
break;
case 57:
colorName = "SeaGreen";
break;
case 40:
colorName = "SkyBlue";
break;
case 47:
colorName = "Tan";
break;
case 21:
colorName = "Teal";
break;
case 15:
colorName = "Turquoise";
break;
case 20:
colorName = "Violet";
break;
case 9:
colorName = "White";
break;
case 13:
colorName = "Yellow";
break;
}
// white-space:nowrap; 自适应宽度
// word-break:keep-all; 避免长单词截断
html += "<td rowspan=" + rowSpan + " colspan=" + colSpan + " width=" + (sheet.GetColumnWidth(colIndex) - 0.72) / 256 + " bgcolor=" + colorName + " style=\"white-space:nowrap;word-break:keep-all;\">";
switch (cell.CellType.ToString())
{// 写入单元格内容
case "Formula":
html += cell.NumericCellValue.ToString("#0.0000");
break;
case "Numeric":
html += cell.ToString();
break;
default:
html += cell.ToString();
break;
}
html += "</td>";
}
}
html += "</tr>";
}
html += "</table>";
return html;
}
}
GetExcelNpoi.cs
最新推荐文章于 2021-12-22 08:13:58 发布
本博客介绍了一个强大的.NET工具GetExcelNpoi，它提供了丰富的功能来处理Excel文件，包括读取、写入、合并单元格等，并且支持通过ADO.NET连接Excel文件进行数据操作。
扫一扫
3334
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
