using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Microsoft.Office.Interop.Excel;
using Excel = Microsoft.Office.Interop.Excel;
using System.IO;
namespace ConsoleApplication2
{
/// <summary>
/// 术语解释
/// 源excel:即利用perl生成的excel
/// 目标excel：即最终excel（它的中项目sheet要提供需要插入的sheet信息等）
/// 打印dialog：
/// </summary>
class Program
{
/// <summary>
/// 功能：保存一个sheet中的所有表的信息
/// </summary>
class MyDialogSheet
{
object MissingValue = Type.Missing;
public List<MyDialog> m_diaList; // 记录该sheet的所有打印预览表的list
string m_fileName; // 该excel的名字
Excel.Workbook m_ew; // 该book的fd
Excel.Worksheet m_ews; // 该sheet的fd
Excel.Application m_ep; // 打开的excel程序
System.Object[,] m_o; // m_ews的有效区
List<MyFile> m_fileList; // "中项目A"那个表的解析的时候获取信息
Microsoft.Office.Interop.Excel.Range m_range; // m_o相关
bool m_flag; // close flag
/// <summary>
/// 功能：
/// </summary>
/// <param name="filename"></param>
/// <param name="ep"></param>
/// <param name="page"></param>
public MyDialogSheet(string filename, Excel.Application ep, int page)
{
m_fileName = filename; // excel文件的名字
m_ep = ep;
m_ew = m_ep.Workbooks.Open(filename.ToString(),
MissingValue, MissingValue, MissingValue);
m_diaList = new List<MyDialog> { };
m_ews = null;
m_flag = false;
InitEWS(page);
}
/// <summary>
/// 功能：关闭excel
/// </summary>
public void CloseExcel()
{
m_ew.Close();
m_flag = true;
}
~MyDialogSheet()
{
if (!m_flag)
{
m_ew.Save();
m_ew.Close();
}
}
/// <summary>
/// 功能：初始化有效页（sheet）
/// </summary>
/// <param name="page"></param>
public void InitEWS(int page)
{
m_ews = (Excel.Worksheet)m_ew.Worksheets[page];
m_range = m_ews.get_Range("A1", "K500");
m_o = (System.Object[,])m_range.Value2;
}
/// <summary>
/// 功能：返回打印页个数
/// </summary>
/// <returns></returns>
public int Count()
{
return m_diaList.Count();
}
/// <summary>
/// 功能：记录该有效sheet的打印表的信息
/// </summary>
/// <param name="factor">标志打印表头</param>
public void InitDialogListInfo(string factor)
{
/*
* zl : K500===> 500 可能不够哦
*/
int flag = 0; // 作为我判断是否还有表格的根据.如果B列的空白格大于5个，则我认为接下来没有表格了。
for (int row = 1; row < 500; row++)
{
string tmp = null;
if (m_o[row, 2] == null)
{
tmp = null;
flag++;
}
else
{
tmp = m_o[row, 2].ToString();
flag = 0;
}
if (flag >= 5)
{
// 如果B列的空白格大于5个，则我认为接下来没有表格了。推出循环
break;
}
// 如果包含factor指定的字符串，则表明是表头
if (tmp != null && tmp.Contains(factor))
{
tmp = tmp.Replace('(', ' ');
string[] strs = tmp.Split(); // 取出了名字是“A-1”
string name = strs[1];
MyDialog diaTmp = new MyDialog(row, name);
m_diaList.Add(diaTmp);
}
else
{
}
}
}
/// <summary>
/// 功能：初始化中项目表记录的.c文件包含哪些函数这样的信息
/// </summary>
public void InitFileInfo()
{
m_fileList = new List<MyFile> { };
string filename1 = null; // 作为我判断是否是同一个.c文件的根据。filename1 == filename2 ： 表明是同一个.c文件的函数
for (int index = 0; index < m_diaList.Count(); index++)
{
int start = m_diaList[index].getRow() + 3; // 打印表的起始行
int end = 0;
// 打印表的结束行
if (index + 1 < m_diaList.Count()) // 不是最后一个表的时候
{
end = m_diaList[index + 1].getRow() - 4;
}
else // 最后一个表的时候
{
end = start + 30; // 有可能大于30哦！
}
for (int j = start; j <= end; j++)
{
if (m_o[j,8] == null) // 作为我判断是否还有行的根据.如果没有内容，则我认为接下来没有表格了。
break;
string filename2 = m_o[j, 8].ToString();
string funcInfo = m_o[j, 3].ToString();
string funcNameTmp = m_o[j, 4].ToString();
string[] tmpStrs = funcNameTmp.Split('\n');
string funcName = tmpStrs[0];
string proStr = m_o[j, 2].ToString();
proStr = m_diaList[index].getName() + "-" + proStr;
if (filename1 == filename2) // 表明是同一个.c文件的函数
{
funcName = funcName.Trim();
MyFuncInfo funcTmp = new MyFuncInfo(proStr, funcName, funcInfo);
m_fileList[m_fileList.Count() - 1].InsertFuncInfo(funcTmp);
}
else // 是一个新的.c文件的时候
{
MyFile tmpFile = new MyFile(filename2); // 添加一个新的file对象
m_fileList.Add(tmpFile);
MyFuncInfo funcTmp = new MyFuncInfo(proStr, funcName, funcInfo);
m_fileList[m_fileList.Count() - 1].InsertFuncInfo(funcTmp);
}
filename1 = filename2;
}
}
}
/// <summary>
/// 功能：添加记录.c文件信息的sheet
/// </summary>
/// <param name="fileNo">处理第几个.c文件</param>
public void AddFileSheet(int fileNo)
{
m_ew.Worksheets.Add(MissingValue, m_ep.Worksheets[m_ew.Sheets.Count], MissingValue, MissingValue); // 插入到excel book的最后
((Excel.Worksheet)m_ew.Worksheets[m_ew.Sheets.Count]).Name = m_fileList[fileNo].getFileName();
for (int fCnt = 0; fCnt < m_fileList[fileNo].getFuncTotal(); fCnt++)
{
((Excel.Worksheet)m_ep.Sheets[m_ew.Sheets.Count]).Cells[fCnt + 1, 1] = m_fileList[fileNo].getFuncInfo(fCnt).getFunPro() + " " + m_fileList[fileNo].getFuncInfo(fCnt).getFunName();
}
Excel.Worksheet ews = (Excel.Worksheet)m_ew.Worksheets[m_ew.Worksheets.Count];
ews.Tab.Color = /*ColorTranslator.ToOle(Color.GreenYellow)*//*12632256*/160160164; // 中灰色
}
/// <summary>
/// 功能：把函数的sheet拷贝这个book中
/// </summary>
/// <param name="sheet">源excel sheet</param>
/// <param name="diaList">源excel sheet的打印表的信息</param>
/// <param name="fileNO">该excel的.c文件的index</param>
/// <param name="funcNO">该函数的index</param>
public void AddFuncSheet(Excel.Worksheet sheet, List<MyDialog> diaList, int fileNO, int funcNO)
{
sheet.Copy(MissingValue, /*m_ep.Worksheets[m_ew.Sheets.Count]*/(Excel.Worksheet)m_ew.Worksheets[m_ew.Worksheets.Count]);
Excel.Worksheet ews = (Excel.Worksheet)m_ew.Worksheets[m_ew.Worksheets.Count];
2-1-1,填入信息
int colPg = 14;
int colPro = 2;
int colFunc = 10;
int colFunc2 = 4;
int rowPg = 0;
int rowPro = 0;
int rowFunc = 0;
int rowFunc2 = 0;
for (int i = 0; i < diaList.Count(); i++)
{
int row = diaList[i].getRow();
rowPg = row - 2;
rowPro = row;
rowFunc = row;
rowFunc2 = row + 5;
ews.Cells[rowPg, colPg] = m_ew.Worksheets.Count.ToString();
ews.Cells[rowPg, colPg + 1] = "/";
//ews.Cells[rowPg, colPg + 2] =
int j = i + 1;
ews.Cells[rowPro, colPro] = "小項目 " + m_fileList[fileNO].getFuncInfo(funcNO).getFunPro() + "(" + j.ToString() + "/";
ews.Cells[rowFunc, colFunc] = m_fileList[fileNO].getFuncInfo(funcNO).getFunInfo();
ews.Cells[rowFunc2, colFunc2] = m_fileList[fileNO].getFuncInfo(funcNO).getFunInfo();
}
2-1-2,设置列宽
((Range)ews.Cells[1, 1]).EntireColumn.ColumnWidth = 1.88;
((Range)ews.Cells[2, 2]).EntireColumn.ColumnWidth = 3.13;
((Range)ews.Cells[3, 3]).EntireColumn.ColumnWidth = 15.33;
((Range)ews.Cells[4, 4]).EntireColumn.ColumnWidth = 8.63;
((Range)ews.Cells[5, 5]).EntireColumn.ColumnWidth = 4.88;
((Range)ews.Cells[6, 6]).EntireColumn.ColumnWidth = 4.88;
((Range)ews.Cells[7, 7]).EntireColumn.ColumnWidth = 4.88;
((Range)ews.Cells[8, 8]).EntireColumn.ColumnWidth = 3.13;
((Range)ews.Cells[9, 9]).EntireColumn.ColumnWidth = 4.88;
((Range)ews.Cells[10, 10]).EntireColumn.ColumnWidth = 3.13;
((Range)ews.Cells[11, 11]).EntireColumn.ColumnWidth = 5.38;
((Range)ews.Cells[12, 12]).EntireColumn.ColumnWidth = 3.13;
((Range)ews.Cells[13, 13]).EntireColumn.ColumnWidth = 2.63;
((Range)ews.Cells[14, 14]).EntireColumn.ColumnWidth = 3.25;
((Range)ews.Cells[15, 15]).EntireColumn.ColumnWidth = 1.25;
((Range)ews.Cells[16, 16]).EntireColumn.ColumnWidth = 3.25;
((Range)ews.Cells[17, 17]).EntireColumn.ColumnWidth = 1.88;
// 设置sheet的颜色
ews.Tab.Color = 0xFF6633;
}
public void MyRun()
{
}
public int getFileSum()
{
return m_fileList.Count();
}
public MyFile getFile(int fileIndex)
{
return m_fileList[fileIndex];
}
public int getSheetTotal()
{
return m_ew.Worksheets.Count;
}
public Excel.Worksheet getSheet(int index)
{
return (Excel.Worksheet)m_ew.Worksheets[index];
}
}
/// <summary>
/// 这里是记录每个表（是指打印预览所呈现的表，以下我称为表）的信息
/// </summary>
class MyDialog
{
int rowNum; // 记录表头的起始行号
string diaName; // 记录表的名字信息。eg：“中项目A"我就是记录”A-1“这样的信息
public MyDialog(int row, string dia)
{
rowNum = row;
diaName = dia;
}
public int getRow()
{
return rowNum;
}
public string getName()
{
return diaName;
}
}
/// <summary>
/// 信息获取：这个类主要是在"中项目A"那个表的解析的时候获取信息。
/// 功能：记录的是每个函数的相关信息
/// 用途：在添加函数的表中用到其中的信息
/// </summary>
class MyFuncInfo
{
string proName; // 项目的名称。eg：A-1-1...
string funName; // 函数的名字
string funInfo; // 函数功能等信息
public MyFuncInfo(string pro, string funNam, string funInf)
{
proName = pro;
funName = funNam;
funInfo = funInf;
}
public string getFunName()
{
return funName;
}
public string getFunPro()
{
return proName;
}
public string getFunInfo()
{
return funInfo;
}
}
/// <summary>
/// 功能：记录每个.c的信息
/// 信息获取：这个类主要是在"中项目A"那个表的解析的时候获取信息。
/// 用途：生成.c文件描述sheet的时候用到
/// </summary>
class MyFile
{
List<MyFuncInfo> funcList; // 包含的函数的list
string fileName; // 文件名字
public MyFile(string name)
{
funcList = new List<MyFuncInfo> { };
fileName = name;
}
// 插入函数信息
public void InsertFuncInfo(MyFuncInfo info)
{
funcList.Add(info);
}
public MyFuncInfo getFuncInfo(int i)
{
return funcList[i];
}
// 得到所包含的函数的总个数
public int getFuncTotal()
{
return funcList.Count();
}
// 你懂的
public string getFileName()
{
return fileName;
}
}
static void Main(string[] args)
{
object MissingValue = Type.Missing;
//
Excel.Application ep = new Excel.ApplicationClass();
string factor = "目标名字";
// filename表示，目标excel
Console.WriteLine("drag your file to here:");
string filename = null;
filename = Console.ReadLine();
filename = filename.Replace("\"", "");
filename = filename.Replace("/", "//");
MyDialogSheet DiaList = new MyDialogSheet(filename, ep, 4); //
DiaList.InitDialogListInfo(factor); // 得到该sheet的打印表的信息
DiaList.InitFileInfo(); // 得到所有.c文件的信息
//bool flag = true; ///不需要的
MyDialogSheet DiaList2 = null;
Console.WriteLine("drag your diretory to here:");
// filename2表示，源excel
string filename2 = null;
filename2 = Console.ReadLine();
filename2 = filename2.Replace("\"", "");
filename2 = filename2.Replace("/", "//");
DirectoryInfo dir = new DirectoryInfo(filename2);
for (int cnt = 0; cnt < DiaList.getFileSum(); cnt++)
{
// 1,添加file sheet
DiaList.AddFileSheet(cnt);
/// 1-2,往file sheet中写入信息
// 2,添加func sheet
MyFile fileTmp = DiaList.getFile(cnt);
for (int fCnt = 0; fCnt < fileTmp.getFuncTotal(); fCnt++)
{
/// 2-1,遍历去找相应的func的sheet。找到则copy，否则，停止，并报错！
string funcNameTmp = fileTmp.getFuncInfo(fCnt).getFunName();
string funcInfoTmp = fileTmp.getFuncInfo(fCnt).getFunInfo();
string funcProTmp = fileTmp.getFuncInfo(fCnt).getFunPro();
bool flag = false;
foreach (FileInfo dChild in dir.GetFiles("*.xls"))
{
int i = 1;
DiaList2 = new MyDialogSheet(dChild.FullName.ToString(), ep, 1);
for (; i <= DiaList2.getSheetTotal(); i++)
{
Excel.Worksheet ewsTmp = (Excel.Worksheet)DiaList2.getSheet(i);
string strTmp = ewsTmp.Name.ToString().Trim();
if (strTmp == funcNameTmp)
{
2-1-0,copy
string name = ewsTmp.Name;
DiaList2.InitEWS(i);
factor = "小項目";
DiaList2.InitDialogListInfo(factor);
DiaList.AddFuncSheet(ewsTmp, DiaList2.m_diaList, cnt, fCnt);
break;
}
}
if (i > DiaList2.getSheetTotal())
{
/// 2-2,没有找到，则报错！
DiaList2.CloseExcel();
flag = false;
}
else
{
/// 本来在这里释放的
flag = true;
DiaList2.CloseExcel();
break;
}
}
if (flag == false)
{
Console.WriteLine("没有找到《{0}》，中断处理", funcNameTmp);
return;
}
}
}
}
}
}
扫一扫
1287
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
