programing

EPPlus를 사용한 Excel to DataTable - 편집용으로 Excel 잠김

newstyles 2023. 4. 24. 22:57

EPPlus를 사용한 Excel to DataTable - 편집용으로 Excel 잠김

EPPlus를 사용하여 Excel을 데이터 테이블로 변환하려면 다음 코드를 사용합니다.

public DataTable ExcelToDataTable(string path)
{
    var pck = new OfficeOpenXml.ExcelPackage();
    pck.Load(File.OpenRead(path));
    var ws = pck.Workbook.Worksheets.First();
    DataTable tbl = new DataTable();
    bool hasHeader = true;
    foreach (var firstRowCell in ws.Cells[1, 1, 1, ws.Dimension.End.Column])
    {
        tbl.Columns.Add(hasHeader ? firstRowCell.Text : string.Format("Column {0}", firstRowCell.Start.Column));
    }
    var startRow = hasHeader ? 2 : 1;
    for (var rowNum = startRow; rowNum <= ws.Dimension.End.Row; rowNum++)
    {
        var wsRow = ws.Cells[rowNum, 1, rowNum, ws.Dimension.End.Column];
        var row = tbl.NewRow();
        foreach (var cell in wsRow)
        {
            row[cell.Start.Column - 1] = cell.Text;
        }
        tbl.Rows.Add(row);
    }
    pck.Dispose();
    return tbl;
}

Excel을 작성하지만 열려고 하면 다른 사용자가 편집하기 위해 잠겼으며 읽기 전용 모드에서만 열 수 있다는 메시지가 나타납니다.

나는 다음과 같이 생각했다.

pck.Dispose();

문제는 해결되지만 여전히 같은 오류가 발생합니다.

또, 파일을 삭제하려고 하면, 다음의 메세지가 표시됩니다.파일이 WebDev에서 열려 있기 때문에 작업을 완료할 수 없습니다.Web Server 40 。EXE.

이 문제를 해결할 방법이 있나요?잘 부탁드립니다.:)

그렇군요, 최근 여기에 올린 글입니다.그 이후로는 개선될 수 있습니다.ExcelPackage및 그FileStream(출처:File.OpenRead)는 사용 후 폐기되지 않습니다.

public static DataTable GetDataTableFromExcel(string path, bool hasHeader = true)
{
    using (var pck = new OfficeOpenXml.ExcelPackage())
    {
        using (var stream = File.OpenRead(path))
        {
            pck.Load(stream);
        }
        var ws = pck.Workbook.Worksheets.First();  
        DataTable tbl = new DataTable();
        foreach (var firstRowCell in ws.Cells[1, 1, 1, ws.Dimension.End.Column])
        {
            tbl.Columns.Add(hasHeader ? firstRowCell.Text : string.Format("Column {0}", firstRowCell.Start.Column));
        }
        var startRow = hasHeader ? 2 : 1;
        for (int rowNum = startRow; rowNum <= ws.Dimension.End.Row; rowNum++)
        {
            var wsRow = ws.Cells[rowNum, 1, rowNum, ws.Dimension.End.Column];
            DataRow row = tbl.Rows.Add();
            foreach (var cell in wsRow)
            {
                row[cell.Start.Column - 1] = cell.Text;
            }
        }
        return tbl;
    }
}

Tim Schmelter의 답변의 확장 버전입니다.

public static DataTable ToDataTable(this ExcelWorksheet ws, bool hasHeaderRow = true)
{
    var tbl = new DataTable();
    foreach (var firstRowCell in ws.Cells[1, 1, 1, ws.Dimension.End.Column])
        tbl.Columns.Add(hasHeaderRow ?
            firstRowCell.Text : string.Format("Column {0}", firstRowCell.Start.Column));
    var startRow = hasHeaderRow ? 2 : 1;
    for (var rowNum = startRow; rowNum <= ws.Dimension.End.Row; rowNum++)
    {
        var wsRow = ws.Cells[rowNum, 1, rowNum, ws.Dimension.End.Column];
        var row = tbl.NewRow();
        foreach (var cell in wsRow) row[cell.Start.Column - 1] = cell.Text;
        tbl.Rows.Add(row);
    }
    return tbl;
}

