C#操作Excel

作者：追风剑情发布于：2015-5-5 11:56 分类：C#

一、导出Excel中的所有表到表名.txt 文件

示例:

项目->添加引用->COM

添加以下引用库

Microsoft Office 14.0 Object Library

Microsoft Excel 14.0 Object Library

Microsoft Word 14.0 Object Library

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.IO;
using System.Reflection;
using Microsoft.Office.Core;
using Microsoft.Office.Interop.Excel;
 
namespace LanguagePack
{
    class Program
    {
        static void Main(string[] args)
        {
            string excelFileName = "语言包.xlsm";
            int keyCol = 1;//语言key所在列号
            int langCol = 6;//要导出的语言所在列号
 
            if (args.Length > 0)
            {
                langCol = int.Parse(args[0]);
            }
            else
            {
            MENU:
                Console.WriteLine("2、简体中文");
                Console.WriteLine("3、繁体中文");
                Console.WriteLine("4、韩文");
                Console.WriteLine("5、越南文");
                Console.WriteLine("6、英文");
                Console.WriteLine("请选择要导出的语言类型: ");
 
                string menu = Console.ReadLine();
                if (string.IsNullOrEmpty(menu))
                    goto MENU;
 
                langCol = int.Parse(menu);
 
                if (langCol < 2 || langCol > 6)
                {
                    Console.WriteLine("选择无效，请重新选择.");
                    goto MENU;
                }
            }
 
            string excelFilePath = Directory.GetCurrentDirectory() + "\\"+excelFileName;
            Console.WriteLine(excelFilePath);
 
            Application app = new Application();
            Workbooks wbks = app.Workbooks;
            _Workbook _wbk = wbks.Add(excelFilePath);
            Sheets shs = _wbk.Sheets;
            Range range_key, range_val;
            string key, value="";
            
            StringBuilder sb = new StringBuilder();
            for (int i = 1; i <= shs.Count; i++)
            {
                _Worksheet _wsh = (_Worksheet)shs.get_Item(i);
                int lastRow = _wsh.UsedRange.Row + _wsh.UsedRange.Rows.Count - 1;//获取最后一行行号
                Console.WriteLine("正在导出 工作表名称: {0}  总行数: {1}", _wsh.Name, lastRow);
 
                for (int j = 1; j <= lastRow; j++)
                {
                    range_key = _wsh.Cells[j, keyCol];
                    if(null == range_key)
                        continue;
 
                    key = range_key.Value;
                    if (string.IsNullOrEmpty(key))
                        continue;
 
                    range_val = _wsh.Cells[j, langCol];
                    if (null != range_val && null != range_val.Value)
                        value = range_val.Value.ToString();
                    else
                        value = "";
 
                    sb.AppendFormat("{0}\t{1}\r\n", key, value);
                }
 
                if (sb.Length > 0)
                {
                    string path =  Directory.GetCurrentDirectory() + "\\" + _wsh.Name + ".txt";
                    StreamWriter sw = File.CreateText(path);
                    sw.AutoFlush = true;
                    sw.Write(sb.ToString());
                    sw.Flush();
                    sw.Close();
                    sw = null;
                }
            }
 
            Console.WriteLine("语言包导出完毕!");
            Console.ReadKey();
        }
    }
}