C# -采用npoi对excel进行生僻字与繁体字查找

因工作需要对批量发工资业务进行加密，但加密码后有好多乱码，后分晰原因是加密程序只能“GB2312”编程的字符进行加密，其它的会产生乱码。需要把生僻字与繁体字找出来，现依excel 文件，找出里面不是"GB2312"的字符。代码见下面：

  1 using System;
  2 using System.Text;
  3 using System.Threading.Tasks;
  4 using System.Windows;
  5 using System.IO;
  6 using NPOI.XSSF.UserModel;
  7 using NPOI.HSSF.UserModel;
  8 using Microsoft.Win32;
  9 using NPOI.SS.UserModel;
 10 namespace 代发工资检查工具
 11 {
 12     /// <summary>
 13     /// MainWindow.xaml 的交互逻辑
 14     /// </summary>
 15     public partial class MainWindow : Window
 16     {
 17         public MainWindow()
 18         {
 19             InitializeComponent();
 20         }
 21         private void button1_Click(object sender, RoutedEventArgs e)
 22         {
 23             OpenFileDialog openFileDialog = new OpenFileDialog();
 24             openFileDialog.Filter = "excel文件(xls,xlsx)|*.xls;*.xlsx";
 25             openFileDialog.ShowDialog();
 26             textbox1.Text = openFileDialog.FileName;
 27             if (openFileDialog.FileName.Length == 0)
 28             {
 29                 MessageBox.Show("请选择文件", "提示", MessageBoxButton.OK, MessageBoxImage.Error);
 30                 return;
 31             }
 32             try
 33             {
 34                 using (FileStream fileStream = new FileStream(openFileDialog.FileName, FileMode.Open, FileAccess.Read))
 35                 {
 36                     ISheet sheet = null;
 37                     if (System.IO.Path.GetExtension(openFileDialog.FileName) == ".xls")
 38                     {
 39                         HSSFWorkbook workbook = new HSSFWorkbook(fileStream);
 40                         sheet = workbook.GetSheetAt(0);
 41                     }
 42                     else
 43                     {
 44                         MessageBox.Show("你的文件扩展名是.xlsx，正确因另存为.xls，但程序照样帮您检查生僻字");
 45                         XSSFWorkbook workbook = new XSSFWorkbook(fileStream);
 46                         sheet = workbook.GetSheetAt(0);
 47                     }
 48                     textbox2.Text = "正在检测中...";
 49                     for (int i = 0; i <= sheet.LastRowNum; i++)
 50                     {
 51                         var row = sheet.GetRow(i);
 52                         textbox2.AppendText($"\n正在检查表格第{i + 1}行......表格共{sheet.LastRowNum + 1}行");
 53                         if (row != null)
 54                         {
 55                             for (int j = 0; j < row.LastCellNum; j++)
 56                             {
 57                                 for (int k = 0; k <= row.GetCell(j).ToString().Length - 1; k++)
 58                                 {
 59                                     // MessageBox.Show($"k的值是：{k}.它的当前值是：{row.GetCell(j)},它当前的长度是：{row.GetCell(j).ToString().Length},它的当前测试值是:{row.GetCell(j).ToString()[k].ToString()}");
 60                                     byte[] bytes = Encoding.GetEncoding("GB2312").GetBytes(row.GetCell(j).ToString()[k].ToString());
 61                                     if (bytes.Length <= 1)
 62                                     {
 63                                         byte byte1 = bytes[0];
 64                                         if (byte1 == 32)
 65                                         {
 66                                             MessageBox.Show($"\n 第{i + 1}行的：{row.GetCell(j).ToString()[k]} 有空格会造成乱码的字符");
 67                                             textbox2.AppendText($"\n 第{i + 1}行的：{row.GetCell(j).ToString()[k]} 有空格会造成乱码的字符");
 68                                         }
 69                                         continue;
 70                                     }
 71                                     else
 72                                     {
 73                                         byte byte1 = bytes[0];
 74                                         byte byte2 = bytes[1];
 75                                         if (byte1 >= 176 && byte1 <= 247 && byte2 >= 160 && byte2 <= 254)   //判断是否是GB2312编码
 76                                         {
 77                                             continue;
 78                                         }
 79                                         else
 80                                         {
 81                                             MessageBox.Show($"\n 第{i + 1}行的：{row.GetCell(j).ToString()[k]} 是生僻字或繁体字或会造成乱码的字符");
 82                                             textbox2.AppendText($"\n 第{i + 1}行的：{row.GetCell(j).ToString()[k]} 是生僻字或繁体字或会造成乱码的字符");
 83                                         }
 84                                     }
 85                                 }
 86                             }
 87                         }
 88                     }
 89                 }
 90                 textbox2.AppendText("\n检测完成...");
 91             }
 92             catch (Exception)
 93             {
 94                 // MessageBox.Show("excel表格数据有误，请检查excel表格再试" + "\n\n" + ea);
 95                 MessageBox.Show("excel表格数据有误，是否只改了文件扩展名，正确因打开文件，另存为*.xls");
 96                 return;
 97             }
 98         }
 99     }
100 }

 

posted @ 2020-05-24 21:27  龍行PP  阅读(929)  评论(0)  编辑  收藏  举报