/// <summary>
/// 把指定的DataTable里面的数据引用模板Excel并且输出到指定位置 引用路径需要自行拼接成绝对路径
/// </summary>
/// <param name="InputDGV">输入的DataGridView</param>
/// <param name="TemplatesExcelPath">模板Excel位置</param>
/// <param name="OutputExcelPath">输出的Excel位置</param>
/// <param name="StartRow">Excel中第一行出现的位置,这样可以通过模板设置列头</param>
/// <returns></returns>
static public bool OutputExcelFromDataTable_path(System.Data.DataTable InputDt, string TemplatesExcelPath, string OutputExcelPath, int StartRow)
{
//引用Excel Application類別
_Application myExcel = null;
//引用活頁簿類別
_Workbook myBook = null;
//引用工作表類別
_Worksheet mySheet = null;
try
{
//開啟一個新的應用程式
myExcel = new Microsoft.Office.Interop.Excel.Application();
myExcel.Workbooks.Open(TemplatesExcelPath, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
//停用警告訊息
myExcel.DisplayAlerts = false;
//讓Excel文件可見
myExcel.Visible = false;
//引用第一個活頁簿
myBook = myExcel.Workbooks[1];
//設定活頁簿焦點
myBook.Activate();
//引用第一個工作表
mySheet = (_Worksheet)myBook.Worksheets[1];
//設工作表焦點
mySheet.Activate();
if (InputDt.Rows.Count != 0)
{
for (int i = 0; i < InputDt.Rows.Count; i++)
{
for (int j = 0; j < InputDt.Columns.Count; j++)
{
mySheet.Cells[i + StartRow + 1, j + 1] = Convert.ToString(InputDt.Rows[i][j]);
}
}
}
myBook.SaveAs(OutputExcelPath, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, XlSaveAsAccessMode.xlExclusive, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
//MessageBox.Show("输出成功！");
return(true);
}
catch (Exception me)
{
//MessageBox.Show("请确认是否有同名Excel文件 " + me.Message);
throw me;
//return false;
}
finally
{
//釋放Excel資源
myBook.Close(false, Type.Missing, Type.Missing);
System.Runtime.InteropServices.Marshal.FinalReleaseComObject(myBook);
myExcel.Quit();
System.Runtime.InteropServices.Marshal.FinalReleaseComObject(myExcel);
GC.Collect();
GC.WaitForPendingFinalizers();
}
}
/// <summary>
/// 读取Excel，返回Xml.第一行建议为列名，方便以后处理 注意只能读取第一页
/// 注意,Excel中的时间格式,读取过来以后,必须用以下方式转换才行
/// DateTime.FromOADate(double.Parse(item.Element("Column" + i.ToString()).Value)))
/// </summary>
/// <param name="ReadExcelPath">读取Excel的路径</param>
/// <returns></returns>
static public string ReadExcelReturnXml(string ReadExcelPath)
{
//引用Excel Application類別
_Application myExcel = null;
//引用活頁簿類別
_Workbook myBook = null;
//引用工作表類別
_Worksheet mySheet = null;
try
{
//開啟一個新的應用程式
myExcel = new Microsoft.Office.Interop.Excel.Application();
//myExcel.Workbooks.Open(AppDomain.CurrentDomain.BaseDirectory + ReadExcelPath, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
myExcel.Workbooks.Open(ReadExcelPath, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
//停用警告訊息
myExcel.DisplayAlerts = false;
//讓Excel文件可見
myExcel.Visible = false;
//引用第一個活頁簿
myBook = myExcel.Workbooks[1];
//設定活頁簿焦點
myBook.Activate();
//引用第一個工作表
mySheet = (_Worksheet)myBook.Worksheets[1];
//設工作表焦點
mySheet.Activate();
Array myvalues = (Array)mySheet.UsedRange.Cells.Value2;
int lie = mySheet.UsedRange.Columns.Count;
int hang = mySheet.UsedRange.Rows.Count;
XElement xmlTree = new XElement("ExcelContent");
for (int mhang = 1; mhang <= hang; mhang++)
{
xmlTree.Add(new XElement("Row"));
for (int mlie = 1; mlie <= lie; mlie++)
{
string mzhi = Convert.ToString(myvalues.GetValue(mhang, mlie));
xmlTree.Elements("Row").Last().Add(new XElement("Column" + mlie.ToString(), mzhi));
}
}
return(xmlTree.ToString());
}
catch (Exception me)
{
throw me;
}
finally
{
//釋放Excel資源
myBook.Close(false, Type.Missing, Type.Missing);
System.Runtime.InteropServices.Marshal.FinalReleaseComObject(myBook);
myExcel.Quit();
System.Runtime.InteropServices.Marshal.FinalReleaseComObject(myExcel);
GC.Collect();
GC.WaitForPendingFinalizers();
}
}
/// <summary>
/// 生成Html格式的Excel，往往用于Web显示
/// </summary>
/// <param name="ExcelPath"></param>
/// <returns></returns>
static public bool CreatHtml(string ExcelPath, string OutPath)
{
//引用Excel Application類別
_Application myExcel = null;
//引用活頁簿類別
_Workbook myBook = null;
//引用工作表類別
_Worksheet mySheet = null;
try
{
//開啟一個新的應用程式
myExcel = new Microsoft.Office.Interop.Excel.Application();
myExcel.Workbooks.Open(AppDomain.CurrentDomain.BaseDirectory + ExcelPath, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
//停用警告訊息
myExcel.DisplayAlerts = false;
//讓Excel文件可見
myExcel.Visible = false;
//引用第一個活頁簿
myBook = myExcel.Workbooks[1];
//設定活頁簿焦點
myBook.Activate();
//引用第一個工作表
mySheet = (_Worksheet)myBook.Worksheets[1];
//設工作表焦點
mySheet.Activate();
//生产Html的Excel模板
object format = Microsoft.Office.Interop.Excel.XlFileFormat.xlHtml;
IEnumerator wsEnumerator =
myExcel.ActiveWorkbook.Worksheets.GetEnumerator();
int i = 1;
//
while (wsEnumerator.MoveNext())
{
Microsoft.Office.Interop.Excel.Worksheet wsCurrent =
(Microsoft.Office.Interop.Excel.Worksheet)wsEnumerator.Current;
String outputFile = AppDomain.CurrentDomain.BaseDirectory + OutPath + "." + i.ToString() + ".html";
wsCurrent.SaveAs(outputFile, format, Type.Missing, Type.Missing, Type.Missing,
Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
++i;
}
return(true);
}
catch (Exception me)
{
throw me;
}
finally
{
myBook.Close(false, Type.Missing, Type.Missing);
System.Runtime.InteropServices.Marshal.FinalReleaseComObject(myBook);
myExcel.Quit();
System.Runtime.InteropServices.Marshal.FinalReleaseComObject(myExcel);
GC.Collect();
GC.WaitForPendingFinalizers();
}
}
/// <summary>
/// 打印的过程
/// </summary>
/// <param name="ExcelPath">Excel文件的名称</param>
public void Print(string ExcelPath)
{
//引用Excel Application類別
_Application myExcel = null;
//引用活頁簿類別
_Workbook myBook = null;
//引用工作表類別
_Worksheet mySheet = null;
try
{
//開啟一個新的應用程式
myExcel = new Microsoft.Office.Interop.Excel.Application();
myExcel.Workbooks.Open(ExcelPath, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
//停用警告訊息
myExcel.DisplayAlerts = false;
//讓Excel文件可見
myExcel.Visible = false;
//引用第一個活頁簿
myBook = myExcel.Workbooks[1];
//設定活頁簿焦點
myBook.Activate();
//引用第一個工作表
mySheet = (_Worksheet)myBook.Worksheets[1];
//設工作表焦點
mySheet.Activate();
//在这个事件中Excel表的赋值方法。如下：
//mySheet.Cells[3, 1] = "sdfsdfs!";
if (_Print != null)
{
Microsoft.Office.Interop.Excel._Worksheet excel = (Microsoft.Office.Interop.Excel._Worksheet)_Print(mySheet);
//myBook.Save();
//直接走默认打印机.
myBook.PrintOutEx(Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
//關閉活頁簿
//myBook.Close(false, Type.Missing, Type.Missing);
//關閉Excel
//excel.Quit();
}
//MessageBox.Show("打印成功！");
}
catch (Exception ex)
{
throw ex;
}
finally
{
//釋放Excel資源
myBook.Close(false, Type.Missing, Type.Missing);
System.Runtime.InteropServices.Marshal.FinalReleaseComObject(myBook);
myExcel.Quit();
System.Runtime.InteropServices.Marshal.FinalReleaseComObject(myExcel);
GC.Collect();
GC.WaitForPendingFinalizers();
}
}
public void openExcel(string path)
{
myExcel = new Microsoft.Office.Interop.Excel.Application();
myExcel.Workbooks.Open(path);
myExcel.DisplayAlerts = false;
myExcel.Visible = false;
myBook = myExcel.Workbooks[1];
myBook.Activate();
mySheet = (_Worksheet)myBook.Worksheets[1];
mySheet.Activate();
}
/// <summary>
/// Activates a workbook via its name.
/// </summary>
/// <param name="name">The name of the workbook</param>
/// <returns>true if the workbook was found, false otherways</returns>
public bool activateWorkbook(string name)
{
foreach (_Workbook workbook in excel.Workbooks)
{
if (workbook.Name == name)
{
workbook.Activate();
this.workbook = workbook;
this.worksheet = (_Worksheet)workbook.ActiveSheet;
return(true);
}
}
return(false);
}
/// <summary>
/// Opens the original word file and initializes the Word.Document object.
/// The original file may be HTML, DOC, or RTF
/// </summary>
private void OpenDocument()
{
object missing = System.Reflection.Missing.Value;
object updateLinks = 0; // doesn't update any reference links
object readOnly = true;
object ignoreReadOnlyRecommended = true; // not display the read-only recommended message
//We could pass in a real password for these two fields, but currently
//there is no way to pass in a document specific password in System4
object password = missing;
object writeResPassword = password; //for write-reserved workbook
m_workbook = m_excelApp.Workbooks.Open(
m_originalFileName, updateLinks, readOnly, missing,
password, writeResPassword, ignoreReadOnlyRecommended,
missing, missing, missing, missing, missing, missing);
m_workbook.Activate();
m_workbook.WebOptions.Encoding = Office.MsoEncoding.msoEncodingUTF8;
}
public void Save(string ExcelPath)
{
//引用Excel Application類別
_Application myExcel = null;
//引用活頁簿類別
_Workbook myBook = null;
//引用工作表類別
_Worksheet mySheet = null;
try
{
//開啟一個新的應用程式
myExcel = new Microsoft.Office.Interop.Excel.Application();
myExcel.Workbooks.Open(ExcelPath, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
//停用警告訊息
myExcel.DisplayAlerts = false;
//讓Excel文件可見
myExcel.Visible = false;
//引用第一個活頁簿
myBook = myExcel.Workbooks[1];
//設定活頁簿焦點
myBook.Activate();
//引用第一個工作表
mySheet = (_Worksheet)myBook.Worksheets[1];
//設工作表焦點
mySheet.Activate();
//在这个事件中Excel表的赋值方法。如下：
//mySheet.Cells[3, 1] = "sdfsdfs!";
if (SaveEvent != null)
{
//Microsoft.Office.Interop.Excel._Worksheet excel = (Microsoft.Office.Interop.Excel._Worksheet)SaveEvent(mySheet);
List <List <string> > fuzhi = SaveEvent();
for (int i = 0; i < fuzhi.Count; i++)
{
for (int j = 0; j < fuzhi[i].Count; j++)
{
if (fuzhi[i][j] != null)
{
mySheet.Cells[i + 1, j + 1] = fuzhi[i][j];
}
}
}
//保存
myBook.Save();
//關閉活頁簿
//myBook.Close(false, Type.Missing, Type.Missing);
//關閉Excel
//excel.Quit();
}
//MessageBox.Show("打印成功！");
}
catch (Exception ex)
{
throw ex;
}
finally
{
//釋放Excel資源
myBook.Close(false, Type.Missing, Type.Missing);
System.Runtime.InteropServices.Marshal.FinalReleaseComObject(myBook);
myExcel.Quit();
System.Runtime.InteropServices.Marshal.FinalReleaseComObject(myExcel);
GC.Collect();
GC.WaitForPendingFinalizers();
}
}
private void button1_Click(object sender, EventArgs e)
{
if (myArray == null)
{
MessageBox.Show("请先读取数据");
return;
}
//開啟一個新的應用程式
myExcel = new Excel.Application();
//加入新的活頁簿
myExcel.Workbooks.Add(true);
//停用警告訊息
myExcel.DisplayAlerts = true;
//讓Excel文件可見
myExcel.Visible = true;
//引用第一個活頁簿
myBook = myExcel.Workbooks[1];
//設定活頁簿焦點
myBook.Activate();
//加入新的工作表在第1張工作表之後
myBook.Sheets.Add(Type.Missing, myBook.Worksheets[1], 1, Type.Missing);
//引用第一個工作表
mySheet = (Worksheet)myBook.Worksheets[1];
//命名工作表的名稱為 "Array"
mySheet.Name = "Array";
//設工作表焦點
mySheet.Activate();
int UpBound1 = myArray.GetUpperBound(0);//二維陣列數上限
int UpBound2 = myArray.GetUpperBound(1);//二維陣列數上限
//寫入報表名稱
myExcel.Cells[1, 4] = "全自动生成報表";
//設定範圍
myRange = (Range)mySheet.Range[mySheet.Cells[2, 1], mySheet.Cells[UpBound1 + 2, UpBound2 + 1]];
myRange.Select();
//用陣列一次寫入資料
myRange.Value2 = myArray;
//設定儲存路徑
string PathFile = Directory.GetCurrentDirectory() + @"\我的报表.xlsx";
//另存活頁簿
myBook.SaveAs(PathFile, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing
, XlSaveAsAccessMode.xlNoChange, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
//關閉活頁簿
//myBook.Close(false, Type.Missing, Type.Missing);
////關閉Excel
//myExcel.Quit();
////釋放Excel資源
//System.Runtime.InteropServices.Marshal.ReleaseComObject(myExcel);
myBook = null;
mySheet = null;
myRange = null;
myExcel = null;
GC.Collect();
}
//读取数据
private void btRead_Click(object sender, EventArgs e)
{
int MAXLINE = 5000;
int i = 0, j = 0, k = 0, m = 0;//m为总行数
int fileCount = lvFile.Items.Count;
string DataTag;
int eCount = 0;//有效工作簿数
int sCount = 0;//当前表中工作簿数
Point point;
Object missing = Type.Missing;
int iCount = lbContent.Items.Count;
//重点区域，范围型读取单元格区域
RangeSelector mainRange = new RangeSelector(tbMainRange.Text);
//预判断块读取还是固定位置读取，初始化总数组大小
if (mainRange.getWidth() > 0)
myArray = new String[MAXLINE, mainRange.getWidth() + iCount + 1];//最多千行
else
myArray = new String[MAXLINE, iCount + 1];//最多千行
//開啟一個新的應用程式
myExcel = new Excel.Application();
for (i = 0; i < fileCount; i++)
{
//停用警告訊息
myExcel.DisplayAlerts = false;
//讓Excel文件可見
myExcel.Visible = true;
//引用第一個活頁簿
myBook = myExcel.Workbooks.Open(lvFile.Items[i].SubItems[2].Text, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing);
//設定活頁簿焦點
myBook.Activate();
//判断所有工作簿
sCount = myBook.Worksheets.Count;
for (k = 1; k <= sCount; k++)
{
//大表判断条件
if (cbSheetSelect.Text != "全部" && Int16.Parse(cbSheetSelect.Text) != k) continue;
//选择当前表
mySheet = (Worksheet)myBook.Worksheets[k];
//設工作表焦點
mySheet.Activate();
//特征值判断
if (tbSheetPos.Text != "")
{
point = pointPos(tbSheetPos.Text);
if (mySheet.Cells[point.Y, point.X].Value != tbSheetCont.Text) continue;
}
eCount++;
//备注列判断
if (tbDataTag.Text == "") DataTag = lvFile.Items[i].SubItems[0].Text; //未设置备注默认使用文件名
else
{
Point tagpos = pointPos(tbDataTag.Text);
DataTag = Convert.ToString(mySheet.Cells[tagpos.Y, tagpos.X].Value);
}
string mainStart = tbMainStart.Text;
string mainEnd = tbMainEnd.Text;
//判断选择哪种模式
if (mainRange.Count() > 1)
{
mainRange = new RangeSelector(tbMainRange.Text);//重新恢复原区域值
//重点区域起始位置判断
Point nowPos = mainRange.getCurPos();
for (j = 0; j < mainRange.Count(); j++)
{
string myCell = Convert.ToString(mySheet.Cells[nowPos.Y, nowPos.X].Value);
if (mainStart == "") break;
if (myCell == mainStart) break;
mainRange.acc();
}
//mainRange.lineacc(); //移到关键字下一行
mainRange.SetStartVal(mainRange.getCurPos());
//读取内容
while (m < MAXLINE) //最大读取行数上限估计
{
nowPos = mainRange.getCurPos();
string lineFirstCell = Convert.ToString(mySheet.Cells[nowPos.Y, nowPos.X].Value);
if (lineFirstCell == null|| lineFirstCell=="") break; //首字为空
if (lineFirstCell == tbMainEnd.Text) break; //符合结束字符串
if (mainRange.pos > mainRange.Count()) break;//读取完了就退出
for (j = 0; j < mainRange.getWidth(); j++)//读取一行
{
point = mainRange.getCurPos();
myArray[m, j] = Convert.ToString(mySheet.Cells[point.Y, point.X].Value); //不管什么类型都转为字符串
mainRange.acc();
}
myArray[m, j] = DataTag;
m++;
}
}
else
{
//准备读取单元格相关信息，固定位置读取单元格
if (iCount >= 1)
{
List<Array> ListOfLine = new List<Array>(); //所有的读取行集合
String[] myLine = new String[iCount]; //单行对象
RangeSelector[] rsContentA = new RangeSelector[iCount];
for (j = 0; j < iCount; j++)
{
rsContentA[j] = new RangeSelector(lbContent.Items[j].ToString());
}
j = 0;
foreach (RangeSelector cont in rsContentA)
{
cont.acc();
point = cont.getCurPos();
myArray[m, j] = Convert.ToString(mySheet.Cells[point.Y, point.X].Value); //不管什么类型都转为字符串
j++;
if (j > iCount) break;//xxxxxxx
}
myArray[m, j - 1] = DataTag;
m++;
}
}
}
//关闭当前活页簿
myBook.Close();
System.Windows.Forms.Application.DoEvents();
}
myExcel.Quit();
}
private void button1_Click(object sender, EventArgs e)
{
//加入新的活頁簿 myExcel.Workbooks.Add(true);
//停用警告訊息myExcel.DisplayAlerts = false;
//讓Excel文件可見 myExcel.Visible = true;
//引用第一個活頁簿myBook = myExcel.Workbooks[1];
//設定活頁簿焦點myBook.Activate();
//引用第一個工作表mySheet = (_Worksheet)myBook.Worksheets[1];
//命名工作表的名稱為 "Array"mySheet.Name = "Cells";
//設工作表焦點mySheet.Activate();
//用offset寫入陣列資料myRange = mySheet.get_Range("A2", Type.Missing);myRange.get_Offset(i, j).Select();myRange.Value2 = "'" + myData[i, j];
//用Cells寫入陣列資料myRange.get_Range(myExcel.Cells[2 + i, 1 + j], myExcel.Cells[2 + i, 1 + j]).Select();myExcel.Cells[2 + i, 1 + j] = "'" + myData[i, j];
//加入新的工作表在第1張工作表之後myBook.Sheets.Add(Type.Missing, myBook.Worksheets[1], 1, Type.Missing);
//引用第2個工作表mySheet = (_Worksheet)myBook.Worksheets[2];
//命名工作表的名稱為 "Array"
if (myArray == null)
{
MessageBox.Show("请先读取数据");
return;
}
//開啟一個新的應用程式
myExcel = new Excel.Application();
//加入新的活頁簿
myExcel.Workbooks.Add(true);
//停用警告訊息
myExcel.DisplayAlerts = false;
//讓Excel文件可見
myExcel.Visible = true;
//引用第一個活頁簿
myBook = myExcel.Workbooks[1];
//設定活頁簿焦點
myBook.Activate();
//加入新的工作表在第1張工作表之後
myBook.Sheets.Add(Type.Missing, myBook.Worksheets[1], 1, Type.Missing);
//引用第一個工作表
mySheet = (Worksheet)myBook.Worksheets[1];
//命名工作表的名稱為 "Array"
mySheet.Name = "Array";
//設工作表焦點
mySheet.Activate();
int UpBound1 = myArray.GetUpperBound(0); //二維陣列數上限
int UpBound2 = myArray.GetUpperBound(1); //二維陣列數上限
//寫入報表名稱
myExcel.Cells[1, 4] = "全自动生成報表";
//設定範圍
myRange = (Range)mySheet.Range[mySheet.Cells[2, 1], mySheet.Cells[UpBound1 + 2, UpBound2 + 1]];
myRange.Select();
//用陣列一次寫入資料
myRange.Value2 = myArray;
//設定儲存路徑
string PathFile = Directory.GetCurrentDirectory() + @"\我的报表.xlsx";
//另存活頁簿
myBook.SaveAs(PathFile, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing
, XlSaveAsAccessMode.xlNoChange, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
//關閉活頁簿
myBook.Close(false, Type.Missing, Type.Missing);
//關閉Excel
myExcel.Quit();
//釋放Excel資源
System.Runtime.InteropServices.Marshal.ReleaseComObject(myExcel);
myBook = null;
mySheet = null;
myRange = null;
myExcel = null;
GC.Collect();
}
//读取数据
private void btRead_Click(object sender, EventArgs e)
{
int MAXLINE = 5000;
int i = 0, j = 0, k = 0, m = 0;//m为总行数
int fileCount = lvFile.Items.Count;
string filename;
int eCount = 0; //有效工作簿数
int sCount = 0; //当前表中工作簿数
Point point;
Object missing = Type.Missing;
int iCount = lbContent.Items.Count;
//重点区域，范围型读取单元格区域
RangeSelector mainRange = new RangeSelector(tbMainRange.Text);
//预判断块读取还是固定位置读取，初始化总数组大小
if (mainRange.getWidth() > 0)
{
myArray = new String[MAXLINE, mainRange.getWidth() + 1];//最多千行
}
else
{
myArray = new String[MAXLINE, iCount + 1];//最多千行
}
//開啟一個新的應用程式
myExcel = new Excel.Application();
for (i = 0; i < fileCount; i++)
{
//停用警告訊息
myExcel.DisplayAlerts = false;
//讓Excel文件可見
myExcel.Visible = true;
//引用第一個活頁簿
myBook = myExcel.Workbooks.Open(lvFile.Items[i].SubItems[2].Text, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing);
//設定活頁簿焦點
myBook.Activate();
//判断所有工作簿
sCount = myBook.Worksheets.Count;
for (k = 1; k <= sCount; k++)
{
//大表判断条件
if (cbSheetSelect.Text != "全部" && Int16.Parse(cbSheetSelect.Text) != k)
{
continue;
}
//选择当前表
mySheet = (Worksheet)myBook.Worksheets[k];
//設工作表焦點
mySheet.Activate();
//特征值判断
if (tbSheetPos.Text != "")
{
point = pointPos(tbSheetPos.Text);
if (mySheet.Cells[point.Y, point.X].Value != tbSheetCont.Text)
{
continue;
}
}
eCount++;
filename = lvFile.Items[i].SubItems[0].Text; //提取文件名
string mainStart = tbMainStart.Text;
string mainEnd = tbMainEnd.Text;
//判断选择哪种模式
if (mainRange.Count() > 1)
{
mainRange = new RangeSelector(tbMainRange.Text);//重新恢复原区域值
//重点区域起始位置判断
Point nowPos = mainRange.getCurPos();
for (j = 0; j < mainRange.Count(); j++)
{
string myCell = Convert.ToString(mySheet.Cells[nowPos.Y, nowPos.X].Value);
if (mainStart == "")
{
break;
}
if (myCell == mainStart)
{
break;
}
mainRange.acc();
}
//mainRange.lineacc(); //移到关键字下一行
mainRange.SetStartVal(mainRange.getCurPos());
//读取内容
while (m < MAXLINE) //最大读取行数上限估计
{
nowPos = mainRange.getCurPos();
string myCell = Convert.ToString(mySheet.Cells[nowPos.Y, nowPos.X].Value);
if (myCell == null)
{
break;
}
if (mainRange.pos > mainRange.Count())
{
break; //读取完了就退出
}
for (j = 0; j < mainRange.getWidth(); j++)
{
point = mainRange.getCurPos();
myArray[m, j] = Convert.ToString(mySheet.Cells[point.Y, point.X].Value); //不管什么类型都转为字符串
mainRange.acc();
}
myArray[m, j] = filename;
m++;
}
}
else
{
//准备读取单元格相关信息，固定位置读取单元格
if (iCount >= 1)
{
List <Array> ListOfLine = new List <Array>(); //所有的读取行集合
String[] myLine = new String[iCount]; //单行对象
RangeSelector[] rsContentA = new RangeSelector[iCount];
for (j = 0; j < iCount; j++)
{
rsContentA[j] = new RangeSelector(lbContent.Items[j].ToString());
}
j = 0;
foreach (RangeSelector cont in rsContentA)
{
cont.acc();
point = cont.getCurPos();
myArray[m, j] = Convert.ToString(mySheet.Cells[point.Y, point.X].Value); //不管什么类型都转为字符串
j++;
}
myArray[m, j] = filename;
m++;
}
}
}
//关闭当前活页簿
myBook.Close();
System.Windows.Forms.Application.DoEvents();
}
myExcel.Quit();
}