EPplus를 사용하여 Excel 파일을 DataTable로 변환하는 방법을 만들어 Type Safety를 유지하려고 했습니다.또한 중복된 열 이름이 처리되며 부울을 사용하여 시트에 헤더가 있는 행이 있음을 메서드에 알 수 있습니다.업로드 후 데이터베이스에 커밋하기 전에 사용자 입력이 필요한 몇 가지 단계가 있는 복잡한 가져오기 프로세스를 위해 작성했습니다.

private DataTable ExcelToDataTable(byte[] excelDocumentAsBytes, bool hasHeaderRow)
{
    DataTable dt = new DataTable();
    string errorMessages = "";

    //create a new Excel package in a memorystream
    using (MemoryStream stream = new MemoryStream(excelDocumentAsBytes))
    using (ExcelPackage excelPackage = new ExcelPackage(stream))
    {
        ExcelWorksheet worksheet = excelPackage.Workbook.Worksheets[1];

        //check if the worksheet is completely empty
        if (worksheet.Dimension == null)
        {
            return dt;
        }

        //add the columns to the datatable
        for (int j = worksheet.Dimension.Start.Column; j <= worksheet.Dimension.End.Column; j++)
        {
            string columnName = "Column " + j;
            var excelCell = worksheet.Cells[1, j].Value;

            if (excelCell != null)
            {
                var excelCellDataType = excelCell;

                //if there is a headerrow, set the next cell for the datatype and set the column name
                if (hasHeaderRow == true)
                {
                    excelCellDataType = worksheet.Cells[2, j].Value;

                    columnName = excelCell.ToString();

                    //check if the column name already exists in the datatable, if so make a unique name
                    if (dt.Columns.Contains(columnName) == true)
                    {
                        columnName = columnName + "_" + j;
                    }
                }

                //try to determine the datatype for the column (by looking at the next column if there is a header row)
                if (excelCellDataType is DateTime)
                {
                    dt.Columns.Add(columnName, typeof(DateTime));
                }
                else if (excelCellDataType is Boolean)
                {
                    dt.Columns.Add(columnName, typeof(Boolean));
                }
                else if (excelCellDataType is Double)
                {
                    //determine if the value is a decimal or int by looking for a decimal separator
                    //not the cleanest of solutions but it works since excel always gives a double
                    if (excelCellDataType.ToString().Contains(".") || excelCellDataType.ToString().Contains(","))
                    {
                        dt.Columns.Add(columnName, typeof(Decimal));
                    }
                    else
                    {
                        dt.Columns.Add(columnName, typeof(Int64));
                    }
                }
                else
                {
                    dt.Columns.Add(columnName, typeof(String));
                }
            }
            else
            {
                dt.Columns.Add(columnName, typeof(String));
            }
        }

        //start adding data the datatable here by looping all rows and columns
        for (int i = worksheet.Dimension.Start.Row + Convert.ToInt32(hasHeaderRow); i <= worksheet.Dimension.End.Row; i++)
        {
            //create a new datatable row
            DataRow row = dt.NewRow();

            //loop all columns
            for (int j = worksheet.Dimension.Start.Column; j <= worksheet.Dimension.End.Column; j++)
            {
                var excelCell = worksheet.Cells[i, j].Value;

                //add cell value to the datatable
                if (excelCell != null)
                {
                    try
                    {
                        row[j - 1] = excelCell;
                    }
                    catch
                    {
                        errorMessages += "Row " + (i - 1) + ", Column " + j + ". Invalid " + dt.Columns[j - 1].DataType.ToString().Replace("System.", "") + " value:  " + excelCell.ToString() + "<br>";
                    }
                }
            }

            //add the new row to the datatable
            dt.Rows.Add(row);
        }
    }

    //show error messages if needed
    Label1.Text = errorMessages;

    return dt;
}

웹 양식 버튼을 클릭하여 데모합니다.

protected void Button1_Click(object sender, EventArgs e)
{
    if (FileUpload1.HasFile)
    {
        DataTable dt = ExcelToDataTable(FileUpload1.FileBytes, CheckBox1.Checked);

        GridView1.DataSource = dt;
        GridView1.DataBind();
    }
}

