C# WPF 프로그래밍 - 윈폼과 달라 헤멘 것들

0. 엑셀화일 읽기

     //   PM > Install-Package QRCoder -Version 1.3.6                QR코드/바코드
     //   PM > Install-Package SpreadsheetLight -Version 3.4.11      엑셀
     //   PM > Install-Package DocumentFormat.OpenXml -Version 2.5.0 (MUST)

     using DocumentFormat.OpenXml.Spreadsheet;   // Excel - by 'http://spreadsheetlight.com/'
     using SpreadsheetLight;                     // SpreadsheetLight -Version 3.4.11
     using SpreadsheetLight.Drawing;

 

1. DataGrid 의 열과 항목 값 읽기

            int dgRowNo = dgAsset.Items.Count;
            for (int rowNdx = 1; rowNdx < dgRowNo - 1; rowNdx++)
            {
                DataRowView d = dgAsset.Items[rowNdx] as System.Data.DataRowView;
                string a = d.Row.ItemArray[4].ToString();
                tblAssetIDFound.Text = a;
                if (a.Equals(tblAssetIDtest.Text))
                {
                    //dgAsset.CurrentItem = dgAsset.Items[rowNdx];
                    dgAsset.SelectedIndex = rowNdx;
                    dgAsset.ScrollIntoView(dgAsset.SelectedItem, null);
                    break;
                }
            }

      두번째

            DataRowView d = dGrid.Items[curRowNo] as System.Data.DataRowView;
            for (int ColNdx = 0; ColNdx < AssetColNoMax + 2; ColNdx++)
            {
                QRcodeText += d.Row.ItemArray[ColNdx].ToString() + ";";
            }

 

2. QR 코드 만들기

            QRCodeGenerator qrGenerator = new QRCodeGenerator();
            QRCodeData qrCodeData = qrGenerator.CreateQrCode(QRcodeText, QRCodeGenerator.ECCLevel.Q);
            QRCode qrCode = new QRCode(qrCodeData);
            Bitmap qrCodeImage = qrCode.GetGraphic(20);

 

3. DataGrid에서 엑셀화일로 데이터들 보내기

            dataGrid.SelectAllCells();                                      // DataGrid -> clipboard
            dataGrid.ClipboardCopyMode = DataGridClipboardCopyMode.IncludeHeader;
            ApplicationCommands.Copy.Execute(null, dataGrid);
            dataGrid.UnselectAllCells();
            String result = (string)Clipboard.GetData(DataFormats.CommaSeparatedValue);
            string[] Lines = result.Split(new string[] { "\r\n", "\n" }, StringSplitOptions.None);
            string[] Fields;
            Fields = Lines[0].Split(new char[] { ',' });                    // 컬럼 갯수확인
            // -----------------------------------------------------------------------------------
            int ColsNo = Fields.GetLength(0);
            string temp = Fields[8];                                        // 전일비 - 보유율 교환
            Fields[8] = Fields[2]; Fields[2] = temp;
            for (int col = 0; col < ColsNo; col++)                          // 첫줄 = 헤더
                sl.SetCellValue(1, col + 1, Fields[col]);
            // -----------------------------------------------------------------------------------
            Double[] maxMin = { 0, 0, 0, 0 };                       // 최대최소 = primary, seconday
            SLStyle style = sl.CreateStyle();
            for (int row = 1; row < Lines.GetLength(0) - 1; row++)
            {
                Fields = Lines[row].Split(new char[] { ',' });              // 모든 줄 처리 시작
                for (int col = 0; col < ColsNo; col++)
                {
                    if (Fields[col].Length < 1) continue;                   // '\n' 만 있는 빈줄 ...
                    string tempX = Fields[8]; Fields[8] = Fields[2];        // 전일비 - 보유율 교환
                    Fields[2] = tempX;
                    if (col == 0)
                    {
                        sl.SetCellValue(row + 1, col + 1, Fields[col]);
                    }
                    //else if (col == 2 || col == 3)                          // 등락율, 보유율
                    else if (Fields[col].Contains("%"))
                    {
                        double percent = Convert.ToDouble((Fields[col].Replace("%", "").ToString()));
                        sl.SetCellValue(row + 1, col + 1, percent / 100);
                        style.FormatCode = "0.00%";
                        sl.SetCellStyle(row + 1, col + 1, style);
                    }
                    else
                    {
                        sl.SetCellValueNumeric(row + 1, col + 1, Fields[col]);
                        style.FormatCode = "#,##0";
                        sl.SetCellStyle(row + 1, col + 1, style);
                    }
                }
            }
            sl.AutoFitColumn(1, ColsNo);                                    // 컬럼 넓이 조정
        }

 

4. 엑셀에서 DataGrid로 데이터 보내기

           SLDocument sl = new SLDocument(XlsFilename);
            List sheetNames = sl.GetSheetNames();
            sl.SelectWorksheet(sheetNames[0]);            // 시트명이 'sheet1'이 아니므로
            SLWorksheetStatistics state = sl.GetWorksheetStatistics();
            int noColumn = state.EndColumnIndex;
            if (noColumn > WorkRoomColNoMax) noColumn = WorkRoomColNoMax;
            DataTable dTable = new DataTable();
            dTable.Clear();
            DataRow dRow = dTable.NewRow();

            // Read 1st row and Insert as header to DatGrid
            for (int colNdx = 1; colNdx <= noColumn; colNdx++)
            {
                string strValue = sl.GetCellValueAsString(1, colNdx);
                dTable.Columns.Add(strValue, typeof(string));
            }
            // read data
            noWorkRoom = state.EndRowIndex;                         
            for (int rowNdx = 2; rowNdx <= noWorkRoom; rowNdx++)
            {
                string ExcelData = "";
                for (int colNdx = 1; colNdx <= noColumn; colNdx++)
                {
                    string strValue = sl.GetCellValueAsString(rowNdx, colNdx);
                    if (colNdx == 1 && strValue == null) continue;  // 빈줄 건너뜀
                    if (colNdx == 1 && strValue == "") continue;
                    ExcelData += strValue + ";";
                }
                ExcelData = ExcelData.Remove(ExcelData.Length - 1, 1);
                dTable.Rows.Add(ExcelData.Split(';'));
            }
            dGrid.ItemsSource = dTable.DefaultView;
            dGrid.IsReadOnly = true;                                
            ExcelWorkRoom = XlsFilename;