private void mapCellOnNode(XSSFCell cell, XmlNode node, ST_XmlDataType outputDataType)
{
String value = "";
switch (cell.CellType)
{
case CellType.String: value = cell.StringCellValue; break;
case CellType.Boolean: value += cell.BooleanCellValue; break;
case CellType.Error: value = cell.ErrorCellString; break;
case CellType.Formula: value = cell.StringCellValue; break;
case CellType.Numeric: value += cell.GetRawValue(); break;
default:
break;
}
if (node is XmlElement)
{
XmlElement currentElement = (XmlElement)node;
currentElement.InnerText = value;
}
else
{
node.Value = value;
}
}
/// <summary>
/// 获取单元格类型(xlsx)
/// </summary>
/// <param name="cell"></param>
/// <returns></returns>
private static object GetValueTypeForXLSX(XSSFCell cell)
{
if (cell == null)
{
return(null);
}
switch (cell.CellType)
{
case CellType.Blank:
return(null);
case CellType.Boolean:
return(cell.BooleanCellValue);
case CellType.Numeric:
return(cell.NumericCellValue);
case CellType.String:
return(cell.StringCellValue);
case CellType.Error:
return(cell.ErrorCellValue);
case CellType.Formula:
default:
return("=" + cell.CellFormula);
}
}
/// <summary>
/// 获取单元格类型(xlsx)
/// </summary>
/// <param name="cell"></param>
/// <returns></returns>
private static object GetValueTypeForXLSX(XSSFCell cell)
{
if (cell == null)
{
return(null);
}
switch (cell.CellType)
{
case CellType.Blank: //BLANK:
return(null);
case CellType.Boolean: //BOOLEAN:
return(cell.BooleanCellValue);
case CellType.Numeric: //NUMERIC:
return(cell.NumericCellValue);
case CellType.String: //STRING:
return(cell.StringCellValue);
case CellType.Error: //ERROR:
return(cell.ErrorCellValue);
case CellType.Formula: //FORMULA:
default:
return("=" + cell.CellFormula);
}
}
public void setCellStyle(int firstRow, int lastRow, int firstColumn, int lastColumn)
{
XSSFCellStyle cellStyle = (XSSFCellStyle)workbook.CreateCellStyle();
cellStyle.CloneStyleFrom(defaultCellStyle);
for (int currentRow = firstRow; currentRow <= lastRow; currentRow++)
{
for (int currentColumn = firstColumn; currentColumn <= lastColumn; currentColumn++)
{
try
{
XSSFCell cell = (XSSFCell)sheet.GetRow(currentRow).GetCell(currentColumn);
if (cell != null)
{
cell.CellStyle = cellStyle;
}
else
{
cell = (XSSFCell)sheet.GetRow(currentRow).CreateCell(currentColumn);
cell.CellStyle = cellStyle;
}
}
catch (Exception ex)
{
logger.logException(ex);
consoleLogger.logError(ex.Message);
}
}
}
}
public void TestPreserveSpaces()
{
String[] samplesWithSpaces =
{
" POI",
"POI ",
" POI ",
"\nPOI",
"\n\nPOI \n",
};
foreach (String str in samplesWithSpaces)
{
IWorkbook swb = _testDataProvider.CreateWorkbook();
ICell sCell = swb.CreateSheet().CreateRow(0).CreateCell(0);
sCell.SetCellValue(str);
Assert.AreEqual(sCell.StringCellValue, str);
// read back as XSSF and check that xml:spaces="preserve" is Set
XSSFWorkbook xwb = (XSSFWorkbook)_testDataProvider.WriteOutAndReadBack(swb);
XSSFCell xCell = xwb.GetSheetAt(0).GetRow(0).GetCell(0) as XSSFCell;
CT_Rst is1 = xCell.GetCTCell().@is;
//XmlCursor c = is1.NewCursor();
//c.ToNextToken();
//String t = c.GetAttributeText(new QName("http://www.w3.org/XML/1998/namespace", "space"));
//c.Dispose();
//write is1 to xml stream writer ,get the xml text and parse it and get space attr.
//Assert.AreEqual("preserve", t, "expected xml:spaces=\"preserve\" \"" + str + "\"");
xwb.Close();
swb.Close();
}
}
/// <summary>
/// 判断一行是否为空
/// </summary>
/// <param name="row"></param>
/// <returns></returns>
public static Boolean IsRowEmpty(IRow row)
{
if (row is XSSFRow)
{
for (int c = row.FirstCellNum; c < row.LastCellNum; c++)
{
XSSFCell cell = (XSSFCell)row.GetCell(c);
if (cell != null && cell.CellType != CellType.Blank)
{
return(false);
}
}
}
else if (row is HSSFRow)
{
for (int c = row.FirstCellNum; c < row.LastCellNum; c++)
{
HSSFCell cell = (HSSFCell)row.GetCell(c);
if (cell != null && cell.CellType != CellType.Blank)
{
return(false);
}
}
}
return(true);
}
public int WriteArray_To_ExcelFormulas(int rowAvailableCell, int startingCol, string[,] infoArray)
{
XSSFCellStyle cellStyle = (XSSFCellStyle)workbook.CreateCellStyle();
cellStyle.CloneStyleFrom(defaultCellStyle);
for (int rowCounter = 0; rowCounter <= infoArray.GetUpperBound(0); rowCounter++)
{
for (int columnCounter = 0; columnCounter <= infoArray.GetUpperBound(1); columnCounter++)
{
try
{
XSSFCell cell = (XSSFCell)sheet.GetRow(rowAvailableCell + rowCounter).CreateCell(columnCounter + startingCol);
cell.SetCellType(CellType.Formula);
cell.SetCellFormula(infoArray[rowCounter, columnCounter]);
cell.CellStyle = cellStyle;
}
catch (Exception ex)
{
logger.logException(ex);
consoleLogger.logError(ex.Message);
}
}
}
return(startingCol + infoArray.GetUpperBound(1) + 1);
}
public int WriteArray_To_Excel(int rowAvailableCell, int startingCol, DateTime?[,] infoArray)
{
XSSFCellStyle cellStyle = (XSSFCellStyle)workbook.CreateCellStyle();
cellStyle.CloneStyleFrom(defaultCellStyle);
var newDataFormat = workbook.CreateDataFormat();
for (int rowCounter = 0; rowCounter <= infoArray.GetUpperBound(0); rowCounter++)
{
for (int columnCounter = 0; columnCounter <= infoArray.GetUpperBound(1); columnCounter++)
{
if (infoArray[rowCounter, columnCounter] != null)
{
DateTime dateTime;
if (DateTime.TryParse(infoArray[rowCounter, columnCounter].ToString(), out dateTime))
{
XSSFCell cell = (XSSFCell)sheet.GetRow(rowAvailableCell + rowCounter).CreateCell(columnCounter + startingCol);
cell.SetCellValue(dateTime.Date);
cell.CellStyle = cellStyle;
cell.CellStyle.DataFormat = newDataFormat.GetFormat("MM/dd/yyyy");
}
}
}
}
return(startingCol + infoArray.GetUpperBound(1) + 1);
}
private void HandleNonStringCell(StringBuilder text, ICell cell, DataFormatter formatter)
{
CellType type = cell.CellType;
if (type == CellType.Formula)
{
type = cell.CachedFormulaResultType;
}
if (type == CellType.Numeric)
{
ICellStyle cs = cell.CellStyle;
if (cs.GetDataFormatString() != null)
{
text.Append(formatter.FormatRawCellContents(
cell.NumericCellValue, cs.DataFormat, cs.GetDataFormatString()
));
return;
}
}
// No supported styling applies to this cell
XSSFCell xcell = (XSSFCell)cell;
text.Append(xcell.GetRawValue());
}
private void updateRowFormulas(XSSFRow row, FormulaShifter Shifter)
{
foreach (ICell c in row)
{
XSSFCell cell = (XSSFCell)c;
CT_Cell ctCell = cell.GetCTCell();
if (ctCell.IsSetF())
{
CT_CellFormula f = ctCell.f;
String formula = f.Value;
if (formula.Length > 0)
{
String ShiftedFormula = ShiftFormula(row, formula, Shifter);
if (ShiftedFormula != null)
{
f.Value = (ShiftedFormula);
}
}
if (f.isSetRef())
{ //Range of cells which the formula applies to.
String ref1 = f.@ref;
String ShiftedRef = ShiftFormula(row, ref1, Shifter);
if (ShiftedRef != null)
{
f.@ref = ShiftedRef;
}
}
}
}
}
/// <summary>
/// Convert Excel sheets to DataTable list
/// </summary>
/// <param name="filename"></param>
/// <returns>list of datatable</returns>
public static List <DataTable> ExceltoDataTable(string filename)
{
XSSFWorkbook xssfwb;
List <DataTable> dts = new List <DataTable>();
using (FileStream file = new FileStream(filename, FileMode.Open, FileAccess.Read))
{
xssfwb = new XSSFWorkbook(file);
}
for (int i = 0; i < xssfwb.NumberOfSheets; i++)
{
XSSFSheet sheet = (XSSFSheet)xssfwb.GetSheetAt(i);
DataTable dt = new DataTable();
int num = 0;
while (sheet.GetRow(num) != null)
{
if (dt.Columns.Count < sheet.GetRow(num).Cells.Count)
{
for (int j = 0; j < sheet.GetRow(num).Cells.Count; j++)
{
dt.Columns.Add("", typeof(string));
}
}
XSSFRow row = (XSSFRow)sheet.GetRow(num);
DataRow dr = dt.Rows.Add();
for (int k = 0; k < row.Cells.Count; k++)
{
XSSFCell cell = (XSSFCell)row.GetCell(k);
if (cell != null)
{
switch (cell.CellType)
{
case CellType.Numeric:
dr[k] = cell.NumericCellValue;
break;
case CellType.String:
dr[k] = cell.StringCellValue;
break;
case CellType.Blank:
dr[k] = "";
break;
case CellType.Boolean:
dr[k] = cell.BooleanCellValue;
break;
}
}
}
num++;
}
dts.Add(dt);
}
return(dts);
}
public static XSSFWorkbook BuildSwitchData <T>(string SheetName, List <T> list, Dictionary <string, string> FiedNames)
{
XSSFWorkbook wb = new XSSFWorkbook();
XSSFSheet sheet = (XSSFSheet)wb.CreateSheet(SheetName); //创建工作表
sheet.CreateFreezePane(0, 1); //冻结列头行
XSSFRow row_Title = (XSSFRow)sheet.CreateRow(0); //创建列头行
#region 生成列头
int ii = 0;
foreach (string key in FiedNames.Keys)
{
XSSFCell cell_Title = (XSSFCell)row_Title.CreateCell(ii); //创建单元格
//cell_Title.CellStyle = cs_Title; //将样式绑定到单元格
cell_Title.SetCellValue(key);
//sheet.SetColumnWidth(ii, 25 * 256);//设置列宽
ii++;
}
#endregion
//获取 实体类 类型对象
Type t = typeof(T); // model.GetType();
//获取 实体类 所有的 公有属性
List <PropertyInfo> proInfos = t.GetProperties(BindingFlags.Instance | BindingFlags.Public).ToList();
//创建 实体属性 字典集合
Dictionary <string, PropertyInfo> dictPros = new Dictionary <string, PropertyInfo>();
//将 实体属性 中要修改的属性名 添加到 字典集合中 键：属性名 值：属性对象
proInfos.ForEach(p =>
{
if (FiedNames.Values.Contains(p.Name))
{
dictPros.Add(p.Name, p);
}
});
for (int i = 0; i < list.Count; i++)
{
XSSFRow row_Content = (XSSFRow)sheet.CreateRow(i + 1); //创建行
row_Content.HeightInPoints = 20;
int jj = 0;
foreach (string proName in FiedNames.Values)
{
if (dictPros.ContainsKey(proName))
{
XSSFCell cell_Conent = (XSSFCell)row_Content.CreateCell(jj); //创建单元格
//如果存在，则取出要属性对象
PropertyInfo proInfo = dictPros[proName];
//获取对应属性的值
object value = proInfo.GetValue(list[i], null); //object newValue = model.uName;
string cell_value = value == null ? "" : value.ToString();
cell_Conent.SetCellValue(cell_value);
jj++;
}
}
}
return(wb);
}
private void CopyCellStyle(HSSFCell oldCell, XSSFCell newCell)
{
if (oldCell.CellStyle == null)
{
return;
}
newCell.CellStyle = newCell.Sheet.Workbook.GetCellStyleAt((short)(oldCell.CellStyle.Index + 1));
}
private void GetSalaryList(ISheet mainSheet)
{
FileStream salaryFileStream = new FileStream(@"e:/2018年3月发薪名单.xlsx", FileMode.Open);
XSSFWorkbook salaryWorkbook = new XSSFWorkbook(salaryFileStream);
XSSFSheet salarySheet = (XSSFSheet)salaryWorkbook.GetSheet("Sheet1");
for (int i = 0; i < salarySheet.PhysicalNumberOfRows; i++)
{
XSSFRow tRow = (XSSFRow)mainSheet.CreateRow(i);
XSSFRow sRow = (XSSFRow)salarySheet.GetRow(i);
if (sRow != null && tRow != null)
{
for (int j = 0; j < 2; j++)
{
XSSFCell sCell = (XSSFCell)sRow.GetCell(j);
if (sCell == null)
{
break;
}
string cellValue = sCell.ToString();
XSSFCell tCell = (XSSFCell)tRow.CreateCell(j);
//CopyCellStyle(mainWorkbook, salaryWorkbook, tCell, sCell);
XSSFCellStyle style = (XSSFCellStyle)sCell.CellStyle;
XSSFCellStyle style1 = (XSSFCellStyle)mainWorkbook.CreateCellStyle();
XSSFColor color = null;
if (style.FillForegroundColorColor != null)
{
byte[] pa = style.FillForegroundColorColor.RGB;
string key = pa[0] + "," + pa[1] + "," + pa[2];
if (dictionary.ContainsKey(key))
{
style1.FillForegroundColor = dictionary[key];
}
else
{
Console.WriteLine("找不到该颜色!" + key);
style1.FillForegroundColor = HSSFColor.Automatic.Index;
}
}
else
{
Console.WriteLine("找不到该颜色!");
style1.FillForegroundColor = HSSFColor.Automatic.Index;
}
//byte[] pa1 = style.FillBackgroundColorColor.RGB;
//style1.FillForegroundColor = 20;//GetColor(sCell.CellStyle.FillForegroundColor);
style1.FillPattern = sCell.CellStyle.FillPattern;
//style1.FillBackgroundColor = 20;//GetColor(sCell.CellStyle.FillForegroundColor);
tCell.CellStyle = style1;
tCell.SetCellValue(cellValue);
}
}
}
salaryFileStream.Close();
salaryWorkbook.Close();
}
private void CopyFontStyle(IWorkbook wb, XSSFCell oldCell, XSSFCellStyle newCellStyle)
{
NPOI.SS.UserModel.IFont font = destinationWb.CreateFont();
NPOI.SS.UserModel.IFont sourceFont = oldCell.CellStyle.GetFont(wb);
font.FontName = sourceFont.FontName;
font.FontHeightInPoints = sourceFont.FontHeightInPoints;
font.Boldweight = sourceFont.Boldweight;
newCellStyle.SetFont(font);
}
/// <summary>
/// 读取2007以上版本.xlsx
/// </summary>
/// <param name="path"></param>
/// <returns></returns>
public static string Read2007ToString(string path)
{
XSSFWorkbook hssfworkbook;
path = HttpContext.Current.Server.MapPath(path);
using (FileStream file = new FileStream(path, FileMode.Open, FileAccess.Read))
{
hssfworkbook = new XSSFWorkbook(file);
}
XSSFSheet sheet = (XSSFSheet)hssfworkbook.GetSheetAt(0);
System.Collections.IEnumerator rows = sheet.GetRowEnumerator();
StringBuilder sb = new StringBuilder();
int irow = 0;
sb.Append("<table>");
while (rows.MoveNext())
{
XSSFRow row = (XSSFRow)rows.Current;
irow++;
sb.Append("<tr>");
for (int i = 0; i < row.LastCellNum; i++)
{
XSSFCell cell = (XSSFCell)row.GetCell(i);
string dr = "";
if (cell == null)
{
dr = "";
}
else
{
dr = cell.ToString();
}
sb.Append("<td>" + dr + "</td>");//("+irow+","+i+")"+
}
sb.Append("</tr>");
}
/*
* ②：将文档保存到指定路径
*/
string destFileName = @"D:\test.xlsx";
//HSSFWorkbook hssfworkbook2 = writeToExcel();
MemoryStream msfile = new MemoryStream();
hssfworkbook.Write(msfile);
System.IO.File.WriteAllBytes(destFileName, msfile.ToArray());
sb.Append("</table>");
return(sb.ToString());
}
/// <summary>
/// 获取单元格类型(xlsx)
/// </summary>
/// <param name="cell"></param>
/// <returns></returns>
private static object GetValueTypeForXLSX(XSSFCell cell)
{
if (cell == null)
{
return(null);
}
if (datetimeFormats.IndexOf(cell.CellStyle.GetDataFormatString()) > -1)
{
try
{
return(cell.DateCellValue);
}
catch { }
}
switch (cell.CellType)
{
case NPOI.SS.UserModel.CellType.BLANK:
return(null);
case NPOI.SS.UserModel.CellType.BOOLEAN:
return(cell.BooleanCellValue);
case NPOI.SS.UserModel.CellType.ERROR:
return(cell.ErrorCellValue);
case NPOI.SS.UserModel.CellType.NUMERIC:
return(cell.NumericCellValue);
case NPOI.SS.UserModel.CellType.STRING:
return(cell.StringCellValue);
case NPOI.SS.UserModel.CellType.Unknown:
return(null);
case NPOI.SS.UserModel.CellType.FORMULA:
default:
return("=" + cell.CellFormula);
}
//switch (cell.CellType)
//{
// case CellType.Blank: //BLANK:
// return null;
// case CellType.Boolean: //BOOLEAN:
// return cell.BooleanCellValue;
// case CellType.Numeric: //NUMERIC:
// return cell.NumericCellValue;
// case CellType.String: //STRING:
// return cell.StringCellValue;
// case CellType.Error: //ERROR:
// return cell.ErrorCellValue;
// case CellType.Formula: //FORMULA:
// default:
// return "=" + cell.CellFormula;
//}
}
private static void CopyRow(XSSFSheet srcSheet, HSSFSheet destSheet, XSSFRow srcRow, HSSFRow destRow,
Dictionary <int, XSSFCellStyle> styleMap, HSSFWorkbook retVal)
{
// manage a list of merged zone in order to not insert two times a
// merged zone
List <CellRangeAddress> mergedRegions = new List <CellRangeAddress>();
destRow.Height = srcRow.Height;
// pour chaque row
for (int j = srcRow.FirstCellNum; j <= srcRow.LastCellNum; j++)
{
XSSFCell oldCell = (XSSFCell)srcRow.GetCell(j); // ancienne cell
HSSFCell newCell = (HSSFCell)destRow.GetCell(j); // new cell
if (oldCell != null)
{
if (newCell == null)
{
newCell = (HSSFCell)destRow.CreateCell(j);
}
// copy chaque cell
CopyCell(oldCell, newCell, styleMap, retVal);
// copy les informations de fusion entre les cellules
CellRangeAddress mergedRegion = GetMergedRegion(srcSheet, srcRow.RowNum,
(short)oldCell.ColumnIndex);
if (mergedRegion != null)
{
CellRangeAddress newMergedRegion = new CellRangeAddress(mergedRegion.FirstRow,
mergedRegion.LastRow, mergedRegion.FirstColumn, mergedRegion.LastColumn);
if (IsNewMergedRegion(newMergedRegion, mergedRegions))
{
mergedRegions.Add(newMergedRegion);
destSheet.AddMergedRegion(newMergedRegion);
}
if (newMergedRegion.FirstColumn == 0 && newMergedRegion.LastColumn == 6 && newMergedRegion.FirstRow == newMergedRegion.LastRow)
{
HSSFCellStyle style2 = (HSSFCellStyle)retVal.CreateCellStyle();
style2.VerticalAlignment = VerticalAlignment.Center;
style2.Alignment = HorizontalAlignment.Left;
style2.FillForegroundColor = HSSFColor.Teal.Index;
style2.FillPattern = FillPattern.SolidForeground;
for (int i = destRow.FirstCellNum; i <= destRow.LastCellNum; i++)
{
if (destRow.GetCell(i) != null)
{
destRow.GetCell(i).CellStyle = style2;
}
}
}
}
}
}
}
public static XSSFSheet ChangeColor(XSSFSheet sheet, int x, int y, Color color, XSSFCellStyle cellstyle)
{
XSSFRow row = (XSSFRow)sheet.GetRow(x);
XSSFCell cell = (XSSFCell)row.GetCell(y);
XSSFColor XlColour = new XSSFColor(color);
cellstyle.SetFillForegroundColor(XlColour);
cellstyle.FillPattern = NPOI.SS.UserModel.FillPattern.SolidForeground;
cell.CellStyle = cellstyle;
return(sheet);
}
private static void CopyCell(XSSFCell oldCell, HSSFCell newCell, Dictionary <int, XSSFCellStyle> styleMap, HSSFWorkbook retVal)
{
if (styleMap != null)
{
int stHashCode = oldCell.CellStyle.Index;
XSSFCellStyle sourceCellStyle = null;
if (styleMap.TryGetValue(stHashCode, out sourceCellStyle))
{
}
HSSFCellStyle destnCellStyle = (HSSFCellStyle)newCell.CellStyle;
if (sourceCellStyle == null)
{
sourceCellStyle = (XSSFCellStyle)oldCell.Sheet.Workbook.CreateCellStyle();
}
// destnCellStyle.CloneStyleFrom(oldCell.CellStyle);
if (!styleMap.Any(p => p.Key == stHashCode))
{
styleMap.Add(stHashCode, sourceCellStyle);
}
destnCellStyle.VerticalAlignment = VerticalAlignment.Top;
newCell.CellStyle = (HSSFCellStyle)destnCellStyle;
}
switch (oldCell.CellType)
{
case CellType.String:
newCell.SetCellValue(oldCell.StringCellValue);
break;
case CellType.Numeric:
newCell.SetCellValue(oldCell.NumericCellValue);
break;
case CellType.Blank:
newCell.SetCellType(CellType.Blank);
break;
case CellType.Boolean:
newCell.SetCellValue(oldCell.BooleanCellValue);
break;
case CellType.Error:
newCell.SetCellErrorValue(oldCell.ErrorCellValue);
break;
case CellType.Formula:
newCell.SetCellFormula(oldCell.CellFormula);
break;
default:
break;
}
}
public void TestMultisheetFormulaEval()
{
XSSFWorkbook wb = new XSSFWorkbook();
try
{
XSSFSheet sheet1 = wb.CreateSheet("Sheet1") as XSSFSheet;
XSSFSheet sheet2 = wb.CreateSheet("Sheet2") as XSSFSheet;
XSSFSheet sheet3 = wb.CreateSheet("Sheet3") as XSSFSheet;
// sheet1 A1
XSSFCell cell = sheet1.CreateRow(0).CreateCell(0) as XSSFCell;
cell.SetCellType(CellType.Numeric);
cell.SetCellValue(1.0);
// sheet2 A1
cell = sheet2.CreateRow(0).CreateCell(0) as XSSFCell;
cell.SetCellType(CellType.Numeric);
cell.SetCellValue(1.0);
// sheet2 B1
cell = sheet2.GetRow(0).CreateCell(1) as XSSFCell;
cell.SetCellType(CellType.Numeric);
cell.SetCellValue(1.0);
// sheet3 A1
cell = sheet3.CreateRow(0).CreateCell(0) as XSSFCell;
cell.SetCellType(CellType.Numeric);
cell.SetCellValue(1.0);
// sheet1 A2 formulae
cell = sheet1.CreateRow(1).CreateCell(0) as XSSFCell;
cell.SetCellType(CellType.Formula);
cell.CellFormula = (/*setter*/ "SUM(Sheet1:Sheet3!A1)");
// sheet1 A3 formulae
cell = sheet1.CreateRow(2).CreateCell(0) as XSSFCell;
cell.SetCellType(CellType.Formula);
cell.CellFormula = (/*setter*/ "SUM(Sheet1:Sheet3!A1:B1)");
wb.GetCreationHelper().CreateFormulaEvaluator().EvaluateAll();
cell = sheet1.GetRow(1).GetCell(0) as XSSFCell;
Assert.AreEqual(3.0, cell.NumericCellValue, 0);
cell = sheet1.GetRow(2).GetCell(0) as XSSFCell;
Assert.AreEqual(4.0, cell.NumericCellValue, 0);
}
finally
{
wb.Close();
}
}
/// <summary>
/// 文件为xlsx 2007
/// </summary>
/// <param name="row"></param>
/// <param name="dr"></param>
/// <param name="i"></param>
private static void GetCellValue(XSSFRow row, DataRow dr, int i)
{
XSSFCell cell = row.GetCell(i) as XSSFCell;
if (cell != null)
{
switch (cell.CellType)
{
case CellType.Blank:
dr[i] = null;
break;
case CellType.Boolean:
dr[i] = cell.BooleanCellValue;
break;
case CellType.Numeric:
////This is a trick to get the correct value of the cell. NumericCellValue will return a numeric value no matter the cell value is a date or a number.
if (DateTime.Compare(cell.DateCellValue, DateTime.Parse("1900-01-01")) > 0)
{
dr[i] = cell.DateCellValue;
}
else
{
dr[i] = cell.ToString();
}
//if (HSSFDateUtil.IsCellDateFormatted(cell))
//{
// dr[i] = cell.DateCellValue;
//}
//if (cell.CellType == NPOI.SS.UserModel.CellType.NUMERIC)
//{
// dr[i] = cell.NumericCellValue;
//}
break;
case CellType.String:
dr[i] = cell.StringCellValue;
break;
case CellType.Error:
dr[i] = cell.ErrorCellValue;
break;
case CellType.Formula:
default:
dr[i] = cell.NumericCellValue;
break;
}
}
}
public void EvaluateInCellReturnsSameDataType()
{
XSSFWorkbook wb = new XSSFWorkbook();
wb.CreateSheet().CreateRow(0).CreateCell(0);
XSSFFormulaEvaluator evaluator = wb.GetCreationHelper().CreateFormulaEvaluator() as XSSFFormulaEvaluator;
XSSFCell cell = wb.GetSheetAt(0).GetRow(0).GetCell(0) as XSSFCell;
XSSFCell same = evaluator.EvaluateInCell(cell) as XSSFCell;
//assertSame(cell, same);
Assert.AreSame(cell, same);
wb.Close();
}
/// <summary>读取excel2007
/// 默认第一行为标头
/// </summary>
/// <param name="strFileName">excel文档路径</param>
/// <returns></returns>
private static DataTable Import2007(string strFileName)
{
//2013/10/22 夏梁峰 add begin
//修改使用NPOI读取excel
//需求编号：OTS_SZDX_01_R00027_D00001
XSSFWorkbook hssfworkbook;
using (FileStream file = new FileStream(strFileName, FileMode.Open, FileAccess.Read))
{
hssfworkbook = new XSSFWorkbook(file);
}
DataTable dt = new DataTable();
ISheet sheet = hssfworkbook.GetSheetAt(0);
System.Collections.IEnumerator rows = sheet.GetRowEnumerator();
XSSFRow headerRow = sheet.GetRow(0) as XSSFRow;
int cellCount = headerRow.LastCellNum;
for (int j = 0; j < cellCount; j++)
{
XSSFCell cell = headerRow.GetCell(j) as XSSFCell;
dt.Columns.Add(cell.ToString());
}
for (int i = (sheet.FirstRowNum + 1); i <= sheet.LastRowNum; i++)
{
XSSFRow row = sheet.GetRow(i) as XSSFRow;
DataRow dataRow = dt.NewRow();
for (int j = row.FirstCellNum; j < cellCount; j++)
{
if (row.GetCell(j) != null)
{
dataRow[j] = row.GetCell(j).ToString();
}
}
dt.Rows.Add(dataRow);
}
return(dt);
//2013/10/22 夏梁峰 add end
//2013/10/22 夏梁峰 删除
//string strCon = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + strFileName + ";Extended Properties=\"Excel 12.0;HDR=YES\"";
//OleDbConnection myConn = new OleDbConnection(strCon);
//string strCom = " SELECT * FROM [Sheet1$]";
//myConn.Open();
//OleDbDataAdapter myCommand = new OleDbDataAdapter(strCom, myConn);
//DataSet myDataSet = new DataSet();
//myCommand.Fill(myDataSet, "[Sheet1$]");
//myConn.Close();
//return myDataSet.Tables[0];
}
//处理excel2007
private static void FillDataRowByHSSFRow(XSSFRow row, XSSFFormulaEvaluator evaluator, ref DataRow dr)
{
if (row != null)
{
for (int j = 0; j < dr.Table.Columns.Count; j++)
{
XSSFCell cell = row.GetCell(j) as XSSFCell;
if (cell != null)
{
switch (cell.CellType)
{
case CellType.BLANK:
dr[j] = DBNull.Value;
break;
case CellType.BOOLEAN:
dr[j] = cell.BooleanCellValue;
break;
case CellType.NUMERIC:
if (DateUtil.IsCellDateFormatted(cell))
{
dr[j] = cell.DateCellValue;
}
else
{
dr[j] = cell.NumericCellValue;
}
break;
case CellType.STRING:
dr[j] = cell.StringCellValue;
break;
case CellType.ERROR:
dr[j] = cell.ErrorCellValue;
break;
case CellType.FORMULA:
cell = evaluator.EvaluateInCell(cell) as XSSFCell;
dr[j] = cell.ToString();
break;
default:
throw new NotSupportedException(string.Format("Catched unhandle CellType[{0}]", cell.CellType));
}
}
}
}
}
/// <summary>
/// Render DataTable to NPOI Excel 2003 MemoryStream
/// NOTE: Limitation of 65,536 rows suppored by XLS
/// </summary>
/// <param name="sourceTable">Source DataTable</param>
/// <returns>MemoryStream containing NPOI Excel workbook</returns>
public static Stream RenderDataTableToExcelXSSF(DataTable sourceTable)
{
XSSFWorkbook xssfworkbook = new XSSFWorkbook();
MemoryStream memoryStream = new MemoryStream();
// By default NPOI creates "Sheet0" which is inconsistent with Excel using "Sheet1"
XSSFSheet sheet = (XSSFSheet)xssfworkbook.CreateSheet("Sheet1");
XSSFRow headerRow = (XSSFRow)sheet.CreateRow(0);
// Header Row
foreach (DataColumn column in sourceTable.Columns)
{
headerRow.CreateCell(column.Ordinal).SetCellValue(column.ColumnName);
}
// Detail Rows
int rowIndex = 1;
// 建立儲存格樣式。
XSSFCellStyle style1 = (XSSFCellStyle)xssfworkbook.CreateCellStyle();//workbook.CreateCellStyle();
style1.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.Blue.Index2;
style1.FillBackgroundColor = NPOI.HSSF.Util.HSSFColor.Red.Index;
style1.FillPattern = NPOI.SS.UserModel.FillPattern.SolidForeground;//HSSFCellStyle.SOLID_FOREGROUND;
XSSFFont f = (XSSFFont)xssfworkbook.CreateFont();
f.Color = NPOI.HSSF.Util.HSSFColor.Red.Index;
f.FontName = "宋体";
foreach (DataRow row in sourceTable.Rows)
{
XSSFRow dataRow = (XSSFRow)sheet.CreateRow(rowIndex);
foreach (DataColumn column in sourceTable.Columns)
{
//HSSFCell cell1 = dataRow.CreateCell(column.Ordinal).SetCellValue(row[column].ToString());
XSSFCell cell = (XSSFCell)dataRow.CreateCell(column.Ordinal);
cell.CellStyle = style1;
cell.CellStyle.SetFont(f);
cell.SetCellValue(row[column].ToString());
}
rowIndex++;
}
xssfworkbook.Write(memoryStream);
memoryStream.Flush();
memoryStream.Position = 0;
return(memoryStream);
}
private static void CopyBordersStyle(XSSFCell oldCell, XSSFCellStyle newCellStyle)
{
byte[] rgb = new byte[3] {
0, 0, 0
};
newCellStyle.BorderBottom = oldCell.CellStyle.BorderBottom;
newCellStyle.SetBottomBorderColor(new XSSFColor(rgb));
newCellStyle.BorderLeft = oldCell.CellStyle.BorderLeft;
newCellStyle.SetLeftBorderColor(new XSSFColor(rgb));
newCellStyle.BorderTop = oldCell.CellStyle.BorderTop;
newCellStyle.SetTopBorderColor(new XSSFColor(rgb));
newCellStyle.BorderRight = oldCell.CellStyle.BorderRight;
newCellStyle.SetRightBorderColor(new XSSFColor(rgb));
}
/// <summary>
/// 读取2007以上版本.xlsx
/// </summary>
/// <param name="path"></param>
/// <returns></returns>
public static DataTable Read2007ToTable(string path)
{
XSSFWorkbook hssfworkbook;
path = HttpContext.Current.Server.MapPath(path);
using (FileStream file = new FileStream(path, FileMode.Open, FileAccess.Read))
{
hssfworkbook = new XSSFWorkbook(file);
}
XSSFSheet sheet = (XSSFSheet)hssfworkbook.GetSheetAt(0);
System.Collections.IEnumerator rows = sheet.GetRowEnumerator();
DataTable dt = new DataTable();
bool firstr = true;
while (rows.MoveNext())
{
XSSFRow row = (XSSFRow)rows.Current;
#region 第一行，初始化dt
if (firstr)
{
for (int j = 0; j < row.LastCellNum; j++)
{
dt.Columns.Add("column" + j);
}
firstr = false;
}
#endregion
for (int i = 0; i < row.LastCellNum; i++)
{
XSSFCell cell = (XSSFCell)row.GetCell(i);
DataRow dr = dt.NewRow();
if (cell == null)
{
dr[i] = null;
}
else
{
dr[i] = cell.ToString();
}
dt.Rows.Add(dr);
}
}
return(dt);
}
public void Bug51158()
{
// create a workbook
XSSFWorkbook wb1 = new XSSFWorkbook();
XSSFSheet sheet = wb1.CreateSheet("Test Sheet") as XSSFSheet;
XSSFRow row = sheet.CreateRow(2) as XSSFRow;
XSSFCell cell = row.CreateCell(3) as XSSFCell;
cell.SetCellValue("test1");
//XSSFCreationHelper helper = workbook.GetCreationHelper();
//cell.Hyperlink=(/*setter*/helper.CreateHyperlink(0));
XSSFComment comment = (sheet.CreateDrawingPatriarch() as XSSFDrawing).CreateCellComment(new XSSFClientAnchor()) as XSSFComment;
Assert.IsNotNull(comment);
comment.SetString("some comment");
// ICellStyle cs = workbook.CreateCellStyle();
// cs.ShrinkToFit=(/*setter*/false);
// row.CreateCell(0).CellStyle=(/*setter*/cs);
// write the first excel file
XSSFWorkbook wb2 = XSSFTestDataSamples.WriteOutAndReadBack(wb1) as XSSFWorkbook;
Assert.IsNotNull(wb2);
sheet = wb2.GetSheetAt(0) as XSSFSheet;
row = sheet.GetRow(2) as XSSFRow;
Assert.AreEqual("test1", row.GetCell(3).StringCellValue);
Assert.IsNull(row.GetCell(4));
// add a new cell to the sheet
cell = row.CreateCell(4) as XSSFCell;
cell.SetCellValue("test2");
// write the second excel file
XSSFWorkbook wb3 = XSSFTestDataSamples.WriteOutAndReadBack(wb2) as XSSFWorkbook;
Assert.IsNotNull(wb3);
sheet = wb3.GetSheetAt(0) as XSSFSheet;
row = sheet.GetRow(2) as XSSFRow;
Assert.AreEqual("test1", row.GetCell(3).StringCellValue);
Assert.AreEqual("test2", row.GetCell(4).StringCellValue);
wb3.Close();
wb2.Close();
wb1.Close();
}
/**
* @param srcSheet
* the sheet to copy.
* @param destSheet
* the sheet to create.
* @param srcRow
* the row to copy.
* @param destRow
* the row to create.
* @param styleMap
* -
*/
public static void copyRow(HSSFSheet srcSheet, XSSFSheet destSheet, HSSFRow srcRow, XSSFRow destRow,
Dictionary <int, HSSFCellStyle> styleMap)
{
// manage a list of merged zone in order to not insert two times a
// merged zone
List <CellRangeAddress> mergedRegions = new List <CellRangeAddress>();
destRow.Height = srcRow.Height;
// pour chaque row
for (int j = srcRow.FirstCellNum; j <= srcRow.LastCellNum; j++)
{
HSSFCell oldCell = (HSSFCell)srcRow.GetCell(j); // ancienne cell
XSSFCell newCell = (XSSFCell)destRow.GetCell(j); // new cell
if (oldCell != null)
{
if (newCell == null)
{
newCell = (XSSFCell)destRow.CreateCell(j);
}
// copy chaque cell
copyCell(oldCell, newCell, styleMap);
// copy les informations de fusion entre les cellules
// System.out.println("row num: " + srcRow.getRowNum() +
// " , col: " + (short)oldCell.getColumnIndex());
CellRangeAddress mergedRegion = getMergedRegion(srcSheet, srcRow.RowNum,
(short)oldCell.ColumnIndex);
if (mergedRegion != null)
{
// System.out.println("Selected merged region: " +
// mergedRegion.toString());
CellRangeAddress newMergedRegion = new CellRangeAddress(mergedRegion.FirstRow,
mergedRegion.LastRow, mergedRegion.FirstColumn, mergedRegion.LastColumn);
// System.out.println("New merged region: " +
// newMergedRegion.toString());
/*
* CellRangeAddress wrapper = new CellRangeAddress(newMergedRegion);
* CellRangeAddress ce=new CellRangeAddress ()
* if (isNewMergedRegion(wrapper, mergedRegions))
* {
* mergedRegions.add(wrapper);
* destSheet.addMergedRegion(wrapper.range);
* }*/
}
}
}
}
/**
* Parse cell formula and re-assemble it back using the specified FormulaRenderingWorkbook.
*
* @param cell the cell to update
* @param frwb the formula rendering workbbok that returns new sheet name
*/
private void UpdateFormula(XSSFCell cell, IFormulaRenderingWorkbook frwb)
{
CT_CellFormula f = cell.GetCTCell().f;
if (f != null)
{
String formula = f.Value;
if (formula != null && formula.Length > 0)
{
int sheetIndex = _wb.GetSheetIndex(cell.Sheet);
Ptg[] ptgs = FormulaParser.Parse(formula, _fpwb, FormulaType.CELL, sheetIndex);
String updatedFormula = FormulaRenderer.ToFormulaString(frwb, ptgs);
if (!formula.Equals(updatedFormula)) f.Value = (updatedFormula);
}
}
}
/**
* Parse cell formula and re-assemble it back using the specified FormulaRenderingWorkbook.
*
* @param cell the cell to update
* @param frwb the formula rendering workbbok that returns new sheet name
*/
private void UpdateFormula(XSSFCell cell, String oldName, String newName)
{
CT_CellFormula f = cell.GetCTCell().f;
if (f != null)
{
String formula = f.Value;
if (formula != null && formula.Length > 0)
{
int sheetIndex = _wb.GetSheetIndex(cell.Sheet);
Ptg[] ptgs = FormulaParser.Parse(formula, _fpwb, FormulaType.Cell, sheetIndex);
foreach (Ptg ptg in ptgs)
{
UpdatePtg(ptg, oldName, newName);
}
String updatedFormula = FormulaRenderer.ToFormulaString(_fpwb, ptgs);
if (!formula.Equals(updatedFormula)) f.Value = (updatedFormula);
}
}
}
