注意,Excel读入DataTable需要使用NPOI包
/// <summary>
/// Excel导入成Datable
/// </summary>
/// <param name="file">导入路径(包含文件名与扩展名)</param>
/// <returns></returns>
public static DataTable ExcelToTable(string file, string sheetName = "")
{
DataTable dt = new DataTable();
IWorkbook workbook = null;
try
{
string fileExt = Path.GetExtension(file).ToLower();
using (FileStream fs = new FileStream(file, FileMode.Open, FileAccess.Read))
{
//XSSFWorkbook 适用XLSX格式,HSSFWorkbook 适用XLS格式
if (fileExt == ".xlsx") { workbook = new XSSFWorkbook(fs); } else if (fileExt == ".xls") { workbook = new HSSFWorkbook(fs); } else { workbook = null; }
if (workbook == null) { return null; }
ISheet sheet = string.IsNullOrWhiteSpace(sheetName) ? workbook.GetSheetAt(0) : workbook.GetSheet(sheetName);
//表头
IRow header = sheet.GetRow(sheet.FirstRowNum);
if (header == null)
{
throw new Exception("Excel表没有数据,请添加数据");
}
List<int> columns = new List<int>();
for (int i = 0; i < header.LastCellNum; i++)
{
object obj = GetValueType(header.GetCell(i));
if (obj == null || obj.ToString().Trim() == string.Empty)
{
continue;
}
else
{
dt.Columns.Add(new DataColumn(obj.ToString()));
}
columns.Add(i);
}
//数据
for (int i = sheet.FirstRowNum + 1; i <= sheet.LastRowNum; i++)
{
DataRow dr = dt.NewRow();
bool hasValue = false;
foreach (int j in columns)
{
if (sheet.GetRow(i) != null) {
dr[j] = GetValueType(sheet.GetRow(i).GetCell(j));
}
//if (dr[j] != null && dr[j].ToString() != string.Empty)
//{
// hasValue = true;
//}
}
//if (hasValue)
//{
// dt.Rows.Add(dr);
//}
dt.Rows.Add(dr);
}
dt.AcceptChanges();
}
return dt;
}
catch (Exception e)
{
return null;
}
finally
{
if (dt != null) { dt.Dispose(); }
if (workbook != null) { workbook = null; }
}
}
/// <summary>
/// 获取单元格类型
/// </summary>
/// <param name="cell"></param>
/// <returns></returns>
private static object GetValueType(ICell cell)
{
if (cell == null)
return null;
switch (cell.CellType)
{
case CellType.Blank: //BLANK:
return "";
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;
}
}
DataTable读入Excel
/// <summary>
/// Datatable生成Excel表格并返回路径
/// </summary>
/// <param name="m_DataTable">Datatable</param>
/// <param name="s_FileName">文件名</param>
/// <returns></returns>
public static string DataToExcel(System.Data.DataTable m_DataTable, string s_FileName)
{
//string FileName = @"C:\Users\JK\Desktop\DataTableToExcel\" + s_FileName + ".xlsx"; //文件存放路径
string FileName = s_FileName;
if (System.IO.File.Exists(FileName)) //存在则删除
{
System.IO.File.Delete(FileName);
}
System.IO.FileStream objFileStream;
System.IO.StreamWriter objStreamWriter;
string strLine = "";
objFileStream = new System.IO.FileStream(FileName, System.IO.FileMode.OpenOrCreate, System.IO.FileAccess.Write);
objStreamWriter = new System.IO.StreamWriter(objFileStream, Encoding.Unicode);
for (int i = 0; i < m_DataTable.Columns.Count; i++)
{
strLine = strLine + m_DataTable.Columns[i].Caption.ToString() + Convert.ToChar(9); //写列标题
}
objStreamWriter.WriteLine(strLine);
strLine = "";
for (int i = 0; i < m_DataTable.Rows.Count; i++)
{
for (int j = 0; j < m_DataTable.Columns.Count; j++)
{
if (m_DataTable.Rows[i].ItemArray[j] == null)
strLine = strLine + " " + Convert.ToChar(9); //写内容
else
{
string rowstr = "";
rowstr = m_DataTable.Rows[i].ItemArray[j].ToString();
if (rowstr.IndexOf("\r\n") > 0)
rowstr = rowstr.Replace("\r\n", " ");
if (rowstr.IndexOf("\t") > 0)
rowstr = rowstr.Replace("\t", " ");
strLine = strLine + rowstr + Convert.ToChar(9);
}
}
objStreamWriter.WriteLine(strLine);
strLine = "";
}
objStreamWriter.Close();
objFileStream.Close();
return FileName; //返回生成文件的绝对路径
}
文章来源地址https://www.toymoban.com/news/detail-675060.html
文章来源:https://www.toymoban.com/news/detail-675060.html
到了这里,关于C# excel与DataTable之间的转换的文章就介绍完了。如果您还想了解更多内容,请在右上角搜索TOY模板网以前的文章或继续浏览下面的相关文章,希望大家以后多多支持TOY模板网!