/// <summary>
/// Excel转换成DataSet(.xlsx/.xls)
/// </summary>
/// <param name="filePath">Excel文件路径</param>
/// <param name="strMsg"></param>
/// <returns></returns>
public static DataSet ExcelToDataSet(string filePath, string strMsg)
{
strMsg = "";
DataSet ds = new DataSet();
DataTable dt = new DataTable();
string fileType = Path.GetExtension(filePath).ToLower();
string fileName = Path.GetFileName(filePath).ToLower();
try
{
ISheet sheet = null;
int sheetNumber = 0;
FileStream fs = new FileStream(filePath, FileMode.Open, FileAccess.Read);
if (fileType == ".xlsx")
{
// 2007版本
XSSFWorkbook workbook = new XSSFWorkbook(fs);
sheetNumber = workbook.NumberOfSheets;
for (int i = 0; i < sheetNumber; i++)
{
string sheetName = workbook.GetSheetName(i);
sheet = workbook.GetSheet(sheetName);
if (sheet != null)
{
dt = GetSheetDataTable(sheet, strMsg);
if (dt != null)
{
dt.TableName = sheetName.Trim();
ds.Tables.Add(dt);
}
else
{
//MessageBox.Show("Sheet数据获取失败,原因:" + strMsg);
}
}
}
}
else if (fileType == ".xls")
{
// 2003版本
HSSFWorkbook workbook = new HSSFWorkbook(fs);
sheetNumber = workbook.NumberOfSheets;
for (int i = 0; i < sheetNumber; i++)
{
string sheetName = workbook.GetSheetName(i);
sheet = workbook.GetSheet(sheetName);
if (sheet != null)
{
dt = GetSheetDataTable(sheet, strMsg);
if (dt != null)
{
dt.TableName = sheetName.Trim();
ds.Tables.Add(dt);
}
else
{
// MessageBox.Show("Sheet数据获取失败,原因:" + strMsg);
}
}
}
}
return ds;
}
catch (Exception ex)
{
strMsg = ex.Message;
return null;
}
}
/// <summary>
/// 获取sheet表对应的DataTable
/// </summary>
/// <param name="sheet">Excel工作表</param>
/// <param name="strMsg"></param>
/// <returns></returns>
private static DataTable GetSheetDataTable(ISheet sheet, string strMsg)
{
strMsg = "";
DataTable dt = new DataTable();
string sheetName = sheet.SheetName;
int startIndex = 0;// sheet.FirstRowNum;
int lastIndex = sheet.LastRowNum;
//最大列数
int cellCount = 0;
IRow maxRow = sheet.GetRow(0);
for (int i = startIndex; i <= lastIndex; i++)
{
IRow row = sheet.GetRow(i);
if (row != null && cellCount < row.LastCellNum)
{
cellCount = row.LastCellNum;
maxRow = row;
}
}
//列名设置
try
{
for (int i = 0; i < maxRow.LastCellNum; i++)//maxRow.FirstCellNum
{
//dt.Columns.Add(Convert.ToChar(((int)'A') + i).ToString());
dt.Columns.Add(maxRow.Cells[i].ToString());
//DataColumn column = new DataColumn("Column" + (i + 1).ToString());
//dt.Columns.Add(column);
}
}
catch
{
strMsg = "工作表" + sheetName + "中无数据";
return null;
}
//数据填充
for (int i = startIndex + 1; i <= lastIndex; i++)
{
IRow row = sheet.GetRow(i);
DataRow drNew = dt.NewRow();
if (row != null)
{
for (int j = row.FirstCellNum; j < row.LastCellNum; ++j)
{
if (row.GetCell(j) != null)
{
ICell cell = row.GetCell(j);
switch (cell.CellType)
{
case CellType.Blank:
drNew[j] = "";
break;
case CellType.Numeric:
short format = cell.CellStyle.DataFormat;
//对时间格式(2015.12.5、2015/12/5、2015-12-5等)的处理
if (format == 14 || format == 31 || format == 57 || format == 58)
{
drNew[j] = cell.DateCellValue;
}
else
{
drNew[j] = cell.NumericCellValue;
}
if (cell.CellStyle.DataFormat == 177 || cell.CellStyle.DataFormat == 178 || cell.CellStyle.DataFormat == 188)
{
drNew[j] = cell.NumericCellValue.ToString("#0.00");
}
if (DateUtil.IsCellDateFormatted(cell))
{
drNew[j] = cell.DateCellValue.ToString("yyyy/MM/dd");
}
else
{
drNew[j] = row.GetCell(j).ToString();
}
break;
case CellType.String:
drNew[j] = cell.StringCellValue;
break;
case CellType.Formula:
try
{
drNew[j] = cell.NumericCellValue;
if (cell.CellStyle.DataFormat == 177 || cell.CellStyle.DataFormat == 178 || cell.CellStyle.DataFormat == 188)
drNew[j] = cell.NumericCellValue.ToString("#0.00");
}
catch
{
try
{
drNew[j] = cell.StringCellValue;
}
catch { }
}
break;
default:
drNew[j] = cell.StringCellValue;
break;
}
//if (cell.CellType == CellType.Numeric && DateUtil.IsCellDateFormatted(cell))
// drNew[j] = cell.DateCellValue.ToString("yyyy/MM/dd");
//else
//{
// drNew[j] = row.GetCell(j).ToString();
//}
}
}
}
dt.Rows.Add(drNew);
}
return dt;
}
/// <summary>
/// 从DataTable导入数据到数据库
/// </summary>
/// <param name="dt"></param>
/// <returns></returns>
public int InsetData(DataTable dt)
{
int i = 0;
string Schedule = "";
string Type = "";
string Material_Number = "";
string Material_Info = "";
int PlanSum = 0;
int Warehousing = 0;
int Cumulative_Warehousing = 0;
string Line = "";
string Factory = "";
string Storage_Location = "";
foreach (DataRow dr in dt.Rows)
{
Schedule = dr["排程"].ToString().Trim();
Type = dr["机型"].ToString().Trim();
Material_Number = dr["物料编号"].ToString().Trim();
Material_Info = dr["物料描述"].ToString().Trim();
PlanSum =Convert.ToInt32( dr["计划量"].ToString().Trim());
//Warehousing = dr["入库量"].ToString().Trim();
Cumulative_Warehousing = Convert.ToInt32( dr["入库量"].ToString().Trim());
Line = dr["线体名称"].ToString().Trim();
Factory = dr["工厂"].ToString().Trim();
Storage_Location = dr["生产物料库存地"].ToString().Trim();
string strSql = string.Format("insert into mesinfo"+
" (Schedule, Type, Material_Number, Material_Info, PlanSum, Warehousing, Cumulative_Warehousing," +
" Line, Factory, Storage_Location, Operating_Time)"+
" values('{0}', '{1}', '{2}', '{3}', {4}, {5}, {6}, '{7}', '{8}', '{9}', getdate())",Schedule,Type,
Material_Number,Material_Info,PlanSum, Warehousing,Cumulative_Warehousing,Line,Factory,Storage_Location);
int num = DBHelpter.GetExecuteNonQuery(strSql);
if (num>0)
{
i++;
}
}
return i;
}
EXECL 数据添加到数据库
最新推荐文章于 2023-09-27 17:35:13 发布