首页 新闻 搜索 专区 学院

C# 关于Excel的操作。在线等!!!

0
悬赏园豆:200 [已解决问题] 解决于 2014-05-23 13:28

如下图(看不到,刷新两下),C# C/s架构

1.我需要导入下面这个EXCEL。

2.将Excel中每一行数据中的加粗斜体字用键盘上相应的字符替换掉。(1=! 2=@ 3=# 4=$  5=%  6=^  7=&  8=*  9=(  0=))

3.将该表加载到一个缓存中或者直接保存修改。  求代码或者具体的思路……

(本人用VBA语言解决了,但求C#语言的解决方法)

论坛没回应的话 可以联系我的邮箱:393278126@qq.com   全部豆压上了!在线等!!!

问题补充:

下面是VBA的解决办法,提供给大家一点思路~C#求大神解决!!!

Sub 替换()

Dim VCell As Range

Dim i As Integer

Dim j As Integer

For i = 1 To 1000

Set VCell = Sheet1.Cells(i, 1)

For j = 1 To 12

If VCell.Characters(j, 1).Font.Bold = True Then

Select Case VCell.Characters(j, 1).Text

Case "0" VCell.Characters(j, 1).Text = "("

Case "1" VCell.Characters(j, 1).Text = "!"

Case "2" VCell.Characters(j, 1).Text = "@"

Case "3" VCell.Characters(j, 1).Text = "#"

Case "4" VCell.Characters(j, 1).Text = "$"

Case "5" VCell.Characters(j, 1).Text = "%"

Case "6" VCell.Characters(j, 1).Text = "^"

Case "7" VCell.Characters(j, 1).Text = "&"

Case "8" VCell.Characters(j, 1).Text = "*"

Case "9" VCell.Characters(j, 1).Text =

"(" End Select End If Next j Next i

End Sub

软动力的主页 软动力 | 初学一级 | 园豆:22
提问于:2014-05-23 09:36
< >
分享
最佳答案
0

图挂了

收获园豆:180
nianhua11 | 小虾三级 |园豆:736 | 2014-05-23 09:45

马上重新上图,刚没注意!

软动力 | 园豆:22 (初学一级) | 2014-05-23 09:46

马上重新上图,刚没注意!

软动力 | 园豆:22 (初学一级) | 2014-05-23 09:50

@软动力: 用NPOI读取excel,然后判断单元格内每个字符的格式,找着格式异于其它字符的,替换掉就好了。

using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using System;
using System.IO;

namespace ConsoleApplication1 {
    class Program {
        static void Main(string[] args) {
            string filePath = @"D:\aaa.xls";
            FileStream file = new FileStream(filePath, FileMode.Open);
            IWorkbook workbook = new HSSFWorkbook(file);
            ISheet sheet = workbook.GetSheetAt(0);
            IRow row = sheet.GetRow(0);
            for (int i = 0; i < row.GetCell(0).StringCellValue.Length; i++) {
                Console.WriteLine(row.GetCell(0).RichStringCellValue.GetFontAtIndex(i));
            }

            file.Close();

            Console.ReadLine();
        }
    }
}
nianhua11 | 园豆:736 (小虾三级) | 2014-05-23 10:39

@Weca: 我先去试验一下 如果OK  马上结贴!

软动力 | 园豆:22 (初学一级) | 2014-05-23 10:40

@Weca: 为什么我的IWorkbook,ISheet ,IROw  找不到对象类型或命名空间?我下载的NPOI.dll有问题?    方便的话  加个QQ呗 哥们   393278126   李权  或者发个私人邮件   393278126@qq.com

软动力 | 园豆:22 (初学一级) | 2014-05-23 10:58
其他回答(2)
0

网上的NOPI 代码貌似有点滞后;因为NPOI.dll更新了;调下就好了;首先要理解IWorkBook;ISheet;IRow 这几个对象类型具体要表达什么;这样就很好操作了

下面是如何读取EXECL 表中行列数据的:

 1      /// <summary>
 2         /// 从文件流导入DataSet
 3         /// </summary>
 4         /// <param name="excelFileStream"></param>
 5         /// <param name="headerRowIndex"></param>
 6         /// <returns></returns>
 7         public static string ImportDataSetFromExcel(Stream excelFileStream, string str)
 8         {
 9            
10                 JavaScriptSerializer JssData = new JavaScriptSerializer();
11                 List<ErpChildVersion> relist = new List<ErpChildVersion>();
12                 HSSFWorkbook workbook = new HSSFWorkbook(excelFileStream);
13                 for (int a = 0, b = workbook.NumberOfSheets; a < b; a++)
14                 {
15                     ISheet sheet = workbook.GetSheetAt(a);
16                     DataTable table = new DataTable();
17                     IRow headerRow = sheet.GetRow(1);
18                     int cellCount = headerRow.LastCellNum;
19                     
20                     for (int i = (sheet.FirstRowNum + 1); i <= sheet.LastRowNum; i++)
21                     {
22                         IRow row = sheet.GetRow(i);
23                         if ( row.GetCell(0).ToString() == str)
24                         {
25                             for (int k = i; k < sheet.LastRowNum; k++)
26                             {
27                                 row = sheet.GetRow(k);
28                                 if (sheet.GetRow(k + 1).GetCell(0).ToString().Trim() != "") break;
29                                 relist.Add(new ErpChildVersion {name = row.GetCell(3).ToString(),Value=row.GetCell(4).ToString() });
30                             }
31                             return JssData.Serialize(relist).ToString(); 
32                         }                                             
33                     }
34                 }
35                 excelFileStream.Close();
36                 workbook = null;
37                 return "NULL";
38            
39         }
40         /// <summary>
41         /// 根据查询条件从Excel查询表格
42         /// </summary>
43         /// <param name="excelFilePath"></param>
44         /// <param name="headerRowIndex"></param>
45         /// <returns></returns>
46         public static string ImportDataSetFromExcel(string excelFilePath,string str)
47         {
48             using (FileStream stream = System.IO.File.OpenRead(excelFilePath))
49             {
50                 return ImportDataSetFromExcel(stream, str);
51             }
52         }
53     }
54     public  class ErpChildVersion
55    {
56         public string name { get; set; }
57         public string Value { get; set; }
58    }

 

收获园豆:20
Sake.Z.L.B | 园豆:262 (菜鸟二级) | 2014-05-23 13:20

不好意思哦~~楼上的兄弟已经帮我解答了~~但还是会给你分的哈~ 谢谢了!~~

支持(0) 反对(0) 软动力 | 园豆:22 (初学一级) | 2014-05-23 13:27
0
        /// <summary>
        /// 导入整个excel
        /// </summary>
        /// <param name="filePath"></param>
        /// <param name="SheetName"></param>
        /// <returns></returns>
        private DataSet LoadDataFromExcel(string fileName, string SheetName)
        {
            try
            {
                string stro = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + fileName + ";Extended Properties='Excel 8.0;HDR=NO;IMEX=1; '";
                OleDbConnection oco = new OleDbConnection(stro);
                string str1o = "SELECT * FROM [" + SheetName + "$]";
                oco.Open();
                OleDbDataAdapter odao = new OleDbDataAdapter(str1o, oco);
                DataSet dso = new DataSet();
                odao.Fill(dso);
                oco.Close();
                return dso;
            }
            catch (Exception err)
            {
                return null;
            }
        }
刘宏玺 | 园豆:14016 (专家六级) | 2014-05-23 13:21
清除回答草稿
   您需要登录以后才能回答,未注册用户请先注册