위의 VDWD의 답변은 타입의 안전을 유지하기 위해 매우 효과적이며, 저는 그것을 기반으로 몇 가지 개선점을 가지고 있습니다.

  • 메서드가 파일에서 직접 읽습니다.
  • 하나의 값뿐만 아니라 모든 행을 사용하여 열 유형을 탐지합니다.열에 유형이 여러 개 있는 경우 열 유형은 문자열로 설정됩니다.
  • 문자열 목록에서 오류 메시지가 반환되었습니다.

업데이트된 버전은 다음과 같습니다.

    public static DataTable ExcelToDataTable(string path, ref List<string> errorList, bool hasHeaderRow = true  )
    {
        DataTable dt = new DataTable();
        errorList = new List<string>();

        //create a new Excel package           
        using (ExcelPackage excelPackage = new ExcelPackage())
        {
            using (var stream = File.OpenRead(path))
            {
                excelPackage.Load(stream);
            }

            ExcelWorksheet worksheet = excelPackage.Workbook.Worksheets[1];

            //check if the worksheet is completely empty
            if (worksheet.Dimension == null)
            {
                return dt;
            }

            //add the columns to the datatable
            for (int j = worksheet.Dimension.Start.Column; j <= worksheet.Dimension.End.Column; j++)
            {
                string columnName = "Column " + j;


                //Build hashset with all types in the row
                var columnTypes = new HashSet<Type>();                   
                for (int i = worksheet.Dimension.Start.Row + Convert.ToInt32(hasHeaderRow); i <= worksheet.Dimension.End.Row; i++)
                {
                    //Only add type if cell value not empty
                    if (worksheet.Cells[i, j].Value != null)
                    {
                        columnTypes.Add(worksheet.Cells[i, j].Value.GetType());
                    }
                }

                var excelCell = worksheet.Cells[1, j].Value;


                if (excelCell != null)
                {
                    Type excelCellDataType = null;

                    //if there is a headerrow, set the next cell for the datatype and set the column name
                    if (hasHeaderRow == true)
                    {

                        columnName = excelCell.ToString();

                        //check if the column name already exists in the datatable, if so make a unique name
                        if (dt.Columns.Contains(columnName) == true)
                        {
                            columnName = columnName + "_" + j;
                        }
                    }

                    //Select  input type for the column
                    if (columnTypes.Count == 1)
                    {
                        excelCellDataType = columnTypes.First();
                    }
                    else
                    {
                        excelCellDataType = typeof(string);
                    }

                    //try to determine the datatype for the column (by looking at the next column if there is a header row)
                    if (excelCellDataType == typeof(DateTime))
                    {
                        dt.Columns.Add(columnName, typeof(DateTime));
                    }
                    else if (excelCellDataType == typeof(Boolean))
                    {
                        dt.Columns.Add(columnName, typeof(Boolean));
                    }
                    else if (excelCellDataType == typeof(Double))
                    {
                        //determine if the value is a decimal or int by looking for a decimal separator
                        //not the cleanest of solutions but it works since excel always gives a double
                        if (excelCellDataType.ToString().Contains(".") || excelCellDataType.ToString().Contains(","))
                        {
                            dt.Columns.Add(columnName, typeof(Decimal));
                        }
                        else
                        {
                            dt.Columns.Add(columnName, typeof(Int64));
                        }
                    }
                    else
                    {
                        dt.Columns.Add(columnName, typeof(String));
                    }
                }
                else
                {
                    dt.Columns.Add(columnName, typeof(String));
                }
            }

            //start adding data the datatable here by looping all rows and columns
            for (int i = worksheet.Dimension.Start.Row + Convert.ToInt32(hasHeaderRow); i <= worksheet.Dimension.End.Row; i++)
            {
                //create a new datatable row
                DataRow row = dt.NewRow();

                //loop all columns
                for (int j = worksheet.Dimension.Start.Column; j <= worksheet.Dimension.End.Column; j++)
                {
                    var excelCell = worksheet.Cells[i, j].Value;

                    //add cell value to the datatable
                    if (excelCell != null)
                    {
                        try
                        {
                            row[j - 1] = excelCell;
                        }
                        catch
                        {
                            errorList.Add("Row " + (i - 1) + ", Column " + j + ". Invalid " + dt.Columns[j - 1].DataType.ToString().Replace("System.", "") + " value:  " + excelCell.ToString() );
                        }
                    }
                }

                //add the new row to the datatable
                dt.Rows.Add(row);
            }
        }

        return dt;
    }

