private void Hoja1_Startup(object sender, System.EventArgs e)
{
IMSClasses.ConfigurationHelpper oCfg = Globals.ThisWorkbook.oCfg;
IMSClasses.Jobs.Job oJob = Globals.ThisWorkbook.oJob;
IMSClasses.DBHelper.db oDB = Globals.ThisWorkbook.oDb;
try
{
//oTable = Helppers.importData(oCfg.ConnectionString, "SELECT * FROM " + oJob.SQLParameters.TableName.Replace(@"%identity%", "1"), this);
oTable = Helppers.importData(oCfg.ConnectionString, "getTemplateData_GAFarmacia", this);
}
catch
{
this.oTable = null;
Globals.ThisWorkbook.StatusMessage = "Error getting data in sheet 1";
Globals.ThisWorkbook.StatusCorrect = false;
oJob.ReportStatus.Message = Globals.ThisWorkbook.StatusMessage;
oJob.ReportStatus.Status = "ERRO";
oDB.updateJob(oJob.Serialize(), oJob.JOBID);
}
if (!Globals.ThisWorkbook.StatusCorrect || this.oTable == null)
{
this.oTable = null;
Globals.ThisWorkbook.StatusMessage = "Error getting data in sheet 1";
Globals.ThisWorkbook.StatusCorrect = false;
oJob.ReportStatus.Message = Globals.ThisWorkbook.StatusMessage;
oJob.ReportStatus.Status = "ERRO";
oDB.updateJob(oJob.Serialize(), oJob.JOBID);
}
}
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);
}
/// <summary>
/// Formats Excel column contrnts
/// </summary>
/// <param name="pFileName"></param>
/// <param name="worksheet"></param>
/// <param name="colCount"></param>
private void TextToExcelReport(string pFileName, Excel.Worksheet worksheet, int colCount = 0, bool isLowerRange = false)
{
Excel.Worksheet wkSheet = worksheet;
object[] obj = new object[10 + (2 * colCount) + colCount + 1];
if (colCount > 0)
{
for (int i = 0; i <= (10 + (2 * colCount) + colCount); i++)
{
if (i >= 11 + (2 * colCount))
{
obj[i] = 2;
}
else
{
obj[i] = 1;
}
}
}
string range = "$A$2";
if (isLowerRange)
{
range = "$A$1";
}
Excel.QueryTable qryTable = wkSheet.QueryTables.Add("TEXT;" + pFileName, wkSheet.Range[range]);
qryTable.FieldNames = true;
qryTable.RowNumbers = false;
qryTable.FillAdjacentFormulas = false;
qryTable.PreserveFormatting = true;
qryTable.RefreshOnFileOpen = false;
qryTable.RefreshStyle = Excel.XlCellInsertionMode.xlOverwriteCells;
qryTable.SavePassword = false;
qryTable.SaveData = true;
qryTable.AdjustColumnWidth = false;
qryTable.RefreshPeriod = 0;
qryTable.TextFilePromptOnRefresh = false;
qryTable.TextFilePlatform = 932;
qryTable.TextFileStartRow = 1;
qryTable.TextFileParseType = Excel.XlTextParsingType.xlDelimited;
qryTable.TextFileTextQualifier = Excel.XlTextQualifier.xlTextQualifierNone;
qryTable.TextFileConsecutiveDelimiter = false;
qryTable.TextFileTabDelimiter = true;
qryTable.TextFileSemicolonDelimiter = false;
qryTable.TextFileCommaDelimiter = false;
qryTable.TextFileSpaceDelimiter = false;
if (colCount > 0)
{
qryTable.TextFileColumnDataTypes = obj;
}
qryTable.TextFileTrailingMinusNumbers = true;
qryTable.Refresh(false);
wkSheet.Range["1:1"].Font.Bold = true;
wkSheet.Columns.AutoFit();
}
private void RefreshSheetData()
{
Excel.Worksheet sheet = null;
Excel.QueryTables queryTables = null;
Excel.QueryTable queryTable = null;
Excel.CustomProperty changesProperty = null;
try
{
var module = this.AddinModule as AddinModule;
module.SheetChangeEvent = false;
sheet = ExcelApp.ActiveSheet as Excel.Worksheet;
if (sheet != null)
{
queryTables = sheet.QueryTables;
if (queryTables.Count > 0)
{
queryTable = queryTables.Item(1);
queryTable.RefreshStyle = Excel.XlCellInsertionMode.xlOverwriteCells;
queryTable.PreserveColumnInfo = true;
queryTable.PreserveFormatting = true;
queryTable.Refresh(false);
}
changesProperty = sheet.GetProperty("uncommittedchanges");
if (changesProperty != null)
{
changesProperty.Delete();
}
}
module.SheetChangeEvent = true;
}
finally
{
if (changesProperty != null)
{
Marshal.ReleaseComObject(changesProperty);
}
if (queryTable != null)
{
Marshal.ReleaseComObject(queryTable);
}
if (queryTables != null)
{
Marshal.ReleaseComObject(queryTables);
}
if (sheet != null)
{
Marshal.ReleaseComObject(sheet);
}
}
}
//to run this example, the local machine must have internet access
private void Listing_2_4()
{
object async = false;
Excel.Range rngDestination = this.Application.get_Range("A1", missing);
object connection = "URL;http://edition.cnn.com/WORLD/";
Excel.QueryTable tblQuery = this.QueryTables.Add(connection, rngDestination, missing);
tblQuery.BackgroundQuery = true;
tblQuery.TablesOnlyFromHTML = true;
tblQuery.Refresh(async);
tblQuery.SaveData = true;
}
public bool importData(String sConnectionString, String sSqlQuery)
{
Boolean bDone = false;
try
{
Excel.Range oRange = this.Range["A1"];
this.oTable = this.QueryTables.Add(sConnectionString, oRange);
this.oTable.CommandType = Excel.XlCmdType.xlCmdSql;
this.oTable.CommandText = sSqlQuery;
this.oTable.Refresh();
bDone = true;
for (int i = 1; i <= Globals.ThisWorkbook.Connections.Count; i++)
Globals.ThisWorkbook.Connections[i].Delete();
}
catch (Exception e)
{
//MessageBox.Show(e.Message);
bDone = false;
}
return bDone;
}
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;
}
}
private void tvTables_NodeMouseDoubleClick(object sender, TreeNodeMouseClickEventArgs e)
{
Excel.Worksheet sheet = null;
Excel.Range insertionRange = null;
Excel.QueryTable queryTable = null;
Excel.QueryTables queryTables = null;
Excel.Range cellRange = null;
Excel.CustomProperties sheetProperties = null;
Excel.CustomProperty primaryKeyProperty = null;
SqlConnectionStringBuilder builder = null;
string connString = "OLEDB;Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=True;Data Source=@servername;Initial Catalog=@databasename";
string connStringSQL = "OLEDB;Provider=SQLOLEDB.1;Persist Security Info=True;User ID=@username;Password=@password;Data Source=@servername;Initial Catalog=@databasename";
string databaseName = string.Empty;
string tableName = string.Empty;
try
{
var module = this.AddinModule as AddinModule;
module.SheetChangeEvent = false;
tableName = e.Node.Text;
sheet = ExcelApp.ActiveSheet as Excel.Worksheet;
cellRange = sheet.Cells;
insertionRange = cellRange[1, 1] as Excel.Range;
builder = new SqlConnectionStringBuilder(dcd.ConnectionString);
databaseName = builder.InitialCatalog;
if (!builder.IntegratedSecurity)
{
connString = connStringSQL;
}
connString =
connString.Replace("@servername", builder.DataSource)
.Replace("@databasename", databaseName)
.Replace("@username", builder.UserID)
.Replace("@password", builder.Password);
queryTables = sheet.QueryTables;
if (queryTables.Count > 0)
{
queryTable = queryTables.Item(1);
queryTable.CommandText = String.Format("SELECT * FROM [{0}].{1}", databaseName, tableName);
}
else
{
queryTable = queryTables.Add(connString, insertionRange,
String.Format("SELECT * FROM [{0}].{1}", databaseName, tableName));
}
queryTable.RefreshStyle = Excel.XlCellInsertionMode.xlOverwriteCells;
queryTable.PreserveColumnInfo = true;
queryTable.PreserveFormatting = true;
queryTable.Refresh(false);
var primaryKey = SqlUtils.GetPrimaryKey(dcd.ConnectionString, tableName);
// save original table
this.tableName = tableName;
// to sheet name must be less then 31 characters long
sheet.Name = tableName.Substring(0, Math.Min(tableName.Length, 30));
chPrimaryKey.Text = primaryKey;
sheetProperties = sheet.CustomProperties;
primaryKeyProperty = sheetProperties.Add("PrimaryKey", primaryKey);
module.SheetChangeEvent = true;
}
catch (Exception ex)
{
Console.Write(ex.Message);
}
finally
{
if (primaryKeyProperty != null)
{
Marshal.ReleaseComObject(primaryKeyProperty);
}
if (sheetProperties != null)
{
Marshal.ReleaseComObject(sheetProperties);
}
if (cellRange != null)
{
Marshal.ReleaseComObject(cellRange);
}
if (queryTables != null)
{
Marshal.ReleaseComObject(queryTables);
}
if (queryTable != null)
{
Marshal.ReleaseComObject(queryTable);
}
if (insertionRange != null)
{
Marshal.ReleaseComObject(insertionRange);
}
if (sheet != null)
{
Marshal.ReleaseComObject(sheet);
}
}
}
private int ExportToExcel(ref Worksheet worksheet, System.Data.DataTable dt)
{
string sCon = this.ConnectionString;
SqlConnection con = new SqlConnection(sCon);
string tbl = "";
try
{
con.Open();
tbl = CreateTempTableFromDataTable(ref con, dt);
using (SqlBulkCopy bulkCopy = new SqlBulkCopy(sCon))
{
bulkCopy.DestinationTableName = tbl;
//if (dt.Columns.Contains(destinationTable.Columns[i].ToString()))//contain method is not case sensitive
//{
// //Once column matched get its index
// int sourceColumnIndex = dt.Columns.IndexOf(destinationTable.Columns[i].ToString());
// //give coluns name of source table rather then destination table so that it would avoid case sensitivity
// bulkCopy.ColumnMappings.Add(dt.Columns[sourceColumnIndex].ToString(), dt.Columns[sourceColumnIndex].ToString());
//}
bulkCopy.WriteToServer(dt);
bulkCopy.Close();
}
}
catch (Exception ex)
{
throw new Exception("Error creating temporary table\nex.Message", ex.InnerException);
}
Microsoft.Office.Interop.Excel.Range rng = null;
Microsoft.Office.Interop.Excel.QueryTable qry = null;
SqlConnectionStringBuilder cb = new SqlConnectionStringBuilder(this.ConnectionString);
StringBuilder sConn = new StringBuilder();
sConn.Append("OLEDB;Provider=SQLOLEDB.1;");
sConn.Append("Data Source=" + con.DataSource + ";");
sConn.Append("Initial Catalog=" + con.Database + ";");
if (cb.UserID.Length > 0)
{
sConn.Append("User ID=" + cb.UserID + ";");
sConn.Append("Password="******";");
}
else
{
sConn.Append("Persist Security Info = False;Integrated Security=SSPI;");
}
try
{
if (AppendToFile)
{
Range rngLast = worksheet.Cells.SpecialCells(XlCellType.xlCellTypeLastCell);
rng = worksheet.Cells[rngLast.Row, 1];
}
else
{
rng = worksheet.Range["$A$1"];
}
qry = worksheet.ListObjects.AddEx(SourceType: Microsoft.Office.Interop.Excel.XlListObjectSourceType.xlSrcExternal, Source: sConn.ToString(), Destination: rng).QueryTable;
qry.CommandText = "Select * from " + tbl;
qry.FillAdjacentFormulas = false;
qry.PreserveFormatting = true;
qry.RefreshOnFileOpen = false;
qry.BackgroundQuery = true;
qry.SavePassword = false;
qry.SaveData = true;
qry.AdjustColumnWidth = true;
qry.RefreshPeriod = 0;
qry.PreserveColumnInfo = true;
qry.ListObject.Name = "qry" + worksheet.ListObjects.Count.ToString();
qry.Refresh(BackgroundQuery: false);
}
catch (Exception ex)
{
new Exception(string.Format("Error in retrieving data from excel\n{0}", ex.Message), ex.InnerException);
}
finally
{
if (con.State == ConnectionState.Open)
{
con.Close();
}
if (con != null)
{
con.Dispose();
}
}
return(qry.ResultRange.Rows.Count - 1); //ללא שורת כותרת
}
private void tvTables_NodeMouseDoubleClick(object sender, TreeNodeMouseClickEventArgs e)
{
Excel.Worksheet sheet = null;
Excel.Range insertionRange = null;
Excel.QueryTable queryTable = null;
Excel.QueryTables queryTables = null;
Excel.Range cellRange = null;
Excel.CustomProperties sheetProperties = null;
Excel.CustomProperty primaryKeyProperty = null;
Excel.CustomProperty tableColumnsProperty = null;
Excel.CustomProperty tableLoadedProperty = null;
SqlConnectionStringBuilder builder = null;
string connString = "OLEDB;Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=True;Data Source=@servername;Initial Catalog=@databasename";
string connStringSQL = "OLEDB;Provider=SQLOLEDB.1;Persist Security Info=True;User ID=@username;Password=@password;Data Source=@servername;Initial Catalog=@databasename";
string databaseName = string.Empty;
string tableName = string.Empty;
string xmlString = string.Empty;
try
{
ExcelApp.EnableEvents = false;
tableName = e.Node.Text;
sheet = ExcelApp.ActiveSheet as Excel.Worksheet;
cellRange = sheet.Cells;
insertionRange = cellRange[1, 1] as Excel.Range;
builder = new SqlConnectionStringBuilder(dcd.ConnectionString);
databaseName = builder.InitialCatalog;
if (!builder.IntegratedSecurity)
{
connString = connStringSQL;
}
connString =
connString.Replace("@servername", builder.DataSource)
.Replace("@databasename", databaseName)
.Replace("@username", builder.UserID)
.Replace("@password", builder.Password);
queryTables = sheet.QueryTables;
//clear Excel Querytables
foreach (Excel.QueryTable prop in queryTables)
{
prop.Delete();
}
sheet.Cells.ClearContents();
if (queryTables.Count > 0)
{
queryTable = queryTables.Item(1);
queryTable.CommandText = String.Format("SELECT * FROM [{0}].{1}", databaseName, tableName);
}
else
{
queryTable = queryTables.Add(connString, insertionRange,
String.Format("SELECT * FROM [{0}].{1}", databaseName, tableName));
}
queryTable.RefreshStyle = Excel.XlCellInsertionMode.xlOverwriteCells;
queryTable.PreserveColumnInfo = true;
queryTable.PreserveFormatting = true;
queryTable.Refresh(false);
var primaryKey = SqlUtils.GetPrimaryKey(dcd.ConnectionString, tableName);
var tableColumns = SqlUtils.GetAllColumns(dcd.ConnectionString, tableName);
// save original table
this.tableName = tableName;
// to sheet name must be less then 31 characters long
sheet.Name = tableName.Substring(0, Math.Min(tableName.Length, 30));
chPrimaryKey.Text = primaryKey;
sheetProperties = sheet.CustomProperties;
//clear Excel properties to prevent duplicate primary key property error
foreach (Excel.CustomProperty prop in sheetProperties)
{
prop.Delete();
}
primaryKeyProperty = sheetProperties.Add("PrimaryKey", primaryKey);
tableLoadedProperty = sheetProperties.Add("TableLoaded", 1);
foreach (var cols in tableColumns)
{
xmlString += "<row column=\"" + cols.Key + "\" ";
xmlString += "columndatatype=\"" + cols.Value + "\">";
xmlString += cols.Key;
xmlString += "</row>";
}
tableColumnsProperty = sheetProperties.Add("TableColumns", xmlString);
ExcelApp.EnableEvents = true;
}
catch (Exception ex)
{
Console.Write(ex.Message);
throw;
}
finally
{
if (primaryKeyProperty != null)
{
Marshal.ReleaseComObject(primaryKeyProperty);
}
if (tableLoadedProperty != null)
{
Marshal.ReleaseComObject(tableLoadedProperty);
}
if (tableColumnsProperty != null)
{
Marshal.ReleaseComObject(tableColumnsProperty);
}
if (sheetProperties != null)
{
Marshal.ReleaseComObject(sheetProperties);
}
if (cellRange != null)
{
Marshal.ReleaseComObject(cellRange);
}
if (queryTables != null)
{
Marshal.ReleaseComObject(queryTables);
}
if (queryTable != null)
{
Marshal.ReleaseComObject(queryTable);
}
if (insertionRange != null)
{
Marshal.ReleaseComObject(insertionRange);
}
if (sheet != null)
{
Marshal.ReleaseComObject(sheet);
}
}
}
/// <param name="strSql"></param>
/// <summary>
/// 使用QueryTable从一个外部数据源创建Worksheet
/// </summary>
/// <param name="intSheetNumber">导出第几个sheet</param>
/// <param name="blIsMoreThan">余下的数据是否大于指定的每个Sheet的最大记录数</param>
/// <param name="strTitle">表头,需与查询sql语句对齐一致。</param>
/// <param name="strSql">查询的sql语句，表头的文字需与该sql语句对齐一致。</param>
/// <param name="strTablName">查询的表名</param>
/// <param name="strMastTitle">标题</param>
/// <param name="TableIDName">主键</param>
/// <param name="sqlwhere">查询条件包含[where]</param>
/// <param name="strFileName">Excel文件导出路径</param>
public void ExportDataByQueryTable(int intSheetNumber, bool blIsMoreThan, string[] strTitle, string strSql, string strTablName, string strMastTitle, string TableIDName, string sqlwhere, string strFileName)
{
try
{
string strQuery = string.Empty;
string sqlwhere1 = sqlwhere != "" ? "" : sqlwhere.Substring(7, sqlwhere.Length - 7);
if (blIsMoreThan)
{
strQuery = String.Format("select top {0} from {1} where not {2} in (select top {3} from {4} {5}) {6}", dbSheetSize + " " + strSql, strTablName, TableIDName, dbSheetSize * (intSheetNumber - 1) + TableIDName, strTablName, sqlwhere, sqlwhere1);
}
else
{
strQuery = String.Format("Select Top {0} from {1} {2}", dbSheetSize + " " + strSql, strTablName, sqlwhere);
}
int strTitleNnamecount = strTitle.Length;
m_objSheet = (Excel.Worksheet)(m_objSheets.get_Item(intSheetNumber));//操作哪个SHEET
//m_objSheet.Name = strMastTitle + intSheetNumber.ToString() + DateTime.Now.ToShortDateString(); //sheet名称
m_objSheet.Cells[1, 1] = strMastTitle;//标题
m_objSheet.Cells[2, 1] = "打印日期" + DateTime.Now.ToShortDateString();
//写入标题
for (int i = 1; i <= strTitleNnamecount; i++)
{
m_objSheet.Cells[3, i] = strTitle[i - 1].ToString();
}
m_objRange = m_objSheet.get_Range("A4", missing);//从第四行开始写入
//格式设置
m_objSheet.get_Range(m_objSheet.Cells[1, 1], m_objSheet.Cells[1, strTitleNnamecount]).MergeCells = true; //合并单元格
m_objSheet.get_Range(m_objSheet.Cells[2, 1], m_objSheet.Cells[2, strTitleNnamecount]).MergeCells = true; //合并单元格
//标题设置
Excel.Range m_objRangeMastTitle = m_objSheet.get_Range(m_objSheet.Cells[1, 1], m_objSheet.Cells[1, strTitleNnamecount]);
m_objRangeMastTitle.Font.Name = "黑体"; //设置字体
m_objRangeMastTitle.Font.Size = 16; //设置字体大小
m_objRangeMastTitle.Font.Bold = true; //字体加粗
m_objRangeMastTitle.HorizontalAlignment = XlHAlign.xlHAlignCenter; //水平居中
m_objRangeMastTitle.VerticalAlignment = XlVAlign.xlVAlignCenter; //垂直居中
//标题设置
Excel.Range m_objRangeTitle = m_objSheet.get_Range(m_objSheet.Cells[3, 1], m_objSheet.Cells[3, strTitleNnamecount]);
m_objRangeTitle.Font.Name = "黑体"; //设置字体
m_objRangeTitle.Font.Size = 12; //设置字体大小
m_objRangeTitle.Font.Bold = true; //字体加粗
m_objRangeTitle.HorizontalAlignment = XlHAlign.xlHAlignCenter; //水平居中
m_objRangeTitle.VerticalAlignment = XlVAlign.xlVAlignCenter; //垂直居中
//参数依次为：数据连接，填充起始单元格，查询SQL语句
tb = m_objSheet.QueryTables.Add("OLEDB;Provider=SQLOLEDB.1;" + _connectionString, m_objRange, strQuery);
tb.Refresh(tb.BackgroundQuery);//是否异步查询
//区域删除【第4行】
//Excel.Range range = objExcel.get_Range(objExcel.Cells[4, strTitleNnamecount], objExcel.Cells[4, strTitleNnamecount]);
//range.Select();
//if (true)//是否整行删除
m_objRange.EntireRow.Delete(XlDeleteShiftDirection.xlShiftUp);
//else
// range.Delete(XlDeleteShiftDirection.xlShiftUp);
}
catch
{
}
}