void ConvertToDataTable()
{
XSSFSheet sheet = (XSSFSheet)hssfworkbook.GetSheetAt(0);
System.Collections.IEnumerator rows = sheet.GetRowEnumerator();
DataTable dt = new DataTable();
for (int j = 0; j < 5; j++)
{
dt.Columns.Add(Convert.ToChar(((int)'A') + j).ToString());
}
while (rows.MoveNext())
{
XSSFRow row = (XSSFRow)rows.Current;
DataRow dr = dt.NewRow();
for (int i = 0; i < row.LastCellNum; i++)
{
XSSFCell cell = (XSSFCell)row.GetCell(i);
if (cell == null)
{
dr[i] = null;
}
else
{
dr[i] = cell.ToString();
}
}
dt.Rows.Add(dr);
}
SetData(dt);
}
/// <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());
}
public void ReadExcel()
{
XSSFWorkbook workbook;
using (FileStream fs = new FileStream(mFilePath, FileMode.Open, FileAccess.Read))
{
workbook = new XSSFWorkbook(fs);
XSSFSheet sheet = workbook.GetSheetAt(0) as XSSFSheet;
if (sheet != null)
{
System.Collections.IEnumerator rows = sheet.GetRowEnumerator();
int t = 0;
while (rows.MoveNext())
{
if (t == 0)
{
t += 1;
continue;
}
IRow row = (XSSFRow)rows.Current;
int cellCount = row.LastCellNum;
if (cellCount < 2)
{
continue;
}
List <ICell> cells = new List <ICell>();
for (int i = 0; i < row.LastCellNum; i++)
{
ICell cell = row.GetCell(i);
cells.Add(cell);
}
string strkey = String.Empty;
string jpText = String.Empty;
string cnText = String.Empty;
string twText = String.Empty;
ColUtil.GetStringValue(cells[CELL_KEY], out strkey);
ColUtil.GetStringValue(cells[CELL_JP], out jpText);
ColUtil.GetStringValue(cells[CELL_CN], out cnText);
//ColUtil.GetStringValue(cells[CELL_TW], out twText);
LocText locText = new LocText(jpText);
locText.zh_CN = cnText;
locText.zh_TW = twText;
DataMgr.Instance.GameTextDictionary.Add(strkey, locText);
t += 1;
}
}
}
}
/// <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 Dictionary <string, string> process(string filepath)
{
Dictionary <string, string> mapa = new Dictionary <string, string>();
using (var fs = new FileStream(filepath, FileMode.Open, FileAccess.Read))
{
XSSFWorkbook wb = new XSSFWorkbook(fs);
//primeira planilha
XSSFSheet sh = (XSSFSheet)wb.GetSheetAt(0);
IEnumerator enumerator = sh.GetRowEnumerator();
while (enumerator.MoveNext())
{
if (enumerator.Current != null)
{
IRow row = (IRow)enumerator.Current;
if (row.GetCell(INDEX_CELL_EVENTOS) != null)
{
string evento = row.GetCell(INDEX_CELL_EVENTOS).ToString();
if (evento.Trim() != "" && Char.IsNumber(evento, 0))
{
string horas = evento.Substring(0, 5);
string data = row.RowNum.ToString();
if (evento.Contains("Débito Banco Horas"))
{
mapa.Add(data, "-" + horas);
}
else if (evento.Contains("Crédito Banco Horas"))
{
mapa.Add(data, horas);
}
else if (evento.Contains("ABONO"))
{
mapa.Add(data, horas);
}
}
}
}
}
}
return(mapa);
}
/// <summary>读取excel
/// 默认第一行为标头
/// </summary>
/// <param name="strFileName">excel文档路径</param>
/// <returns></returns>
public static DataTable Import(string strFileName)
{
DataTable dt = new DataTable();
XSSFWorkbook XSSFworkbook;
using (FileStream file = new FileStream(strFileName, FileMode.Open, FileAccess.Read))
{
XSSFworkbook = new XSSFWorkbook(file);
}
XSSFSheet sheet = (XSSFSheet)XSSFworkbook.GetSheetAt(0);
System.Collections.IEnumerator rows = sheet.GetRowEnumerator();
XSSFRow headerRow = (XSSFRow)sheet.GetRow(0);
int cellCount = headerRow.LastCellNum;
for (int j = 0; j < cellCount; j++)
{
XSSFCell cell = (XSSFCell)headerRow.GetCell(j);
dt.Columns.Add(cell.ToString());
}
for (int i = (sheet.FirstRowNum + 1); i <= sheet.LastRowNum; i++)
{
XSSFRow row = (XSSFRow)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);
}
//private IEnumerable<T> _parseFile<T>(Metadata<T> metadata, byte[] rawData)
//{
// var parsedData = new List<T>();
// //create parser
// try
// {
// using (var stream = new MemoryStream(rawData))
// {
// XSSFWorkbook hssfwb = new XSSFWorkbook(stream);
// for (int i = 0; i < hssfwb.NumberOfSheets; i++)
// {
// XSSFSheet sheet = (XSSFSheet)hssfwb.GetSheetAt(i);
// System.Collections.IEnumerator rows = sheet.GetRowEnumerator();
// rows.MoveNext();
// XSSFRow row = (XSSFRow)rows.Current;
// _headerList = _getHeaderList(row);
// _headerLookup = LNGProvider_Parser_HeaderLookup.BuildHeaderLookup(_headerList, metadata);
// if (_headerList == null)
// throw new Exception("Unable to find Header row");
// while (rows.MoveNext())
// {
// row = (XSSFRow)rows.Current;
// ParsedRowList.Add(LNGProvider_Parser_Mapper._buildLNGFileValueDailyTotal(row, _headerLookup, LNGProvider_Parser_Constants.Countries[i]));
// }
// }
// }
// parsedData.ParsedData = ParsedRowList.ToArray();
// parsedData.RawData = rawData;
// return parsedData;
// }
// catch (Exception ex)
// {
// parsedData.RawData = rawData;
// return parsedData;
// }
// throw new NotImplementedException();
//}
private void _scan()
{
try
{
using (var stream = new MemoryStream(File.ReadAllBytes("c:\\Users\\Peter Vargovcik\\Documents\\Visual Studio 2015\\Projects\\SmartParser\\SmartParser\\Files\\daily_totals_2012-2014.xlsx")))
{
XSSFWorkbook hssfwb = new XSSFWorkbook(stream);
Dictionary <XSSFRow, IEnumerable <XSSFRow> > rowDictionary = new Dictionary <XSSFRow, IEnumerable <XSSFRow> >();
string previousHash = "";
for (int i = 0; i < hssfwb.NumberOfSheets; i++)
{
XSSFSheet sheet = (XSSFSheet)hssfwb.GetSheetAt(i);
IEnumerator rows = sheet.GetRowEnumerator();
int rowNumber = 0;
while (rows.MoveNext())
{
XSSFRow row = (XSSFRow)rows.Current;
_linkedList.Add(row);
Console.Write("Sheet : {0}, Line {1}, ", sheet.SheetName, rowNumber);
// hash of the row types
var rowTypeString = String.Join("-", row.Cells.Select(x => x.CellType.ToString()).ToArray());
var currentHashRow = HelpersMethods.GetMD5(Encoding.ASCII.GetBytes(rowTypeString));
if (_hashNotSame(previousHash, currentHashRow))
{
rowDictionary.Add(row, new List <XSSFRow>());
}
else
{
IEnumerable <XSSFRow> list = rowDictionary.Last().Value;
((List <XSSFRow>)list).Add(row);
}
//for (int j = 0; j < row.Cells.Count; j++)
//{
// Console.Write("[{0} : {1}], ", j, row.Cells[j].CellType.ToString());
//}
//Console.WriteLine();
//var firstCell = row.Cells[0].CellType;
//Console.WriteLine(firstCell.ToString());
rowNumber++;
previousHash = currentHashRow;
}
}
}
}
catch (Exception ex)
{
throw ex;
}
}
public void ReadExcel()
{
FileStream file = new FileStream(YuanShiPath, FileMode.Open, FileAccess.ReadWrite);
FileStream resultFile = new FileStream(ResultPath, FileMode.Create);
XSSFWorkbook wb = new XSSFWorkbook(file);
XSSFSheet sheet = (XSSFSheet)wb.GetSheetAt(0);
var rows = sheet.GetRowEnumerator();
//创建新行
rows.MoveNext();
var row = (XSSFRow)rows.Current;
ICell temCell = row.CreateCell(row.LastCellNum);
temCell.SetCellValue("tem");
ICell highRankCell = row.CreateCell(row.LastCellNum);
highRankCell.SetCellValue("模拟最高分排位");
ICell highScore = row.CreateCell(row.LastCellNum);
highScore.SetCellValue("模拟最高分");
ICell averageScore = row.CreateCell(row.LastCellNum);
averageScore.SetCellValue("模拟平均分");
ICell lowRankCell = row.GetCell(0);
ICell lowScoreCell = row.GetCell(0);
ICell keLeiCell = row.GetCell(0);
for (int i = 0; i < row.LastCellNum; i++)
{
//获取最低分排名列序号
if (row.GetCell(i).StringCellValue.Contains("最低分排名"))
{
lowRankCell = row.GetCell(i);
}
//获取最低分列序号
if (row.GetCell(i).StringCellValue.Contains("投档分") || row.GetCell(i).StringCellValue.Equals("最低分"))
{
lowScoreCell = row.GetCell(i);
}
//获取科类列序号
if (row.GetCell(i).StringCellValue.Contains("科类"))
{
keLeiCell = row.GetCell(i);
}
}
while (rows.MoveNext())
{
row = (XSSFRow)rows.Current;
//获取每行最低分排位、科类、最低分单元格
lowRankCell = row.GetCell(lowRankCell.ColumnIndex);
keLeiCell = row.GetCell(keLeiCell.ColumnIndex);
lowScoreCell = row.GetCell(lowScoreCell.ColumnIndex);
//创建每行的tem、模拟最高分排位、模拟最高分、模拟平均分单元格
temCell = row.CreateCell(temCell.ColumnIndex);
highRankCell = row.CreateCell(highRankCell.ColumnIndex);
highScore = row.CreateCell(highScore.ColumnIndex);
averageScore = row.CreateCell(averageScore.ColumnIndex);
//有时空行也会继续向下
if (lowRankCell == null && keLeiCell == null && lowScoreCell == null)
{
break;
}
if (lowRankCell.CellType == CellType.Numeric && keLeiCell.NumericCellValue == 1)
{
temCell.SetCellValue(lowRankCell.NumericCellValue / (1 + lowRankCell.NumericCellValue * 6 / wenKePersons));
highRankCell.SetCellValue(lowRankCell.NumericCellValue - temCell.NumericCellValue);
highScore.SetCellValue(SelectRank(highRankCell.NumericCellValue, keLeiCell.NumericCellValue));
averageScore.SetCellValue((highScore.NumericCellValue + lowScoreCell.NumericCellValue) / 2.001);
}
else if (lowRankCell.CellType == CellType.Numeric && keLeiCell.NumericCellValue == 2)
{
temCell.SetCellValue(lowRankCell.NumericCellValue / (1 + lowRankCell.NumericCellValue * 6 / liKePersons));
highRankCell.SetCellValue(lowRankCell.NumericCellValue - temCell.NumericCellValue);
highScore.SetCellValue(SelectRank(highRankCell.NumericCellValue, keLeiCell.NumericCellValue));
averageScore.SetCellValue((highScore.NumericCellValue + lowScoreCell.NumericCellValue) / 2.001);
}
}
wb.Write(resultFile);
wb.Close();
//file.Flush();
file.Close();
}
public static List <T> ExcelToList <T>(Type instanceType, string strFileName, int sheetIndex = 0, bool haveTitles = false, Dictionary <string, string> titleDic = null) where T : class, new()
{
PropertyInfo[] myPropertyInfo = instanceType.GetProperties(BindingFlags.Public | BindingFlags.Instance); //获取所有属性
List <T> tList = new List <T>();
var propertys = typeof(T).GetProperties();
HSSFWorkbook hssfworkbook = null;
XSSFWorkbook xssfworkbook = null;
strFileName = HttpContext.Current.Server.MapPath(strFileName);
string fileExt = Path.GetExtension(strFileName); //获取文件的后缀名
using (FileStream file = new FileStream(strFileName, FileMode.Open, FileAccess.Read))
{
if (fileExt == ".xls")
{
hssfworkbook = new HSSFWorkbook(file);
}
else if (fileExt == ".xlsx")
{
xssfworkbook = new XSSFWorkbook(file); //初始化太慢了，不知道这是什么bug
}
}
if (hssfworkbook != null)
{
HSSFSheet sheet = (HSSFSheet)hssfworkbook.GetSheetAt(sheetIndex);
if (sheet != null)
{
System.Collections.IEnumerator rows = sheet.GetRowEnumerator();
if (haveTitles)
{
HSSFRow headerRow = (HSSFRow)sheet.GetRow(0);
int cellCount = headerRow.LastCellNum;
for (int j = 0; j < cellCount; j++)
{
HSSFCell cell = (HSSFCell)headerRow.GetCell(j); //获取Excel标题
}
}
for (int i = (sheet.FirstRowNum + 1); i <= sheet.LastRowNum; i++)
{
HSSFRow row = (HSSFRow)sheet.GetRow(i);
var obj = new T();
for (int j = row.FirstCellNum; j < row.LastCellNum; j++)
{
string cellValue = "";
if (row.GetCell(j) != null)
{
cellValue = row.GetCell(j).ToString();
string dkey = titleDic.Keys.ToArray()[j];
string dataType = (myPropertyInfo[j].PropertyType).FullName; //获取数据类型
foreach (var p in propertys)
{
string name = p.Name;
if (name == dkey)
{
if (dataType == "System.String")
{
p.SetValue(obj, cellValue, null);
}
else if (dataType == "System.DateTime")
{
DateTime pdt = Convert.ToDateTime(cellValue);
p.SetValue(obj, pdt, null);
}
else if (dataType == "System.Boolean")
{
bool pb = Convert.ToBoolean(cellValue);
p.SetValue(obj, pb, null);
}
else if (dataType == "System.Int16")
{
Int16 pi16 = Convert.ToInt16(cellValue);
p.SetValue(obj, pi16, null);
}
else if (dataType == "System.Int32")
{
Int32 pi32 = Convert.ToInt32(cellValue);
p.SetValue(obj, pi32, null);
}
else if (dataType == "System.Int64")
{
Int64 pi64 = Convert.ToInt64(cellValue);
p.SetValue(obj, pi64, null);
}
else if (dataType == "System.Byte")
{
Byte pb = Convert.ToByte(cellValue);
p.SetValue(obj, pb, null);
}
else if (dataType == "System.Decimal")
{
System.Decimal pd = Convert.ToDecimal(cellValue);
p.SetValue(obj, pd, null);
}
else if (dataType == "System.Double")
{
double pd = Convert.ToDouble(cellValue);
p.SetValue(obj, pd, null);
}
else
{
p.SetValue(obj, null, null);
}
}
}
}
}
tList.Add(obj);
}
}
}
else if (xssfworkbook != null)
{
XSSFSheet xSheet = (XSSFSheet)xssfworkbook.GetSheetAt(sheetIndex);
if (xSheet != null)
{
System.Collections.IEnumerator rows = xSheet.GetRowEnumerator();
if (haveTitles)
{
XSSFRow headerRow = (XSSFRow)xSheet.GetRow(0);
int cellCount = headerRow.LastCellNum;
for (int j = 0; j < cellCount; j++)
{
XSSFCell cell = (XSSFCell)headerRow.GetCell(j); //获取Excel标题
}
}
for (int i = (xSheet.FirstRowNum + 1); i <= xSheet.LastRowNum; i++)
{
XSSFRow row = (XSSFRow)xSheet.GetRow(i);
var obj = new T();
for (int j = row.FirstCellNum; j < row.LastCellNum; j++)
{
string value = "";
if (row.GetCell(j) != null)
{
value = row.GetCell(j).ToString();
string dkey = titleDic.Keys.ToArray()[j];
string dvalue = titleDic.Values.ToArray()[j];
string str = (myPropertyInfo[j].PropertyType).FullName; //获取数据类型
foreach (var p in propertys)
{
string name = p.Name;
if (name == dkey)
{
if (str == "System.String")
{
p.SetValue(obj, value, null);
}
else if (str == "System.DateTime")
{
if (!string.IsNullOrEmpty(value))
{
DateTime pdt = Convert.ToDateTime(value);
p.SetValue(obj, pdt, null);
}
}
else if (str == "System.Boolean")
{
bool pb = Convert.ToBoolean(value);
p.SetValue(obj, pb, null);
}
else if (str == "System.Int16")
{
Int16 pi16 = Convert.ToInt16(value);
p.SetValue(obj, pi16, null);
}
else if (str == "System.Int32")
{
if (!string.IsNullOrEmpty(value))
{
Int32 pi32 = Convert.ToInt32(value);
p.SetValue(obj, pi32, null);
}
}
else if (str == "System.Int64")
{
Int64 pi64 = Convert.ToInt64(value);
p.SetValue(obj, pi64, null);
}
else if (str == "System.Byte")
{
Byte pb = Convert.ToByte(value);
p.SetValue(obj, pb, null);
}
else if (str == "System.Decimal")
{
System.Decimal pd = Convert.ToDecimal(value);
p.SetValue(obj, pd, null);
}
else if (str == "System.Double")
{
double pd = Convert.ToDouble(value);
p.SetValue(obj, pd, null);
}
else if (str == "System.Single")
{
float pd = Convert.ToSingle(value);
p.SetValue(obj, pd, null);
}
else
{
p.SetValue(obj, null, null);
}
}
}
}
}
tList.Add(obj);
}
}
}
return(tList);
}
public IEnumerator GetRowEnumerator()
{
return(_sh.GetRowEnumerator());
}
/// <summary>读取excel
/// 根据索引读取Sheet表数据，默认读取第一个sheet
/// </summary>
/// <param name="strFileName">excel文档路径</param>
/// <param name="sheetIndex">sheet表的索引，从0开始</param>
/// <returns>数据集</returns>
public static DataTable ExcelToDataTable(string strFileName, int sheetIndex = 0)
{
DataTable dt = new DataTable();
HSSFWorkbook hssfworkbook = null;
XSSFWorkbook xssfworkbook = null;
string fileExt = Path.GetExtension(strFileName);//获取文件的后缀名
using (FileStream file = new FileStream(strFileName, FileMode.Open, FileAccess.Read))
{
if (fileExt == ".xls")
{
hssfworkbook = new HSSFWorkbook(file);
}
else if (fileExt == ".xlsx")
{
xssfworkbook = new XSSFWorkbook(file);//初始化太慢了，不知道这是什么bug
}
}
if (hssfworkbook != null)
{
HSSFSheet sheet = (HSSFSheet)hssfworkbook.GetSheetAt(sheetIndex);
if (sheet != null)
{
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);
}
}
}
else if (xssfworkbook != null)
{
XSSFSheet xSheet = (XSSFSheet)xssfworkbook.GetSheetAt(sheetIndex);
if (xSheet != null)
{
System.Collections.IEnumerator rows = xSheet.GetRowEnumerator();
XSSFRow headerRow = (XSSFRow)xSheet.GetRow(0);
int cellCount = headerRow.LastCellNum;
for (int j = 0; j < cellCount; j++)
{
XSSFCell cell = (XSSFCell)headerRow.GetCell(j);
dt.Columns.Add(cell.ToString());
}
for (int i = (xSheet.FirstRowNum + 1); i <= xSheet.LastRowNum; i++)
{
XSSFRow row = (XSSFRow)xSheet.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
/// 根据索引读取Sheet表数据，默认读取第一个sheet
/// </summary>
/// <param name="strFileName">excel文档路径</param>
/// <param name="sheetIndex">sheet表的索引，从0开始</param>
/// <returns>数据集</returns>
public static DataTable ExcelToDataTable(string strFileName, int sheetIndex = 0, ProgressBar prb = null)
{
DataTable dt = new DataTable();
try
{
HSSFWorkbook hssfworkbook = null;
XSSFWorkbook xssfworkbook = null;
string fileExt = Path.GetExtension(strFileName);//获取文件的后缀名
using (FileStream file = new FileStream(strFileName, FileMode.Open, FileAccess.Read, FileShare.ReadWrite))
{
if (fileExt == ".xls")
{
hssfworkbook = new HSSFWorkbook(file);
}
else if (fileExt == ".xlsx")
{
xssfworkbook = new XSSFWorkbook(file);//初始化太慢了，不知道这是什么bug
}
}
if (hssfworkbook != null)
{
HSSFSheet sheet = (HSSFSheet)hssfworkbook.GetSheetAt(sheetIndex);
if (sheet != null)
{
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)
{
var columnName = cell.ToString();
foreach (var column in dt.Columns)
{
if (column.ToString() == cell.ToString())
{
columnName = columnName + j;
break;
}
}
dt.Columns.Add(columnName, typeof(string));
}
}
for (int i = (sheet.FirstRowNum + 1); i <= sheet.LastRowNum; i++)
{
int pro = Convert.ToInt32(Math.Ceiling(Convert.ToDouble(i / 100)));
if (i == pro)
{
prb.Value += 1;
}
HSSFRow row = (HSSFRow)sheet.GetRow(i);
if (row == null)
{
continue;
}
if (IsEmptyRow(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);
}
}
}
else if (xssfworkbook != null)
{
XSSFSheet xSheet = (XSSFSheet)xssfworkbook.GetSheetAt(sheetIndex);
if (xSheet != null)
{
System.Collections.IEnumerator rows = xSheet.GetRowEnumerator();
XSSFRow headerRow = (XSSFRow)xSheet.GetRow(0);
int cellCount = headerRow.LastCellNum;
for (int j = 0; j < cellCount; j++)
{
XSSFCell cell = (XSSFCell)headerRow.GetCell(j);
if (cell != null)
{
var columnName = cell.ToString();
foreach (var column in dt.Columns)
{
if (column.ToString() == cell.ToString())
{
columnName = columnName + j;
break;
}
}
dt.Columns.Add(columnName, typeof(string));
}
}
for (int i = (xSheet.FirstRowNum + 1); i <= xSheet.LastRowNum; i++)
{
XSSFRow row = (XSSFRow)xSheet.GetRow(i);
if (row == null)
{
continue;
}
if (IsEmptyRow(null, row))//一整行都是空值过滤掉
{
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);
}
}
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, AppMessage.MSG0000, MessageBoxButtons.OK, MessageBoxIcon.Error);
}
return(dt);
}
public static void CreateInventoryReport(string template, string savepath, List <string> datas)
{
XSSFWorkbook hssfworkbook;
var fpath = template;// HttpContext.Current.Server.MapPath(template);
using (FileStream file = new FileStream(fpath, FileMode.Open, FileAccess.Read))
{
hssfworkbook = new XSSFWorkbook(file);
}
XSSFSheet sheet = (XSSFSheet)hssfworkbook.GetSheetAt(0);
System.Collections.IEnumerator rows = sheet.GetRowEnumerator();
int irow = 0;
while (rows.MoveNext())
{
XSSFRow row = (XSSFRow)rows.Current;
irow++;
for (int i = 0; i < row.LastCellNum; i++)
{
XSSFCell cell = (XSSFCell)row.GetCell(i);
string dr = "";
if (cell == null)
{
dr = "";
}
else
{
//customercode,customername,logourl,materialcode materialname,materialmodel,materialunit,formonth beginamount,begincost,inamount,incost,
//outamount,outcost,endamount,endcost,beginlocalamount,beginlocalcost,endlocalamount,endlocalcost
dr = cell.ToString();
string strrow = datas.FirstOrDefault(p => p.Contains("|M001|"));
string[] array = strrow.Split('|');
if (dr.Contains("$title$"))
{
cell.SetCellValue(dr.Replace("$title$", array[0]));
}
if (dr.Contains("$number$"))
{
cell.SetCellValue(dr.Replace("$number$", array[1]));
}
//if (dr.Contains("$customername$")) cell.SetCellValue(dr.Replace("$customername$", customername));
//if (dr.Contains("$starttime$")) cell.SetCellValue(dr.Replace("$starttime$", starttime));
//if (dr.Contains("$completetime$")) cell.SetCellValue(dr.Replace("$completetime$", completetime));
//if (dr.Contains("$content$")) cell.SetCellValue(dr.Replace("$content$", content));
//if (dr.Contains("$checkperson$")) cell.SetCellValue(dr.Replace("$checkperson$", checkperson));
//if (dr.Contains("$checktime$")) cell.SetCellValue(dr.Replace("$checktime$", checktime));
//if (dr.Contains("$checkcontent$")) cell.SetCellValue(dr.Replace("$checkcontent$", checkcontent));
}
}
}
#region 另存为excel
var destFileName = savepath;// HttpContext.Current.Server.MapPath(savepath);
//HSSFWorkbook hssfworkbook2 = writeToExcel();
MemoryStream msfile = new MemoryStream();
hssfworkbook.Write(msfile);
System.IO.File.WriteAllBytes(destFileName, msfile.ToArray());
#endregion
#region excel转化成pdf
#endregion
}
/// <summary>
/// 读取2007以上版本.xlsx
/// </summary>
/// <param name="path"></param>
/// <returns></returns>
public static void CreateServiceregulatory(string template, string savepath, string title,
string customer, string starttime, string serviceperson, string completetime, string content,
string checktime, string checkperson, string checkcontent
)
{
XSSFWorkbook hssfworkbook;
var fpath = HttpContext.Current.Server.MapPath(template);
using (FileStream file = new FileStream(fpath, FileMode.Open, FileAccess.Read))
{
hssfworkbook = new XSSFWorkbook(file);
}
XSSFSheet sheet = (XSSFSheet)hssfworkbook.GetSheetAt(0);
System.Collections.IEnumerator rows = sheet.GetRowEnumerator();
int irow = 0;
while (rows.MoveNext())
{
XSSFRow row = (XSSFRow)rows.Current;
irow++;
for (int i = 0; i < row.LastCellNum; i++)
{
XSSFCell cell = (XSSFCell)row.GetCell(i);
string dr = "";
if (cell == null)
{
dr = "";
}
else
{
dr = cell.ToString();
if (dr.Contains("$title$"))
{
cell.SetCellValue(dr.Replace("$title$", title));
}
if (dr.Contains("$customer$"))
{
cell.SetCellValue(dr.Replace("$customer$", customer));
}
if (dr.Contains("$serviceperson$"))
{
cell.SetCellValue(dr.Replace("$serviceperson$", serviceperson));
}
if (dr.Contains("$starttime$"))
{
cell.SetCellValue(dr.Replace("$starttime$", starttime));
}
if (dr.Contains("$completetime$"))
{
cell.SetCellValue(dr.Replace("$completetime$", completetime));
}
if (dr.Contains("$content$"))
{
cell.SetCellValue(dr.Replace("$content$", content));
}
if (dr.Contains("$checkperson$"))
{
cell.SetCellValue(dr.Replace("$checkperson$", checkperson));
}
if (dr.Contains("$checktime$"))
{
cell.SetCellValue(dr.Replace("$checktime$", checktime));
}
if (dr.Contains("$checkcontent$"))
{
cell.SetCellValue(dr.Replace("$checkcontent$", checkcontent));
}
}
}
}
#region 另存为excel
var destFileName = HttpContext.Current.Server.MapPath(savepath);
//HSSFWorkbook hssfworkbook2 = writeToExcel();
MemoryStream msfile = new MemoryStream();
hssfworkbook.Write(msfile);
System.IO.File.WriteAllBytes(destFileName, msfile.ToArray());
#endregion
#region excel转化成pdf
#endregion
}