public static void FullSheet(NPOI.SS.UserModel.ISheet iSheet, System.Data.DataTable dataTable, bool firstRowIsColumnHead)
{
if (iSheet == null)
{
throw new AggregateException("参数NPOI.SS.UserModel.ISheet对象为null。");
}
NPOI.SS.UserModel.IRow iRow = iSheet.CreateRow(0);
for (int dataTableColumnIndex = 0; dataTableColumnIndex < dataTable.Columns.Count; dataTableColumnIndex++)
{
NPOI.SS.UserModel.ICell iCell = iRow.CreateCell(dataTableColumnIndex);
iCell.SetCellValue(dataTable.Columns[dataTableColumnIndex].ColumnName);
}
int startRowIndex = 0;
if (firstRowIsColumnHead)
{
startRowIndex = 1;
}
for (int dataTableRowIndex = 0; dataTableRowIndex < dataTable.Rows.Count; dataTableRowIndex++)
{
iRow = iSheet.CreateRow(dataTableRowIndex + startRowIndex);
for (int dataTableColumnIndex = 0; dataTableColumnIndex < dataTable.Columns.Count; dataTableColumnIndex++)
{
NPOI.SS.UserModel.ICell iCell = iRow.CreateCell(dataTableColumnIndex);
SetCellValue(iCell, dataTable.Rows[dataTableRowIndex][dataTableColumnIndex]);
}
}
}
/// <summary>
/// 合并单元格
/// </summary>
/// <param name="fromCell">起始单元格</param>
/// <param name="toCell">终止单元格</param>
/// <param name="isExpand">扩充模式</param>
public static void Merge(this NPOI.SS.UserModel.ICell fromCell, NPOI.SS.UserModel.ICell toCell,
bool isExpand = false)
{
if (!fromCell.Sheet.Equals(toCell.Sheet))
{
throw new OfficeException("单元格不在同一个工作表上");
}
var sheet = fromCell.Sheet;
var fromRange = fromCell.GetRangeInfo();
var toRange = toCell.GetRangeInfo();
var firstRowIndex = Math.Min(fromRange.FirstRow, toRange.FirstRow);
var firstColIndex = Math.Min(fromRange.FirstCol, toRange.FirstCol);
var lastRowIndex = Math.Max(fromRange.LastRow, toRange.LastRow);
var lastColIndex = Math.Max(fromRange.LastCol, toRange.LastCol);
var regionInfoList = sheet.GetMergedRegionInfos(firstRowIndex, lastRowIndex, firstColIndex, lastColIndex, false);
foreach (var regionInfo in regionInfoList)
{
if (isExpand)
{
firstRowIndex = Math.Min(firstRowIndex, regionInfo.FirstRow);
firstColIndex = Math.Min(firstColIndex, regionInfo.FirstCol);
lastRowIndex = Math.Max(lastRowIndex, regionInfo.LastRow);
lastColIndex = Math.Max(lastColIndex, regionInfo.LastCol);
}
sheet.RemoveMergedRegion(regionInfo.Index);
}
var region = new CellRangeAddress(firstRowIndex, lastRowIndex, firstColIndex, lastColIndex);
fromCell.Sheet.AddMergedRegion(region);
}
public void OnRender(NPOI.SS.UserModel.ICell exCell)
{
foreach (var render in renders)
{
render.Render(exCell);
}
}
static void DoMergeExcelSheet(NPOI.SS.UserModel.ISheet sheet)
{
string[] heads = new string[] { "姓名", "复训带教", "复训检查", "复训翻译" };
//增加列头
//设置列头直接进行单元格合并
NPOI.SS.UserModel.IRow row = sheet.CreateRow(0);
row.Height = 1024;
for (int i = 0; i < heads.Length; i++)
{
int cellIndex = i > 0 ? 2 * i - 1 : i;
NPOI.SS.UserModel.ICell cell = row.CreateCell(cellIndex);
cell.SetCellValue(heads[i]);
}
for (int i = 0; i < heads.Length; i++)
{
if (i == 0)
{
sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(0, 1, 0, 0));
}
else
{
sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(0, 0, 2 * i - 1, 2 * i));
}
}
}
void WriteExcel(ref NPOI.SS.UserModel.IWorkbook book, DataTable dt)
{
NPOI.SS.UserModel.ISheet sheet = book.CreateSheet("Sheet1");
// 添加表头
NPOI.SS.UserModel.IRow row = sheet.CreateRow(0);
int index = 0;
foreach (DataColumn item in dt.Columns)
{
NPOI.SS.UserModel.ICell cell = row.CreateCell(index);
cell.SetCellType(NPOI.SS.UserModel.CellType.String);
cell.SetCellValue(item.Caption);
index++;
}
// 添加数据
for (int i = 0; i < dt.Rows.Count; i++)
{
index = 0;
row = sheet.CreateRow(i + 1);
foreach (DataColumn item in dt.Columns)
{
NPOI.SS.UserModel.ICell cell = row.CreateCell(index);
cell.SetCellType(NPOI.SS.UserModel.CellType.String);
cell.SetCellValue(dt.Rows[i][item].ToString());
index++;
}
}
}
public override bool Equals(Object obj)
{
NPOI.SS.UserModel.ICell cellb = ((HSSFEvaluationCell)obj)._cell;
return(_cell.RowIndex == cellb.RowIndex &&
_cell.ColumnIndex == cellb.ColumnIndex &&
_cell.CellFormula == cellb.CellFormula &&
_cell.Sheet == cellb.Sheet);
}
public static Cell GetAdapter(this NpoiCell cell)
{
if (null == cell)
{
return(null);
}
return(new Cell(cell));
}
//ExcelHelper excle = new ExcelHelper();
//Response.AddHeader("Content-Disposition", string.Format("attachment; filename={0}.xls", DateTime.Now.ToString("yyyyMMddHHmmssfff")));
//Response.BinaryWrite(excle.ExportXLS());
public static DataTable Excel2DataTable(string filePath)
{
NPOI.SS.UserModel.ISheet sheet = null;
#region//初始化信息
try
{
string fileExt = Path.GetExtension(filePath).ToLower();
using (FileStream file = new FileStream(filePath, FileMode.Open, FileAccess.Read))
{
if (fileExt == ".xls")
{
sheet = new NPOI.HSSF.UserModel.HSSFWorkbook(file).GetSheetAt(0);
}
else if (fileExt == ".xlsx")
{
sheet = new NPOI.XSSF.UserModel.XSSFWorkbook(file).GetSheetAt(0);
}
else
{
sheet = null;
}
}
}
catch (Exception e)
{
throw e;
}
#endregion
if (sheet == null)
{
return(null);
}
System.Collections.IEnumerator rows = sheet.GetRowEnumerator();
DataTable dt = new DataTable();
for (int j = 0; j < (sheet.GetRow(0).LastCellNum); j++)
{
dt.Columns.Add(Convert.ToChar(((int)'A') + j).ToString());
}
while (rows.MoveNext())
{
NPOI.HSSF.UserModel.HSSFRow row = (NPOI.HSSF.UserModel.HSSFRow)rows.Current;
DataRow dr = dt.NewRow();
for (int i = 0; i < row.LastCellNum; i++)
{
NPOI.SS.UserModel.ICell cell = row.GetCell(i);
if (cell == null)
{
dr[i] = null;
}
else
{
dr[i] = cell.ToString();
}
}
dt.Rows.Add(dr);
}
return(dt);
}
/// <summary>
/// 添加条件格式规则
/// </summary>
/// <param name="cell">单元格</param>
/// <param name="cfrs">条件格式规则</param>
public static void AddConditionalFormattingRules(this NPOI.SS.UserModel.ICell cell, NPOI.SS.UserModel.IConditionalFormattingRule[] cfrs)
{
var regions = new[]
{
new CellRangeAddress(cell.RowIndex, cell.RowIndex, cell.ColumnIndex, cell.ColumnIndex),
};
cell.Sheet.SheetConditionalFormatting.AddConditionalFormatting(regions, cfrs);
}
public static DataTable ImportExcelXlsFile(string filePath, string extension, int sheetnum = 0, int columnnum = 0)
{
try
{
using (FileStream file = new FileStream(filePath, FileMode.Open, FileAccess.Read))
{
hssfworkbook = new HSSFWorkbook(file);
}
}
catch (Exception e)
{
throw e;
}
NPOI.SS.UserModel.ISheet sheet;
sheet = hssfworkbook.GetSheetAt(0);
System.Collections.IEnumerator rows = sheet.GetRowEnumerator();
DataTable dt = new DataTable();
//给DdataTable添加表头
if (sheet.GetRow(sheetnum) != null)
{
for (int j = 0; j < (sheet.GetRow(columnnum).LastCellNum); j++)
{
dt.Columns.Add(sheet.GetRow(columnnum).Cells[j].ToString());
}
//读取数据
while (rows.MoveNext())
{
HSSFRow row1 = null;
row1 = (HSSFRow)rows.Current;
int j = row1.LastCellNum;
DataRow dr = dt.NewRow();
if (j > dt.Columns.Count)
{
j = dt.Columns.Count;
}
for (int i = 0; i < j; i++)
{
NPOI.SS.UserModel.ICell cell = row1.GetCell(i);
if (cell == null)
{
dr[i] = null;
}
else
{
dr[i] = cell.ToString();
}
}
dt.Rows.Add(dr);
}
}
return(dt);
}
private NPOI.SS.UserModel.ICell SetBorder(NPOI.SS.UserModel.ICell cell)
{
cell.CellStyle.BorderLeft = BorderStyle.Thin;
cell.CellStyle.BorderRight = BorderStyle.Thin;
cell.CellStyle.BorderTop = BorderStyle.Thin;
cell.CellStyle.BorderBottom = BorderStyle.Thin;
cell.CellStyle.Alignment = HorizontalAlignment.Center;
cell.CellStyle.VerticalAlignment = VerticalAlignment.Center;
return(cell);
}
/// <summary>
/// 读取模板，根据List(list(string))"创建一个Excel的WorkBook并返回
/// </summary>
/// <param name="dt">数据表</param>
/// <param name="columnName">表头</param>
/// <param name="columnWidth">列宽度*256</param>
/// <param name="ModUrl">模板地址</param>
/// <param name="startRowIndex">起始行,首行=0，默认0</param>
/// <param name="RemoveOtherRows">是否移除起始行后的数据，默认=是</param>
/// <param name="SheetName">表名称</param>
/// <returns>XSSFWorkbook对象</returns>
public XSSFWorkbook CreateExcelByList_Mod(System.Collections.Generic.List <System.Collections.Generic.List <object> > dt, string[] columnName, int[] columnWidth, string ModUrl, int startRowIndex = 0, bool RemoveOtherRows = true, string SheetName = "Sheet1")
{
startRowIndex = startRowIndex >= 0?startRowIndex:0;
string ExcelModePath = NodeBase + ModUrl;
if (!File.Exists(ExcelModePath))
{
return(null);
}
//创建一个工作博
XSSFWorkbook wk = new XSSFWorkbook();
using (FileStream fileExcelMod = new FileStream(ExcelModePath, FileMode.Open, FileAccess.Read))
{
wk = new XSSFWorkbook(fileExcelMod);
fileExcelMod.Close();
}
if (wk == null)
{
return(null);
}
//创建一个Sheet
ISheet tb = wk.GetSheetAt(0);
#region 移除起始行后的所有行
if (RemoveOtherRows && tb.LastRowNum > startRowIndex + 1)
{
for (int rmr = tb.LastRowNum - 1; rmr > startRowIndex; rmr--)
{
tb.ShiftRows(rmr, rmr + 1, -1);
}
}
#endregion
//设置单元格宽度
//创建表头(在第0行)
#region 根据List<List<object>>内容创建Excel内容
for (int i = 0; i < dt.Count; i++)
{
IRow rows = tb.CreateRow(i + startRowIndex);
for (int j = 0; j < dt[i].Count; j++)
{
NPOI.SS.UserModel.ICell cell = rows.CreateCell(j);
cell.SetCellValue(dt[i][j].ToStr());
SetBorder(cell);
}
}
#endregion
return(wk);
}
/// <summary>
/// 导出excel
/// </summary>
/// <param name="response"></param>
/// <param name="dt"></param>
/// <param name="fileName"></param>
/// <param name="sheetname"></param>
public static void ExportExcel(HttpResponse response, DataTable dt, string fileName, string sheetname)
{
NPOI.HSSF.UserModel.HSSFWorkbook book = new NPOI.HSSF.UserModel.HSSFWorkbook();
NPOI.SS.UserModel.ISheet sheet = book.CreateSheet(sheetname);
NPOI.SS.UserModel.IRow row = sheet.CreateRow(0);
NPOI.SS.UserModel.ICell cell = null;
NPOI.SS.UserModel.IFont font = book.CreateFont();
NPOI.SS.UserModel.ICellStyle style = book.CreateCellStyle();
font.Boldweight = (short)NPOI.SS.UserModel.FontBoldWeight.Bold;
font.FontName = "微软雅黑";
style.SetFont(font);
for (int i = 0; i < dt.Columns.Count; i++)
{
cell = row.CreateCell(i);
cell.CellStyle = style;
cell.SetCellValue(dt.Columns[i].ColumnName);
}
for (int i = 0; i < dt.Rows.Count; i++)
{
NPOI.SS.UserModel.IRow row2 = sheet.CreateRow(i + 1);
for (int j = 0; j < dt.Columns.Count; j++)
{
string strColDataType = dt.Columns[j].DataType.ToString();
if (strColDataType.Equals("System.Int32"))
{
int intValue = 0;
int.TryParse(dt.Rows[i][j].ToString(), out intValue);
row2.CreateCell(j).SetCellValue(intValue);
}
else if (strColDataType.Equals("System.String"))
{
row2.CreateCell(j).SetCellValue(dt.Rows[i][j].ToString());
}
else if (strColDataType.Equals("System.Double"))
{
double dblValue = 0;
double.TryParse(dt.Rows[i][j].ToString(), out dblValue);
row2.CreateCell(j).SetCellValue(dblValue);
}
}
}
//写入到客户端
System.IO.MemoryStream ms = new System.IO.MemoryStream();
book.Write(ms);
response.AddHeader("Content-Disposition", string.Format("attachment; filename=" + HttpUtility.UrlEncode(fileName, Encoding.UTF8) + DateTime.Now.ToShortDateString() + ".xls"));
response.ContentType = "application/vnd.ms-excel";
//response.ContentEncoding = System.Text.Encoding.GetEncoding("gb2312");
response.BinaryWrite(ms.ToArray());
book = null;
ms.Close();
ms.Dispose();
}
public static DataTable ImportExcelFile(string filePath)
{
HSSFWorkbook hssfworkbook;
#region//初始化信息
try
{
using (FileStream file = new FileStream(filePath, FileMode.Open, FileAccess.Read))
{
hssfworkbook = new HSSFWorkbook(file);
}
}
catch (Exception e)
{
throw e;
}
#endregion
NPOI.SS.UserModel.ISheet sheet = hssfworkbook.GetSheetAt(0);
System.Collections.IEnumerator rows = sheet.GetRowEnumerator();
DataTable dt = new DataTable();
for (int j = 0; j < (sheet.GetRow(0).LastCellNum); j++)
{
dt.Columns.Add(sheet.GetRow(0).GetCell(j).ToString());
}
//sheet.cu
while (rows.MoveNext())
{
HSSFRow row = (HSSFRow)rows.Current;
if (row.RowNum != 0)
{
DataRow dr = dt.NewRow();
for (int i = 0; i < row.LastCellNum; i++)
{
NPOI.SS.UserModel.ICell cell = row.GetCell(i);
if (cell == null)
{
dr[i] = null;
}
else
{
dr[i] = cell.ToString();
}
}
dt.Rows.Add(dr);
}
}
return(dt);
}
/// <summary>
/// 获取单元格的值。
/// </summary>
/// <param name="iCell">NPOI.SS.UserModel.ICell对象。</param>
/// <returns>单元格的值。</returns>
public static object GetCellValue(NPOI.SS.UserModel.ICell iCell)
{
if (iCell == null)
{
return(null);
}
else
{
if (iCell.CellType == NPOI.SS.UserModel.CellType.Blank)
{
return("");
}
else if (iCell.CellType == NPOI.SS.UserModel.CellType.Boolean)
{
return(iCell.BooleanCellValue);
}
else if (iCell.CellType == NPOI.SS.UserModel.CellType.Error)
{
return(iCell.ErrorCellValue.ToString(System.Globalization.CultureInfo.InvariantCulture));
}
else if (iCell.CellType == NPOI.SS.UserModel.CellType.Formula)
{
if (iCell.CachedFormulaResultType == NPOI.SS.UserModel.CellType.Error)
{
return("");
}
else
{
return(iCell.NumericCellValue);
}
}
else if (iCell.CellType == NPOI.SS.UserModel.CellType.Numeric)
{
if (NPOI.SS.UserModel.DateUtil.IsCellDateFormatted(iCell))
{
return(iCell.DateCellValue);
}
else
{
return(iCell.NumericCellValue);
}
}
else if (iCell.CellType == NPOI.SS.UserModel.CellType.String)
{
return(iCell.StringCellValue);
}
else
{
return(iCell.ToString());
}
}
}
private void SetNewCell(NPOI.SS.UserModel.IRow row, int iIdx, string strValue, NPOI.SS.UserModel.ICellStyle style = null)
{
NPOI.SS.UserModel.ICell cell = row.CreateCell(iIdx, NPOI.SS.UserModel.CellType.String);
cell.SetCellValue(strValue);
if (style != null)
{
cell.CellStyle = style;
}
else
{
cell.CellStyle.BorderBottom = 0;
}
}
/// <summary>
/// 设置样式
/// </summary>
private static void SetExcelValue(DataTable dt, string sheetname, NPOI.HSSF.UserModel.HSSFWorkbook book, NPOI.SS.UserModel.ICellStyle style)
{
NPOI.SS.UserModel.ISheet sheet = book.CreateSheet(sheetname);
NPOI.SS.UserModel.IRow row = sheet.CreateRow(0);
NPOI.SS.UserModel.ICell cell = null;
NPOI.SS.UserModel.ICell newCell = null;
for (int i = 0; i < dt.Columns.Count; i++)
{
cell = row.CreateCell(i);
cell.CellStyle = style;
cell.SetCellValue(dt.Columns[i].ColumnName);
}
for (int i = 0; i < dt.Rows.Count; i++)
{
NPOI.SS.UserModel.IRow row2 = sheet.CreateRow(i + 1);
for (int j = 0; j < dt.Columns.Count; j++)
{
string strColDataType = dt.Columns[j].DataType.ToString();
if (strColDataType.Equals("System.Int32"))
{
int intValue = 0;
int.TryParse(dt.Rows[i][j].ToString(), out intValue);
row2.CreateCell(j).SetCellValue(intValue);
}
else if (strColDataType.Equals("System.String"))
{
row2.CreateCell(j).SetCellValue(dt.Rows[i][j].ToString());
}
else if (strColDataType.Equals("System.Double"))
{
double dblValue = 0;
double.TryParse(dt.Rows[i][j].ToString(), out dblValue);
row2.CreateCell(j).SetCellValue(dblValue);
}
else if (strColDataType.Equals("System.DateTime"))
{
DateTime dateV;
DateTime.TryParse(dt.Rows[i][j].ToString(), out dateV);
newCell = row2.CreateCell(j);
newCell.SetCellValue(dateV);
//格式化显示
HSSFCellStyle cellStyle = (HSSFCellStyle)book.CreateCellStyle();
HSSFDataFormat format = (HSSFDataFormat)book.CreateDataFormat();
cellStyle.DataFormat = format.GetFormat("yyyy-m-d");
newCell.CellStyle = cellStyle;
}
}
}
}
/// <summary>
/// 获取合并区域信息
/// </summary>
/// <param name="cell">NPOI单元格</param>
private static MergedRegionInfo GetRangeInfo(this NPOI.SS.UserModel.ICell cell)
{
var sheet = cell.Sheet;
for (var i = 0; i < sheet.NumMergedRegions; i++)
{
var range = sheet.GetMergedRegion(i);
if (range.IsInRange(cell.RowIndex, cell.ColumnIndex))
{
return(new MergedRegionInfo(i, range.FirstRow, range.LastRow, range.FirstColumn, range.LastColumn));
}
}
return(new MergedRegionInfo(-1, cell.RowIndex, cell.RowIndex, cell.ColumnIndex, cell.ColumnIndex));
}
/// <summary>
/// 根据List(list(string))"创建一个Excel的WorkBook并返回
/// </summary>
/// <param name="dt">数据表</param>
/// <param name="columnName">表头</param>
/// <param name="columnWidth">列宽度*256</param>
/// <param name="SheetName">表名称</param>
/// <returns>XSSFWorkbook对象</returns>
public XSSFWorkbook CreateExcelByList(System.Collections.Generic.List <System.Collections.Generic.List <object> > dt, string[] columnName, int[] columnWidth, string SheetName = "Sheet1")
{
//创建一个工作博
XSSFWorkbook wk = new XSSFWorkbook();
//创建以个Sheet
ISheet tb = wk.CreateSheet(SheetName);
//设置单元格宽度
for (int i = 0; i < columnWidth.Length; i++)
{
tb.SetColumnWidth(i, columnWidth[i] * 256);
}
//创建表头(在第0行)
IRow row = tb.CreateRow(0);
#region 表头根据参数
for (int i = 0; i < columnName.Length; i++)
{
//创建单元格
NPOI.SS.UserModel.ICell cell = row.CreateCell(i);
cell.SetCellValue(columnName[i]);
//背景
//cell.CellStyle.FillForegroundColor = NPOI.XSSF.Util.XSSFColor.DarkTeal.Index;
//cell.CellStyle.FillPattern = FillPattern.SolidForeground;
//边框颜色
//cell.CellStyle.TopBorderColor = NPOI.XSSF.Util.XSSFColor.OliveGreen.Index;
//cell.CellStyle.LeftBorderColor = NPOI.XSSF.Util.XSSFColor.OliveGreen.Index;
//cell.CellStyle.BottomBorderColor = NPOI.XSSF.Util.XSSFColor.OliveGreen.Index;
//cell.CellStyle.RightBorderColor = NPOI.XSSF.Util.XSSFColor.OliveGreen.Index;
cell.CellStyle.IsLocked = true;
SetBorder(cell);
}
#endregion
#region 根据List<List<object>>内容创建Excel内容
for (int i = 0; i < dt.Count; i++)
{
IRow rows = tb.CreateRow(i + 1);
for (int j = 0; j < dt[i].Count; j++)
{
NPOI.SS.UserModel.ICell cell = rows.CreateCell(j);
cell.SetCellValue(dt[i][j].ToStr());
SetBorder(cell);
}
}
#endregion
return(wk);
}
public void TestExtraSheetRefs_bug45978()
{
HSSFWorkbook wb = HSSFTestDataSamples.OpenSampleWorkbook("ex45978-extraLinkTableSheets.xls");
/*
* ex45978-extraLinkTableSheets.xls is a cut-down version of attachment 22561.
* The original file produces the same error.
*
* This bug was caused by a combination of invalid sheet indexes in the EXTERNSHEET
* record, and eager initialisation of the extern sheet references. Note - the worbook
* has 2 sheets, but the EXTERNSHEET record refers to sheet indexes 0, 1 and 2.
*
* Offset 0x3954 (14676)
* recordid = 0x17, size = 32
* [EXTERNSHEET]
* numOfRefs = 5
* refrec #0: extBook=0 firstSheet=0 lastSheet=0
* refrec #1: extBook=1 firstSheet=2 lastSheet=2
* refrec #2: extBook=2 firstSheet=1 lastSheet=1
* refrec #3: extBook=0 firstSheet=-1 lastSheet=-1
* refrec #4: extBook=0 firstSheet=1 lastSheet=1
* [/EXTERNSHEET]
*
* As it turns out, the formula in question doesn't even use externSheetIndex #1 - it
* uses #4, which resolves to sheetIndex 1 -> 'Data'.
*
* It is not Clear exactly what externSheetIndex #4 would refer to. Excel seems to
* display such a formula as "''!$A2", but then complains of broken link errors.
*/
NPOI.SS.UserModel.ICell cell = wb.GetSheetAt(0).GetRow(1).GetCell(1);
String cellFormula;
try
{
cellFormula = cell.CellFormula;
}
catch (IndexOutOfRangeException e)
{
if (e.Message.Equals("Index: 2, Size: 2"))
{
throw new AssertionException("Identified bug 45798");
}
throw e;
}
Assert.AreEqual("Data!$A2", cellFormula);
}
protected void btnExport_Click(object sender, EventArgs e)
{
DateTime start = Common.St.ToDateTime(txtStart.Value + " 00:00:00");
DateTime end = Common.St.ToDateTime(txtEnd.Value + " 23:59:59");
var list = DAL.WorkPlanRule.Get(start, end);
NPOI.HSSF.UserModel.HSSFWorkbook book = new NPOI.HSSF.UserModel.HSSFWorkbook(new System.IO.FileStream(Server.MapPath("~/template/template_statistics_2.xls"), System.IO.FileMode.Open, System.IO.FileAccess.Read));
NPOI.SS.UserModel.ISheet sheet = book.GetSheet("周上线记录");
NPOI.SS.UserModel.ICellStyle style = book.CreateCellStyle();
style.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
style.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;
style.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
style.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
style.WrapText = true;
style.VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment.Center;
NPOI.SS.UserModel.IRow row = sheet.GetRow(0);
NPOI.SS.UserModel.ICell cell = row.GetCell(0);
cell.SetCellValue("技术测试周上线记录（" + start.ToString("yyyy年MM月dd日") + "-" + end.ToString("yyyy年MM月dd日") + "）");
// 内容
int i = 2;
foreach (var o in list)
{
NPOI.SS.UserModel.IRow row2 = sheet.CreateRow(i);
NPOI.SS.UserModel.ICell cell0 = row2.CreateCell(0);
cell0.CellStyle = style;
cell0.SetCellValue(o.SheepNo);
NPOI.SS.UserModel.ICell cell1 = row2.CreateCell(1);
cell1.CellStyle = style;
cell1.SetCellValue(o.Project.Name);
NPOI.SS.UserModel.ICell cell2 = row2.CreateCell(2);
cell2.CellStyle = style;
cell2.SetCellValue(Common.St.ToDateTimeString(o.PublishTime, "yyyy-MM-dd"));
i++;
}
// 写入到客户端
System.IO.MemoryStream ms = new System.IO.MemoryStream();
book.Write(ms);
Response.AddHeader("Content-Disposition", string.Format("attachment; filename={0}.xls", System.Web.HttpUtility.UrlEncode("每周项目上线记录", System.Text.Encoding.UTF8)));
Response.BinaryWrite(ms.ToArray());
book = null;
ms.Close();
ms.Dispose();
}
public static dynamic GetCellValue(NPOI.SS.UserModel.ICell cell)
{
if (cell == null)
{
return(null);
}
else
{
switch (cell.CellType)
{
case NPOI.SS.UserModel.CellType.Blank:
return("");
case NPOI.SS.UserModel.CellType.Boolean:
return(cell.BooleanCellValue);
case NPOI.SS.UserModel.CellType.Error:
return(cell.ErrorCellValue.ToString(System.Globalization.CultureInfo.InvariantCulture));
case NPOI.SS.UserModel.CellType.Formula:
if (cell.CachedFormulaResultType == NPOI.SS.UserModel.CellType.Error)
{
return("#NUM!");
}
else
{
return(cell.NumericCellValue);
}
case NPOI.SS.UserModel.CellType.Numeric:
if (NPOI.SS.UserModel.DateUtil.IsCellDateFormatted(cell))
{
return(cell.DateCellValue);
}
else
{
return(cell.NumericCellValue);
}
case NPOI.SS.UserModel.CellType.String:
return(cell.StringCellValue);
default:
return(cell.ToString());
}
}
}
public static void SetCellValue(NPOI.SS.UserModel.ICell iCell, object cellValue)
{
if (iCell == null)
{
return;
}
if (cellValue != null)
{
if (cellValue.GetType() == typeof(DBNull))
{
iCell.SetCellType(NPOI.SS.UserModel.CellType.Blank);
}
else if (cellValue.GetType() == typeof(bool))
{
iCell.SetCellValue((bool)cellValue);
}
else if (cellValue.GetType() == typeof(string))
{
if (cellValue.ToString() == "#NUM!")
{
iCell.SetCellType(NPOI.SS.UserModel.CellType.Blank);
}
else
{
iCell.SetCellValue((string)cellValue);
}
}
else if (cellValue.GetType() == typeof(DateTime))
{
NPOI.SS.UserModel.ICellStyle iCellStyle = iCell.Sheet.Workbook.CreateCellStyle();
NPOI.SS.UserModel.IDataFormat iDataFormat = iCell.Sheet.Workbook.CreateDataFormat();
iCellStyle.DataFormat = iDataFormat.GetFormat("yyyy/MM/dd HH:mm:ss");
iCell.CellStyle = iCellStyle;
iCell.SetCellValue((DateTime)cellValue);
}
else if (cellValue.GetType() == typeof(int) || cellValue.GetType() == typeof(double) || cellValue.GetType() == typeof(float))
{
iCell.SetCellValue((double)cellValue);
}
else
{
iCell.SetCellValue(cellValue.ToString());
}
}
}
public static DataTable ImportExcelFile(string filePath)
{
try
{
using (FileStream file = new FileStream(filePath, FileMode.Open, FileAccess.Read))
{
hssworkbook = new HSSFWorkbook(file);
}
}
catch (Exception)
{
}
NPOI.SS.UserModel.ISheet sheet = hssworkbook.GetSheetAt(0);
IEnumerator rows = sheet.GetRowEnumerator();
DataTable dt = new DataTable();
for (int i = 0; i < sheet.GetRow(0).LastCellNum; i++)
{
dt.Columns.Add(Convert.ToChar((int)'A' + i) + "");
}
while (rows.MoveNext())
{
HSSFRow row = (HSSFRow)rows.Current;
DataRow dr = dt.NewRow();
for (int j = 0; j < row.LastCellNum; j++)
{
NPOI.SS.UserModel.ICell cell = row.GetCell(j);
if (cell == null)
{
dr[j] = null;
}
else
{
dr[j] = cell + "";
}
}
dt.Rows.Add(dr);
}
return(dt);
}
protected void btnExport_Click(object sender, EventArgs e)
{
var list = GetList().AsEnumerable().Select(a => new { Name = GetBugzillaUserName(a.Field <int>("reporter")), CC = a.Field <Int64>("cc") }).Where(a => a.Name != "");
NPOI.HSSF.UserModel.HSSFWorkbook book = new NPOI.HSSF.UserModel.HSSFWorkbook(new System.IO.FileStream(Server.MapPath("~/template/template_statistics_5.xls"), System.IO.FileMode.Open, System.IO.FileAccess.Read));
NPOI.SS.UserModel.ISheet sheet = book.GetSheet("bug统计");
NPOI.SS.UserModel.ICellStyle style = book.CreateCellStyle();
style.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
style.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;
style.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
style.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
style.WrapText = true;
style.VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment.Center;
// 内容
int i = 2;
foreach (var o in list)
{
NPOI.SS.UserModel.IRow row2 = sheet.CreateRow(i);
NPOI.SS.UserModel.ICell cell0 = row2.CreateCell(0);
cell0.CellStyle = style;
cell0.SetCellValue(o.Name);
NPOI.SS.UserModel.ICell cell1 = row2.CreateCell(1);
cell1.CellStyle = style;
cell1.SetCellValue(o.CC.ToString());
i++;
}
// 写入到客户端
System.IO.MemoryStream ms = new System.IO.MemoryStream();
book.Write(ms);
Response.AddHeader("Content-Disposition", string.Format("attachment; filename={0}.xls", System.Web.HttpUtility.UrlEncode("Bug数统计", System.Text.Encoding.UTF8)));
Response.BinaryWrite(ms.ToArray());
book = null;
ms.Close();
ms.Dispose();
}
static void CopyStyleFromCell(NPOI.SS.UserModel.ICellStyle To, NPOI.SS.UserModel.ICell Cell)
{
To.Alignment = Cell.CellStyle.Alignment;
To.BorderBottom = Cell.CellStyle.BorderBottom;
To.BorderLeft = Cell.CellStyle.BorderLeft;
To.BorderRight = Cell.CellStyle.BorderRight;
To.BorderTop = Cell.CellStyle.BorderTop;
To.BottomBorderColor = Cell.CellStyle.BottomBorderColor;
To.FillBackgroundColor = Cell.CellStyle.FillBackgroundColor;
To.FillForegroundColor = Cell.CellStyle.FillForegroundColor;
To.FillPattern = Cell.CellStyle.FillPattern;
To.SetFont(Cell.CellStyle.GetFont(Cell.Row.Sheet.Workbook));
To.IsHidden = Cell.CellStyle.IsHidden;
To.IsLocked = Cell.CellStyle.IsLocked;
To.LeftBorderColor = Cell.CellStyle.LeftBorderColor;
To.RightBorderColor = Cell.CellStyle.RightBorderColor;
To.Rotation = Cell.CellStyle.Rotation;
To.ShrinkToFit = Cell.CellStyle.ShrinkToFit;
To.TopBorderColor = Cell.CellStyle.TopBorderColor;
To.VerticalAlignment = Cell.CellStyle.VerticalAlignment;
To.WrapText = Cell.CellStyle.WrapText;
To.DataFormat = Cell.CellStyle.DataFormat;
}
/// <summary>
/// 根据DataTable创建一个Excel的WorkBook并返回
/// </summary>
/// <param name="dt">数据表</param>
/// <param name="columnName">表头</param>
/// <param name="SheetName">表名称</param>
/// <returns>XSSFWorkbook对象</returns>
public XSSFWorkbook CreateExcelByDataTable(DataTable dt, string[] columnName, string SheetName = "Sheet1")
{
//创建一个工作博
XSSFWorkbook wk = new XSSFWorkbook();
//创建以个Sheet
ISheet tb = wk.CreateSheet(SheetName);
//创建表头(在第0行)
IRow row = tb.CreateRow(0);
#region 表头根据参数
for (int i = 0; i < columnName.Length; i++)
{
//创建单元格
NPOI.SS.UserModel.ICell cell = row.CreateCell(i);
cell.SetCellValue(columnName[i]);
cell.CellStyle.FillBackgroundColor = NPOI.HSSF.Util.HSSFColor.DarkBlue.Index;
SetBorder(cell);
}
#endregion
#region 根据DataTable内容创建Excel内容
for (int i = 0; i < dt.Rows.Count; i++)
{
IRow rows = tb.CreateRow(i);
for (int j = 0; j < dt.Columns.Count; j++)
{
if (j >= columnName.Length)
{
break;
}
NPOI.SS.UserModel.ICell cell = rows.CreateCell(j);
cell.SetCellValue(dt.Rows[i][j].ToString());
SetBorder(cell);
}
}
#endregion
return(wk);
}
/// <summary>
/// 根据DataTable创建一个Excel的WorkBook并返回
/// </summary>
/// <param name="dt">数据表</param>
/// <returns>XSSFWorkbook对象</returns>
public XSSFWorkbook CreateExcelByDataTable(DataTable dt)
{
//创建一个工作博
XSSFWorkbook wk = new XSSFWorkbook();
//创建以个Sheet
ISheet tb = wk.CreateSheet("Sheet1");
//创建表头(在第0行)
IRow row = tb.CreateRow(0);
#region 根据Datable表头创建Excel表头
for (int i = 0; i < dt.Columns.Count; i++)
{
//创建单元格
NPOI.SS.UserModel.ICell cell = row.CreateCell(i);
//cell.CellStyle.FillBackgroundColor = NPOI.HSSF.Util.HSSFColor.DarkRed.Index;
//cell.CellStyle.FillForegroundColor= NPOI.HSSF.Util.HSSFColor.Yellow.Index;
cell.SetCellValue(dt.Columns[i].ColumnName);
tb.AutoSizeColumn(i);//自动调整宽度，貌似对中文支持不好
SetBorder(cell);
}
#endregion
#region 根据DataTable内容创建Excel内容
for (int i = 0; i < dt.Rows.Count; i++)
{
IRow rows = tb.CreateRow(i + 1);
for (int j = 0; j < dt.Columns.Count; j++)
{
NPOI.SS.UserModel.ICell cell = rows.CreateCell(j);
cell.SetCellValue(dt.Rows[i][j].ToString());
SetBorder(cell);
}
}
#endregion
return(wk);
}
public object Cell2Object(NPOI.SS.UserModel.ICell cell, System.Type type, out bool doSet)
{
if (cell.Hyperlink != null)
{
var segs = cell.Hyperlink.Address.Split('!');
if (segs.Length != 2)
{
doSet = false;
EngineNS.Profiler.Log.WriteLine(EngineNS.Profiler.ELogTag.Warning, "Excel", $"HyperLink({cell.Hyperlink}) is invalid");
return(null);
}
var tab = segs[0];
var linkCell = segs[1];
var sheet = Workbook.GetSheet(tab);
if (sheet == null)
{
doSet = false;
return(null);
}
int col, row;
GetCellPosition(linkCell, out col, out row);
var rowObj = sheet.GetRow(row);
if (rowObj == null)
{
doSet = false;
return(null);
}
var linker = rowObj.GetCell(col);
if (type.IsGenericType && type.GetGenericTypeDefinition() == typeof(List <>))
{//是一个数组
var obj = System.Activator.CreateInstance(type) as System.Collections.IList;
if (linker != null)
{
FillObjectList(obj, type.GenericTypeArguments[0], linker.Row);
}
doSet = true;
return(obj);
}
else
{//是一个对象
if (linker == null)
{
doSet = false;
return(null);
}
var atts = type.GetCustomAttributes(typeof(EngineNS.IO.Serializer.ExcelSheetAttribute), true);
if (atts == null || atts.Length == 0)
{
doSet = false;
return(null);
}
var obj = System.Activator.CreateInstance(type);
if (obj != null)
{
FillObject(obj, linker.Row);
}
doSet = true;
return(obj);
}
}
else
{
doSet = true;
if (type == typeof(sbyte))
{
return(System.Convert.ToSByte(cell.ToString()));
}
else if (type == typeof(Int16))
{
return(System.Convert.ToInt16(cell.ToString()));
}
else if (type == typeof(Int32))
{
return(System.Convert.ToInt32(cell.ToString()));
}
else if (type == typeof(Int64))
{
return(System.Convert.ToInt64(cell.ToString()));
}
if (type == typeof(byte))
{
return(System.Convert.ToByte(cell.ToString()));
}
else if (type == typeof(UInt16))
{
return(System.Convert.ToUInt16(cell.ToString()));
}
else if (type == typeof(UInt32))
{
return(System.Convert.ToUInt32(cell.ToString()));
}
else if (type == typeof(UInt64))
{
return(System.Convert.ToUInt64(cell.ToString()));
}
else if (type == typeof(double))
{
return(System.Convert.ToDouble(cell.ToString()));
}
else if (type == typeof(float))
{
return(System.Convert.ToSingle(cell.ToString()));
}
else if (type == typeof(string))
{
return(cell.StringCellValue);
}
else if (type == typeof(bool))
{
return(cell.BooleanCellValue);
}
else if (type == typeof(DateTime))
{
return(System.Convert.ToDateTime(cell.ToString()));
}
doSet = false;
}
return(null);
}
public HSSFEvaluationCell(NPOI.SS.UserModel.ICell cell, IEvaluationSheet evalSheet)
{
_cell = cell;
_evalSheet = evalSheet;
}
public static void CreateExcelColorFile(string fileName)
{
System.IO.FileInfo fileInfo = new System.IO.FileInfo(fileName);
// 创建Workbook。
NPOI.SS.UserModel.IWorkbook workbook = null;// new NPOI.XSSF.UserModel.XSSFWorkbook();
if (fileInfo.Extension == ".xlsx")
{
workbook = new NPOI.XSSF.UserModel.XSSFWorkbook();
}
else if (fileInfo.Extension == ".xls")
{
workbook = new NPOI.XSSF.UserModel.XSSFWorkbook();
}
else
{
return;
}
// 创建Sheet
NPOI.SS.UserModel.ISheet sheet = workbook.CreateSheet("ExcelColor");
// 设置列宽。
sheet.SetColumnWidth(0, 15 * 256);
sheet.SetColumnWidth(1, 15 * 256);
sheet.SetColumnWidth(2, 20 * 256);
sheet.SetColumnWidth(3, 20 * 256);
sheet.SetColumnWidth(4, 15 * 256);
// 创建标题。
int rowIndex = 0;
NPOI.SS.UserModel.ICell cell = sheet.CreateRow(rowIndex).CreateCell(0);
cell.SetCellValue("Excel颜色");
cell.CellStyle = NPOIExtension.GetCellStyle(workbook, ExcelColor.None.IndexNPOI, ExcelColor.Black.IndexNPOI, new System.Drawing.Font("Arial", 16, System.Drawing.FontStyle.Bold));
// 空一行
rowIndex++;
// 创建标题行
rowIndex++;
NPOI.SS.UserModel.IRow row = sheet.CreateRow(rowIndex);
row.Height = 30 * 20;
NPOI.SS.UserModel.ICellStyle columnHeadCellStyle = NPOIExtension.GetCellStyle(workbook, ExcelColor.None.IndexNPOI, ExcelColor.Black.IndexNPOI, new System.Drawing.Font("Consolas", 11, System.Drawing.FontStyle.Regular), NPOI.SS.UserModel.HorizontalAlignment.Center, NPOI.SS.UserModel.VerticalAlignment.Center);
cell = row.CreateCell(0);
cell.SetCellValue("索引");
cell.CellStyle = columnHeadCellStyle;
cell = row.CreateCell(1);
cell.SetCellValue("索引（NPOI）");
cell.CellStyle = columnHeadCellStyle;
cell = row.CreateCell(2);
cell.SetCellValue("名称");
cell.CellStyle = columnHeadCellStyle;
sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(rowIndex, rowIndex, 2, 3));
cell = row.CreateCell(4);
cell.SetCellValue("十六进制代码");
cell.CellStyle = columnHeadCellStyle;
ExcelColor.KnownColors.ForEach(x =>
{
rowIndex++;
NPOI.SS.UserModel.IRow contentRow = sheet.CreateRow(rowIndex);
contentRow.Height = 30 * 20;
NPOI.SS.UserModel.ICellStyle contentCellStyle = NPOIExtension.GetCellStyle(workbook, x.IndexNPOI, (short)(x.IsDarkColor ? 9 : 8), new System.Drawing.Font("Consolas", 11, System.Drawing.FontStyle.Regular), NPOI.SS.UserModel.HorizontalAlignment.Center, NPOI.SS.UserModel.VerticalAlignment.Center);
NPOI.SS.UserModel.ICell contentCell = contentRow.CreateCell(0);
contentCell.SetCellValue(x.Index);
contentCell.CellStyle = contentCellStyle;
contentCell = contentRow.CreateCell(1);
contentCell.SetCellValue(x.IndexNPOI + "（NPOI）");
contentCell.CellStyle = contentCellStyle;
contentCell = contentRow.CreateCell(2);
contentCell.SetCellValue(x.Name);
contentCell.CellStyle = NPOIExtension.GetCellStyle(workbook, x.IndexNPOI, (short)(x.IsDarkColor ? 9 : 8), new System.Drawing.Font("Consolas", 11, System.Drawing.FontStyle.Regular), NPOI.SS.UserModel.HorizontalAlignment.Right, NPOI.SS.UserModel.VerticalAlignment.Center);
contentCell = contentRow.CreateCell(3);
contentCell.SetCellValue(x.Description);
contentCell.CellStyle = NPOIExtension.GetCellStyle(workbook, x.IndexNPOI, (short)(x.IsDarkColor ? 9 : 8), new System.Drawing.Font("Consolas", 11, System.Drawing.FontStyle.Regular), NPOI.SS.UserModel.HorizontalAlignment.Left, NPOI.SS.UserModel.VerticalAlignment.Center);
contentCell = contentRow.CreateCell(4);
contentCell.SetCellValue(x.HexString);
contentCell.CellStyle = contentCellStyle;
});
// 保存到文件。
System.IO.FileStream fileStream = new System.IO.FileStream(fileInfo.FullName, System.IO.FileMode.Create, System.IO.FileAccess.ReadWrite);
workbook.Write(fileStream);
fileStream.Close();
workbook.Close();
}
public HSSFEvaluationCell(NPOI.SS.UserModel.ICell cell)
{
_cell = cell;
_evalSheet = new HSSFEvaluationSheet((HSSFSheet)cell.Sheet);
}
