/// <summary>
/// Creates and externaly referencable worksheet
/// </summary>
/// <param name="wsName">The name of the worksheet</param>
/// <returns>BaseExcelInteropWorksheet for further manipulations</returns>
public BaseExcelInteropWorksheet CreateWorksheet(string wsName, bool setActive = true)
{
Worksheet ws = null;
if (null == subs)
{
subs = new List <Worksheet>();
if (null == workbook)
{
workbook = app.Workbooks.Add(XlWBATemplate.xlWBATWorksheet);
}
ws = (XL.Worksheet)(workbook.Worksheets[workbook.Worksheets.Count]);
ws.Activate();
ws.Name = ((string.IsNullOrEmpty(wsName)) ? "ws" + workbook.Worksheets.Count.ToString() : wsName);
subs.Add(ws);
return(new BaseExcelInteropWorksheet(ws));
}
else
{
ws = addWorksheet(setActive);
if (null != ws)
{
ws.Activate();
ws.Name = ((string.IsNullOrEmpty(wsName)) ? "ws" + workbook.Worksheets.Count.ToString() : wsName);
subs.Add(ws);
return(new BaseExcelInteropWorksheet(ws));
}
}
return(null);
}
// Método de exportar arquivo excel usando a dll Microsoft.Office.Interop.Excel
public static void ExportToExcelUsingMicrosoft <T>(T item)
{
// Representa o programa excel instalado no PC do usuário
Application app = new Application();
// Representa um arquivo excel
Workbook excelFile = app.Workbooks.Add();
// Representa as abas dos excel
Sheets sheets = excelFile.Worksheets;
// Cria uma aba em específico
Worksheet usuarioSheet = sheets.Add() as Worksheet;
usuarioSheet.Name = "Usuários";
PopulateSheet(usuarioSheet, item);
// Se houver mais de uma aba, a de usuários terá o foco.
usuarioSheet.Activate();
usuarioSheet.Columns.AutoFit();
app.Visible = true;
SaveExcelFile(excelFile, "");
}
/// <summary> 以隐藏的方式在后台开启一个新的Excel程序，并在其中打开指定的Excel工作簿 </summary>
/// <param name="workbookPath"> 要打开的工作簿的绝对路径 </param>
/// <param name="sheetName"> 指定要打开的工作表的名称 </param>
/// <param name="readOnly"> 是否要以只读的方式打开工作簿 </param>
/// <param name="visible"> 新创建的Excel程序是否要可见 </param>
public ExcelApplication(string workbookPath, string sheetName, bool readOnly = true, bool visible = false)
{
// Application
var app = new Application {
Visible = visible
};
Application = app;
// ActiveWorkbook
_activeWorkbook = app.Workbooks.Open(workbookPath, ReadOnly: readOnly);
_activeWorkbook.Activate();
// ActiveWorksheet
_activeWorksheet = null;
foreach (Worksheet sht in ActiveWorkbook.Worksheets)
{
if (string.Compare(sht.Name, sheetName, StringComparison.OrdinalIgnoreCase) == 0)
{
_activeWorksheet = sht;
break;
}
}
if (_activeWorksheet == null)
{
throw new NullReferenceException("未找到指定名称的工作表");
}
else
{
_activeWorksheet.Activate();
}
}
private void Openexcel_Click_1(object sender, EventArgs e)
{
List <int> processesbeforegen = getRunningProcesses();
if (excelac.ShowDialog() == DialogResult.OK)
{
textBox3.Text = excelac.FileName;
Excel.Application exc = new Excel.Application();
{
exc.Visible = false;
}
exc.Workbooks.Open(textBox3.Text);
Worksheet sheet = exc.Worksheets[1];
sheet.Activate();
foreach (Worksheet ws in exc.Worksheets)
{
ChartObjects chartobjects = ws.ChartObjects();
foreach (ChartObject co in chartobjects)
{
co.Select();
Excel.Chart chart = co.Chart;
chart.Export(exportpath.SelectedPath + @"\" + chart.Name + ".png", "PNG", false);
}
}
progressBar1.Value = 40;
MessageBox.Show("The graphs are imported successfully.");
List <int> processesaftergen = getRunningProcesses();
killProcesses(processesbeforegen, processesaftergen);
}
}
public static void SaveExcelFile(string fileName, string tempFileName,string[,] contents, int startColumn, int startRow)
{
try
{
excelApp = new ApplicationClass();
Workbooks myWorkBooks = excelApp.Workbooks;
myWorkBooks.Open(tempFileName, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing);
Sheets sheets = excelApp.Sheets;
mySheet1 = (Worksheet)sheets[1];
mySheet1.Activate();
//写入测试信息
Range range1 = mySheet1.get_Range(mySheet1.Cells[startRow, startColumn], mySheet1.Cells[contents.GetLength(0)+startRow-1,contents.GetLength(1)+startColumn-1]);
range1.Value2 = contents;
mySheet1.SaveAs(fileName, missing, missing, missing, missing, missing, missing, missing, missing, missing);
myWorkBooks.Close();
excelApp.Quit();
excelApp = null;
}
catch (Exception ee)
{
throw ee;
}
}
public void ExportToExcel(System.Data.DataTable dataTable, Application excel, Workbook workBook)
{
FetchFiles ff = new FetchFiles();
Worksheet workSheet = workBook.ActiveSheet;
int Column = workSheet.UsedRange.Columns.Count - 1;
int Column1 = Column;
int Column2 = Column;
foreach (DataColumn dc in dataTable.Columns)
{
Column++;
excel.Cells[1, Column] = dc.ColumnName;
}
foreach (DataRow datarow in dataTable.Rows)
{
int mainRow = 0;
mainRow++;
foreach (DataColumn dataColumn in dataTable.Columns)
{
Column1++;
excel.Cells[mainRow + 1, Column1] = datarow[dataColumn.ColumnName];
}
Column1 = Column2;
}
workSheet.Activate();
System.Runtime.InteropServices.Marshal.ReleaseComObject(workSheet);
}
public Excel.Worksheet GetGraphsAndTablesWorksheet()
{
Excel.Worksheet newWorksheet = null;
Worksheet activeWorksheet = (Worksheet)Globals.Factory.GetVstoObject(this.Application.ActiveWorkbook.ActiveSheet);
foreach (Excel.Worksheet worksheet in Application.Worksheets)
{
if (worksheet.Name.Equals("Wykresy i Tabele"))
{
newWorksheet = worksheet;
}
}
if (newWorksheet == null)
{
newWorksheet = (Excel.Worksheet)Application.Worksheets.Add();
newWorksheet.Move(After: Application.Worksheets.Item[Application.Worksheets.Count]);
newWorksheet.Name = "Wykresy i Tabele";
activeWorksheet.Activate();
}
return(newWorksheet);
}
public void SelectFirstSheet()
{
Worksheet firstSheet = excel.Sheets[1];
firstSheet.Activate();
NAR(firstSheet);
}
/// <summary>
/// Gets worksheet by the NAME
/// </summary>
/// <param name="wsNumber"></param>
/// <returns></returns>
public BaseExcelInteropWorksheet getWorksheet(string theWorksheetName)
{
Worksheet foundWS = null;
try
{
for (int wsIndex = 1; wsIndex <= workbook.Worksheets.Count; wsIndex++)
{
worksheet = (XL.Worksheet)(workbook.Worksheets[wsIndex]);
if (worksheet.Name.Equals(theWorksheetName, StringComparison.InvariantCultureIgnoreCase))
{
foundWS = worksheet;
break;
}
}
}
catch (Exception e)
{
Console.Write("Error:" + e.Message);
return(null);
}
finally
{
}
if (null == foundWS)
{
foundWS = (Worksheet)(this.workbook.Worksheets.Add());
}
foundWS.Activate();
return(new BaseExcelInteropWorksheet(worksheet));
}
protected override void Start()
{
int type = GetType();
GetHolidays();
if (type == 1)
{
if (File.Exists(configObj.ResultFilePath))
{
File.Delete(configObj.ResultFilePath);
}
workbook = InitializeExcel(configObj.ResultFilePath);
workbook.Worksheets.Add(Type.Missing, workbook.Worksheets[1]);
DownloadFirstFile();
FillTemplate();
FillTemplate2();
ReadExcel();
FillExcel();
}
else
{
workbook = InitializeExcel(configObj.ResultFilePath);
DownloadSecondFile();
ReadExcel2();
FillExcel2();
}
Worksheet worksheet = workbook.Worksheets[1] as Worksheet;
worksheet.Activate();
workbook.SaveAs(workbook.FullName);
AddResult("Result file", workbook.FullName, "file");
workbook.Close();
app.Dispose();
}
public void exportaraexcel(DataGridView tabla)
{
Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.Application();
excel.Application.Workbooks.Add(true);
int ColumnIndex = 0;
foreach (DataGridViewColumn col in tabla.Columns)
{
ColumnIndex++;
excel.Cells[1, ColumnIndex] = col.Name;
}
int rowIndex = 0;
foreach (DataGridViewRow row in tabla.Rows)
{
rowIndex++;
ColumnIndex = 0;
foreach (DataGridViewColumn col in tabla.Columns)
{
ColumnIndex++;
excel.Cells[rowIndex + 1, ColumnIndex] = row.Cells[col.Name].Value;
}
}
excel.Visible = true;
Worksheet worksheet = (Worksheet)excel.ActiveSheet;
worksheet.Activate();
MessageBox.Show("Exportación Exitosa", "San Agustin", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
static void Main(string[] args)
{
Application MyExcel = new ApplicationClass();
Workbook MyWorkbook = MyExcel.Workbooks.Add(Missing.Value);
MyWorkbook.Windows.get_Item(1).Caption = "Using Delegates";
Worksheet MyWorksheet1 = (Worksheet)MyWorkbook.Worksheets.get_Item(1);
Worksheet MyWorksheet2 = (Worksheet)MyWorkbook.Worksheets.get_Item(2);
Worksheet MyWorksheet3 = (Worksheet)MyWorkbook.Worksheets.get_Item(3);
MyWorksheet1.Activate();
//Add Event Handler for the BeforeClose Event
Event_BeforeBookClose = new AppEvents_WorkbookBeforeCloseEventHandler(BeforeBookClose);
MyExcel.WorkbookBeforeClose += Event_BeforeBookClose;
//Add Event Handler for the change Event
Event_ChangeEvent = new DocEvents_ChangeEventHandler(CellChange);
MyWorksheet1.Change += Event_ChangeEvent;
MyWorksheet2.Change += Event_ChangeEvent;
MyWorksheet3.Change += Event_ChangeEvent;
MyExcel.Visible = true;
MyExcel.UserControl = true;
}
public TimeSheet Read(Worksheet sheet)
{
sheet.Activate();
// 名前
var userName = sheet.Rows[nameY].Cells[nameX].GetString();
// 所属
var company = sheet.Rows[companyY].Cells[companyX].GetString();
// 件名
var assignName = sheet.Rows[reasonY].Cells[reasonX].GetString();
// 見積もり番号
var orderNo = sheet.Rows[quoteNoY].Cells[quoteNoX].GetString();
var worker = new Worker
{
Name = userName,
AssignName = assignName,
Company = company,
OrderID = orderNo,
};
var records = GetRecords(sheet).ToArray();
var assigns = GetAssigns(sheet).ToArray();
return(new TimeSheet
{
Worker = worker,
WorkRecords = records,
WorkAssigns = assigns,
});
}
//-----------------------------METODOS DE ARCHIVOS -----------------------------------------
public void exportaraexcel(DataGridView tabla)
{
Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.Application();
excel.Application.Workbooks.Add(true);
int ColumnIndex = 0;
foreach (DataGridViewColumn col in tabla.Columns)
{
ColumnIndex++;
excel.Cells[1, ColumnIndex] = col.Name;
}
int rowIndex = 0;
foreach (DataGridViewRow row in tabla.Rows)
{
rowIndex++;
ColumnIndex = 0;
foreach (DataGridViewColumn col in tabla.Columns)
{
ColumnIndex++;
excel.Cells[rowIndex + 1, ColumnIndex] = row.Cells[col.Name].Value;
}
}
excel.Visible = true;
Worksheet worksheet = (Worksheet)excel.ActiveSheet;
worksheet.Activate();
}
public static string UpdateReportExcel(string cardname)
{
var path = Environment.GetEnvironmentVariable("USERPROFILE") + "\\Downloads\\" + cardname + ".xlsm";
Application xlsmApp = new Application();
Workbook xlsm = xlsmApp.Workbooks.Open(path, ReadOnly: false);
Application xlApp = new Application();
Workbook xlWorkBook = xlsm;
Worksheet xlWorkSheet = (Worksheet)xlWorkBook.Worksheets.get_Item(2);
xlWorkSheet.Activate();
xlApp.DefaultSheetDirection = (int)Constants.xlRTL;
xlWorkSheet.Cells[14, 4] = 9;
path = Environment.GetEnvironmentVariable("USERPROFILE") + "\\Downloads\\" + cardname + "update.xlsm";
xlWorkBook.SaveAs(path);
xlWorkBook.Close();
xlApp.Quit();
return(path);
}
/// <summary>
/// Excel 儲存資料使用的函數
/// </summary>
/// <param name="strPath"> 儲存檔案路徑 </param>
/// <param name="Data"> 儲存資料 </param>
/// <param name="poRow"> 啟始列(row)的位置 </param>
/// <param name="poCol"> 啟始欄(column)的位置 </param>
/// <param name="excelApp"> 目標儲存的excel app 物件 </param>
/// <param name="sheet">目標儲存的excel sheet 物件 </param>
/// <param name="book">目標儲存的excel book 物件 </param>
private void SaveFunction(string strPath, string[,] Data, int poRow, int poCol, Excel.Application excelApp, Worksheet sheet, Workbook book)
{
//// All into sheet
int endRow = Data.GetLength(0) + poRow - 1;
int endCol = Data.GetLength(1) + poCol - 1;
string StartPoString = GetPo(poCol);
string EngPoString = GetPo(endCol);
sheet.Activate();
Range range = sheet.get_Range(StartPoString + poRow.ToString(),
EngPoString + endRow.ToString());
range.Value2 = Data;
range.Value2 = range.Value2;
book.SaveAs(strPath, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Excel.XlSaveAsAccessMode.xlNoChange, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
sheet.SaveAs(strPath);
book.Close();
excelApp.Quit();
System.Runtime.InteropServices.Marshal.ReleaseComObject(excelApp);
System.Runtime.InteropServices.Marshal.ReleaseComObject(book);
System.Runtime.InteropServices.Marshal.ReleaseComObject(sheet);
GC.Collect();
}
public void exportaraexcelsiigo(DataGridView tabla)
{
int filastotales = tabla.Rows.Count;
excel.Application.Workbooks.Add(true);
int IndiceColumna = 0;
int IndeceFila = 0;
foreach (DataGridViewRow row in tabla.Rows) // Filas
{
IndeceFila++;
IndiceColumna = 0;
foreach (DataGridViewColumn col in tabla.Columns)
{
IndiceColumna++;
excel.Cells[IndeceFila, IndiceColumna] = row.Cells[col.Name].Value;
}
//progress.Value = (IndeceFila * 100) / filastotales;
}
excel.Visible = true;
Worksheet worksheet = (Worksheet)excel.ActiveSheet;
worksheet.Activate();
MessageBox.Show("Exportación Exitosa", "Exportación", MessageBoxButtons.OK, MessageBoxIcon.Information);
//progress.Value = 0;
}
public void exportaraexcelTimer(DataGridView tabla, ProgressBar progress)
{
int filasTotales = tabla.Rows.Count;
Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.Application();
excel.Application.Workbooks.Add(true);
int ColumnIndex = 0;
foreach (DataGridViewColumn col in tabla.Columns)
{
ColumnIndex++;
excel.Cells[1, ColumnIndex] = col.Name;
}
int rowIndex = 0;
foreach (DataGridViewRow row in tabla.Rows)
{
rowIndex++;
ColumnIndex = 0;
foreach (DataGridViewColumn col in tabla.Columns)
{
ColumnIndex++;
excel.Cells[rowIndex + 1, ColumnIndex] = row.Cells[col.Name].Value;
}
progress.Value = (rowIndex * 100) / filasTotales;
}
excel.Visible = true;
Worksheet worksheet = (Worksheet)excel.ActiveSheet;
worksheet.Activate();
MessageBox.Show("Exportación Exitosa", "FactuTED", MessageBoxButtons.OK, MessageBoxIcon.Information);
progress.Value = 0;
}
public void InitializePaymentRequest()
{
Ws.Activate();
_payReqLabelCell = Ws.Cells[StartRow, StartColumn];
_payReqLabelCell.Value2 = "Payment request:";
_payReqLabelCell.Font.Bold = true;
_payReqLabelCell.Columns.AutoFit();
_payReqInputCell = Ws.Cells[StartRow, StartColumn + 1];
_payReqInputCell.Interior.Color = Color.AliceBlue;
Formatting.WideTableColumn(_payReqInputCell);
_payReqRange = Ws.Range[_payReqLabelCell, _payReqInputCell];
Formatting.UnderlineBorder(_payReqRange);
Ws.Change += WsOnChangeParsePayReq;
PaymentRequestTable = new VerticalTableSheet <PayReq>(Ws, LApp, PayReq.Descriptor);
PaymentRequestTable.SetupVerticalTable("Decoded Payment Request", StartRow + 2);
PotentialRoutesTable = new TableSheet <Route>(Ws, LApp, Route.Descriptor, "hops");
PotentialRoutesTable.SetupTable("Potential Routes", MaxRoutes, StartRow = PaymentRequestTable.EndRow + 2);
var sendPaymentButtonRow = PotentialRoutesTable.EndRow + 4;
Button sendPaymentButton = Utilities.CreateButton("sendPayment", Ws, Ws.Cells[sendPaymentButtonRow, StartColumn], "Send Payment");
sendPaymentButton.Click += SendPaymentButtonOnClick;
_errorData = Ws.Cells[sendPaymentButtonRow + 3, StartColumn + 1];
_sendStatusRange = Ws.Cells[sendPaymentButtonRow + 3, StartColumn];
_sendStatusRange.Font.Italic = true;
Button clearPaymentInfoButton = Utilities.CreateButton("clearPaymentInfo", Ws, Ws.Cells[sendPaymentButtonRow + 3, StartColumn], "Clear");
clearPaymentInfoButton.Click += ClearPaymentInfoButtonOnClick;
var paymentResponseDataStartRow = sendPaymentButtonRow + 5;
_paymentPreimageLabel = Ws.Cells[paymentResponseDataStartRow, StartColumn];
_paymentPreimageLabel.Value2 = "Proof of Payment";
_paymentPreimageLabel.Font.Italic = true;
Formatting.WideTableColumn(_paymentPreimageLabel);
_paymentPreimageCell = Ws.Cells[paymentResponseDataStartRow, StartColumn + 1];
_paymentPreimageCell.Interior.Color = Color.PaleGreen;
_paymentPreimageCell.RowHeight = 14.3;
_paymentPreimageCell.WrapText = true;
RouteTakenTable = new VerticalTableSheet <Route>(Ws, LApp, Route.Descriptor, new List <string> {
"hops"
});
RouteTakenTable.SetupVerticalTable("Payment Summary", paymentResponseDataStartRow + 3);
HopTable = new TableSheet <Hop>(Ws, LApp, Hop.Descriptor, "chan_id");
HopTable.SetupTable("Route", 4, RouteTakenTable.EndRow + 2);
_payReqInputCell.Columns.ColumnWidth = PayReqColumnWidth;
Utilities.RemoveLoadingMark(Ws);
}
private void DrawGraphFromSelectedCells(int graphRow, int graphColumn, string title, string valueName, Worksheet worksheet)
{
bool screenUpdating = Application.ScreenUpdating;
Application.ScreenUpdating = false;
Excel.Application exApp = Globals.ThisAddIn.Application;
Excel.Range selectedRange = exApp.Selection;
long row = selectedRange.Row;
long col = selectedRange.Column;
Worksheet activeWorksheet = Globals.Factory.GetVstoObject(Application.ActiveWorkbook.ActiveSheet);
string name = GetGraphName(activeWorksheet);
Chart chart = activeWorksheet.Controls.AddChart(activeWorksheet.Range["A1"].Resize[_GRAPH_HEIGHT, _GRAPH_WIDTH]
.Offset[graphRow * _GRAPH_HEIGHT, graphColumn * _GRAPH_WIDTH], name);
chart.ChartType = Excel.XlChartType.xlXYScatterLinesNoMarkers;
chart.SetSourceData(selectedRange, Excel.XlRowCol.xlColumns);
chart.HasTitle = true;
chart.ChartTitle.Text = title;
Excel.Axis xAxis = (Excel.Axis)chart.Axes(Excel.XlAxisType.xlCategory);
xAxis.HasTitle = true;
xAxis.AxisTitle.Text = activeWorksheet.Cells[row, col].Text;
Excel.Axis yAxis = (Excel.Axis)chart.Axes(Excel.XlAxisType.xlValue);
yAxis.HasTitle = true;
yAxis.AxisTitle.Text = valueName;
xAxis.MinimumScaleIsAuto = false;
Excel.Name newName = activeWorksheet.Names.Add("firstCol", selectedRange.Resize[selectedRange.Rows.Count, 1], false);
xAxis.MinimumScale = (double)activeWorksheet.Evaluate("MIN(firstCol)");
xAxis.MaximumScale = (double)activeWorksheet.Evaluate("MAX(firstCol)");
newName.Delete();
newName = activeWorksheet.Names.Add("col", selectedRange.Resize[selectedRange.Rows.Count, 1].Offset[missing, 1], false);
double maxValue = (double)activeWorksheet.Evaluate("MAX(col)");
double minValue = (double)activeWorksheet.Evaluate("MIN(col)");
newName.Delete();
for (int i = 2; i < selectedRange.Columns.Count; ++i)
{
newName = activeWorksheet.Names.Add("col", selectedRange.Resize[selectedRange.Rows.Count, 1].Offset[missing, i], false);
double maxValueNow = (double)activeWorksheet.Evaluate("MAX(col)");
double minValueNow = (double)activeWorksheet.Evaluate("MIN(col)");
newName.Delete();
if (maxValueNow > maxValue)
{
maxValue = maxValueNow;
}
if (minValueNow < minValue)
{
minValue = minValueNow;
}
}
yAxis.MinimumScaleIsAuto = false;
yAxis.MinimumScale = minValue;
yAxis.MaximumScale = maxValue;
chart.Location(Excel.XlChartLocation.xlLocationAsObject, worksheet.Name);
activeWorksheet.Activate();
Application.ScreenUpdating = screenUpdating;
}
public System.Data.DataTable GetData(string pSheetName, int intGetMaxRowColIndex = 1, int intGetMaxColRowIndex = 1, int intbeginRow = 1)
{
int eachRow = 50000;
System.Data.DataTable dt = null;
ws = wb.Worksheets[pSheetName];
ws.Activate();
Range rngBegin, rngEnd, rngCell;
int intmaxRowFromSpecialCol = ws.Cells[ws.Rows.Count, intGetMaxRowColIndex].End(XlDirection.xlUp).row;
int intmaxColFromSpecialCol = ws.Cells[intGetMaxColRowIndex, ws.Columns.Count].End(XlDirection.xlToLeft).column;
int colNum = ws.UsedRange.CurrentRegion.Columns.Count;
int rowNum = ws.UsedRange.CurrentRegion.Rows.Count;
if (intmaxRowFromSpecialCol > rowNum)
{
rowNum = intmaxRowFromSpecialCol;
}
if (intmaxColFromSpecialCol > colNum)
{
colNum = intmaxColFromSpecialCol;
}
int beginRow = intbeginRow;
for (int endRow = 0; endRow < rowNum;)
{
endRow = (endRow + eachRow) > rowNum ? rowNum : (endRow + eachRow);
rngBegin = ws.Cells[beginRow, 1];
rngEnd = ws.Cells[endRow, colNum];
rngCell = ws.Range[rngBegin, rngEnd];
rngCell.Select();
object[,] obj = (object[, ])rngCell.Value;
if (dt == null)
{
dt = ObjectHelper.ObjectToDataTable(obj);
}
else
{
System.Data.DataTable tempDT = dt.Clone();
tempDT = ObjectHelper.ObjectToDataTable(obj, false, tempDT);
foreach (DataRow item in tempDT.Rows)
{
dt.ImportRow(item);
}
}
Marshal.FinalReleaseComObject(rngBegin);
Marshal.FinalReleaseComObject(rngEnd);
Marshal.FinalReleaseComObject(rngCell);
beginRow = beginRow + eachRow;
}
return(dt);
}
public void descargarmemoria()
{
excel.Visible = true;
Worksheet worksheet = (Worksheet)excel.ActiveSheet;
worksheet.Activate();
MessageBox.Show("Exportación Exitosa", "Exportación", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
public MessageForm(Worksheet ws, AsyncLightningApp lApp, Func <TRequestMessage, TResponseMessage> query, MessageDescriptor descriptor, string title, int startRow = 2,
int startColumn = 2)
{
Ws = ws;
Ws.Activate();
_lApp = lApp;
_query = query;
_fieldToRow = new Dictionary <string, int>();
Fields = descriptor.Fields.InDeclarationOrder();
StartRow = startRow;
StartColumn = startColumn;
EndColumn = StartColumn + 1;
var titleCell = ws.Cells[StartRow, StartColumn];
titleCell.Font.Italic = true;
titleCell.Value2 = title;
_dataStartRow = StartRow + 1;
var endDataRow = startRow + Fields.Count;
var form = ws.Range[ws.Cells[_dataStartRow, StartColumn], ws.Cells[endDataRow, EndColumn]];
Formatting.VerticalTable(form);
var header = ws.Range[ws.Cells[_dataStartRow, StartColumn], ws.Cells[endDataRow, StartColumn]];
Formatting.VerticalTableHeaderColumn(header);
var data = ws.Range[ws.Cells[_dataStartRow, EndColumn], ws.Cells[endDataRow, EndColumn]];
Formatting.VerticalTableDataColumn(data);
var rowNumber = _dataStartRow;
foreach (var field in Fields)
{
var headerCell = ws.Cells[rowNumber, StartColumn];
var fieldName = Utilities.FormatFieldName(field.Name);
headerCell.Value2 = fieldName;
var rowRange = ws.Range[ws.Cells[rowNumber, StartColumn], ws.Cells[rowNumber, EndColumn]];
Formatting.VerticalTableRow(rowRange, rowNumber);
_fieldToRow.Add(field.Name, rowNumber);
rowNumber++;
}
var submitButtonRow = rowNumber + 2;
Button submitButton = Utilities.CreateButton("submit" + descriptor.Name, ws, ws.Cells[submitButtonRow, StartColumn], "Submit");
submitButton.Click += SubmitButtonOnClick;
ErrorData = ws.Cells[submitButtonRow, StartColumn + 2];
ErrorData.WrapText = false;
ErrorData.RowHeight = 14.3;
EndRow = submitButtonRow + 1;
titleCell.Columns.AutoFit();
}
private void SetFormatForHeader(Worksheet ws)
{
ws.Range["A1"].EntireRow.HorizontalAlignment = XlHAlign.xlHAlignCenter;
ws.Range["A1"].EntireRow.WrapText = true; // высота строки не должна была быть выставлена явно
ws.Range["A1"].EntireRow.VerticalAlignment = XlVAlign.xlVAlignCenter;
ws.Activate();
ws.Application.ActiveWindow.SplitRow = 2;
ws.Application.ActiveWindow.FreezePanes = true;
}
/// <summary>
/// 指定位置插入一行
/// </summary>
/// <param name="Row"></param>
/// <param name="sheetName"></param>
public void InsertRow(int Row, string sheetName)
{
Worksheet worksheet = (Worksheet)m_pExcelApp.Worksheets[sheetName];
worksheet.Activate();
Range range = (Range)worksheet.Rows[Row, Missing.Value];
range.Insert(XlInsertShiftDirection.xlShiftDown, Missing.Value);
}
public void CopyColumn2AnotherWorkbook(int sourcerow, int sourcecolumn, int destinationrow,
int destinationcolumn, Worksheet destinationworksheet)
{
_currentWorksheet.Activate();
destinationworksheet.Activate();
destinationworksheet.Cells.PasteSpecial(destinationworksheet.Range[destinationworksheet.Cells[destinationrow, destinationcolumn]].EntireColumn.Select());
}
/// <summary>
/// Sets the data into sheet messure.
/// </summary>
/// <param name="sheet">The sheet.</param>
/// <param name="roothCalculation">The rooth calculation.</param>
/// <param name="mouthCalculation">The mouth calculation.</param>
private static void SetDataIntoSheetMessure(Worksheet sheet, RoothCalculationEntity roothCalculation, MouthCalculationEntity mouthCalculation, PatientInformation patientInformation)
{
sheet.Activate();
SetRoothValues(sheet, roothCalculation);
SetMouthValues(sheet, mouthCalculation);
SetPatientInfo(sheet, patientInformation);
}
private void BtnOpenBook(object sender, EventArgs e)
{
excWb = exc.Workbooks.Open(txtExcelBook.Text);
excWs = excWb.Worksheets[1];
// Define o handler do eevnto Change
// Torna visivel a aplication e ativa a worksheet
exc.Visible = true;
excWs.Activate();
}
public void writeRow(string Filename, string [] ary)
{
//===================================================
//打開已經存在的EXCEL工件簿文件
if (!System.IO.File.Exists(Filename))
{
xlBook = xlApp.Workbooks.Add(true);
}
else
{
xlBook = xlApp.Workbooks.Open(Filename, null, null, null, null, null, null, null, null, null, null, null, null, null, null);
}
//停用警告訊息
xlApp.DisplayAlerts = false;
xlApp.Visible = false;
xlBook.Activate();
xlSheet = (Worksheet )xlBook.Worksheets[1];
xlSheet.Activate();
//===================================================
Range userRange = xlSheet.UsedRange;
int rowCount = userRange.Rows.Count;
for (Int32 i = 0; i <= ary.Length - 1; i++)
{
if (!System.IO.File.Exists(Filename))
{
xlSheet.Cells[rowCount, i + 1] = ary[i].ToString();
}
else
{
xlSheet.Cells[rowCount + 1, i + 1] = ary[i].ToString();
}
}
if (!System.IO.File.Exists(Filename))
{
xlBook.SaveAs(Filename, XlFileFormat.xlExcel8, Type.Missing, Type.Missing, Type.Missing, Type.Missing, XlSaveAsAccessMode.xlNoChange, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
}
else
{
xlBook.Save();
}
xlBook.Close(false, Type.Missing, Type.Missing);
System.Runtime.InteropServices.Marshal.ReleaseComObject(xlApp);
xlApp = null;
xlBook = null;
xlSheet = null;
xlRange = null;
GC.Collect();
}
/// <summary>
/// 删除一个工作表
/// </summary>
/// <param name="SheetName">删除的工作表序号</param>
public void DeleteSheet(string sheetName)
{
string mySheetName = mySheet.Name;
((Worksheet)myWorkBook.Worksheets[sheetName]).Delete();
if (mySheetName == sheetName)
{
mySheet = myWorkBook.Worksheets.get_Item(1);
mySheet.Activate();
}
}
/// <summary>
/// 删除一个工作表
/// </summary>
/// <param name="SheetName">删除的工作表名</param>
public void DeleteSheet(int sheetNum)
{
int mySheetIndex = mySheet.Index;
((Worksheet)myWorkBook.Worksheets[sheetNum]).Delete();
if (mySheetIndex == sheetNum)
{
mySheet = myWorkBook.Worksheets.get_Item(1);
mySheet.Activate();
}
}