이것은, 상기의 일반적인 것에 대한 개량입니다."Name", "Surname", "Telephone", "Fax" 등의 속성을 가진 클래스가 있고 이름이 같은 첫 번째 행의 Excel 시트가 있는 경우 Excel 행이 클래스 객체에 로드되어 목록에 팝업됩니다.

public static List<T> GetClassFromExcel<T>(string path, int fromRow, int fromColumn, int toRow = 0, int toColumn = 0)
{
if (toColumn != 0 && toColumn < fromColumn) throw new          Exception("toColumn can not be less than fromColumn");
if (toRow != 0 && toRow < fromRow) throw new Exception("toRow can not be less than fromRow");
List<T> retList = new List<T>();
using (var pck = new ExcelPackage())
{
            using (var stream = File.OpenRead(path))
            {
                pck.Load(stream);
            }
            //Retrieve first Worksheet
            var ws = pck.Workbook.Worksheets.First();
            //If the to column is empty or 0, then make the tocolumn to the count of the properties
            //Of the class object inserted
            toColumn = toColumn == 0 ? typeof(T).GetProperties().Count() : toColumn;

            //Read the first Row for the column names and place into a list so that
            //it can be used as reference to properties
            Dictionary<string, int> columnNames = new Dictionary<string, int>();
            // wsRow = ws.Row(0);
            var colPosition = 0;
            foreach (var cell in ws.Cells[1, 1, 1, toColumn == 0 ? ws.Dimension.Columns : toColumn])
            {
                columnNames.Add(cell.Value.ToString(), colPosition);
                colPosition++;
            }
            //create a instance of T
            T objT = Activator.CreateInstance<T>();
            //Retrieve the type of T
            Type myType = typeof(T);
            //Get all the properties associated with T
            PropertyInfo[] myProp = myType.GetProperties();


            //Loop through the rows of the excel sheet
            for (var rowNum = fromRow; rowNum <= (toRow == 0? ws.Dimension.End.Row : toRow); rowNum++)
            {
                var wsRow = ws.Cells[rowNum, fromColumn, rowNum, ws.Cells.Count()];

                foreach (var propertyInfo in myProp)
                {
                    if (columnNames.ContainsKey(propertyInfo.Name))
                    {
                        int position = 0;
                        columnNames.TryGetValue(propertyInfo.Name, out position);
                        //int position = columnNames.IndexOf(propertyInfo.Name);
                        //To prevent an exception cast the value to the type of the property.
                        propertyInfo.SetValue(objT, Convert.ChangeType(wsRow[rowNum, position + 1].Value, propertyInfo.PropertyType));
                    }
                }

                retList.Add(objT);
            }

        }
        return retList;
    }

필요한 경우 목록을 데이터 바인딩 소스로 사용할 수 있습니다.내가 너에게 준 선물...:) Daniel C.브레이

toColumn이 작동하도록 업데이트하고 Row에 추가한 후 Andreas 제안을 따릅니다.Andreas를 위해 엄지손가락을 치켜세우다

public static List<T> getClassFromExcel<T>(string path, int fromRow, int fromColumn, int toColumn = 0) where T : class
        {
            using (var pck = new OfficeOpenXml.ExcelPackage())
            {
                List<T> retList = new List<T>();

                using (var stream = File.OpenRead(path))
                {
                    pck.Load(stream);
                }
                var ws = pck.Workbook.Worksheets.First();
                toColumn = toColumn == 0 ? typeof(T).GetProperties().Count() : toColumn;

                for (var rowNum = fromRow; rowNum <= ws.Dimension.End.Row; rowNum++)
                {
                    T objT = Activator.CreateInstance<T>();
                    Type myType = typeof(T);
                    PropertyInfo[] myProp = myType.GetProperties();

                    var wsRow = ws.Cells[rowNum, fromColumn, rowNum, toColumn];

                    for (int i = 0; i < myProp.Count(); i++)
                    {
                        myProp[i].SetValue(objT, wsRow[rowNum, fromColumn + i].Text);
                    }
                    retList.Add(objT);
                }
                return retList;
            }
        }
