/// <summary>
/// 读取A1相关信息 初始化excel实体
/// </summary>
bool readHeader()
{
bool result = true;
if (fileName.Contains("轨道区段与道岔"))
{
tm = new TableMapping(fileName);
sheet = (HSSFSheet)workbook.GetSheetAt(0);
}
else
{
//获取A1内容
sheet = (HSSFSheet)workbook.GetSheetAt(0);
System.Collections.IEnumerator rows = sheet.GetRowEnumerator();
rows.MoveNext();
HSSFRow row = (HSSFRow)rows.Current;
cell = (HSSFCell)row.GetCell(0);
if (cell != null && !string.IsNullOrEmpty(cell.ToString()))
{
tm = new TableMapping(cell.ToString().Trim());
tm.SheetName = sheet.SheetName;
if (string.IsNullOrEmpty(tm.header))
{
reportError("没有获取到类型数据");
result = false;
}
}
else
{
reportError("A1无数据");
result = false;
}
}
return(result);
}
/// <summary>读取excel
/// 默认第一行为标头
/// </summary>
/// <param name="strFileName">excel文档路径</param>
/// <returns></returns>
public static DataTable ImportExcel(Stream fileStream)
{
DataTable dt = new DataTable();
HSSFWorkbook hssfworkbook;
hssfworkbook = new HSSFWorkbook(fileStream);
HSSFSheet sheet = (HSSFSheet)hssfworkbook.GetSheetAt(0);
System.Collections.IEnumerator rows = sheet.GetRowEnumerator();
HSSFRow headerRow = (HSSFRow)sheet.GetRow(0);
int cellCount = headerRow.LastCellNum;
for (int j = 0; j < cellCount; j++)
{
HSSFCell cell = (HSSFCell)headerRow.GetCell(j);
dt.Columns.Add(cell.ToString());
}
for (int i = (sheet.FirstRowNum + 1); i <= sheet.LastRowNum; i++)
{
HSSFRow row = (HSSFRow)sheet.GetRow(i);
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);
}
/// <summary>
/// 将Excel转成DataTable
/// </summary>
/// <param name="fileupload">上传控件实例引用</param>
/// <returns></returns>
public static DataTable ImportExcelByNPOI(FileUpload fileupload)
{
DataTable dt = new DataTable();
using (FileStream file = new FileStream(fileupload.PostedFile.FileName, FileMode.Open, FileAccess.Read))
{
HSSFWorkbook hssfworkbook = new HSSFWorkbook(file);
HSSFSheet sheet = (HSSFSheet)hssfworkbook.GetSheetAt(0);
System.Collections.IEnumerator rows = sheet.GetRowEnumerator();
//将Excel内数据读取到DataTable 中
while (rows.MoveNext())
{
HSSFRow row = (HSSFRow)rows.Current;
DataRow dr = dt.NewRow();
for (int i = 0; i < row.LastCellNum; i++)
{
HSSFCell cell = (HSSFCell)row.GetCell(i);
if (cell == null)
{
dr[i] = null;
}
else
{
dr[i] = cell.ToString();
}
}
dt.Rows.Add(dr);
}
}
return(dt);
}
/// <summary>读取excel
/// 默认第一行为标头
/// </summary>
/// <param name="path">excel文档路径</param>
/// <returns></returns>
public static DataTable RenderDataTableFromExcel(string path)
{
DataTable dt = new DataTable();
HSSFWorkbook hssfworkbook;
using (FileStream file = new FileStream(path, FileMode.Open, FileAccess.Read))
{
hssfworkbook = new HSSFWorkbook(file);
}
HSSFSheet sheet = hssfworkbook.GetSheetAt(0);
System.Collections.IEnumerator rows = sheet.GetRowEnumerator();
HSSFRow headerRow = sheet.GetRow(0);
int cellCount = headerRow.LastCellNum;
for (int j = 0; j < cellCount; j++)
{
HSSFCell cell = headerRow.GetCell(j);
dt.Columns.Add(cell.ToString());
}
for (int i = (sheet.FirstRowNum + 1); i <= sheet.LastRowNum; i++)
{
HSSFRow row = sheet.GetRow(i);
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);
}
//while (rows.MoveNext())
//{
// HSSFRow row = (HSSFRow)rows.Current;
// DataRow dr = dt.NewRow();
// for (int i = 0; i < row.LastCellNum; i++)
// {
// HSSFCell 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>
/// <returns></returns>
private string GetCellValue(HSSFCell cell)
{
if (cell == null)
{
return("");
}
return(cell.ToString().Trim());//修改于11-07-07 添加了Trim()
}
/// <summary>
/// 读取2007以上版本.xlsx
/// </summary>
/// <param name="path"></param>
/// <returns></returns>
public static string Read2003ToString(string path, string name)
{
HSSFWorkbook hssfworkbook;
var fpath = HttpContext.Current.Server.MapPath(path + name);
using (FileStream file = new FileStream(fpath, FileMode.Open, FileAccess.Read))
{
hssfworkbook = new HSSFWorkbook(file);
}
HSSFSheet sheet = (HSSFSheet)hssfworkbook.GetSheetAt(0);
System.Collections.IEnumerator rows = sheet.GetRowEnumerator();
StringBuilder sb = new StringBuilder();
int irow = 0;
sb.Append("<table>");
while (rows.MoveNext())
{
HSSFRow row = (HSSFRow)rows.Current;
irow++;
sb.Append("<tr>");
for (int i = 0; i < row.LastCellNum; i++)
{
HSSFCell cell = (HSSFCell)row.GetCell(i);
string dr = "";
if (cell == null)
{
dr = "";
}
else
{
dr = cell.ToString();
if (dr == "$ClientName$")
{
cell.SetCellValue("这是写名称");
}
if (dr == "$Content$")
{
cell.SetCellValue("这是写联系人");
}
}
sb.Append("<td>" + dr + "</td>");//("+irow+","+i+")"+
}
sb.Append("</tr>");
} /*
* ②：将文档保存到指定路径
*/
var destFileName = HttpContext.Current.Server.MapPath(path + "new_" + name);
//HSSFWorkbook hssfworkbook2 = writeToExcel();
MemoryStream msfile = new MemoryStream();
hssfworkbook.Write(msfile);
System.IO.File.WriteAllBytes(destFileName, msfile.ToArray());
sb.Append("</table>");
return(sb.ToString());
}
private DataTable XlsToDataTable(HSSFWorkbook hw)
{
int hasNull = 0;
try
{
DataTable dt = new DataTable();
HSSFSheet sheet = (HSSFSheet)hw.GetSheetAt(SheetNum);
IEnumerator rows = sheet.GetRowEnumerator();
for (int i = 0; i < MaxColumNum; i++)
{
dt.Columns.Add(GetColumnName(i + 1).Trim());
}
while (rows.MoveNext())
{
hasNull = 0;
HSSFRow row = (HSSFRow)rows.Current;
DataRow dr = dt.NewRow();
for (int i = 0; i < MaxColumNum; i++)
{
HSSFCell cell = (HSSFCell)row.GetCell(i);
if (cell == null)
{
dr[i] = null;
}
else
{
dr[i] = cell.ToString().Trim();
}
}
foreach (object obj in dr.ItemArray)
{
if (string.IsNullOrEmpty(obj.ToString().Trim()))
{
hasNull++;
}
}
if (hasNull < MaxColumNum)
{
dt.Rows.Add(dr);
}
}
if (dt.Rows.Count < 1)
{
return(null);
}
else
{
return(dt);
}
}
catch (Exception ex)
{
throw ex;
}
}
public void InsertPageBreaks(HSSFWorkbook workbook, string shtName)
{
//Find instances of the word "pagebreak" and replace with a page break
HSSFSheet sheet = (HSSFSheet)workbook.GetSheet(shtName);
int cellctr = 0;
HSSFCell range = (HSSFCell)sheet.GetRow(cellctr).GetCell(0);
while (range == null || range.ToString() != "EOF")
{
if (range != null && range.ToString() == "pagebreak")
{
sheet.SetRowBreak(cellctr);
range.SetCellValue(string.Empty);
}
cellctr++;
range = (HSSFCell)sheet.GetRow(cellctr).GetCell(0);
}
range.SetCellValue(string.Empty);
range = (HSSFCell)sheet.GetRow(0).GetCell(0);
}
/// <summary>
/// 添加DataTable表数据
/// </summary>
bool addDataToDataTable()
{
System.Collections.IEnumerator rows = sheet.GetRowEnumerator();
int index = 0;
while (index < rowE)
{
rows.MoveNext();
index++;
if (index < rowS)
{
continue;
}
HSSFRow row = (HSSFRow)rows.Current;
if (row != null)
{
DataRow dr = dt.NewRow();
for (int i = columnS; i <= columnE; i++)
{
cell = (HSSFCell)row.GetCell(i - 1);
if (cell == null)
{
continue;
}
if (cell.CellType == CellType.FORMULA)
{
dr[i - columnS] = (int)cell.NumericCellValue;
}
else
{
dr[i - columnS] = cell.ToString();
}
}
//GroupID
dr[columnE - columnS + 1] = tm.GUIDValue;
//Category
if (tm.IsCategoryNeeded)
{
dr[columnE - columnS + 2] = tm.CategoryValue;
}
dt.Rows.Add(dr);
}
else
{
reportError(string.Format("解析sheet数据:{0}失败", sheet.SheetName));
return(false);
}
}
recordResult(string.Format("解析sheet数据:{0}成功", sheet.SheetName));
return(true);
}
/// <summary>
///
/// </summary>
/// <param name="dtSource"></param>
/// <param name="strFileName"></param>
/// <remarks></remarks>
/// <Author></Author>
public static DataTable FromExcel(string filePath, string sheetname, int StartRow, int StartColumns, int LenColumns)
{
DataTable dt = new DataTable();
HSSFWorkbook hssfWorkbook = new HSSFWorkbook(new FileStream(filePath, FileMode.Open));
for (int sheetIndex = 0; sheetIndex < hssfWorkbook.NumberOfSheets; ++sheetIndex)
{
HSSFSheet hssfSheet = hssfWorkbook.GetSheetAt(sheetIndex) as HSSFSheet;
if (hssfSheet.SheetName == sheetname)
{
HSSFRow currentRow = hssfSheet.GetRow(StartRow) as HSSFRow;
for (int columnIndex = StartColumns; columnIndex < StartColumns + LenColumns; columnIndex++)
{
HSSFCell currentCell = currentRow.GetCell(columnIndex) as HSSFCell;
string title = currentCell.ToString().Trim();
bool b = true;
for (int s = 0; s < dt.Columns.Count; s++)
{
if (dt.Columns[s].ColumnName == title)
{
dt.Columns.Add(title + "1");
b = false;
break;
}
}
if (b == true)
{
dt.Columns.Add(title);
}
}
for (int j = StartRow + 1; j <= hssfSheet.LastRowNum; j++)
{
HSSFRow cRow = hssfSheet.GetRow(j) as HSSFRow;
if (cRow != null)
{
DataRow dw = dt.NewRow();
for (int columnIndex = StartColumns; columnIndex < StartColumns + LenColumns; columnIndex++)
{
HSSFCell cCell = cRow.GetCell(columnIndex) as HSSFCell;
if (cCell != null)
{
dw[columnIndex] = cCell.ToString().Trim();
}
}
dt.Rows.Add(dw);
}
}
}
}
return(dt);
}
/// <summary>
/// 定位sheet内有效坐标
/// </summary>
/// 利用泛型
bool convertExcelToDataTable <T>() where T : class
{
dt = new DataTable();
System.Collections.IEnumerator rows = sheet.GetRowEnumerator();
int index = 0;
while (index < rowE)
{
rows.MoveNext();
index++;
if (index < rowS)
{
continue;
}
HSSFRow row = (HSSFRow)rows.Current;
if (row != null)
{
if (index == rowS)
{
//通过反射提取表头注入datatable
Type type = typeof(T);
PropertyInfo[] pArray = type.GetProperties();
foreach (var p in pArray)
{
dt.Columns.Add(p.Name);
}
columnE = columnS + pArray.Length - 1;
}
else
{
DataRow dr = dt.NewRow();
for (int i = columnS; i <= columnE; i++)
{
cell = (HSSFCell)row.GetCell(i - 1);
if (cell == null)
{
continue;
}
dr[i - columnS] = cell.ToString();
}
dt.Rows.Add(dr);
}
}
else
{
return(false);
}
}
return(true);
}
/// <summary>读取excel
/// 默认第一行为标头
/// </summary>
/// <param name="path">excel文档路径</param>
/// <returns></returns>
public static DataTable RenderDataTableFromExcel(string path)
{
DataTable dt = new DataTable();
HSSFWorkbook hssfworkbook;
using (FileStream file = new FileStream(path, FileMode.Open, FileAccess.Read))
{
hssfworkbook = new HSSFWorkbook(file);
}
HSSFSheet sheet = (HSSFSheet)hssfworkbook.GetSheetAt(0);
System.Collections.IEnumerator rows = sheet.GetRowEnumerator();
HSSFRow headerRow = (HSSFRow)sheet.GetRow(0);
int cellCount = headerRow.LastCellNum;
for (int j = 0; j < cellCount; j++)
{
HSSFCell cell = (HSSFCell)headerRow.GetCell(j);
if (cell == null)
{
dt.Columns.Add("");
}
else
{
dt.Columns.Add(cell.ToString());
}
}
for (int i = (sheet.FirstRowNum + 1); i <= sheet.LastRowNum; i++)
{
HSSFRow row = (HSSFRow)sheet.GetRow(i);
if (row == null)
{
continue;
}
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);
}
/// <summary>
/// 文件为xls 2003
/// </summary>
/// <param name="row"></param>
/// <param name="dr"></param>
/// <param name="i"></param>
private static void GetCellValue(HSSFRow row, DataRow dr, int i)
{
HSSFCell cell = row.GetCell(i) as HSSFCell;
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 static string GetStringCellValue(this HSSFSheet sheet, int row, string AlphaBet)
{
HSSFCell cell = sheet.GetRow(row).GetCell(Col(AlphaBet));
if (cell == null)
{
return("");
}
else
{
return(cell.ToString().Trim());
}
}
/// <summary>读取excel
/// 默认第一行为标头
/// </summary>
/// <param name="strFileName">excel文档路径</param>
/// <param name="nameList">指定列命名</param>
/// <returns></returns>
public static DataTable Import(string strFileName, string[] nameList)
{
DataTable dt = new DataTable();
HSSFWorkbook hssfworkbook;
using (FileStream file = new FileStream(strFileName, FileMode.Open, FileAccess.Read))
{
hssfworkbook = new HSSFWorkbook(file);
}
HSSFSheet sheet = (HSSFSheet)hssfworkbook.GetSheetAt(0);
System.Collections.IEnumerator rows = sheet.GetRowEnumerator();
HSSFRow headerRow = (HSSFRow)sheet.GetRow(0);
int cellCount = headerRow.LastCellNum;
if (nameList.Length == cellCount)//如果指定的列总数跟导入的一样，指定为列命名
{
for (int j = 0; j < nameList.Length; j++)
{
HSSFCell cell = (HSSFCell)headerRow.GetCell(j);
dt.Columns.Add(nameList[j]);
}
}
else
{
for (int j = 0; j < cellCount; j++)
{
HSSFCell cell = (HSSFCell)headerRow.GetCell(j);
dt.Columns.Add(cell.ToString());
}
}
for (int i = (sheet.FirstRowNum + 1); i <= sheet.LastRowNum; i++)
{
HSSFRow row = (HSSFRow)sheet.GetRow(i);
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);
}
/// <summary>
/// 将xls文件流读取为datatable
/// </summary>
/// <param name="stream"></param>
public static DataTable ReadXlsToDataTable(Stream stream)
{
DataTable dt = new DataTable();
IWorkbook hssfworkbook = new HSSFWorkbook(stream);
ISheet sheet = hssfworkbook.GetSheetAt(0);
System.Collections.IEnumerator rows = sheet.GetRowEnumerator();
HSSFRow headerRow = (HSSFRow)sheet.GetRow(0);
//一行最后一个方格的编号 即总的列数
for (int j = 0; j < (sheet.GetRow(0).LastCellNum); j++)
{
//SET EVERY COLUMN NAME
HSSFCell cell = (HSSFCell)headerRow.GetCell(j);
dt.Columns.Add(cell.ToString());
}
while (rows.MoveNext())
{
IRow row = (HSSFRow)rows.Current;
DataRow dr = dt.NewRow();
if (row.RowNum == 0)
{
continue; //The firt row is title,no need import
}
for (int i = 0; i < row.LastCellNum; i++)
{
ICell cell = row.GetCell(i);
if (cell == null)
{
dr[i] = null;
}
if (cell.CellType == CellType.Numeric && DateUtil.IsCellDateFormatted(cell))
{
dr[i] = cell.DateCellValue;
}
else
{
dr[i] = cell.ToString();
}
}
dt.Rows.Add(dr);
}
return(dt);
}
public static DataTable ConvertToDataTable(HSSFWorkbook hssfworkbook)
{
HSSFSheet sheet = (HSSFSheet)hssfworkbook.GetSheetAt(0);
System.Collections.IEnumerator rows = sheet.GetRowEnumerator();
//预设表头
rows.MoveNext();
HSSFRow header = (HSSFRow)rows.Current;
System.Data.DataTable dt = new System.Data.DataTable();
for (int j = 0; j < header.Cells.Count; j++)
{
dt.Columns.Add(header.Cells[j].ToString());
}
while (rows.MoveNext())
{
HSSFRow row = (HSSFRow)rows.Current;
DataRow dr = dt.NewRow();
for (int i = 0; i < row.LastCellNum; i++)
{
HSSFCell cell = (HSSFCell)row.GetCell(i);
if (cell == null)
{
dr[i] = null;
}
else
{
if (cell.CellStyle.DataFormat == 0xe)//预设日期单元格处理
{
dr[i] = cell.DateCellValue.ToShortDateString();
}
else if (cell.CellStyle.DataFormat == 0x7)//预设金额处理
{
dr[i] = cell.NumericCellValue.ToString();
}
else
{
dr[i] = cell.ToString();
}
}
}
dt.Rows.Add(dr);
}
return(dt);
}
public static DataTable RenderDataTableFromExcel(string path, string sheetname)
{
DataTable dataTable = new DataTable();
HSSFWorkbook hSSFWorkbook;
using (FileStream fileStream = new FileStream(path, FileMode.Open, FileAccess.Read))
{
hSSFWorkbook = new HSSFWorkbook(fileStream);
}
HSSFSheet hSSFSheet = (HSSFSheet)hSSFWorkbook.GetSheet(sheetname);
IEnumerator rowEnumerator = hSSFSheet.GetRowEnumerator();
HSSFRow hSSFRow = (HSSFRow)hSSFSheet.GetRow(0);
int lastCellNum = hSSFRow.LastCellNum;
for (int i = 0; i < lastCellNum; i++)
{
HSSFCell hSSFCell = (HSSFCell)hSSFRow.GetCell(i);
bool flag = hSSFCell == null;
if (flag)
{
dataTable.Columns.Add("");
}
else
{
dataTable.Columns.Add(hSSFCell.ToString());
}
}
for (int j = hSSFSheet.FirstRowNum + 1; j <= hSSFSheet.LastRowNum; j++)
{
HSSFRow hSSFRow2 = (HSSFRow)hSSFSheet.GetRow(j);
bool flag2 = hSSFRow2 == null;
if (!flag2)
{
DataRow dataRow = dataTable.NewRow();
for (int k = hSSFRow2.FirstCellNum; k < lastCellNum; k++)
{
bool flag3 = hSSFRow2.GetCell(k) != null;
if (flag3)
{
dataRow[k] = hSSFRow2.GetCell(k).ToString();
}
}
dataTable.Rows.Add(dataRow);
}
}
return(dataTable);
}
private string notNull(HSSFCell paramHSSFCell)
{
string str = "";
if (paramHSSFCell == null)
{
return("");
}
if (paramHSSFCell != null && paramHSSFCell.CellType == 0)
{
str = "" + paramHSSFCell.NumericCellValue;
if (str.EndsWith(".0", StringComparison.Ordinal))
{
str = "" + (long)paramHSSFCell.NumericCellValue;
}
}
else if (paramHSSFCell != null && paramHSSFCell.CellType == 2)
{
CellValue cellValue = null;
try
{
cellValue = this.o_evaluator.evaluate(paramHSSFCell);
}
catch (Exception)
{
Console.WriteLine("Could not evaluate: " + paramHSSFCell);
return(paramHSSFCell.ToString());
}
if (cellValue.CellType == 0)
{
str = "" + cellValue.NumberValue;
}
else if (cellValue.CellType == 1)
{
str = cellValue.StringValue;
}
else
{
return("");
}
}
else
{
str = paramHSSFCell.RichStringCellValue.String;
}
return((string.ReferenceEquals(str, null)) ? "" : str);
}
/// <summary>
/// 打开excel文件
/// </summary>
/// <param name="excelPath">excel文件路径</param>
public void Open(string excelPath)
{
HSSFWorkbook hssfworkbook;
using (FileStream file = new FileStream(excelPath, FileMode.Open, FileAccess.Read)) {
hssfworkbook = new HSSFWorkbook(file);
}
for (int k = 0; k < hssfworkbook.NumberOfSheets; k++)
{
DataTable dt = new DataTable();
HSSFSheet sheet = (HSSFSheet)hssfworkbook.GetSheetAt(k);
if (ds.Tables.IndexOf(sheet.SheetName) == -1)
{
System.Collections.IEnumerator rows = sheet.GetRowEnumerator();
HSSFRow headerRow = (HSSFRow)sheet.GetRow(0);
int cellCount = headerRow.LastCellNum;
for (int j = 0; j < cellCount; j++)
{
HSSFCell cell = (HSSFCell)headerRow.GetCell(j);
dt.Columns.Add(cell.ToString());
}
for (int i = (sheet.FirstRowNum + 1); i <= sheet.LastRowNum; i++)
{
HSSFRow row = (HSSFRow)sheet.GetRow(i);
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);
}
dt.TableName = sheet.SheetName;
ds.Tables.Add(dt);
}
}
}
public void TestCellType()
{
HSSFWorkbook wb = new HSSFWorkbook();
HSSFSheet sheet = wb.CreateSheet() as HSSFSheet;
HSSFRow row = sheet.CreateRow(0) as HSSFRow;
HSSFCell cell = row.CreateCell(0) as HSSFCell;
cell.SetCellType(CellType.Blank);
Assert.AreEqual("9999-12-31 23:59:59.999", cell.DateCellValue.ToString("yyyy-MM-dd HH:mm:ss.fff"));
Assert.IsFalse(cell.BooleanCellValue);
Assert.AreEqual("", cell.ToString());
cell.SetCellType(CellType.String);
Assert.AreEqual("", cell.ToString());
cell.SetCellType(CellType.String);
cell.SetCellValue(1.2);
cell.SetCellType(CellType.Numeric);
Assert.AreEqual("1.2", cell.ToString());
cell.SetCellType(CellType.Boolean);
Assert.AreEqual("TRUE", cell.ToString());
cell.SetCellType(CellType.Boolean);
cell.SetCellValue("" + FormulaError.VALUE.String);
cell.SetCellType(CellType.Error);
Assert.AreEqual("#VALUE!", cell.ToString());
cell.SetCellType(CellType.Error);
cell.SetCellType(CellType.Boolean);
Assert.AreEqual("FALSE", cell.ToString());
cell.SetCellValue(1.2);
cell.SetCellType(CellType.Numeric);
Assert.AreEqual("1.2", cell.ToString());
cell.SetCellType(CellType.Boolean);
cell.SetCellType(CellType.String);
cell.SetCellType(CellType.Error);
cell.SetCellType(CellType.String);
cell.SetCellValue(1.2);
cell.SetCellType(CellType.Numeric);
cell.SetCellType(CellType.String);
Assert.AreEqual("1.2", cell.ToString());
cell.SetCellValue((string)null);
cell.SetCellValue((IRichTextString)null);
wb.Close();
}
/// <summary>
/// 读取2007以上版本.xlsx
/// </summary>
/// <param name="path"></param>
/// <returns></returns>
public static string Read2003ToString(string path)
{
HSSFWorkbook hssfworkbook;
path = HttpContext.Current.Server.MapPath(path);
using (FileStream file = new FileStream(path, FileMode.Open, FileAccess.Read))
{
hssfworkbook = new HSSFWorkbook(file);
}
HSSFSheet sheet = (HSSFSheet)hssfworkbook.GetSheetAt(0);
System.Collections.IEnumerator rows = sheet.GetRowEnumerator();
StringBuilder sb = new StringBuilder();
int irow = 0;
sb.Append("<table>");
while (rows.MoveNext())
{
HSSFRow row = (HSSFRow)rows.Current;
irow++;
sb.Append("<tr>");
for (int i = 0; i < row.LastCellNum; i++)
{
HSSFCell cell = (HSSFCell)row.GetCell(i);
string dr = "";
if (cell == null)
{
dr = "";
}
else
{
dr = cell.ToString();
}
sb.Append("<td>" + dr + "</td>");//("+irow+","+i+")"+
}
sb.Append("</tr>");
}
sb.Append("</table>");
return(sb.ToString());
}
/// <summary>
/// 导入Excel
/// </summary>
/// <param name="path"></param>
/// <returns></returns>
public static DataTable Upload2DataTable(string path)
{
using (FileStream file = new FileStream(path, FileMode.Open, FileAccess.Read))
{
HSSFWorkbook hssfworkbook = new HSSFWorkbook(file);
HSSFSheet sheet = hssfworkbook.GetSheetAt(0) as HSSFSheet;
IEnumerator rows = sheet.GetRowEnumerator();
DataTable dt = new DataTable();
//创建列名，以第一行为名
if (rows.MoveNext())
{
HSSFRow row = rows.Current as HSSFRow;
for (int i = 0; i < row.LastCellNum; i++)
{
HSSFCell cell = row.GetCell(i) as HSSFCell;
if (cell == null)
{
dt.Columns.Add("无效");
}
else
{
dt.Columns.Add(cell.ToString());
}
}
}
//数据从第二行开始
while (rows.MoveNext())
{
HSSFRow row = rows.Current as HSSFRow;
DataRow dr = dt.NewRow();
for (int i = 0; i < row.LastCellNum; i++)
{
GetCellValue(row, dr, i);
}
dt.Rows.Add(dr);
}
return(dt);
}
}
public System.Data.DataTable ReadExcelToDataTable(string filePath)
{
//打开要读取的Excel
FileStream file = new FileStream(filePath, FileMode.Open);
//读入Excel
HSSFWorkbook workbook = new HSSFWorkbook(file);
file.Close();
HSSFSheet sheet = workbook.GetSheetAt(0);
//建立一个新的table
DataTable dtNew = new DataTable();;
HSSFRow row = sheet.GetRow(0);
//读取取第0列作为column name
for (int columnIndex = 0; columnIndex < row.LastCellNum; columnIndex++)
{
DataColumn dc = new DataColumn(row.GetCell(columnIndex).ToString());
dtNew.Columns.Add(dc);
}
int rowId = 1;
//第一列以后为资料，一直读到最后一行
while (rowId <= sheet.LastRowNum)
{
DataRow newRow = dtNew.NewRow();
//读取所有column
for (int colIndex = 0; colIndex < dtNew.Columns.Count; colIndex++)
{
string str = string.Empty;
HSSFCell CellVal = sheet.GetRow(rowId).GetCell(colIndex);
if (CellVal != null)
{
str = CellVal.ToString();
}
newRow[dtNew.Columns[colIndex]] = str;
}
dtNew.Rows.Add(newRow);
rowId++;
}
return(dtNew);
}
/// <summary>
/// 将Excel转成DataTable
/// </summary>
/// <param name="fileupload">上传控件实例引用</param>
/// <returns></returns>
public static DataTable ImportExcelByNPOI(FileStream file)
{
DataTable dt = new DataTable();
using (file)
{
HSSFWorkbook hssfworkbook = new HSSFWorkbook(file);
HSSFSheet sheet = (HSSFSheet)hssfworkbook.GetSheetAt(0);
System.Collections.IEnumerator rows = sheet.GetRowEnumerator();
bool flag = true;
//将Excel内数据读取到DataTable 中
while (rows.MoveNext())
{
HSSFRow row = (HSSFRow)rows.Current;
DataRow dr = dt.NewRow();
for (int i = 0; i < row.LastCellNum; i++)
{
HSSFCell cell = (HSSFCell)row.GetCell(i);
if (flag)
{
dt.Columns.Add(i.ToString());
}
if (cell == null)
{
dr[i] = null;
}
else
{
dr[i] = cell.ToString();
}
}
flag = false;
dt.Rows.Add(dr);
}
}
return(dt);
}
/// <summary>
/// 读取指定位置的单元格的值string
/// </summary>
/// <param name="row"></param>
/// <param name="col"></param>
/// <param name="sheet"></param>
/// <returns></returns>
public static string ReadCell(int row, int col, ref HSSFSheet sheet)
{
if (sheet.GetRow(row - 1) == null)
{
return("null");
}
else
{
HSSFRow t_row = (HSSFRow)sheet.GetRow(row - 1);
if (t_row.GetCell(col - 1) == null)
{
return("null");
}
else
{
HSSFCell t_cell = (HSSFCell)t_row.GetCell(col - 1);
return(t_cell.ToString());
}
}
}
