/// <summary>
/// 设置字体和大小
/// </summary>
/// <param name="startRow">开始行</param>
/// <param name="startColumn">开始列</param>
/// <param name="endRow">结束行</param>
/// <param name="endColumn">结束列</param>
/// <param name="fontName">字体名称</param>
/// <param name="fontSize">字体大小</param>
public void SetFontNameSize(int startRow, int startColumn, int endRow, int endColumn, string fontName, int fontSize)
{
Range range = MyExcel.get_Range(MyExcel.Cells[startRow, startColumn], MyExcel.Cells[endRow, endColumn]);
range.Font.Name = fontName;
range.Font.Size = fontSize;
}
private void BtnMergeTable_Click(object sender, EventArgs e)
{
if (((this.txtMergePath.Text.Trim().Length == 0) || (this.txtStartRow.Text.Trim().Length == 0)) || (this.lbxSelectBox.Items.Count <= 0))
{
MessageBox.Show("请核对输入数据，确保源表，保存位置，开始行存在");
}
else
{
List <string> list = new List <string> {
this.txtFiled1.Text.Trim(),
this.txtFiled2.Text.Trim(),
this.txtFiled3.Text.Trim()
};
list.RemoveAll(s => s == "");
MyExcel myExcel = new MyExcel
{
SouceStartRow = int.Parse(this.txtStartRow.Text.Trim()),
LastRowOffset = 0,
SaveFile = this.txtMergePath.Text.Trim(),
AddFileNames = list
};
List <string> souceFileNames = new List <string>();
foreach (string str in this.lbxSelectBox.Items)
{
souceFileNames.Add(str);
}
this.bll.MergeExcel(myExcel, souceFileNames);
}
}
/// <summary>
///设置单元格背景色及填充方式
/// </summary>
/// <param name="startRow">开始行</param>
/// <param name="startColumn">开始列</param>
/// <param name="endRow">结束行</param>
/// <param name="endColumn">结束列</param>
/// <param name="color">颜色索引</param>
/// <param name="pattern">填充方式</param>
public void CellsBackColor(int startRow, int startColumn, int endRow, int endColumn, ExcelStyle.ColorIndex color, ExcelStyle.Pattern pattern)
{
Range range = MyExcel.get_Range(MyExcel.Cells[startRow, startColumn], MyExcel.Cells[endRow, endColumn]);
range.Interior.ColorIndex = color;
range.Interior.Pattern = pattern;
}
/// <summary>
///
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void lblResult_Click(object sender, EventArgs e)
{
//打开改文件
MyExcel myExcel = new MyExcel(xlsFilePath);
AppManagement.add(myExcel.open(true));
}
/// <summary>
/// 下载模板。
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void tsBtnDownLoadTemplate_Click(object sender, EventArgs e)
{
IDownloadTemplate iDownloadTemplate = new DownloadExcelTemplate();
myExcel = iDownloadTemplate.write_Template_For_Record_P_C();
notifyIcon.ShowBalloonTip(6000, "提示：", "模板保存于：" + myExcel.Path, ToolTipIcon.Info);
}
/// <summary>
/// 判断Products_Cost 原始数据中,具体工序是否为空！
/// </summary>
/// <param name="xlsPath"></param>
/// <returns></returns>
public bool check_P_C_Template(string xlsPath, out string msg)
{
MyExcel myExcel = new MyExcel(xlsPath);
myExcel.open();
Worksheet wS = myExcel.getFirstWorkSheetAfterOpen();
Usual_Excel_Helper uEHelper = new Usual_Excel_Helper(wS);
int rowMaxIndex = wS.UsedRange.Rows.Count;
int colMaxIndex = wS.UsedRange.Columns.Count;
//具体 工序在第四列
string specificProcess = string.Empty;
for (int rowIndex = rowMaxIndex; rowIndex >= 1; rowIndex--)
{
specificProcess = uEHelper.getSpecificCellValue("D" + rowIndex);
if (string.IsNullOrEmpty(specificProcess))
{
myExcel.close();
msg = "第" + rowIndex + "行，具体工序不能为空！";
return(false);
}
}
myExcel.close();
msg = "";
return(true);
}
/// <summary>
/// 设置对齐方式
/// </summary>
/// <param name="startRow">开始行</param>
/// <param name="startColumn">开始列</param>
/// <param name="endRow">结束行</param>
/// <param name="endColumn">结束列</param>
/// <param name="hAlign">水平对齐</param>
/// <param name="vAlign">垂直对齐</param>
public void AlignmentCells(int startRow, int startColumn, int endRow, int endColumn, ExcelStyle.ExcelHAlign hAlign, ExcelStyle.ExcelVAlign vAlign)
{
Range range = MyExcel.get_Range(MyExcel.Cells[startRow, startColumn], MyExcel.Cells[endRow, endColumn]);
range.VerticalAlignment = vAlign;
range.HorizontalAlignment = hAlign;
}
private bool check4thRow(List <String> excelPathList, out int maxColIndex)
{
maxColIndex = 0;
//先清除所有记录。
AR_Temp.deleteTheARTemp();
foreach (string excelPath in excelPathList)
{
//打开文档
MyExcel myExcel = new MyExcel(excelPath);
myExcel.open();
Worksheet firstWS = myExcel.getFirstWorkSheetAfterOpen();
string fileNameWithoutSuffix = DirectoryHelper.getFileNameWithoutSuffix(excelPath);
int checkedColIndex = 0;
if (!AttendanceRHelper.isAllDigit(firstWS, 4, out checkedColIndex))
{
myExcel.close();
lblPrompt.Visible = false;
ShowResult.show(lblResult, fileNameWithoutSuffix + ": 第4行" + checkedColIndex.ToString() + "列非数字; 导入取消。", false);
//timerRestoreTheLblResult.Start();
return(false);
}
if (maxColIndex == 0)
{
Usual_Excel_Helper uEHelper = new Usual_Excel_Helper(firstWS);
maxColIndex = uEHelper.getMaxColIndexBeforeBlankCellInSepcificRow(4);
}
myExcel.close();
}
return(true);
}
public ActionResult Uk(int id = 0, string additionalAction = null)
{
if (id == 0)
{
id = 100;
}
if (string.IsNullOrEmpty(additionalAction))
{
CabinetUkData data = MyExcel.GetDataFromDataSource(id);
if (TempData["postHref"] != null)
{
ViewData["postHref"] = TempData["postHref"];
}
else
{
ViewData["postHref"] = "NULL";
}
return(View("UkView", data));
}
else if (additionalAction == "editsummary")
{
CabinetUkData data = MyExcel.GetDataFromDataSource(id);
return(View("UkEditSummary", data));
}
else if (additionalAction == "editdetails")
{
CabinetUkData data = MyExcel.GetDataFromDataSource(id);
return(View("UkEditDetails", data));
}
else
{
return(View());
}
}
public int _xls_ReturnDataRow2Test(string sTestData)
{
MyExcel _xlsTD = new MyExcel(sTestData, false);
if (!_xlsTD.OpenExcelFile(1))
{
_gLib._Report(_PassFailStep.Fail, "Fail to open excel file: " + sTestData);
_xlsTD.CloseExcelApplication();
_gLib._MsgBoxYesNo("Continue Testing?", "Fail to open excel file: " + sTestData);
return(0);
}
int iTotalRow = _xlsTD.getTotalRowCount();
string sStatus = "";
int iRow_Ready = 0;
for (int i = 2; i <= iTotalRow; i++)
{
sStatus = _xlsTD.getOneCellValue(i, 1);
if (sStatus.ToLower() == "ready")
{
iRow_Ready = i;
break;
}
}
_xlsTD.CloseExcelApplication();
return(iRow_Ready);
}
/// <summary>
///
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void showTemplateToolStripMenuItem_Click(object sender, EventArgs e)
{
if (null == myExcel)
{
myExcel = new MyExcel(DownloadExcelTemplate.path);
}
AppManagement.add(myExcel.open(true));
}
/// <summary>
/// 设置字体样式
/// </summary>
/// <param name="startRow">起始行</param>
/// <param name="startColumn">起始列</param>
/// <param name="endRow">结束行</param>
/// <param name="endColumn">结束列</param>
/// <param name="isBold">是否加粗</param>
/// <param name="isItalic">是否斜体</param>
/// <param name="underline">下划线类型</param>
public void SetFontStyle(int startRow, int startColumn, int endRow, int endColumn, bool isBold, bool isItalic, ExcelStyle.UnderlineStyle underline)
{
Range range = MyExcel.get_Range(MyExcel.Cells[startRow, startColumn], MyExcel.Cells[endRow, endColumn]);
range.Font.Bold = isBold;
range.Font.Italic = isItalic;
range.Font.Underline = underline;
}
// GET: Stat
public ActionResult Index()
{
var menu = MyExcel.GetMenuTree();
ViewBag.lastModifiedDate = MyExcel.GetLastModifiedDate();
return(View(menu));
}
public ActionResult Company(int id)
{
var tableName = "co";
DataRow row = MyExcel.GetDetails(tableName, id);
//пока не буду делать связки
return(View(row));
}
public ActionResult Upload(HttpPostedFileBase upload)
{
if (upload != null)
{
string fileName = "test.xlsx";
upload.SaveAs(Server.MapPath("~/Files/" + fileName));
}
MyExcel.LoadExcel(Server.MapPath("~/Files/test.xlsx"));
return(RedirectToAction("Index", "Stat"));
}
protected void Application_Start()
{
//https://metanit.com/sharp/mvc5/12.4.php
Database.SetInitializer <ApplicationDbContext>(new AppDbInitializer());
AreaRegistration.RegisterAllAreas();
FilterConfig.RegisterGlobalFilters(GlobalFilters.Filters);
RouteConfig.RegisterRoutes(RouteTable.Routes);
BundleConfig.RegisterBundles(BundleTable.Bundles);
MyExcel.LoadExcel(Server.MapPath("~/Files/test.xlsx"));
}
public static void Main()
{
MyExcel excel = new MyExcel();
if (excel.Open("db.xls"))
{
ArrayList al = excel.GetSheetNameList();
Console.WriteLine("sheet number: " + al.Count);
Console.WriteLine("============================");
IEnumerator etr = al.GetEnumerator();
while (etr.MoveNext())
Console.WriteLine(etr.Current);
Console.WriteLine("============================");
OleDbDataReader odr = excel.GetData("Sheet1$");
while (odr.Read())
{
Console.WriteLine("{0}\t{1}\t{2}", odr.GetValue(0), odr.GetValue(1), odr.GetValue(2));
}
odr = excel.GetData("Sheet2$");
Console.WriteLine("=== Origin ===");
while (odr.Read())
{
Console.WriteLine("{0}\t{1}", odr.GetValue(0), odr.GetValue(1));
}
/*
//excel.InsertData("Delete * from [Sheet2$] where name='ema'");
excel.InsertData("Insert into [Sheet2$] (id,name) values('5','ema')");
odr = excel.GetData("Sheet2$");
Console.WriteLine("=== Insert ===");
while (odr.Read())
{
Console.WriteLine("{0}\t{1}", odr.GetValue(0), odr.GetValue(1));
}
excel.InsertData("Update [Sheet2$] set name='Jane' where id =1");
Console.WriteLine("=== Update ===");
odr = excel.GetData("Sheet2$");
while (odr.Read())
{
Console.WriteLine("{0}\t{1}", odr.GetValue(0), odr.GetValue(1));
}
*/
excel.Close();
}
Console.ReadKey();
}
private void saveCriticalARInfo(List <string> xlsFilePathList)
{
//先清除所有记录。
AR_Temp.deleteTheARTemp();
for (int i = 0; i <= xlsFilePathList.Count - 1; i++)
{
string excelPath = xlsFilePathList[i];
//打开文档
MyExcel myExcel = new MyExcel(excelPath);
myExcel.openWithoutAlerts();
Worksheet firstWS = myExcel.getFirstWorkSheetAfterOpen();
//删除 时间后立即为空的行。
AttendanceRHelper.clearSheet(firstWS);
Usual_Excel_Helper uEHelper = new Usual_Excel_Helper(firstWS);
string excelName = Usual_Excel_Helper.getExcelName(excelPath);
//先获取第4行的最大行列数目。
int rowMaxIndex = firstWS.UsedRange.Rows.Count;
int pbMaximum = rowMaxIndex - 4;
int pbValue = 0;
//0: 表示 lblResult.Visible
checkNameBGWorker.ReportProgress(0, "lblResult.Visible");
checkNameBGWorker.ReportProgress(pbMaximum, "pb.Maximum");
checkNameBGWorker.ReportProgress(pbValue, "pb.Value");
//lblPrompt.Text = excelName + ": 基本信息采集中...";
checkNameBGWorker.ReportProgress(0, string.Format(@"lblPrompt.Text={0}: 姓名采集中...", excelName));
for (int rowIndex = 5; rowIndex <= rowMaxIndex; rowIndex++)
{
//偶数行为 时间。
if (0 == rowIndex % 2)
{
checkNameBGWorker.ReportProgress(pbValue++, "pb.Value");
continue;
}
//姓名 存于第11列。
string name = uEHelper.getCellContentByRowAndColIndex(rowIndex, 11);
AR_Temp ar_Temp = new AR_Temp();
ar_Temp.Attendance_machine_flag = int.Parse(excelName.Substring(excelName.Length - 1, 1));
ar_Temp.Row_Index = rowIndex;
ar_Temp.Job_number = uEHelper.getCellContentByRowAndColIndex(rowIndex, 3);
ar_Temp.Name = name;
/// <summary>
/// 保存 ATTENDANCE_MACHINE_FLAG,
/// row_index,
/// job_number,
/// name
/// 至AR_Temp中
ar_Temp.saveRecord();
checkNameBGWorker.ReportProgress(pbValue++, "pb.Value");
}
myExcel.close();
}
}
public string GetSearchTableByFilters(string fields, string filters)
{
var curFilters = DataFilters.Deserialize(filters);
var curFields = DataFields.DecriptAndDeserialize(fields);
curFields.AddDetailsUrlField();
FilterCorrector(curFilters, curFields);
var tableJS = MyExcel.GetSubDataTable(curFilters, curFields);
return(tableJS);
}
public ActionResult Import()
{
MyExcel.DB_PATH = @"D:\output\My projects\ASP.NET\Management System v2\Movies App\Movies App\App_Data\movies.xlsx";
MyExcel.InitializeExcel();
List <Movie> excelMovies = MyExcel.ReadMyExcel().ToList();
foreach (var movie in excelMovies)
{
Movie.addMovie(movie);
}
MyExcel.CloseExcel();
return(View(excelMovies));
}
public ActionResult Export()
{
MyExcel.DB_PATH = @"D:\output\My projects\ASP.NET\Management System v2\Movies App\Movies App\App_Data\movies_export.xlsx";
MyExcel.InitializeExcel();
var movies = Movie.getMovies().ToList();
foreach (var movie in movies)
{
MyExcel.WriteToExcel(movie);
}
MyExcel.CloseExcel();
return(View());
}
public ActionResult Exit(int id)
{
var tableName = "e";
DataRow row = MyExcel.GetDetails(tableName, id);
var fundsTmp = MyExcel.GetDetailsList("fe", $"[Номер выхода]='{id}'");
var fundsIds = String.Join(", ", fundsTmp.Select(x => $"'{x["Id"]}'").ToArray());
fundsIds = fundsIds != "" ? fundsIds : "NULL";
var funds = MyExcel.GetDetailsList("f_2", $"[Id] In ({fundsIds})");
ViewBag.funds = funds;
return(View(row));
}
public ActionResult Mc(int id)
{
//var tableName = "m";
//DataRow row = MyExcel.GetDetails(tableName, id);
var oldFunds = MyExcel.GetDetailsList("f_2", $"[Номер УК]='{id}' And [действующие]=0");
var curFunds = MyExcel.GetDetailsList("f_2", $"[Номер УК]='{id}' And [действующие]=1");
CabinetUkData data = MyExcel.GetDataFromDataSource(id);
ViewBag.oldFunds = oldFunds;
ViewBag.curFunds = curFunds;
return(View(data));
}
/// <summary>
///
/// </summary>
/// <returns></returns>
private MSG checkExistSameStaffsButDifferentAttendanceMachine(List <String> excelPathList)
{
MSG msg = new MSG();
List <SimpleARInfo> simpleARInfoList = new List <SimpleARInfo>();
foreach (string excelPath in excelPathList)
{
//打开文档
MyExcel myExcel = new MyExcel(excelPath);
myExcel.open();
Worksheet firstWS = myExcel.getFirstWorkSheetAfterOpen();
//删除 时间为空 的行。
AttendanceRHelper.clearSheet(firstWS);
Usual_Excel_Helper uEHelper = new Usual_Excel_Helper(firstWS);
SimpleARInfo simpleARInfo = null;
string fileNameWithout = DirectoryHelper.getFileNameWithoutSuffix(excelPath);
int rowMaxIndex = firstWS.UsedRange.Rows.Count;
for (int rowIndex = 5; rowIndex <= rowMaxIndex; rowIndex++)
{
if (0 == rowIndex % 2)
{
continue;
}
//姓名 存于第11列。
string name = uEHelper.getCellContentByRowAndColIndex(rowIndex, 11);
simpleARInfo = new SimpleARInfo();
simpleARInfo.AttendanceMachineFlag = int.Parse(fileNameWithout.Substring(fileNameWithout.Length - 1, 1));
simpleARInfo.Name = name;
simpleARInfo.RowIndex = rowIndex;
SimpleARInfo sARInfo = simpleARInfoList.Find(x => x.Name == simpleARInfo.Name);
if (sARInfo != null)
{
//存在重复的员工.
msg.Msg = string.Format(@"{0} 与 {1} 同名;同一人请汇总为一行,不同人,请姓名相异。", sARInfo.toString(), simpleARInfo.toString());
myExcel.close();
return(msg);
}
simpleARInfoList.Add(simpleARInfo);
}
myExcel.save();
myExcel.close();
}
msg.Flag = true;
msg.Msg = "未发现同名用户";
return(msg);
}
/// <summary>
///
/// </summary>
/// <returns></returns>
public MyExcel write_Template_For_Record_P_C()
{
path = System.Windows.Forms.Application.StartupPath + "\\模板\\成衣报价.xls";
string default_dir = System.Windows.Forms.Application.StartupPath + "\\模板";
DirectoryHelper.createDirecotry(default_dir);
System.Data.DataTable dt = OracleDaoHelper.getDTBySql("select * from v_p_c_template");
ExcelHelper.saveDtToExcel(dt, path);
MyExcel myExcel = new MyExcel(path);
myExcel.open();
Worksheet wS = myExcel.getFirstWorkSheetAfterOpen();
Usual_Excel_Helper uEHelper = new Usual_Excel_Helper(wS);
uEHelper.insertAboveTheSpecificRow(1);
uEHelper.setSpecificCellValue("A1", "请在此填写成品名称", 18);
uEHelper.merge("A1", "E1");
uEHelper.setSpecificCellValue("A3", "1", 12, true);
myExcel.save();
myExcel.close();
return(myExcel);
/*V_New_Excel v_New_Excel = new V_New_Excel(path);
* Worksheet wS = v_New_Excel.WS;
* Usual_Excel_Helper uEHelper = new Usual_Excel_Helper(wS);
* uEHelper.setSpecificCellValue("A1", "请在此填写成品名称", 18);
* uEHelper.setSpecificCellValue("A2", "序号", 16);
* uEHelper.setSpecificCellValue("B2", "工序", 16);
* uEHelper.setSpecificCellValue("F2", "序号", 16);
* uEHelper.setSpecificCellValue("G2", "工序", 16);
* uEHelper.merge("A1", "I1");
* //设置列宽
* uEHelper.setColumnWidth("A", 5.5M);
* uEHelper.setColumnWidth("B", 32.75M);
* uEHelper.setColumnWidth("C", 4.15M);
* uEHelper.setColumnWidth("D", 5.38M);
* uEHelper.setColumnWidth("E", 0.15M);
* uEHelper.setColumnWidth("F", 5.5M);
* uEHelper.setColumnWidth("G", 32.75M);
* uEHelper.setColumnWidth("H", 4.15M);
* uEHelper.setColumnWidth("I", 5.38M);
*
* v_New_Excel.saveWithoutAutoFit();
* v_New_Excel.closeTheApp();
* return new MyExcel(path);
*/
}
public ActionResult Investment(int id)
{
//var tableName = "i";
//DataRow row = MyExcel.GetDetails(tableName, id);
//var fundsTmp = MyExcel.GetDetailsList("fi", $"[Номер инвестиции]='{id}'");
//var fundsIds = String.Join(", ", fundsTmp.Select(x => $"'{x["Funds.Id"]}'").ToArray());
//fundsIds = fundsIds != "" ? fundsIds : "NULL";
//var funds = MyExcel.GetDetailsList("f_2", $"[Id] In ({fundsIds})");
//ViewBag.funds = funds;
InvestmentData data = MyExcel.GetInvestmentDataFromDataSource(id);
return(View(data));
}
private void saveCriticalARInfo(List <String> excelPathList)
{
//先清除所有记录。
AR_Temp.deleteTheARTemp();
foreach (string excelPath in excelPathList)
{
//打开文档
MyExcel myExcel = new MyExcel(excelPath);
myExcel.open();
Worksheet firstWS = myExcel.getFirstWorkSheetAfterOpen();
//删除 时间后立即为空的行。
AttendanceRHelper.clearSheet(firstWS);
Usual_Excel_Helper uEHelper = new Usual_Excel_Helper(firstWS);
string fileNameWithoutSuffix = DirectoryHelper.getFileNameWithoutSuffix(excelPath);
//先获取第4行的最大行列数目。
int rowMaxIndex = firstWS.UsedRange.Rows.Count;
pb.Value = 0;
pb.Maximum = rowMaxIndex - 4;
pb.Visible = true;
lblPrompt.Visible = true;
lblResult.Visible = false;
lblPrompt.Text = fileNameWithoutSuffix + ": 基本信息采集中...";
for (int rowIndex = 5; rowIndex <= rowMaxIndex; rowIndex++)
{
if (0 == rowIndex % 2)
{
pb.Value++;
continue;
}
//姓名 存于第11列。
string name = uEHelper.getCellContentByRowAndColIndex(rowIndex, 11);
AR_Temp ar_Temp = new AR_Temp();
ar_Temp.Attendance_machine_flag = int.Parse(fileNameWithoutSuffix.Substring(fileNameWithoutSuffix.Length - 1, 1));
ar_Temp.Row_Index = rowIndex;
ar_Temp.Job_number = uEHelper.getCellContentByRowAndColIndex(rowIndex, 3);
ar_Temp.Name = name;
ar_Temp.saveRecord();
pb.Value++;
}
lblPrompt.Visible = false;
pb.Visible = false;
myExcel.save();
myExcel.close();
}
}
public int _xls_ReturnTotalRow(string sTestData)
{
MyExcel _xlsTD = new MyExcel(sTestData, false);
if (!_xlsTD.OpenExcelFile(1))
{
_gLib._Report(_PassFailStep.Fail, "Fail to open excel file: " + sTestData);
_gLib._MsgBoxYesNo("Continue Testing?", "Fail to open excel file: " + sTestData);
return(0);
}
int totalRow = _xlsTD.getTotalRowCount();
_xlsTD.CloseExcelApplication();
return(totalRow);
}
/// <summary>
/// 保存一份文件到Prepared中。
/// </summary>
/// <param name="xlsFilePath"></param>
private void saveTheExcel(string xlsFilePath)
{
//获取该记录表所对应的月份。
MyExcel srcExceFile = new MyExcel(xlsFilePath);
srcExceFile.openWithoutAlerts();
Worksheet firstSheet_Src = srcExceFile.getFirstWorkSheetAfterOpen();
string year_and_month_src_str = new Usual_Excel_Helper(firstSheet_Src).getCellContentByRowAndColIndex(3, 3).Substring(0, 7);
string fileNameWithoutSuffix = DirectoryHelper.getFileNameWithoutSuffix(xlsFilePath);
string attendanceMachineFlag = fileNameWithoutSuffix.Substring(fileNameWithoutSuffix.Length - 1);
year_and_month_src_str += string.Format(@"_{0}", attendanceMachineFlag);
srcExceFile.close();
//year_and_month_src_str 即为文件名
//string fileName = DirectoryHelper.getFileName(xlsFilePath);
//1.复制该excel 到 Prepared中。
Tools.CmdHelper.copyFileToDestDir(xlsFilePath, System.Windows.Forms.Application.StartupPath + "\\prepared\\" + year_and_month_src_str + ".xls");
string destFilePath = System.Windows.Forms.Application.StartupPath + "\\prepared\\" + year_and_month_src_str + ".xls";
//打开该文件
MyExcel myExcel = new MyExcel(destFilePath);
myExcel.openWithoutAlerts();
//新增一个sheet.
Worksheet firstSheet = myExcel.getFirstWorkSheetAfterOpen();
Usual_Excel_Helper uHelper = new Usual_Excel_Helper(firstSheet);
//获取月份.
string C3ContentStr = uHelper.getCellContentByRowAndColIndex(3, 3);
string year_and_month_str = C3ContentStr.Substring(0, 7);
Worksheet theLastestExcel = myExcel.AddSheetToLastIndex(year_and_month_str);
myExcel.copyRangeFromOneToAnotherSheet(firstSheet, theLastestExcel);
int sheetsCount = myExcel.getCountsOfAllSheet();
//删掉之前的表格,保留最后一个。
for (int i = 1; i <= sheetsCount - 1; i++)
{
myExcel.delTheSheet(1);
}
//一定要保存否则，无效。
myExcel.saveAndColumnsAutoFit();
//关闭该文件
myExcel.close();
}
public string _xls_ReturnGivenName(int iRow, string sTestData)
{
string sName = "";
MyExcel _xlsTD = new MyExcel(sTestData, false);
if (!_xlsTD.OpenExcelFile(1))
{
_gLib._Report(_PassFailStep.Fail, "Fail to open excel file: " + sTestData);
_xlsTD.CloseExcelApplication();
_gLib._MsgBoxYesNo("Continue Testing?", "Fail to open excel file: " + sTestData);
return("");
}
sName = _xlsTD.getOneCellValue(iRow, 14);
_xlsTD.CloseExcelApplication();
return(sName);
}
public string _xls_ReturnOutputDir(string sTestData)
{
string sDir = "";
MyExcel _xlsTD = new MyExcel(sTestData, false);
if (!_xlsTD.OpenExcelFile(1))
{
_gLib._Report(_PassFailStep.Fail, "Fail to open excel file: " + sTestData);
_xlsTD.CloseExcelApplication();
_gLib._MsgBoxYesNo("Continue Testing?", "Fail to open excel file: " + sTestData);
return("");
}
sDir = _xlsTD.getOneCellValue(2, 13);
_xlsTD.CloseExcelApplication();
return(sDir);
}
