private List <ExcelReferenceTable> GetReferenceTables(Excel.Sheets xlWorkSheets)
{
List <ExcelReferenceTable> Result = new List <ExcelReferenceTable>();
string Temp = "";
Excel.Worksheet xlWorkSheet = null;
Excel.ListObjects xlListObjects = null;
Excel.ListObject ThisItem = null;
for (int x = 1; x <= xlWorkSheets.Count; x++)
{
ExcelReferenceTable Item = new ExcelReferenceTable();
xlWorkSheet = (Excel.Worksheet)xlWorkSheets[x];
xlListObjects = xlWorkSheet.ListObjects;
Int32 TotalCount = xlListObjects.Count - 1;
for (int y = 0; y <= TotalCount; y++)
{
ThisItem = xlListObjects.Item[y + 1];
Item.Name = ThisItem.Name;
Item.SheetName = xlWorkSheet.Name;
// TODO: Need to tinker with this.
try
{
Excel.QueryTable QT = ThisItem.QueryTable;
Item.SourceDataFile = QT.SourceDataFile;
ReleaseComObject(QT);
}
catch (Exception)
{
Item.SourceDataFile = "";
}
Excel.Range ThisRange = ThisItem.Range;
Temp = ThisRange.Address;
Item.Address = Temp.Replace("$", "");
Result.Add(Item);
Marshal.FinalReleaseComObject(ThisRange);
ThisRange = null;
Marshal.FinalReleaseComObject(ThisItem);
ThisItem = null;
Marshal.FinalReleaseComObject(xlListObjects);
xlListObjects = null;
}
}
ReleaseComObject(xlWorkSheet);
mReferenceTables = Result;
return(Result);
}
private void Tablize()
{
mListObjects = mActiveSheet.ListObjects;
mListObjects.Add(Excel.XlListObjectSourceType.xlSrcRange,
mActiveSheet.Range["A:D"],
Type.Missing,
Excel.XlYesNoGuess.xlYes,
Type.Missing);
//.Name = "TestTable";
//oSheet.ListObjects["TestTable"].TableStyle = "TableStyleMedium3";
}
public void ExportCSV(Excel.Application oAppXls, Excel.Workbooks oWBBs, Excel.Worksheet oWS, string strTableName, string strFileName)
{
RAIIFuncs rFuncs = new RAIIFuncs();
using (RAIICont _raiiCont = new RAIICont())
{
Excel.Workbook _destWB = oWBBs.Add();
_raiiCont.Push(rFuncs.GetFunc(_destWB));
Excel.Sheets _oWSs = _destWB.Sheets;
_raiiCont.Push(rFuncs.GetFunc(_oWSs));
Excel.Worksheet _destWS = _oWSs[1];
_raiiCont.Push(rFuncs.GetFunc(_destWS));
Excel.Range _rngDest = _destWS.Range["A1"];
_raiiCont.Push(rFuncs.GetFunc(_rngDest));
Excel.ListObjects _LOs = oWS.ListObjects;
_raiiCont.Push(rFuncs.GetFunc(_LOs));
Excel.ListObject _srcLO = _LOs[strTableName];
_raiiCont.Push(rFuncs.GetFunc(_srcLO));
Excel.Range _srcRng = _srcLO.Range;
_raiiCont.Push(rFuncs.GetFunc(_srcRng));
_srcRng.Copy(_rngDest);
oAppXls.DisplayAlerts = false;
_destWB.SaveAs(
Filename: @"C:\Users\A.Roennburg\Documents\GH_ArbeitPlatz\Arbeits vom Sandra\MARKANT_CalculateSheet\CSV Daten\" + strFileName + ".csv"
, FileFormat: Excel.XlFileFormat.xlCSV
, CreateBackup: false
, Local: true
);
}
oAppXls.DisplayAlerts = true;
}
/// <summary>
/// Cria as fórmulas do workbook passado como parâmetro
/// </summary>
/// <param name="eBook"></param>
/// <param name="xWorkbook"></param>
private Excel.Workbook createFormulas(Excel.Workbook eBook, XWorkbook xWorkbook)
{
foreach (XWorksheet xWorksheet in xWorkbook.getWorksheets())
{
Excel.Worksheet eWorksheet = eBook.Sheets[xWorksheet.getName()];
foreach (XDataTable xDataTable in xWorksheet.getDataTables())
{
Excel.ListObjects eListObjects = eWorksheet.ListObjects;
Excel.ListObject eListObject = eListObjects[xDataTable.getName()];
foreach (XDataTableColumn sheetColumn in xDataTable.getDataTableColumns())
{
Excel.ListColumns eListColumns = eListObject.ListColumns;
Excel.ListColumn eListColumn = eListColumns[sheetColumn.getName()];
if (sheetColumn.getDataContent() is XTextExp)
{
Excel.Range rng = eListColumn.DataBodyRange;
XTextExp formula = (XTextExp)sheetColumn.getDataContent();
rng.Formula = string.Format("{0}", formula.getTextSymbol());
Marshal.ReleaseComObject(rng);
}
Marshal.ReleaseComObject(eListColumn);
Marshal.ReleaseComObject(eListColumns);
}
Marshal.ReleaseComObject(eListObject);
Marshal.ReleaseComObject(eListObjects);
}
Marshal.ReleaseComObject(eWorksheet);
}
return(eBook);
}
public void SetPrintAreaToTable(Excel.Worksheet oWS, string strTblName)
{
RAIIFuncs rFuncs = new RAIIFuncs();
using (RAIICont _raiiCont = new RAIICont())
{
Excel.ListObjects oLOs = oWS.ListObjects;
_raiiCont.Push(rFuncs.GetFunc(oLOs));
Excel.ListObject oLO = oLOs[strTblName];
_raiiCont.Push(rFuncs.GetFunc(oLO));
Excel.Range rngLO = oLO.Range;
_raiiCont.Push(rFuncs.GetFunc(rngLO));
string prntArea = rngLO.Address;
Excel.PageSetup pageSetup = oWS.PageSetup;
//_raiiCont.Push(rFuncs.GetFunc(pageSetup));
pageSetup.PrintArea = prntArea;
}
}
public void OnClick_Test(Office.IRibbonControl ctrl)
{
try
{
Excel.Worksheet _thisSheet = (Excel.Worksheet)Globals.ThisAddIn.Application.ActiveSheet;
switch (ctrl.Id)
{
case "btn_Test_UnDo":
//Globals.ThisAddIn.Application.Undo();
Globals.ThisAddIn.Application.OnUndo("撤销这个", "ToUnDo");
break;
case "btn_Test_QueryTable":
System.Data.SqlClient.SqlConnectionStringBuilder ssb = new System.Data.SqlClient.SqlConnectionStringBuilder();
ssb.UserID = "oasa";
ssb.Password = "******";
ssb.InitialCatalog = "oa";
ssb.DataSource = "10.90.0.2";
string sqlString = "OLEDB;Provider=SQLOLEDB.1;Persist Security Info=True;User ID=oasa;Initial Catalog=OA;Data Source=10.90.0.2;Use Procedure for Prepare=1;Auto Translate=Tru";
Excel.ListObjects listObjects = _thisSheet.ListObjects;
Excel.ListObject listObject = listObjects.AddEx(Excel.XlListObjectSourceType.xlSrcExternal,
sqlString,
Type.Missing,
Excel.XlYesNoGuess.xlYes,
_thisSheet.Range["$A$2"],
Type.Missing);
Excel.QueryTable queryTable = listObject.QueryTable;
queryTable.CommandType = Excel.XlCmdType.xlCmdSql;
queryTable.CommandText = _thisSheet.Range["A1"].Value;
queryTable.AdjustColumnWidth = true;
queryTable.Refresh();
break;
case "btn_TextResize":
Excel.Range oldRng = _thisSheet.Range["A3:F3"];
ShowInfo("旧区域地址：" + oldRng.Address);
Excel.Range newRng = oldRng.Resize[oldRng.Rows.Count + 2, oldRng.Columns.Count];
ShowInfo("新区域地址：" + newRng.Address);
break;
case "btn_InputCheck":
object f = _thisSheet.Application.InputBox("输入一个，测试bool");
ShowInfo(f.ToString() == "1" ? "有效" : "都是无效");
break;
case "ZS_BTN_Test_ShowForm":
Test_ShowCoverForm();
break;
case "ZS_BTN_Test_TestForm":
Controls.frmTest frm = new Controls.frmTest();
frm.Show();
break;
case "ZS_TEST_GETDEPVERSION":
try
{
String ver = System.Deployment.Application.ApplicationDeployment.CurrentDeployment.CurrentVersion.ToString();
Globals.ThisAddIn.Application.ActiveCell.Value = ver;
}
catch (Exception ex)
{
Globals.ThisAddIn.Application.ActiveCell.Value = ex.Message;
}
break;
case "ZS_TEST_GetCellValue":
Excel.Worksheet worksheet = (Excel.Worksheet)Globals.ThisAddIn.Application.ActiveSheet;
foreach (Excel.Range r in worksheet.UsedRange.Rows)
{
foreach (Excel.Range r2 in r.Cells)
{
r2.ID = "TT" + new Random().Next(10000, 999999).ToString();
StringBuilder sb = new StringBuilder();
sb.AppendLine("Value:" + Convert.ToString(r2.Value));
sb.AppendLine("ID:" + Convert.ToString(r2.ID));
sb.AppendLine("AddressLocal:" + Convert.ToString(r2.AddressLocal));
sb.AppendLine("NumberFormat:" + Convert.ToString(r2.NumberFormat));
sb.AppendLine("NumberFormatLocal:" + Convert.ToString(r2.NumberFormatLocal));
WriteConsole(sb.ToString());
}
}
break;
default:
System.Windows.Forms.MessageBox.Show("未指定的分支");
break;
}
}
catch (Exception)
{
throw;
}
}
/// <summary>
/// Cria validações de dados nas colunas de referência a tabelas
/// </summary>
/// <param name="eBook">workbook correspondente do excel (COM)</param>
/// <param name="xWorkbook">xworkbook</param>
private Excel.Workbook createValidation(Excel.Workbook eBook, XWorkbook xWorkbook)
{
foreach (XWorksheet xWorksheet in xWorkbook.getWorksheets())
{
Excel.Worksheet eWorksheet = eBook.Sheets[xWorksheet.getName()];
foreach (XDataTable xDataTable in xWorksheet.getDataTables())
{
Excel.ListObjects eListObjects = eWorksheet.ListObjects;
Excel.ListObject eListObject = eListObjects[xDataTable.getName()];
foreach (XDataTableColumn sheetColumn in xDataTable.getDataTableColumns())
{
Excel.ListColumns eListColumns = eListObject.ListColumns;
Excel.ListColumn eListColumn = eListColumns[sheetColumn.getName()];
string xreference = sheetColumn.getXReference();
if (xreference != null)
{
var targettable = new XDataTable();
var targetsheet = GetXReference(xWorkbook, xreference, ref targettable);
if (targettable != null)
{
var index = targettable.getKeyIndex();
eWorksheet = eBook.Sheets[targetsheet.getName()];
eListObjects = eWorksheet.ListObjects;
eListObject = eListObjects[targettable.getName()];
eListColumns = eListObject.ListColumns;
eListColumn = eListColumns[index];
string rangename = targettable.getName() + index.ToString(CultureInfo.InvariantCulture);
eBook.Names.Add(rangename, eListColumn.DataBodyRange);
Excel.Name targetName = eBook.Names.Item(rangename, Type.Missing, Type.Missing);
string nameLocal = "=" + targetName.NameLocal;
eWorksheet = eBook.Sheets[xWorksheet.getName()];
eListObjects = eWorksheet.ListObjects;
eListObject = eListObjects[xDataTable.getName()];
eListColumns = eListObject.ListColumns;
eListColumn = eListColumns[sheetColumn.getName()];
eListColumn.DataBodyRange.Validation.Add(Excel.XlDVType.xlValidateList,
Excel.XlDVAlertStyle.xlValidAlertStop, Missing.Value,
nameLocal, Missing.Value);
}
Marshal.ReleaseComObject(eListColumn);
}
Marshal.ReleaseComObject(eListColumn);
Marshal.ReleaseComObject(eListColumns);
}
Marshal.ReleaseComObject(eListObject);
Marshal.ReleaseComObject(eListObjects);
}
Marshal.ReleaseComObject(eWorksheet);
}
return(eBook);
}
/// <summary>
/// Cria a workbook e todos os seus objetos no formato do Excel (COM)
/// </summary>
/// <param name="eApp"></param>
/// <param name="xWorkbook"></param>
private Excel.Workbook createWorkbook(Excel.Application eApp, XWorkbook xWorkbook)
{
Excel.Workbook eBook = eApp.Workbooks.Add();
eBook.Title = xWorkbook.getName();
PrepareWorkbook(eBook);
int numWorksheets = 1;
foreach (XWorksheet xWorksheet in xWorkbook.getWorksheets())
{
Excel.Worksheet eWorksheet = numWorksheets == 1
? (Excel.Worksheet)eBook.Worksheets.Add(Missing.Value, Missing.Value, Missing.Value, Missing.Value)
: (Excel.Worksheet)eBook.Worksheets.Add(Missing.Value, eBook.Worksheets[numWorksheets - 1], Missing.Value, Missing.Value);
eWorksheet.Name = xWorksheet.getName();
int i = 1;
foreach (XDataTable xDataTable in xWorksheet.getDataTables())
{
Excel.Range title = eWorksheet.Cells[i, 1];
title.Value = xDataTable.getName();
Excel.Font titleFont = title.Font;
titleFont.Bold = true;
titleFont.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Red);
Marshal.ReleaseComObject(titleFont);
Marshal.ReleaseComObject(title);
Excel.ListObjects eListObjects = eWorksheet.ListObjects;
Excel.Range bBegin = (Excel.Range)eWorksheet.Cells[++i, 1];
Excel.ListObject eListObject = eListObjects.Add(Excel.XlListObjectSourceType.xlSrcRange, bBegin, Missing.Value, Excel.XlYesNoGuess.xlNo, Missing.Value);
eListObject.Name = xDataTable.getName() ?? "";
eListObject.ShowTotals = false;
eListObject.ListRows.Add();
eListObject.ListRows.Add();
int columns = 1;
foreach (XDataTableColumn sheetColumn in xDataTable.getDataTableColumns())
{
Excel.ListColumns eListColumns = eListObject.ListColumns;
Excel.ListColumn eListColumn = columns == 1 ? eListColumns[1] : eListColumns.Add();
eListColumn.Name = sheetColumn.getName();
eListColumn.TotalsCalculation = Excel.XlTotalsCalculation.xlTotalsCalculationSum;
eListColumn.Range.EntireColumn.ColumnWidth = 14;
if (xDataTable.getDataTableColumns().Count == 1 &&
xDataTable.getDataTableColumns()[0].getDataContent() != null)
{
var datacontent = xDataTable.getDataTableColumns()[0].getDataContent();
if (datacontent is XDataArray)
{
var datacontentimpl = (XDataArray)datacontent;
var arraysymbol = datacontentimpl.getArray();
int numSymbols = 0;
foreach (var symbol in arraysymbol)
{
if (eListColumn.DataBodyRange.Count < numSymbols)
{
eListObject.ListRows.Add();
}
eListColumn.DataBodyRange[numSymbols + 1] = symbol;
numSymbols++;
}
}
}
columns++;
Marshal.ReleaseComObject(eListColumn);
Marshal.ReleaseComObject(eListColumns);
}
Marshal.ReleaseComObject(eListObject);
Marshal.ReleaseComObject(bBegin);
Marshal.ReleaseComObject(eListObjects);
}
numWorksheets++;
Marshal.ReleaseComObject(eWorksheet);
}
return(eBook);
}
