/// <summary>
/// 使用COM组件，向当前工作表中的单元格区域写入数据
/// </summary>
/// <param name="sRangeString"></param>
/// <param name="sValueString"></param>
/// <returns></returns>
public static bool SetRange(string sRangeString, string sValueString, MyDB.Database DB)
{
try
{
if (xlSheet == null)
{
return(false);
}
sRangeString = sRangeString.Trim().ToUpper();
string[] sA1Cell = sRangeString.Split('-'); //Excel单元格区域引用使用A1样式，冒号用中划线代替
xlRange = xlSheet.get_Range(sA1Cell[0], sA1Cell[1]);
sValueString = sValueString.Trim();
if (sValueString.Length >= 4 && sValueString.Substring(0, 4).Equals("SQL=", StringComparison.OrdinalIgnoreCase))
{
string strSQL = sValueString.Substring(4);
#if (MYSQL)
if (MySQLDB.MySQLDB.ExecuteDBDataSet(strSQL))
{
int row = MySQLDB.MySQLDB.dataSet.Tables[0].Rows.Count;
int col = MySQLDB.MySQLDB.dataSet.Tables[0].Columns.Count;
#else
if (DB.ExecuteDataSet(strSQL))
{
int row = DB.dataSet.Tables[0].Rows.Count;
int col = DB.dataSet.Tables[0].Columns.Count;
#endif
row = (row < xlRange.Rows.Count) ? row : xlRange.Rows.Count;
col = (col < xlRange.Columns.Count) ? col : xlRange.Columns.Count;
object[,] objValue = new object[row, col]; //二维数组
for (int i = 0; i < row; i++)
{
for (int j = 0; j < col; j++)
{
#if (MYSQL)
objValue[i, j] = MySQLDB.MySQLDB.dataSet.Tables[0].Rows[i][j];
#else
objValue[i, j] = DB.dataSet.Tables[0].Rows[i][j];
#endif
}
}
xlRange.Value2 = objValue;
}
}
else if (sValueString.Length >= 4 && sValueString.Substring(0, 4).Equals("TXT=", StringComparison.OrdinalIgnoreCase))
{
string strValue = sValueString.Substring(4);
xlRange.NumberFormatLocal = "@"; //强制文本类型
xlRange.Value2 = strValue;
}
else
{
xlRange.Value2 = sValueString; //EXCEL默认类型
}
xlBook.Save();
return(true);
}
catch (Exception ex)
{
MessageBox.Show(Form.ActiveForm, ex.Message + "\r\n" + "SetRange(" + sRangeString + "," + sValueString + ")", "SetRange()");
return(false);
}
}
/// <summary>
/// 根据模板文件及配置参数生成EXCEL报表
/// </summary>
/// <param name="sTemplateFile"></param>
/// <param name="sNewFile"></param>
/// <returns></returns>
public static bool ExportReport(string sTemplateExcel, bool bXmlConfig, string sReportExcel, MyDB.Database DB)
{
try
{
sReportExcel = GetTemplateExcel(sTemplateExcel, bXmlConfig, sReportExcel);
if (sReportExcel != null)
{
for (int iSheetIndex = 0; iSheetIndex < g_stTemplateInfo.stSheet.Length; iSheetIndex++)
{
if (OpenExcel(sReportExcel, g_stTemplateInfo.stSheet[iSheetIndex].strSheetName))
{
for (int iRangeIndex = 0; iRangeIndex < g_stTemplateInfo.stSheet[iSheetIndex].stRange.Length; iRangeIndex++)
{
if (!SetRange(g_stTemplateInfo.stSheet[iSheetIndex].stRange[iRangeIndex].strRange,
g_stTemplateInfo.stSheet[iSheetIndex].stRange[iRangeIndex].strValue, DB))
{
return(false);
}
}
}
else
{
return(false);
}
}
}
else
{
return(false);
}
return(true);
}
catch
{
return(false);
}
finally
{
CloseExcel();
}
}