public static List<T> GetClassFromExcel<T>(string path, int fromRow, int fromColumn, int toRow = 0, int toColumn = 0) where T: class, new()
{
        if (toColumn != 0 && toColumn < fromColumn) throw new Exception("toColumn can not be less than fromColumn");
        if (toRow != 0 && toRow < fromRow) throw new Exception("toRow can not be less than fromRow");
        List<T> retList = new List<T>();
        using (var pck = new ExcelPackage())
        {
            using (var stream = File.OpenRead(path))
            {
                pck.Load(stream);
            }
            //Retrieve first Worksheet
            var ws = pck.Workbook.Worksheets.First();

            toColumn = toColumn == 0 ? typeof(T).GetProperties().Count() : toColumn; //If the to column is empty or 0, then make the tocolumn to the count of the properties Of the class object inserted

            //Read the first Row for the column names and place into a list so that
            //it can be used as reference to properties
            Dictionary<string, int> columnNames = new Dictionary<string, int>();
            // wsRow = ws.Row(0);
            var colPosition = 0;
            foreach (var cell in ws.Cells[1, 1, 1, toColumn == 0 ? ws.Dimension.Columns : toColumn])
            {
                columnNames.Add(cell.Value.ToString(), colPosition);
                colPosition++;
            }

            //Retrieve the type of T
            Type myType = typeof(T);

            //Get all the properties associated with T
            PropertyInfo[] myProp = myType.GetProperties();

            //Loop through the rows of the excel sheet
            for (var rowNum = fromRow + 1; rowNum <= (toRow == 0 ? ws.Dimension.End.Row : toRow); rowNum++) // fromRow + 1 to read from next row after columnheader
            {

                //create a instance of T
                //T objT = Activator.CreateInstance<T>();
                T objT = new T();

                // var wsRow = ws.Cells[rowNum, fromColumn, rowNum, ws.Cells.Count()]; //ws.Cells.Count() causing out of range error hence using ws.Dimension.Columns to get last column index 
                var wsRow = ws.Cells[rowNum, fromColumn, rowNum, ws.Dimension.Columns];
                foreach (var propertyInfo in myProp)
                {
                    var attribute = propertyInfo.GetCustomAttributes(typeof(DisplayNameAttribute), true).Cast<DisplayNameAttribute>().SingleOrDefault();
                    string displayName = attribute != null && !string.IsNullOrEmpty(attribute.DisplayName) ? attribute.DisplayName : propertyInfo.Name; // If DisplayName annotation not used then get property name itself                       
                    if (columnNames.ContainsKey(displayName))
                    {
                        int position = 0;                           
                        columnNames.TryGetValue(displayName, out position);
                        ////int position = columnNames.IndexOf(propertyInfo.Name);
                        ////To prevent an exception cast the value to the type of the property.
                        propertyInfo.SetValue(objT, Convert.ChangeType(wsRow[rowNum, position + 1].Value, propertyInfo.PropertyType));
                    }
                }                   
                retList.Add(objT);
            }

        }
        return retList;
    }
//IMPLEMENTATION DONE BY PLACING Code IT IN SEPARATE Helpers.CS file  and 
//Consuming it in this manner
List<CustomerExcelModel> records = 
Helpers.GetClassFromExcel<CustomerExcelModel>(filelocation, 1, 1);

코드를 제출한 사용자와 제안해 주신 Andreas에게 감사드립니다.다음 변경사항이 있습니다.저는 제네릭스에 익숙하지 않기 때문에, 아래의 수정된 코드를 찾아주세요.

  • Excel 열 이름으로 매핑할 주석 표시 엔티티 모델이 추가되어 공백이 있는 열 이름도 처리할 수 있습니다.
  • 「T은, 「 fixed it 」, 「T objT」에 이 됩니다.이것은, 「T objT」가 for 루프의 외부에 있기 때문에, 같은 값을 반복해 리스트에 삽입해, 다음의 방법으로 수정했습니다.
    즉 " T "new T()"를 사용합니다
  • ws를 사용하여 고정 컬럼이 범위를 벗어났습니다.치수.ws 대신 Column count를 가져오려면 ColumnsCells.Count()가 out range 컬럼에러를 일으켰습니다.
  • RowNum=1이 헤더 이름을 읽는 동안 "rowNum = fromRow + 1"의 사소한 변경도 수행되었기 때문에 행 데이터를 통해 루프하기 위해 +1을 추가했습니다.

