C# (NPOI) Excel 읽기

(NPOI) Excel 읽기

 

 

1. 누겟에서 NPOI 설치

 

2. code 작성

using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;
using System;
using System.Collections.Generic;
using System.Data;
using System.IO;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows;
using System.Windows.Controls;
using System.Windows.Data;
using System.Windows.Documents;
using System.Windows.Input;
using System.Windows.Media;
using System.Windows.Media.Imaging;
using System.Windows.Navigation;
using System.Windows.Shapes;
using System.Xml.Linq;
 
namespace WPFExcelTest
{
    /// <summary>
    /// MainWindow.xaml에 대한 상호 작용 논리
    /// </summary>
    public partial class MainWindow : Window
    {
        string filepath = @"C:\2018_09_18.xlsx";
        string filepath_copy = @"D:\2018_09_18.xlsx";
        string filepath_save = @"D:\2018_09_18.xml";
 
        public MainWindow()
        {
            InitializeComponent();
 
            File.Copy(filepath, filepath_copy, true);
            GetDataTableFromExcel(filepath_copy);
        }
 
        private void GetDataTableFromExcel(String Path)
        {
            CreateXElement();
 
            XSSFWorkbook wb;
            XSSFSheet sh;
            String Sheet_name;
 
            using (var fs = new FileStream(Path, FileMode.Open, FileAccess.Read))
            {
                wb = new XSSFWorkbook(fs);
 
                Sheet_name = wb.GetSheetAt(0).SheetName;  //get first sheet name
            }
 
            // get sheet
            sh = (XSSFSheet)wb.GetSheet(Sheet_name);
 
            int scenarioNum = 0;
            int scenarioStartRow = 5;
            int i = scenarioStartRow - 1;
 
            int tvCell = 7;
 
            while (sh.GetRow(i) != null && i < 29)
            {
                AddNode(++scenarioNum, 
                    ConvertCellToString(sh.GetRow(i).GetCell(0)), 
                    ConvertCellToDouble(sh.GetRow(i).GetCell(1)), 
                    (int)ConvertCellToDouble(sh.GetRow(i).GetCell(2)), 
                    ConvertCellToString(sh.GetRow(i).GetCell(3)),
                    ConvertCellToString(sh.GetRow(i).GetCell(tvCell)),
                    ConvertCellToString(sh.GetRow(i).GetCell(tvCell + 1)));
                i++;
            }
 
            SaveXml(filepath_save);
 
            //while (sh.GetRow(i) != null)
            //{
            //    // add neccessary columns
            //    if (DT.Columns.Count < sh.GetRow(i).Cells.Count)
            //    {
            //        for (int j = 0; j < sh.GetRow(i).Cells.Count; j++)
            //        {
            //            DT.Columns.Add("", typeof(string));
            //        }
            //    }
 
            //    // add row
            //    DT.Rows.Add();
 
            //    // write row value
            //    for (int j = 0; j < sh.GetRow(i).Cells.Count; j++)
            //    {
            //        var cell = sh.GetRow(i).GetCell(j);
 
            //        if (cell != null)
            //        {
            //            // TODO: you can add more cell types capatibility, e. g. formula
            //            switch (cell.CellType)
            //            {
            //                case NPOI.SS.UserModel.CellType.Numeric:
            //                    var num = sh.GetRow(i).GetCell(j).NumericCellValue;
            //                    //dataGridView1[j, i].Value = sh.GetRow(i).GetCell(j).NumericCellValue;
 
            //                    break;
            //                case NPOI.SS.UserModel.CellType.String:
            //                    //DT.Rows[i][j] 
            //                    var str = sh.GetRow(i).GetCell(j).StringCellValue;
 
            //                    break;
            //            }
            //        }
            //    }
 
            //    i++;
            //}
 
            //return DT;
        }
 
        XElement doc;
        public void CreateXElement()
        {
            doc = new XElement("Root");
        }
 
        public void AddNode(int id, string player, double timming, int code, string scenarioName, string tv, string tvMsg)
        {
            XElement contents = new XElement("Scenario", new XAttribute("Id", id),
            new XElement("Scene",
                 new XElement("Player", player)
               , new XElement("Timming", timming)
               , new XElement("Code", code)
               , new XElement("ScenarioName", scenarioName)
               , new XElement("Device",
                      new XElement("Name", "tv")
                    , new XElement("ScenarioName", tv)
                    , new XElement("Msg", tvMsg)
                    )
               ));
 
            doc.Add(contents);
        }
 
        public void SaveXml(string path)
        {
            doc.Save(path);
        }
 
        public double ConvertCellToDouble(ICell cell)
        {
            if (cell == null)
            {
                return 0;
            }
 
            return cell.NumericCellValue;
        }
 
        public string ConvertCellToString(ICell cell)
        {
            if (cell == null)
            {
                return string.Empty;
            }
            if (cell.CellType == CellType.Numeric)
            {
                return cell.NumericCellValue.ToString();
            }
 
            return cell.StringCellValue;
        }
    }
}