public void SortRanges(CellDescriptor cd1Top, CellDescriptor cd1Bottom, int SortColumnIdx1, bool sortAscending1, int SortColumnIdx2, bool sortAscending2)
{
try
{
_Worksheet ws = (_Worksheet)(this.worksheet);
ws.Activate();
Range sortRange1 = ws.get_Range(GetCellName(cd1Top), GetCellName(cd1Bottom));
sortRange1.Sort
(sortRange1.Columns[SortColumnIdx1, _MISSING_], ((sortAscending1) ? XlSortOrder.xlAscending : XlSortOrder.xlDescending),
sortRange1.Columns[SortColumnIdx2, _MISSING_], _MISSING_, ((sortAscending2) ? XlSortOrder.xlAscending : XlSortOrder.xlDescending),
_MISSING_, XlSortOrder.xlAscending,
XlYesNoGuess.xlNo, _MISSING_, _MISSING_,
XlSortOrientation.xlSortColumns,
XlSortMethod.xlPinYin,
XlSortDataOption.xlSortNormal,
XlSortDataOption.xlSortNormal,
XlSortDataOption.xlSortNormal);
/*
* SortRange(cd1Top, cd1Bottom, SortColumnIdx1, sortAscending1);
* SortRange(cd1Top, cd1Bottom, SortColumnIdx2, sortAscending2);*/
}
catch (Exception e)
{
}
}
protected override void PostProcessing(Application exApp, _Worksheet ws)
{
//Устанавливаем шрифт листа
ws.Rows.Font.Size = 10;
ws.Rows.Font.Name = "Calibri";
ws.Activate();
}
public void ExportToExcelWithColumn(System.Data.DataTable table)
{
try
{
ApplicationClass excel = new ApplicationClass();
excel.Application.Workbooks.Add(true);
int ColumnIndex = 0;
foreach (System.Data.DataColumn col in table.Columns)
{
ColumnIndex++;
excel.Cells[1, ColumnIndex] = col.ColumnName;
}
int rowIndex = 0;
foreach (DataRow row in table.Rows)
{
rowIndex++;
ColumnIndex = 0; foreach (DataColumn col in table.Columns)
{
ColumnIndex++;
excel.Cells[rowIndex + 1, ColumnIndex] = row[col.ColumnName];
}
}
excel.Visible = true;
_Worksheet worksheet = (_Worksheet)excel.ActiveSheet;
worksheet.Activate();
}
catch (Exception exml)
{
}
}
static public void ReadExcel(String filepath)
{
//打开已有excel文档，或者创建一个新的excel文档
Microsoft.Office.Interop.Excel.Application app = new Microsoft.Office.Interop.Excel.Application();
if (app == null)
{
MessageBox.Show("可能您的机子未安装excel，无法创建excel对象！", "系统提示 ", MessageBoxButtons.OK, MessageBoxIcon.Error);
return;
}
Workbooks wbks = app.Workbooks;
Workbook wbk = wbks.Add(filepath);
//注：若新建一个excel文档，“filepath”替换成true即可；不过这里新建的excel文档默认只有一个sheet。
//Workbook wbk = wbks.Open(filepath, missing, true, missing, missing, missing,
// missing, missing, missing, true, missing, missing, missing, missing, missing);
Sheets shs = wbk.Sheets;//定义一个新的工作表
//i是要取得的sheet的index
_Worksheet wsh = (_Worksheet)shs.get_Item(1);
wsh.Activate();//激活工作表
#region 取得总记录行数(包括标题列)
///UsedRange:返回一个Microsoft.Office.Interop.Excel.Range对象，该对象表示指定工作表上使用的范围。 只读。
///Cells:返回一个Range对象，它表示指定范围内的单元格。
///Rows返回表示指定范围内的行的Microsoft.Office.Interop.Excel.Range对象。
///Count:返回集合中的对象数量。
#endregion
int rowsint = wsh.UsedRange.Cells.Rows.Count; //得到非空行数
int columnsint = wsh.UsedRange.Cells.Columns.Count; //得到非空列数
//System.Diagnostics.Debug.Assert(false, "rowsint" + rowsint);
//System.Diagnostics.Debug.Assert(false, "columnsint" + columnsint);
Form1.PersonList = new List <List <String> >();
//Range range = (Range)wsh.Cells[2, 3];
//System.Diagnostics.Debug.Assert(false, range.Text.ToString());
for (int i = 1; i <= rowsint; i++)
{
List <String> array = new List <String>();
for (int j = 1; j <= columnsint; j++)
{
Range range = (Range)wsh.Cells[i, j];
array.Add(range.Text.ToString());
}
Form1.PersonList.Add(array);
}
app.Quit();
return;
}
/// <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>
/// Método privado que abre a planilha de dados do excel.
/// </summary>
/// <param name="ArqExcel">Caminho completo da planilha de dados do Excel.</param>
public void AbrirExcel()
{
if (mExcelApp == null)
{
throw new Exception("Não há instalações válidas do Microsoft Office Excel");
}
else if (!System.IO.File.Exists(mCaminhoArqExcel))
{
throw new System.IO.FileNotFoundException(String.Format("Não foi possível encontrar o arquivo '{0}'.", mCaminhoArqExcel));
}
else
{
mWorkBook = (Workbook)mExcelApp.Workbooks.Open(mCaminhoArqExcel, Type.Missing, (object)false, (object)Missing.Value, (object)Missing.Value, (object)Missing.Value, (object)Missing.Value, (object)Missing.Value, (object)Missing.Value, (object)Missing.Value, (object)Missing.Value, (object)Missing.Value, (object)Missing.Value, (object)Missing.Value, (object)Missing.Value);
//Abre a visualização da planilha designada (define a mesma como ativa).
mWorkSheet = (_Worksheet)mWorkBook.Worksheets.get_Item(mNumPagPlanilha);
mWorkSheet.Activate();
_WorkSheetAtiva = mWorkSheet.Application.ActiveSheet.Name;
for (int i = 1; i <= mWorkBook.Worksheets.Count; i++)
{
_Worksheet temp = (_Worksheet)mWorkBook.Worksheets.get_Item(i);
mLstWorkSheetsNames.Add(temp.Name);
}
}
}
private void fileOpen()
{
try
{
mApp = new Excel.Application();
mApp.Visible = true;
//myBook = mApp.Workbooks.Add(filepath);
myBook = mApp.Workbooks.Open(filepath, 0, false, 5, "", "", true, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "t", false, false, 0, true, Type.Missing, Type.Missing);
sheets = myBook.Sheets;
mySheet = sheets[1];
if (mySheet == null)
{
Console.WriteLine("没有工作簿");
return;
}
mySheet.Activate();
rowCount = mySheet.UsedRange.Rows.Count + 1;
Console.WriteLine("加载时行数：" + rowCount);
status_textBox.Text = "文件初始化成功";
insertTitle();
}catch (Exception ex)
{
status_textBox.Text = ex.Message;
mApp.Quit();
}
}
/// <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>
/// 生成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();
}
}
/// <summary>
/// handle tricare report sheet
/// </summary>
/// <param name="generateDate"></param>
public string handleBenchmarkReportSheet(DateTime generateDate)
{
string errorMsg = string.Empty;
DateTime endDay = generateDate.AddDays(-1);
DateTime startDay = generateDate.AddDays(-7);
DateTime priorEndDay = generateDate.AddDays(-8);
try
{
string endDateStr = endDay.ToString("yyyyMMdd");
string startDateStr = startDay.ToString("yyyyMMdd");
string priorEndDateStr = priorEndDay.ToString("yyyyMMdd");
string priorGenerateDateStr = startDateStr;
string priorReportPath = trackWeekly4Path + priorEndDateStr + @"\Tricare Benchmark Report -" + startDateStr + ".xls";
string ReportPath = trackWeekly4Path + endDateStr + @"\Tricare Benchmark Report -" + generateDate.ToString("yyyyMMdd") + ".xls";
string reportTempPath = trackWeekly4Path + endDateStr + @"\TRICATE Team Weekly-" + startDateStr + "-" + endDateStr + ".xls";
File.Copy(priorReportPath, ReportPath, true);
_ExcelApp = new Microsoft.Office.Interop.Excel.Application();
_ExcelWBook = (_Workbook)(_ExcelApp.Workbooks.Open(ReportPath, Missing.Value, Missing.Value
, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value,
Missing.Value, Missing.Value, Missing.Value));
_ExcelWBook.CheckCompatibility = false;//Add for Diable Compatibility for saving excel
_Worksheet worksheet = _ExcelWBook.Worksheets["Dashboard"];
DateTime originalDate = Convert.ToDateTime("2013-1-3");
int weekNum = (int)(generateDate - originalDate).Days / 7;
string weeklyMsg = "Week-" + weekNum.ToString() + ": " + startDay.ToString("MM/dd/yyyy") + "-" + endDay.ToString("MM/dd/yyyy");
worksheet.get_Range("D1", "D1").Value = weeklyMsg;
worksheet.get_Range("E3", "I58").Value = null;
worksheet.get_Range("D3", "D58").Copy(Type.Missing);
worksheet.get_Range("I3", "I58").PasteSpecial(Microsoft.Office.Interop.Excel.XlPasteType.xlPasteValues, Microsoft.Office.Interop.Excel.XlPasteSpecialOperation.xlPasteSpecialOperationNone,
Type.Missing, Type.Missing);
worksheet.get_Range("D3", "D58").Value = null;
_Workbook workbook2 = (_Workbook)(_ExcelApp.Workbooks.Open(reportTempPath, Missing.Value, Missing.Value
, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value,
Missing.Value, Missing.Value, Missing.Value));
_Worksheet worksheet2 = workbook2.Worksheets["Benchmark Result"];
worksheet2.get_Range("D3", "H58").Copy(Type.Missing);
worksheet.get_Range("D3", "H58").PasteSpecial(Microsoft.Office.Interop.Excel.XlPasteType.xlPasteValues, Microsoft.Office.Interop.Excel.XlPasteSpecialOperation.xlPasteSpecialOperationNone,
Type.Missing, Type.Missing);
worksheet.Activate();
worksheet.get_Range("A1", "A1").Select();
_ExcelWBook.Save();
_ExcelWBook.Close();
workbook2.Close();
_ExcelApp.Quit();
}
catch (Exception ex)
{
errorMsg = ex.Message;
}
return(errorMsg);
}
public void ActivateSheet(string name)
{
sheet = GetSheetByName(name);
if (sheet == null && !string.IsNullOrEmpty(name))
{
throw new ArgumentException($"Could not find sheet name with specified name '{name}'.");
}
sheet = sheet ?? workbook.ActiveSheet;
sheet.Activate();
}
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 worksheet on the current active workbook via its name.
/// </summary>
/// <param name="name">The name of the workbook</param>
/// <returns>true if the worksheet was found, false otherways</returns>
public bool activateWorksheet(string name)
{
foreach (_Worksheet worksheet in excel.ActiveWorkbook.Sheets)
{
if (worksheet.Name == name)
{
worksheet.Activate();
this.worksheet = worksheet;
return(true);
}
}
return(false);
}
/// <summary>
/// Active une feuille selon son numéro
/// </summary>
public bool ChangerDeFeuilleActive(int NumeroFeuille)
{
try
{
_MaFeuille = (_Worksheet)_MonClasseur.Sheets[NumeroFeuille];
_MaFeuille.Activate();
return(true);
}
catch (Exception e)
{
MessageBox.Show(e.Message);
return(false);
}
}
public void SortRange(CellDescriptor cd1, CellDescriptor cd2, int SortColumnIdx, bool sortAscending = true)
{
_Worksheet ws = (_Worksheet)(this.worksheet);
ws.Activate();
Range sortRange = ws.get_Range(GetCellName(cd1), GetCellName(cd2));
sortRange.Sort
(sortRange.Columns[SortColumnIdx, _MISSING_], XlSortOrder.xlDescending,
_MISSING_, _MISSING_, ((sortAscending)?XlSortOrder.xlAscending:XlSortOrder.xlDescending),
_MISSING_, XlSortOrder.xlAscending,
XlYesNoGuess.xlNo, _MISSING_, _MISSING_,
XlSortOrientation.xlSortColumns,
XlSortMethod.xlPinYin,
XlSortDataOption.xlSortNormal,
XlSortDataOption.xlSortNormal,
XlSortDataOption.xlSortNormal);
}
/// <summary>
/// NomFichier : chemin complet.
/// </summary>
public bool OuvrirFichierExistant(string NomFichier)
{
try
{
_LesWorkBooks = _ApplicationXL.Workbooks;
//ouvrir le fichier Excel désiré
_MonClasseur = _LesWorkBooks.Open(NomFichier, _M, _M, _M, _M, _M, _M, _M, _M, _M, _M, _M, _M, _M, _M);
//Active la feuille 1
_MaFeuille = (_Worksheet)_MonClasseur.Sheets[1];
_MaFeuille.Activate();
//on initialise un Range
_MonRange = _MaFeuille.get_Range("A1", "A1");
return(true);
}
catch (Exception e)
{
MessageBox.Show(e.Message);
return(false);
}
}
/// <summary>
/// 指定されたウィンドウおよびシートをデフォルト設定に設定しなおします。
/// </summary>
/// <param name="window">アクティブなウィンドウ</param>
/// <param name="sheet">アクティブなシート</param>
/// <param name="config">設定</param>
private void SetDefault(Window window, _Worksheet sheet, A1Config config)
{
sheet.Activate();
// "A1"セルをフォーカス
sheet.Range["A1"].Activate();
// スクロールを左上へ設定
window.ScrollRow = 1;
window.ScrollColumn = 1;
// 表示倍率を設定
if (config.ZoomEnabled)
{
window.Zoom = config.ZoomRatio;
}
// グループ化を設定
if (config.GroupEnabled)
{
sheet.Outline.ShowLevels(RowLevels: config.RowLevels, ColumnLevels: config.ColumnLevels);
}
}
/// <summary>
/// NomFichier : chemin complet.
/// NomFeuille : Active la feuille dans le classeur
/// </summary>
public bool OuvrirFichierExistant(string NomFichier, string NomFeuille)
{
try
{
if (!OuvrirFichierExistant(NomFichier))
{
return(false);
}
//Active la page choisie
_MaFeuille = (_Worksheet)_MonClasseur.Sheets[NomFeuille];
_MaFeuille.Activate();
//on initialise un Range
_MonRange = _MaFeuille.get_Range("A1", "A1");
return(true);
}
catch (Exception e)
{
MessageBox.Show(e.Message);
return(false);
}
}
///////////////////////////////////////////////////////////////////////////
//
// METHODS
//
///////////////////////////////////////////////////////////////////////////
#region METHODS
/// <summary>
/// 현재 Sheet를 Wrokbook의 활성 Worksheet로 설정합니다.
/// </summary>
public void Activate()
{
_xlSheet.Activate();
}
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();
}
}
/// <summary>
/// 效率慢
/// </summary>
/// <returns></returns>
public static bool FillForms()
{
try
{
string destinationFile = System.IO.Directory.GetCurrentDirectory() + "\\Project\\" + projname + "\\Forms\\Form.xlsx";
if (!File.Exists(destinationFile))
{
string sourceFile = System.IO.Directory.GetCurrentDirectory() + "\\Form.xlsx"; // 此处是默认的表格模板 //string = @"D:\\ProjectFormTemplet\\testCopy2.xlsx";
bool isrewrite = true; // true=覆盖已存在的同名文件,false则反之
System.IO.File.Copy(sourceFile, destinationFile, isrewrite);
}
else
{
}
xls = new Microsoft.Office.Interop.Excel.Application();
book = xls.Workbooks.Open(destinationFile, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value);
string Data = System.IO.File.ReadAllText(System.IO.Directory.GetCurrentDirectory() + "\\Project\\" + projname + "\\Forms\\all.txt", Encoding.Default);
List <Forms> _projectData = JsonConvert.DeserializeObject <List <Forms> >(Data);
for (int formNum = 1; formNum < 3; formNum++)
{
sheet = (_Worksheet)book.Worksheets.get_Item(formNum);
sheet.Activate();
if (formNum == 1)
{
sheet.Cells[2, 9] = _projectData[0].F1.TableID;
sheet.Cells[26, 6] = _projectData[0].F1.ParcelCode;
sheet.Cells[31, 6] = _projectData[0].F1.InvestigateOrganization;
sheet.Cells[43, 6] = _projectData[0].F1.InvestigateDate; //特殊 日期起止
sheet.ExportAsFixedFormat(XlFixedFormatType.xlTypePDF, System.IO.Directory.GetCurrentDirectory() + "\\Project\\" + projname + "\\Forms\\Cover.pdf");
}
else if (formNum == 2)
{
sheet.Cells[2, 3] = _projectData[0].F1.OwnPowerSide;// C2=2,3 D5=5,4 先列后行
sheet.Cells[3, 3] = _projectData[0].F1.UsePowerSide;
sheet.Cells[16, 9] = _projectData[0].F1.ParcelCode;
sheet.Cells[3, 9] = _projectData[0].F1.PowerSideType;
sheet.Cells[4, 9] = _projectData[0].F1.PowerSideCertificateType;
sheet.Cells[5, 9] = _projectData[0].F1.PowerSideCertificateCode;
sheet.Cells[6, 9] = _projectData[0].F1.PowerSideAddress;
sheet.Cells[7, 3] = _projectData[0].F1.PowerType;
sheet.Cells[7, 8] = _projectData[0].F1.PowerCharacter;
sheet.Cells[7, 10] = _projectData[0].F1.LandPowerCertificatePaper;
sheet.Cells[8, 3] = _projectData[0].F1.Location;
sheet.Cells[9, 3] = _projectData[0].F1.PrincipalCertificateCode;
sheet.Cells[9, 6] = _projectData[0].F1.PrincipalCertificateType;
sheet.Cells[10, 6] = _projectData[0].F1.ProcuratorCertificateCode;
sheet.Cells[9, 10] = _projectData[0].F1.PrincipalCertificateTelephone;
sheet.Cells[11, 3] = _projectData[0].F1.ProcuratorName;
sheet.Cells[11, 6] = _projectData[0].F1.ProcuratorCertificateType;
sheet.Cells[12, 6] = _projectData[0].F1.ProcuratorCertificateCode;
sheet.Cells[11, 10] = _projectData[0].F1.ProcuratorCertificateTelephone;
sheet.Cells[13, 3] = _projectData[0].F1.PowerSetPattern;
sheet.Cells[14, 3] = _projectData[0].F1.NationalEconomyIndustryClassificationCode;
sheet.Cells[16, 3] = _projectData[0].F1.PreParcelCode;
sheet.Cells[16, 9] = _projectData[0].F1.ParcelCode;
sheet.Cells[17, 3] = _projectData[0].F1.UnitNumber;
sheet.Cells[18, 5] = _projectData[0].F1.MapScale;
sheet.Cells[19, 5] = _projectData[0].F1.MapCode;
sheet.Cells[20, 3] = _projectData[0].F1.ParcelRangeNorth;
sheet.Cells[21, 3] = _projectData[0].F1.ParcelRangeEast;
sheet.Cells[22, 3] = _projectData[0].F1.ParcelRangeSouth;
sheet.Cells[23, 3] = _projectData[0].F1.ParcelRangeWest;
sheet.Cells[24, 3] = _projectData[0].F1.Rank;
sheet.Cells[24, 9] = _projectData[0].F1.Price;
sheet.Cells[25, 3] = _projectData[0].F1.PermittedUsefor;
sheet.Cells[26, 5] = _projectData[0].F1.PermittedTypeCode;
sheet.Cells[25, 8] = _projectData[0].F1.PracticalUsefor;
sheet.Cells[26, 10] = _projectData[0].F1.PracticalTypeCode;
sheet.Cells[27, 3] = _projectData[0].F1.PermittedArea;
sheet.Cells[27, 6] = _projectData[0].F1.ParcelArea;
sheet.Cells[27, 10] = _projectData[0].F1.BuildLandArea;
sheet.Cells[29, 10] = _projectData[0].F1.BuildTotalArea;
string _landUseTime = _projectData[0].F1.LandUseStartTime + "--" + _projectData[0].F1.LandUseEndTime;
sheet.Cells[30, 3] = _landUseTime; //特殊 日期起止
sheet.Cells[31, 3] = _projectData[0].F1.CommonUse;
//sheet.Cells[33, 3] = _projectData[0].F1.Explain; 说明不填
sheet.ExportAsFixedFormat(XlFixedFormatType.xlTypePDF, System.IO.Directory.GetCurrentDirectory() + "\\Project\\" + projname + "\\Forms\\F1.pdf");
}
}
book.Save();
book.Close(false, Missing.Value, Missing.Value); //关闭打开的表
xls.Quit(); //Excel
sheet = null;
book = null;
xls = null;
GC.Collect();
return(true);
}
catch (Exception) { return(false); }
}
public static bool receivePrintForm(int formNum)
{
try
{
string destinationFile = System.IO.Directory.GetCurrentDirectory() + "\\Project\\" + projname + "\\Forms\\Form.xlsx";;
if (!File.Exists(destinationFile))
{
string sourceFile = System.IO.Directory.GetCurrentDirectory() + "\\Form.xlsx"; // 此处是默认的表格模板 //string = @"D:\\ProjectFormTemplet\\testCopy2.xlsx";
bool isrewrite = true; // true=覆盖已存在的同名文件,false则反之
System.IO.File.Copy(sourceFile, destinationFile, isrewrite);
}
else
{
}
xls = new Microsoft.Office.Interop.Excel.Application();
book = xls.Workbooks.Open(destinationFile, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value);
string Data = System.IO.File.ReadAllText(System.IO.Directory.GetCurrentDirectory() + "\\Project\\" + projname + "\\Forms\\all.txt", Encoding.Default);
List <Forms> _projectData = JsonConvert.DeserializeObject <List <Forms> >(Data);
sheet = (_Worksheet)book.Worksheets.get_Item(formNum);
sheet.Activate();
if (formNum == 1)
{
sheet.Cells[2, 3] = _projectData[0].F1.OwnPowerSide;// C2=2,3 D5=5,4 先列后行
sheet.Cells[3, 3] = _projectData[0].F1.UsePowerSide;
sheet.Cells[16, 9] = _projectData[0].F1.ParcelCode;
sheet.Cells[3, 9] = _projectData[0].F1.PowerSideType;
sheet.Cells[4, 9] = _projectData[0].F1.PowerSideCertificateType;
sheet.Cells[5, 9] = _projectData[0].F1.PowerSideCertificateCode;
sheet.Cells[6, 9] = _projectData[0].F1.PowerSideAddress;
sheet.Cells[7, 3] = _projectData[0].F1.PowerType;
sheet.Cells[7, 8] = _projectData[0].F1.PowerCharacter;
sheet.Cells[7, 10] = _projectData[0].F1.LandPowerCertificatePaper;
sheet.Cells[8, 3] = _projectData[0].F1.Location;
sheet.Cells[9, 3] = _projectData[0].F1.PrincipalCertificateCode;
sheet.Cells[9, 6] = _projectData[0].F1.PrincipalCertificateType;
sheet.Cells[10, 6] = _projectData[0].F1.ProcuratorCertificateCode;
sheet.Cells[9, 10] = _projectData[0].F1.PrincipalCertificateTelephone;
sheet.Cells[11, 3] = _projectData[0].F1.ProcuratorName;
sheet.Cells[11, 6] = _projectData[0].F1.ProcuratorCertificateType;
sheet.Cells[12, 6] = _projectData[0].F1.ProcuratorCertificateCode;
sheet.Cells[11, 10] = _projectData[0].F1.ProcuratorCertificateTelephone;
sheet.Cells[13, 3] = _projectData[0].F1.PowerSetPattern;
sheet.Cells[14, 3] = _projectData[0].F1.NationalEconomyIndustryClassificationCode;
sheet.Cells[16, 3] = _projectData[0].F1.PreParcelCode;
sheet.Cells[16, 9] = _projectData[0].F1.ParcelCode;
sheet.Cells[17, 3] = _projectData[0].F1.UnitNumber;
sheet.Cells[18, 5] = _projectData[0].F1.MapScale;
sheet.Cells[19, 5] = _projectData[0].F1.MapCode;
sheet.Cells[20, 3] = _projectData[0].F1.ParcelRangeNorth;
sheet.Cells[21, 3] = _projectData[0].F1.ParcelRangeEast;
sheet.Cells[22, 3] = _projectData[0].F1.ParcelRangeSouth;
sheet.Cells[23, 3] = _projectData[0].F1.ParcelRangeWest;
sheet.Cells[24, 3] = _projectData[0].F1.Rank;
sheet.Cells[24, 9] = _projectData[0].F1.Price;
sheet.Cells[25, 3] = _projectData[0].F1.PermittedUsefor;
sheet.Cells[26, 5] = _projectData[0].F1.PermittedTypeCode;
sheet.Cells[25, 8] = _projectData[0].F1.PracticalUsefor;
sheet.Cells[26, 10] = _projectData[0].F1.PracticalTypeCode;
sheet.Cells[27, 3] = _projectData[0].F1.PermittedArea;
sheet.Cells[27, 6] = _projectData[0].F1.ParcelArea;
sheet.Cells[27, 10] = _projectData[0].F1.BuildLandArea;
sheet.Cells[29, 10] = _projectData[0].F1.BuildTotalArea;
string _landUseTime = _projectData[0].F1.LandUseStartTime + "--" + _projectData[0].F1.LandUseEndTime;
sheet.Cells[30, 3] = _landUseTime; //特殊 日期起止
sheet.Cells[31, 3] = _projectData[0].F1.CommonUse;
sheet.Cells[33, 3] = _projectData[0].F1.Explain;
//排版
AutoRange(33, 3, 1);
}
else if (formNum == 2)// 表2
{
int l1, l2, l3, l4, l5, l6;
l1 = _projectData[0].F2.LandPointCodeList.Length;
l2 = _projectData[0].F2.LandPointTypeList.Length;
l3 = _projectData[0].F2.LandBoundaryExplain.Length;
l4 = _projectData[0].F2.LandBoundaryLocation.Length;
l5 = _projectData[0].F2.LandBoundaryType.Length;
l6 = _projectData[0].F2.LandPointDistance.Length;
if (l1 == l2 && l1 == l3 + 1 && l1 == l4 + 1 && l1 == l5 + 1 && l1 == l6 + 1)
{
sheet.Cells[4, 1] = _projectData[0].F2.LandPointCodeList[0];
sheet.Cells[4, (_projectData[0].F2.LandPointTypeList[0] + 2)] = "√";
for (int n = 0; n < l3; n++)
{
sheet.Cells[(2 * n + 5), 1] = _projectData[0].F2.LandPointCodeList[n + 1];
sheet.Cells[(2 * n + 5), (_projectData[0].F2.LandPointTypeList[n + 1] + 2)] = "√";
sheet.Cells[(2 * n + 4), 7] = _projectData[0].F2.LandPointDistance[n];
sheet.Cells[(2 * n + 4), (_projectData[0].F2.LandBoundaryType[n] + 8)] = "√";
sheet.Cells[(2 * n + 4), (_projectData[0].F2.LandBoundaryLocation[n] + 16)] = "√";
sheet.Cells[(2 * n + 4), 19] = _projectData[0].F2.LandBoundaryExplain[n];
AutoRange((2 * n + 4), 19, 2); // 效率有待提高！！！ 10.10
}
}
else
{
return(false);
}
}
else if (formNum == 3)
{
int l1, l2, l3;
l1 = _projectData[0].F3.StartPointCodeList.Length;
l2 = _projectData[0].F3.InnerPointCodeList.Length;
l3 = _projectData[0].F3.EndPointCodeList.Length;
if (l1 == l2 && l2 == l3)
{
for (int n = 0; n < l1; n++)
{
sheet.Cells[n + 5, 1] = _projectData[0].F3.StartPointCodeList[n];
sheet.Cells[n + 5, 2] = _projectData[0].F3.InnerPointCodeList[n];
sheet.Cells[n + 5, 3] = _projectData[0].F3.EndPointCodeList[n];
}
}
else
{
return(false);
}
}
else if (formNum == 4)
{
return(false);
} // 图暂时没有
else if (formNum == 5)
{
sheet.Cells[2, 2] = _projectData[0].F5.BoundaryPointExplain;
sheet.Cells[29, 2] = _projectData[0].F5.MainBoundaryDirectionExplain;
AutoRange(29, 2, 5);
AutoRange(2, 2, 5);
}
else if (formNum == 6)
{
sheet.Cells[2, 2] = _projectData[0].F6.PowerInvestigateRecord;
sheet.Cells[19, 5] = _projectData[0].F6.PowerInvestigator;
sheet.Cells[19, 8] = _projectData[0].F6.PowerInvestigateDate;
sheet.Cells[20, 2] = _projectData[0].F6.SurveyRecord;
sheet.Cells[38, 5] = _projectData[0].F6.SurveyRecorder;
sheet.Cells[38, 8] = _projectData[0].F6.SurveyRecordDate;
sheet.Cells[39, 2] = _projectData[0].F6.AuditOpinion;
sheet.Cells[56, 5] = _projectData[0].F6.Auditor;
sheet.Cells[56, 8] = _projectData[0].F6.AuditOpinionDate;
AutoRange(2, 2, 6);
AutoRange(20, 2, 6);
AutoRange(39, 2, 6);
}
else if (formNum == 7)
{
sheet.Cells[2, 2] = _projectData[0].F1.Location;
sheet.Cells[3, 2] = _projectData[0].F1.ParcelCode;
sheet.Cells[4, 2] = _projectData[0].F1.ParcelArea;
sheet.Cells[4, 4] = _projectData[0].F7.FixedCount;
int intFixedCount;
int.TryParse(_projectData[0].F7.FixedCount, out intFixedCount);
if (intFixedCount > 11)
{
for (int n = 0; n < intFixedCount - 11; n++)
{
Range insertRow = (Range)sheet.Rows[8, Missing.Value];
insertRow.Insert(Missing.Value, Missing.Value);
}
}
for (int n = 0; n < _projectData[0].F7.FixedCode.Length; n++)
{
sheet.Cells[n + 6, 1] = _projectData[0].F7.FixedCode[n];
sheet.Cells[n + 6, 2] = _projectData[0].F7.LandOwnUseArea[n];
sheet.Cells[n + 6, 3] = _projectData[0].F7.LandUniqueArea[n];
sheet.Cells[n + 6, 4] = _projectData[0].F7.CommonArea[n];
}
// F7 三个合计和备注没有字段
}
return(true);
}
catch (Exception)
{ //var s = e.ToString();
return(false);
}
}
public static void AutoRange(int inputRow, int inputCol, int sheetNumber)
{
try
{
///此处的item是要选择的
sheet = (_Worksheet)book.Worksheets.get_Item(sheetNumber);
sheet.Activate();
///开始调整间距
int firstRowNum = ((Range)sheet.Cells[inputRow, inputCol]).MergeArea.Row;
int firstColNum = ((Range)sheet.Cells[inputRow, inputCol]).MergeArea.Column;
int tempRowCount = ((Range)sheet.Cells[inputRow, inputCol]).MergeArea.Rows.Count;
int tempColCount = ((Range)sheet.Cells[inputRow, inputCol]).MergeArea.Columns.Count;
double beforeWidth = 0.00;
for (int n = 1; n <= tempColCount; n++)
{
beforeWidth = beforeWidth + ((Range)sheet.Columns[firstColNum + n - 1]).ColumnWidth;
}
for (int n = 1; n <= tempRowCount; n++)
{
((Range)sheet.Rows[firstRowNum + n - 1, Missing.Value]).Rows.AutoFit();
}
Worksheet temp = book.Sheets.Add();
temp.Cells[1, 1] = ((Range)sheet.Cells[inputRow, inputCol]).Value2;
temp.Cells.WrapText = true;
((Range)temp.Cells[1, 1]).ColumnWidth = beforeWidth;
((Range)temp.Cells[1, 1]).Font.Name = ((Range)sheet.Cells[inputRow, inputCol]).Font.Name;
((Range)temp.Cells[1, 1]).Font.Size = ((Range)sheet.Cells[inputRow, inputCol]).Font.Size;
((Range)temp.Cells[1, 1]).RowHeight = 0;
((Range)temp.Cells[1, 1]).Rows.AutoFit();
double beforeOneRowHeight = ((Range)sheet.Cells[inputRow, inputCol]).RowHeight;// 一个单元格的高度
double beforeHeight = beforeOneRowHeight * tempRowCount;
double laterHeight = ((Range)temp.Cells[1, 1]).RowHeight;
if (beforeHeight < laterHeight)
{
// double tempHeight = ((Range)temp.Cells[1, 1]).RowHeight;
for (int n = 1; n <= tempRowCount; n++)
{
((Range)sheet.Cells[firstRowNum + n - 1, inputCol]).EntireRow.RowHeight = laterHeight / tempRowCount + 2;
}
}
else
{
}
xls.DisplayAlerts = false;
temp.Delete();
}
catch (Exception e)
{
//var s = e;
//Console.WriteLine(s.ToString());
//Console.ReadKey();
}
}
public static bool FillForms3()
{
try
{
//暂时支持40条界址线 11.14
string destinationFile = System.IO.Directory.GetCurrentDirectory() + "\\Project\\" + projname + "\\Forms\\F3.xlsx";
if (!File.Exists(destinationFile))
{
string sourceFile = System.IO.Directory.GetCurrentDirectory() + "\\F3.xlsx"; // 此处是默认的表格模板 //string = @"D:\\ProjectFormTemplet\\testCopy2.xlsx";
bool isrewrite = true; // true=覆盖已存在的同名文件,false则反之
System.IO.File.Copy(sourceFile, destinationFile, isrewrite);
}
else
{
}
xls = new Microsoft.Office.Interop.Excel.Application();
book = xls.Workbooks.Open(destinationFile, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value);
string Data = System.IO.File.ReadAllText(System.IO.Directory.GetCurrentDirectory() + "\\Project\\" + projname + "\\Forms\\all.txt", Encoding.Default);
List <Forms> _projectData = JsonConvert.DeserializeObject <List <Forms> >(Data);
sheet = (_Worksheet)book.Worksheets.get_Item(1);
sheet.Activate();
int l1, l2, l3;
l1 = _projectData[0].F3.StartPointCodeList.Length;
l2 = _projectData[0].F3.InnerPointCodeList.Length;
l3 = _projectData[0].F3.EndPointCodeList.Length;
if (l1 == l2 && l2 == l3)
{
if (l1 - 21 <= 0)
{
for (int n = 0; n < l1; n++)
{
sheet.Cells[n + 5, 1] = _projectData[0].F3.StartPointCodeList[n];
sheet.Cells[n + 5, 2] = _projectData[0].F3.InnerPointCodeList[n];
sheet.Cells[n + 5, 3] = _projectData[0].F3.EndPointCodeList[n];
}
sheet.ExportAsFixedFormat(XlFixedFormatType.xlTypePDF, System.IO.Directory.GetCurrentDirectory() + "\\Project\\" + projname + "\\Forms\\F3-1.pdf");
}
else if (l1 - 21 <= 21)
{
for (int n = 0; n < 21; n++)
{
sheet.Cells[n + 5, 1] = _projectData[0].F3.StartPointCodeList[n];
sheet.Cells[n + 5, 2] = _projectData[0].F3.InnerPointCodeList[n];
sheet.Cells[n + 5, 3] = _projectData[0].F3.EndPointCodeList[n];
}
sheet.ExportAsFixedFormat(XlFixedFormatType.xlTypePDF, System.IO.Directory.GetCurrentDirectory() + "\\Project\\" + projname + "\\Forms\\F3-1.pdf");
sheet = (_Worksheet)book.Worksheets.get_Item(2);
sheet.Activate();
for (int n = 0; n < l1 - 21; n++)
{
sheet.Cells[n + 5, 1] = _projectData[0].F3.StartPointCodeList[21 + n];
sheet.Cells[n + 5, 2] = _projectData[0].F3.InnerPointCodeList[21 + n];
sheet.Cells[n + 5, 3] = _projectData[0].F3.EndPointCodeList[21 + n];
}
sheet.ExportAsFixedFormat(XlFixedFormatType.xlTypePDF, System.IO.Directory.GetCurrentDirectory() + "\\Project\\" + projname + "\\Forms\\F3-2.pdf");
}
else
{
return(false);
} // 超过42条线暂时不支持 11.14
}
else
{
return(false);
}
}
catch (Exception) { return(false); }
book.Save();
book.Close(false, Missing.Value, Missing.Value); //关闭打开的表
xls.Quit(); //Excel
sheet = null;
book = null;
xls = null;
GC.Collect();
return(true);
}
public void FormatExcelFile(_Worksheet _ws, DataTable _result, string _caption, int CountDownRows)
{
var oldCI = Thread.CurrentThread.CurrentCulture;
Thread.CurrentThread.CurrentCulture = new CultureInfo("en-US");
_ws.Name = _caption.Substring(0, (_caption.Length < MaxListName) ? _caption.Length : MaxListName);
if (CountDownRows > 0)
{
for (int j = 1; j < HeaderCollumnCount; j++)
{
for (int i = 0; i < CountDownRows - 3; i++)
{
_ws.Cells[1 + i, j] = _ws.Cells[2 + i, j];
}
_ws.Cells[CountDownRows - 2, j] = "";
GetRangeForMerge(_ws, j).Merge();
}
}
if (CountDownRows == 0)
{
CountDownRows = 2;
}
for (int i = 4; i < 20; i++)
{
_ws.Cells[1, i] = "";
}
for (int i = 0; i < _result.Columns.Count; i++)
{
_ws.Cells[CountDownRows - 1, i + 1] = "";
_ws.Cells[CountDownRows - 1, i + 1] = _result.Columns[i].Caption;
if (CountDownRows != 2)
{
_ws.Cells[1, 4] = "";
}
if (_result.Columns[i].ExtendedProperties.ContainsKey("Width"))
{
((Range)_ws.Columns[i + 1, Type.Missing]).ColumnWidth = ((int?)_result.Columns[i].ExtendedProperties["Width"]).Value;
}
else
{
((Range)_ws.Columns[i + 1, Type.Missing]).AutoFit();
}
if (_result.Columns[i].ExtendedProperties.ContainsKey("Color"))
{
_ws.get_Range(_ws.Cells[CountDownRows, i + 1], _ws.Cells[_result.Rows.Count + 1, i + 1]).Interior.Color = ColorTranslator.ToOle((Color)_result.Columns[i].ExtendedProperties["Color"]);
}
}
//рисуем границы на всю таблицу
_ws.get_Range(_ws.Cells[CountDownRows - 1, 1], _ws.Cells[_result.Rows.Count + 1, _result.Columns.Count]).Borders.Weight = XlBorderWeight.xlThin;
//Устанавливаем шрифт листа
_ws.Rows.Font.Size = 8;
_ws.Rows.Font.Name = "Arial Narrow";
_ws.Activate();
//Устанавливаем АвтоФильтр на все колонки
_ws.Range[_ws.Cells[CountDownRows - 1, 1], _ws.Cells[_result.Rows.Count + 1, _result.Columns.Count]].Select();
((Range)_ws.Application.Selection).AutoFilter(1, Missing.Value, XlAutoFilterOperator.xlAnd, Missing.Value, true);
Thread.CurrentThread.CurrentCulture = oldCI;
}
public static bool FillForms2()
{
try
{
//暂时支持40个点 11.14
string destinationFile = System.IO.Directory.GetCurrentDirectory() + "\\Project\\" + projname + "\\Forms\\F2.xlsx";
if (!File.Exists(destinationFile))
{
string sourceFile = System.IO.Directory.GetCurrentDirectory() + "\\F2.xlsx"; // 此处是默认的表格模板 //string = @"D:\\ProjectFormTemplet\\testCopy2.xlsx";
bool isrewrite = true; // true=覆盖已存在的同名文件,false则反之
System.IO.File.Copy(sourceFile, destinationFile, isrewrite);
}
else
{
}
xls = new Microsoft.Office.Interop.Excel.Application();
book = xls.Workbooks.Open(destinationFile, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value);
string Data = System.IO.File.ReadAllText(System.IO.Directory.GetCurrentDirectory() + "\\Project\\" + projname + "\\Forms\\all.txt", Encoding.Default);
List <Forms> _projectData = JsonConvert.DeserializeObject <List <Forms> >(Data);
sheet = (_Worksheet)book.Worksheets.get_Item(1);
sheet.Activate();
int l1, l2, l3, l4, l5, l6;
l1 = _projectData[0].F2.LandPointCodeList.Length;
l2 = _projectData[0].F2.LandPointTypeList.Length;
//l3 = _projectData[0].F2.LandBoundaryExplain.Length;
l4 = _projectData[0].F2.LandBoundaryLocation.Length;
l5 = _projectData[0].F2.LandBoundaryType.Length;
l6 = _projectData[0].F2.LandPointDistance.Length;
if (l1 - 19 <= 0)
{
if (l1 == l2 && l1 == l4 + 1 && l1 == l5 + 1 && l1 == l6 + 1)
{
sheet.Cells[4, 1] = _projectData[0].F2.LandPointCodeList[0];
sheet.Cells[4, (_projectData[0].F2.LandPointTypeList[0] + 2)] = "√";
for (int n = 0; n < l4; n++)
{
sheet.Cells[(2 * n + 5), 1] = _projectData[0].F2.LandPointCodeList[n + 1];
sheet.Cells[(2 * n + 5), (_projectData[0].F2.LandPointTypeList[n + 1] + 2)] = "√";
sheet.Cells[(2 * n + 4), 7] = _projectData[0].F2.LandPointDistance[n];
sheet.Cells[(2 * n + 4), (_projectData[0].F2.LandBoundaryType[n] + 8)] = "√";
sheet.Cells[(2 * n + 4), (_projectData[0].F2.LandBoundaryLocation[n] + 16)] = "√";
//sheet.Cells[(2 * n + 4), 19] = _projectData[0].F2.LandBoundaryExplain[n]; 说明不填写
}
sheet.ExportAsFixedFormat(XlFixedFormatType.xlTypePDF, System.IO.Directory.GetCurrentDirectory() + "\\Project\\" + projname + "\\Forms\\F2-1.pdf");
}
else
{
return(false);
}
}
else if (l1 - 19 <= 19)
{
if (l1 == l2 && l1 == l4 + 1 && l1 == l5 + 1 && l1 == l6 + 1)
{
sheet.Cells[4, 1] = _projectData[0].F2.LandPointCodeList[0];
sheet.Cells[4, (_projectData[0].F2.LandPointTypeList[0] + 2)] = "√";
for (int n = 0; n < 19; n++)
{
sheet.Cells[(2 * n + 5), 1] = _projectData[0].F2.LandPointCodeList[n + 1];
sheet.Cells[(2 * n + 5), (_projectData[0].F2.LandPointTypeList[n + 1] + 2)] = "√";
sheet.Cells[(2 * n + 4), 7] = _projectData[0].F2.LandPointDistance[n];
sheet.Cells[(2 * n + 4), (_projectData[0].F2.LandBoundaryType[n] + 8)] = "√";
sheet.Cells[(2 * n + 4), (_projectData[0].F2.LandBoundaryLocation[n] + 16)] = "√";
//sheet.Cells[(2 * n + 4), 19] = _projectData[0].F2.LandBoundaryExplain[n]; 说明不填写
}
sheet.ExportAsFixedFormat(XlFixedFormatType.xlTypePDF, System.IO.Directory.GetCurrentDirectory() + "\\Project\\" + projname + "\\Forms\\F2-1.pdf");
sheet = (_Worksheet)book.Worksheets.get_Item(2);
sheet.Activate();
sheet.Cells[4, 1] = _projectData[0].F2.LandPointCodeList[19];
sheet.Cells[4, (_projectData[0].F2.LandPointTypeList[19] + 2)] = "√";
for (int n = 0; n < l1 - 19; n++)
{
sheet.Cells[(2 * n + 5), 1] = _projectData[0].F2.LandPointCodeList[19 + n + 1];
sheet.Cells[(2 * n + 5), (_projectData[0].F2.LandPointTypeList[19 + n + 1] + 2)] = "√";
sheet.Cells[(2 * n + 4), 7] = _projectData[0].F2.LandPointDistance[19 + n];
sheet.Cells[(2 * n + 4), (_projectData[0].F2.LandBoundaryType[19 + n] + 8)] = "√";
sheet.Cells[(2 * n + 4), (_projectData[0].F2.LandBoundaryLocation[19 + n] + 16)] = "√";
//sheet.Cells[(2 * n + 4), 19] = _projectData[0].F2.LandBoundaryExplain[n]; 说明不填写
}
sheet.ExportAsFixedFormat(XlFixedFormatType.xlTypePDF, System.IO.Directory.GetCurrentDirectory() + "\\Project\\" + projname + "\\Forms\\F2-2.pdf");
}
else
{
return(false);
}
}
else
{
return(false);
} // 超过38个点暂时不支持 11.14
book.Save();
book.Close(false, Missing.Value, Missing.Value); //关闭打开的表
xls.Quit(); //Excel
sheet = null;
book = null;
xls = null;
GC.Collect();
return(true);
}
catch (Exception) { return(false); }
}
public static bool FillForm()
{
try
{
projname = _importProjectName;
string destinationFile = System.IO.Directory.GetCurrentDirectory() + "\\Project\\" + projname + "\\Forms\\Forms.xlsx";
if (!File.Exists(destinationFile))
{
string sourceFile = System.IO.Directory.GetCurrentDirectory() + "\\Forms.xlsx"; // 此处是默认的表格模板 //string = @"D:\\ProjectFormTemplet\\testCopy2.xlsx";
bool isrewrite = true; // true=覆盖已存在的同名文件,false则反之
System.IO.File.Copy(sourceFile, destinationFile, isrewrite);
}
else
{
}
xls = new Microsoft.Office.Interop.Excel.Application();
book = xls.Workbooks.Open(destinationFile, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value);
string Data = System.IO.File.ReadAllText(System.IO.Directory.GetCurrentDirectory() + "\\Project\\" + projname + "\\Forms\\all.txt", Encoding.Default);
List <Forms> _projectData = JsonConvert.DeserializeObject <List <Forms> >(Data);
//填写完Form1
for (int formNum = 1; formNum < 3; formNum++)
{
sheet = (_Worksheet)book.Worksheets.get_Item(formNum);
sheet.Activate();
if (formNum == 1)
{
sheet.Cells[2, 9] = _projectData[0].F1.TableID;
sheet.Cells[26, 6] = _projectData[0].F1.ParcelCode;
sheet.Cells[31, 6] = _projectData[0].F1.InvestigateOrganization;
sheet.Cells[43, 6] = _projectData[0].F1.InvestigateDate; //特殊 日期起止
sheet.ExportAsFixedFormat(XlFixedFormatType.xlTypePDF, System.IO.Directory.GetCurrentDirectory() + "\\Project\\" + projname + "\\Forms\\Cover.pdf");
}
else if (formNum == 2)
{
sheet.Cells[2, 3] = _projectData[0].F1.OwnPowerSide;// C2=2,3 D5=5,4 先列后行
sheet.Cells[3, 3] = _projectData[0].F1.UsePowerSide;
sheet.Cells[16, 9] = _projectData[0].F1.ParcelCode;
sheet.Cells[3, 9] = _projectData[0].F1.PowerSideType;
sheet.Cells[9, 3] = _projectData[0].F1.PrincipalName;
sheet.Cells[4, 9] = _projectData[0].F1.PowerSideCertificateType;
sheet.Cells[5, 9] = _projectData[0].F1.PowerSideCertificateCode;
sheet.Cells[6, 9] = _projectData[0].F1.PowerSideAddress;
sheet.Cells[7, 3] = _projectData[0].F1.PowerType;
sheet.Cells[7, 8] = _projectData[0].F1.PowerCharacter;
sheet.Cells[7, 10] = _projectData[0].F1.LandPowerCertificatePaper;
sheet.Cells[8, 3] = _projectData[0].F1.Location;
sheet.Cells[10, 6] = _projectData[0].F1.PrincipalCertificateCode;
sheet.Cells[9, 6] = _projectData[0].F1.PrincipalCertificateType;
sheet.Cells[9, 10] = _projectData[0].F1.PrincipalCertificateTelephone;
sheet.Cells[11, 3] = _projectData[0].F1.ProcuratorName;
sheet.Cells[11, 6] = _projectData[0].F1.ProcuratorCertificateType;
sheet.Cells[12, 6] = _projectData[0].F1.ProcuratorCertificateCode;
sheet.Cells[11, 10] = _projectData[0].F1.ProcuratorCertificateTelephone;
sheet.Cells[13, 3] = _projectData[0].F1.PowerSetPattern;
sheet.Cells[14, 3] = _projectData[0].F1.NationalEconomyIndustryClassificationCode;
sheet.Cells[16, 3] = _projectData[0].F1.PreParcelCode;
sheet.Cells[16, 9] = _projectData[0].F1.ParcelCode;
sheet.Cells[17, 3] = _projectData[0].F1.UnitNumber;
sheet.Cells[18, 5] = _projectData[0].F1.MapScale;
sheet.Cells[19, 5] = _projectData[0].F1.MapCode;
sheet.Cells[20, 3] = _projectData[0].F1.ParcelRangeNorth;
sheet.Cells[21, 3] = _projectData[0].F1.ParcelRangeEast;
sheet.Cells[22, 3] = _projectData[0].F1.ParcelRangeSouth;
sheet.Cells[23, 3] = _projectData[0].F1.ParcelRangeWest;
sheet.Cells[24, 3] = _projectData[0].F1.Rank;
sheet.Cells[24, 9] = _projectData[0].F1.Price;
sheet.Cells[25, 3] = _projectData[0].F1.PermittedUsefor;
sheet.Cells[26, 5] = _projectData[0].F1.PermittedTypeCode;
sheet.Cells[25, 8] = _projectData[0].F1.PracticalUsefor;
sheet.Cells[26, 10] = _projectData[0].F1.PracticalTypeCode;
sheet.Cells[27, 3] = _projectData[0].F1.PermittedArea;
sheet.Cells[27, 6] = _projectData[0].F1.ParcelArea;
sheet.Cells[27, 10] = _projectData[0].F1.BuildLandArea;
sheet.Cells[29, 10] = _projectData[0].F1.BuildTotalArea;
string _landUseTime = _projectData[0].F1.LandUseStartTime + "--" + _projectData[0].F1.LandUseEndTime;
sheet.Cells[30, 3] = _landUseTime; //特殊 日期起止
sheet.Cells[31, 3] = _projectData[0].F1.CommonUse;
//sheet.Cells[33, 3] = _projectData[0].F1.Explain; 说明不填
sheet.ExportAsFixedFormat(XlFixedFormatType.xlTypePDF, System.IO.Directory.GetCurrentDirectory() + "\\Project\\" + projname + "\\Forms\\F1.pdf");
}
}
//填写Form2
sheet = (_Worksheet)book.Worksheets.get_Item(3);
sheet.Activate();
int l1, l2, l3, l4, l5, l6;
l1 = _projectData[0].F2.LandPointCodeList.Length;
l2 = _projectData[0].F2.LandPointTypeList.Length;
//l3 = _projectData[0].F2.LandBoundaryExplain.Length;
l4 = _projectData[0].F2.LandBoundaryLocation.Length;
l5 = _projectData[0].F2.LandBoundaryType.Length;
l6 = _projectData[0].F2.LandPointDistance.Length;
_Form2Number = l1 / 19;
if (_Form2Number == 0 || l1 == 19)
{
_Form2Number = 1;
if (l1 == l2 && l1 == l4 + 1 && l1 == l5 + 1 && l1 == l6 + 1)
{
sheet.Cells[4, 1] = _projectData[0].F2.LandPointCodeList[0];
if (_projectData[0].F2.LandPointTypeList[0] == -1)
{
}
else
{
sheet.Cells[4, (_projectData[0].F2.LandPointTypeList[0] + 2)] = "√";
}
for (int n = 0; n < l4; n++)
{
sheet.Cells[(2 * n + 5), 1] = _projectData[0].F2.LandPointCodeList[n + 1];
if (_projectData[0].F2.LandPointTypeList[n + 1] == -1)
{
}
else
{
sheet.Cells[(2 * n + 5), (_projectData[0].F2.LandPointTypeList[n + 1] + 2)] = "√";
}
sheet.Cells[(2 * n + 4), 7] = _projectData[0].F2.LandPointDistance[n];
if (_projectData[0].F2.LandBoundaryType[n] == -1)
{
}
else
{
sheet.Cells[(2 * n + 4), (_projectData[0].F2.LandBoundaryType[n] + 8)] = "√";
}
if (_projectData[0].F2.LandBoundaryLocation[n] == -1)
{
}
else
{
sheet.Cells[(2 * n + 4), (_projectData[0].F2.LandBoundaryLocation[n] + 16)] = "√";
}
//sheet.Cells[(2 * n + 4), 19] = _projectData[0].F2.LandBoundaryExplain[n]; 说明不填写
}
sheet.ExportAsFixedFormat(XlFixedFormatType.xlTypePDF, System.IO.Directory.GetCurrentDirectory() + "\\Project\\" + projname + "\\Forms\\F2-1.pdf");
}
else
{
return(false);
}
}
else if (_Form2Number == 1 && l1 > 19)
{
_Form2Number = 2;
if (l1 == l2 && l1 == l4 + 1 && l1 == l5 + 1 && l1 == l6 + 1)
{
sheet.Cells[4, 1] = _projectData[0].F2.LandPointCodeList[0];
sheet.Cells[4, (_projectData[0].F2.LandPointTypeList[0] + 2)] = "√";
for (int n = 0; n < 19; n++)
{
sheet.Cells[(2 * n + 5), 1] = _projectData[0].F2.LandPointCodeList[n + 1];
sheet.Cells[(2 * n + 5), (_projectData[0].F2.LandPointTypeList[n + 1] + 2)] = "√";
sheet.Cells[(2 * n + 4), 7] = _projectData[0].F2.LandPointDistance[n];
sheet.Cells[(2 * n + 4), (_projectData[0].F2.LandBoundaryType[n] + 8)] = "√";
sheet.Cells[(2 * n + 4), (_projectData[0].F2.LandBoundaryLocation[n] + 16)] = "√";
//sheet.Cells[(2 * n + 4), 19] = _projectData[0].F2.LandBoundaryExplain[n]; 说明不填写
}
sheet.ExportAsFixedFormat(XlFixedFormatType.xlTypePDF, System.IO.Directory.GetCurrentDirectory() + "\\Project\\" + projname + "\\Forms\\F2-1.pdf");
sheet = (_Worksheet)book.Worksheets.get_Item(4);
sheet.Activate();
sheet.Cells[4, 1] = _projectData[0].F2.LandPointCodeList[19];
sheet.Cells[4, (_projectData[0].F2.LandPointTypeList[19] + 2)] = "√";
for (int n = 0; n < l1 - 19; n++)
{
sheet.Cells[(2 * n + 5), 1] = _projectData[0].F2.LandPointCodeList[19 + n + 1];
sheet.Cells[(2 * n + 5), (_projectData[0].F2.LandPointTypeList[19 + n + 1] + 2)] = "√";
sheet.Cells[(2 * n + 4), 7] = _projectData[0].F2.LandPointDistance[19 + n];
sheet.Cells[(2 * n + 4), (_projectData[0].F2.LandBoundaryType[19 + n] + 8)] = "√";
sheet.Cells[(2 * n + 4), (_projectData[0].F2.LandBoundaryLocation[19 + n] + 16)] = "√";
//sheet.Cells[(2 * n + 4), 19] = _projectData[0].F2.LandBoundaryExplain[n]; 说明不填写
}
sheet.ExportAsFixedFormat(XlFixedFormatType.xlTypePDF, System.IO.Directory.GetCurrentDirectory() + "\\Project\\" + projname + "\\Forms\\F2-2.pdf");
}
else
{
return(false);
}
}
else
{
return(false);
} // 超过38个点暂时不支持 11.14
//填写Form3
sheet = (_Worksheet)book.Worksheets.get_Item(5);
sheet.Activate();
l1 = _projectData[0].F3.StartPointCodeList.Length;
l2 = _projectData[0].F3.InnerPointCodeList.Length;
l3 = _projectData[0].F3.EndPointCodeList.Length;
_Form3Number = l1 / 21;
if (_Form3Number == 0 || l1 == 21)
{
_Form3Number = 1;
for (int n = 0; n < l1; n++)
{
sheet.Cells[n + 5, 1] = _projectData[0].F3.StartPointCodeList[n];
sheet.Cells[n + 5, 2] = _projectData[0].F3.InnerPointCodeList[n];
sheet.Cells[n + 5, 3] = _projectData[0].F3.EndPointCodeList[n];
}
sheet.ExportAsFixedFormat(XlFixedFormatType.xlTypePDF, System.IO.Directory.GetCurrentDirectory() + "\\Project\\" + projname + "\\Forms\\F3-1.pdf");
}
else if (_Form3Number == 1 && l1 > 21)
{
_Form3Number = 2;
for (int n = 0; n < 21; n++)
{
sheet.Cells[n + 5, 1] = _projectData[0].F3.StartPointCodeList[n];
sheet.Cells[n + 5, 2] = _projectData[0].F3.InnerPointCodeList[n];
sheet.Cells[n + 5, 3] = _projectData[0].F3.EndPointCodeList[n];
}
sheet.ExportAsFixedFormat(XlFixedFormatType.xlTypePDF, System.IO.Directory.GetCurrentDirectory() + "\\Project\\" + projname + "\\Forms\\F3-1.pdf");
sheet = (_Worksheet)book.Worksheets.get_Item(6);
sheet.Activate();
for (int n = 0; n < l1 - 21; n++)
{
sheet.Cells[n + 5, 1] = _projectData[0].F3.StartPointCodeList[21 + n];
sheet.Cells[n + 5, 2] = _projectData[0].F3.InnerPointCodeList[21 + n];
sheet.Cells[n + 5, 3] = _projectData[0].F3.EndPointCodeList[21 + n];
}
sheet.ExportAsFixedFormat(XlFixedFormatType.xlTypePDF, System.IO.Directory.GetCurrentDirectory() + "\\Project\\" + projname + "\\Forms\\F3-2.pdf");
}
else
{
return(false);
} // 超过42条线暂时不支持 11.14
book.Save();
book.Close(false, Missing.Value, Missing.Value); //关闭打开的表
xls.Quit(); //Excel
sheet = null;
book = null;
xls = null;
GC.Collect();
return(true);
}
catch (Exception e)
{
var s = e.ToString();
return(false);
}
}
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 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 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 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();
}
