.net解析Excel

最新推荐文章于 2024-03-13 15:33:42 发布

chenjian0415

最新推荐文章于 2024-03-13 15:33:42 发布

阅读量698

点赞数

分类专栏： .net 文章标签： .net

本文链接：https://blog.csdn.net/chenjian0415/article/details/48781813

版权

.net 专栏收录该内容

13 篇文章 0 订阅

订阅专栏

/// <summary>
/// 
/// </summary>
/// <param name="filepath">文件地址</param>
/// <param name="sheetName">工作博名称</param>
/// <param name="x_start">从第几行开始</param>
/// <param name="y_start">从第几列开始</param>
/// <returns></returns>
public DataTable ReaderExcel(string filepath, string sheetName, int x_start, int y_start, int x_end, int y_end)
{
    DataTable dt = new DataTable();
    using (FileStream fs = File.OpenRead(filepath))
    {
        IWorkbook wk;

        if (Path.GetExtension(filepath) == ".xls")
        {
            wk = new HSSFWorkbook(fs);
        }
        else if (Path.GetExtension(filepath) == ".xlsx")
        {
            wk = new XSSFWorkbook(fs);
        }
        else
        {
            wk = null;
        }

        ISheet sheet = wk.GetSheet(sheetName);
        for (int i = x_start; i < sheet.GetRow(y_start).LastCellNum; i++)
        {
            dt.Columns.Add(new DataColumn(sheet.GetRow(y_start - 1).GetCell(i).ToString())); //y_start - 1是为了读取表头
        }


        for (int i = y_start; i < y_end; i++)
        {
            DataRow row = dt.NewRow();
            for (int j = 0; j < sheet.GetRow(0).LastCellNum; j++)
            {
                bool isDateCell = false;
                try
                {
                    isDateCell = DateUtil.IsCellDateFormatted(sheet.GetRow(i).GetCell(j + x_start));
                }
                catch { }
                if (isDateCell)
                {
                    row[j] = sheet.GetRow(i).GetCell(j + x_start).DateCellValue.ToString("yyyy/MM/dd");
                }
                else
                {
                    row[j] = sheet.GetRow(i).GetCell(j + x_start).ToString();
                }
            }
            dt.Rows.Add(row);
        }

    }

    return dt;
}