엑셀을 읽는 방법을 알려드립니다.각 날짜를 데이터 테이블에 저장하도록 수정할 수 있습니다.

public void readXLS(string FilePath)
{
    FileInfo existingFile = new FileInfo(FilePath);
    using (ExcelPackage package = new ExcelPackage(existingFile))
    {
        //get the first worksheet in the workbook
        ExcelWorksheet worksheet = package.Workbook.Worksheets[1];
        int colCount = worksheet.Dimension.End.Column;  //get Column Count
        int rowCount = worksheet.Dimension.End.Row;     //get row count
        for (int row = 1; row <= rowCount; row++)
        {
            for (int col = 1; col <= colCount; col++)
            {
                //You can update code here to add each cell value to DataTable.
                Console.WriteLine(" Row:" + row + " column:" + col + " Value:" + worksheet.Cells[row, col].Value.ToString().Trim());
            }
        }
    }
}

연락처: http://sforsuresh.in/read-data-excel-sheet-insert-database-table-c/

워크시트에 특정 열에 대한 날짜 형식 데이터가 포함되어 있는 경우에도 Excel에서 각 워크시트의 데이터를 읽으려면 아래 코드를 사용하십시오.

public static DataSet ReadExcelFileToDataSet2(string filePath, bool isFirstRowHeader=true)

    {
        DataSet result = new DataSet();

        Excel.ExcelPackage xlsPackage = new Excel.ExcelPackage(new FileInfo(filePath));  //using Excel = OfficeOpenXml;    <--EPPLUS
        Excel.ExcelWorkbook workBook = xlsPackage.Workbook;

        try
        {
            for (int count = 1; count <= workBook.Worksheets.Count; count++)
            {
                Excel.ExcelWorksheet wsworkSheet = workBook.Worksheets[count];

                if (wsworkSheet.Name.ToLower() == "sheetName")
                {
                    wsworkSheet.Column(4).Style.Numberformat.Format = "MM-dd-yyyy";  // set column value to read as Date Type or numberformat
                }

                DataTable tbl = new DataTable();
                // wsworkSheet.Dimension - (It will return cell dimesion like A1:N7 , means returning the worksheet dimesions.)
                // wsworkSheet.Dimension.End.Address - (It will return right bottom cell like N7)
                // wsworkSheet.Dimension.End.Columns - (It will return count from A1 to N7  like here 14)
                foreach (var firstRowCell in wsworkSheet.Cells[1, 1, 1, wsworkSheet.Dimension.End.Column])  //.Cells[Row start, Column Start, Row end, Column End]
                {
                   var colName = "";
                   colName = firstRowCell.Text;
                   tbl.Columns.Add(isFirstRowHeader ? colName : string.Format("Column {0}", firstRowCell.Start.Column));  //Geth the Column index (index starting with 1) from the left top.
                }
                var startRow = isFirstRowHeader ? 2 : 1;
                for (int rowNum = startRow; rowNum <= wsworkSheet.Dimension.End.Row; rowNum++)
                {
                    var wsRow = wsworkSheet.Cells[rowNum, 1, rowNum, wsworkSheet.Dimension.End.Column]; //  wsworkSheet.Cells[Row start, Column Start, Row end, Column End]
                    DataRow row = tbl.Rows.Add();
                    foreach (var cell in wsRow)
                    {
                        row[cell.Start.Column - 1] = cell.Text;
                    }
                }

                tbl.TableName = wsworkSheet.Name;

                result.Tables.Add(tbl);
            }

        }
        catch (Exception ex)
        {
            Console.WriteLine(ex.Message);
        }

        return result;
    }

언급URL : https://stackoverflow.com/questions/13396604/excel-to-datatable-using-epplus-excel-locked-for-editing