public Export(string embedded)
{
string tmp = String.Empty;
try
{
//получаем шаблон из прикладных ресурсов
Stream template = GetResourceFileStream(embedded);
//создаем временный файл
tmp = System.IO.Path.GetTempFileName().Replace(".tmp", ".xlsx");
//сохраняем шаблон во временный файл
using (var fileStream = File.Create(tmp))
{
template.CopyTo(fileStream);
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
// Создаем приложение и открываем в нём временный файл
objApp = new Excel.Application();
objBook = objApp.Workbooks.Open(tmp);
objBook.Activate();
objSheets = objBook.Worksheets;
objSheet = (Excel._Worksheet)objSheets.get_Item(1);
}
public void CreateWorkbook()
{
var templateFile = Path.Combine(System.Windows.Forms.Application.StartupPath,
@"Templates\TemplateWorkBook.xlsx");
MainForm.LogWriter.WriteDebugMessage("Creating new workbook.");
//mWorkbook = mApplication.Workbooks.Add(Missing.Value);
mWorkbook = mApplication.Workbooks.Add(templateFile);
mMeasurementDataWorksheet = (Excel.Worksheet)mWorkbook.Sheets.Item[1];
mMiscellaneousDataWorksheet = (Excel.Worksheet)mWorkbook.Sheets.Item[2];
//var sheet3 = (Excel.Worksheet)mWorkbook.Sheets.Item[3];
//Rename first and second worksheet, delete third worksheet
//mMeasurementDataWorksheet.Name = "Measurement Data";
//mMiscellaneousDataWorksheet.Name = "Miscellaneous Data";
//sheet3.Delete();
//Set measurement data worksheet as active worksheet
mMeasurementDataWorksheet.Activate();
//Display windows in splitview
mWorkbook.Windows.Arrange(Excel.XlArrangeStyle.xlArrangeStyleHorizontal);
MainForm.HeadersWritten = false;
MainForm.LogWriter.WriteDebugMessage("Created new workbook.");
}
public void readExcel()
{
string valueString = string.Empty;
objExcelApp = new Microsoft.Office.Interop.Excel.Application();
objBooks = (Excel.Workbooks)objExcelApp.Workbooks;
//Open the workbook containing the address data.
objBook = objBooks.Open(@"C:\Temp\data\Test.xlsx", Missing.Value, Missing.Value,
Missing.Value, Missing.Value,
Missing.Value, Missing.Value,
Missing.Value, Missing.Value,
Missing.Value, Missing.Value,
Missing.Value, Missing.Value,
Missing.Value, Missing.Value);
//Get a reference to the first sheet of the workbook.
objSheets = objBook.Worksheets;
objSheet = (Excel._Worksheet)objSheets.get_Item(1);
//Select the range of data containing the addresses and get the outer boundaries.
rngLast = objSheet.get_Range("A1").SpecialCells(Microsoft.Office.Interop.Excel.XlCellType.xlCellTypeLastCell);
long lLastRow = rngLast.Row;
long lLastCol = rngLast.Column;
// Iterate through the data and concatenate the values into a comma-delimited string.
for (long rowCounter = 1; rowCounter <= lLastRow; rowCounter++)
{
for (long colCounter = 1; colCounter <= lLastCol; colCounter++)
{
//Write the next value into the string.
Excel.Range cell = (Excel.Range)objSheet.Cells[rowCounter, colCounter];
string cellvalue = cell.Value.ToString();
//TODO: add your business logic for retrieve cell value
}
}
}
/**
* if previous logs exist we open it and update it
**/
public bool OpenWorkBook()
{
if (!File.Exists(m_logName))
{
return false;
}
try
{
m_WorkBook = (Excel._Workbook)(m_XLApp.Workbooks.Open(m_logName));
m_Sheet = (Excel._Worksheet)m_WorkBook.ActiveSheet;
}
catch (Exception e)
{
throw new LoggerException("Cannot open log file: " + e.Message);
}
if (m_Sheet == null)
{
return false;
}
m_columnsMap = new Dictionary<string, int>();
string projectName;
int lastRow = m_Sheet.UsedRange.Rows.Count;
for (int column = 2; column < m_Sheet.UsedRange.Columns.Count + 1; column++)
{
projectName = GetStringFromCell(1, column);
m_columnsMap.Add(projectName, column);
}
return true;
}
public Export()
{
// Instantiate Excel and start a new workbook.
objApp = new Excel.Application();
objBooks = objApp.Workbooks;
objBook = objBooks.Add(Missing.Value);
objSheets = objBook.Worksheets;
objSheet = (Excel._Worksheet)objSheets.get_Item(1);
}
public ExcelManipulation()
{
_excelApp = new Excel.Application();
_flagexcelapp = 1;
_excelApp.Visible = true;
//Создаём новую книгу
_workBook = (Excel._Workbook)(_excelApp.Workbooks.Add(Missing.Value));
_workSheet = (Excel._Worksheet)_workBook.ActiveSheet;
}
public void generatExcelNoSubmit(string f)
{
string file_Name = f;
m_oExcelApp = new Excel.Application();
m_oExcelApp.Visible = false;
m_oExcelApp.UserControl = false;
m_oSheet = null;
excelRange = null;
try
{
m_oBook = m_oExcelApp.Workbooks.Add(missing);
Excel.Sheets sheets = m_oBook.Worksheets;
//Add new 4 Sheet
//sheets.Add(System.Type.Missing, m_oBook.Sheets[3], 1, Excel.XlSheetType.xlWorksheet);
//Product Sheet [Sheet1]
m_oSheet = (Excel._Worksheet)m_oBook.Sheets[1];
m_oSheet.Activate();
m_oSheet.Name = "รายการเสนอซื้อ";
SetData_to_SheetNoSubmit();
string template = Application.StartupPath;
string strRunReport = file_Name;
//string strPass = "******"; password
m_oBook.SaveAs(strRunReport, Excel.XlFileFormat.xlWorkbookNormal, null, null, null, null, Excel.XlSaveAsAccessMode.xlShared, null, null, null, null, null);
m_oExcelApp.Visible = true;
}
catch (interop.COMException ex)
{
MessageBox.Show("Error accessing Excel: " + ex.ToString());
}
catch (Exception ex)
{
MessageBox.Show("Error: " + ex.ToString());
}
finally
{
if (m_oExcelApp == null)
{
m_oExcelApp.Quit();
m_oExcelApp = null;
GC.Collect();
GC.WaitForPendingFinalizers();
GC.Collect();
GC.WaitForPendingFinalizers();
}
}
}
private void constructExcelObject(String sheetPath)
{
excelApp = new Excel.Application();
excelWorkbook = excelApp.Workbooks.Open(@sheetPath);
excelWorksheet = excelWorkbook.Sheets[1];
Excel.Range excelRange = excelWorksheet.UsedRange;
addressMap = new Dictionary<int, Utilities.ExcelRowBinder>();
fillAddressList(excelWorkbook, excelWorksheet, excelRange);
}
public Logger(Table table)
{
_table = table;
excel = new Excel.Application();
excel.Workbooks.Add();
_worksheet = (Excel.Worksheet)excel.ActiveSheet;
_iteration = 1;
}
public ReportBuilder()
{
app = new Excel.Application();
appBooks = app.Workbooks;
currentBook = appBooks.Add(Missing.Value);
sheets = currentBook.Worksheets;
currentSheet = (Excel._Worksheet)sheets.get_Item(1);
range = currentSheet.get_Range("A1", Missing.Value);
charts = currentSheet.ChartObjects(Type.Missing);
chartObject = charts.Add(400, LastIndex, chartWidth, chartHeight);
}
public ExcelWorksheet()
{
excelApp.Visible = true;
System.Threading.Thread.CurrentThread.CurrentCulture = new System.Globalization.CultureInfo("en-US");
excelWorkBook = excelApp.ActiveWorkbook;
if (excelWorkBook == null)
excelWorkBook = (Excel._Workbook) excelApp.Workbooks.Add(Missing.Value);
excelSheet = (Excel._Worksheet) excelWorkBook.ActiveSheet;
excelSheet.Name = "SpaceClaim";
}
/// <summary>
/// Метод создающий новый файл
/// </summary>
/// <param name="filename">Имя и путь файла</param>
/// <param name="background">Создать в бэкграунде</param>
/// <param name="overwrite">Флаг перезаписи файле, если таковой уже существует</param>
public static void CreateNewFile(string filename, bool background, bool overwrite)
{
if (!background)
excelApp.Visible = true;
else
excelApp.Visible = false;
excelApp.Workbooks.Add();
workSheet = excelApp.ActiveSheet;
if (File.Exists(filename))
if (overwrite)
File.Delete(filename);
workSheet.SaveAs(filename);
}
public override string Read()
{
try
{
excelApp = new Excel.Application();
excelApp.Visible = false;
object missingValue = System.Reflection.Missing.Value;
object readOnly = true;
string filePath = (string)FilePath;
workBook = excelApp.Workbooks.Open(filePath,
missingValue,
readOnly,
missingValue,
missingValue,
missingValue,
missingValue,
missingValue,
missingValue,
missingValue,
missingValue,
missingValue,
missingValue,
missingValue,
missingValue);
objectSheet = (Excel.Worksheet)excelApp.ActiveSheet;
// Get full range which used.
Excel.Range usedRange = objectSheet.UsedRange;
string result = string.Empty;
for (int i = 1; i <= usedRange.Rows.Count; i++)
{
for (int j = 1; j <= usedRange.Columns.Count; j++)
{
result += usedRange.Cells[i, j].Text;
}
}
excelApp.Quit();
return result;
}
catch (Exception e)
{
throw e;
}
}
public static void superswitch(int colSour, int colID, string[] cellsCol, string openingVar, string closingVar, bool Numericalcheck, bool VariableCheck)
{
for (int owbb = 0; owbb < WorkBooks.Count; owbb++)
{
oWB = (Excel._Workbook)WorkBooks[owbb];
for (int owss = 1; owss <= oWB.Worksheets.Count; owss++)
{
oWS = (Excel._Worksheet)oWB.Worksheets.get_Item(owss);
foreach (Excel.Range fila in oWS.UsedRange.Rows)
{
actualizarSID((string)(oWS.Cells[fila.Row, colSour] as Excel.Range).Value, (string)(oWS.Cells[fila.Row, colID] as Excel.Range).Value, openingVar, closingVar);
if (cellValueS != null)
{
variablesInString = getVariablesinString(cellValueS);
if (Numericalcheck == true)
{
numerosenSource = getNumbers(cellValueS, variablesInString);
}
switch (cellsCol.Length)
{
case 1:
if (cellsCol[0] != "")
{
if (VariableCheck)
{
issuefounded = VarCompare((string)(oWS.Cells[fila.Row, int.Parse(cellsCol[0])] as Excel.Range).Value, owbb);
}
if ((issuefounded == false) && (Numericalcheck == true))
{
NumCompare((string)(oWS.Cells[fila.Row, int.Parse(cellsCol[0])] as Excel.Range).Value, owbb);
}
}
else
{
System.Windows.Forms.MessageBox.Show("Please make sure that you fill \nin all the mandatory fields properly.");
}
break;
case 2:
int[] columnass = { -1, -1 };
if ((cellsCol[0] != null) && (cellsCol[0] != ""))
{
columnass[0] = int.Parse(cellsCol[0]);
}
if ((cellsCol[1] != null) && (cellsCol[1] != ""))
{
columnass[1] = int.Parse(cellsCol[1]);
}
if ((columnass[0] != -1) && (columnass[1] != -1))
{
if (columnass[0] > columnass[1])
{
int exc = columnass[0];
columnass[0] = columnass[1];
columnass[1] = exc;
}
for (int b = columnass[0]; b <= columnass[1]; b++)
{
if (VariableCheck)
{
issuefounded = VarCompare((string)(oWS.Cells[fila.Row, int.Parse(cellsCol[0])] as Excel.Range).Value, owbb);
}
if ((issuefounded == false) && (Numericalcheck == true))
{
NumCompare((string)(oWS.Cells[fila.Row, b] as Excel.Range).Value, owbb);
}
}
}
else
{
if ((columnass[0] == -1) && (columnass[1] == -1))
{
System.Windows.Forms.MessageBox.Show("Please make sure that you fill \nin all the mandatory fields properly.");
}
else
{
if (columnass[0] == -1)
{
columnass[0] = columnass[1];
}
if (VariableCheck)
{
issuefounded = VarCompare((string)(oWS.Cells[fila.Row, int.Parse(cellsCol[0])] as Excel.Range).Value, owbb);
}
if ((issuefounded == false) && (Numericalcheck == true))
{
NumCompare((string)(oWS.Cells[fila.Row, columnass[0]] as Excel.Range).Value, owbb);
}
}
}
break;
default:
foreach (string cols in cellsCol)
{
if ((cols != null) && (cols != ""))
{
if (VariableCheck)
{
issuefounded = VarCompare((string)(oWS.Cells[fila.Row, int.Parse(cellsCol[0])] as Excel.Range).Value, owbb);
}
if ((issuefounded == false) && (Numericalcheck == true))
{
NumCompare((string)(oWS.Cells[fila.Row, int.Parse(cols)] as Excel.Range).Value, owbb);
}
}
}
break;
}
}
}
}
}
}
private static string SaveDataToSingleExcel(List<DataTable> dataTables, List<ExcelInfo> excelInfos, ExcelInfo dataTableInfo = null)
{
try
{
OpenExcel(excelInfos[0], false);
for (int n = 0; n < dataTables.Count; n++)
{
_objSheet = OpenSheet(excelInfos[n]);
if (_objSheet == null)
{
return "没有指定页签";
}
SaveDataToExcelImpl(dataTables[n], excelInfos[n], dataTableInfo);
}
SaveAs(excelInfos[0].FilePath);
}
catch (Exception err)
{
CloseExcel();
Console.WriteLine(err.Message);
return err.Message;
}
return "";
}
public CellGetter(Excel._Worksheet worksheet)
{
this.worksheet = worksheet;
}
private void Form1_Load(object sender, EventArgs e)
{
Excel.Application xlApp = new Excel.Application();
Excel.Workbook xlWorkbook = xlApp.Workbooks.Open(@"C:\Users\user\Downloads\monte-carlo\monte-carlo\monte-carlo\monte-carlo\bin\Debug\data1.xlsx");
Excel._Worksheet xlWorksheet = xlWorkbook.Sheets[1];
Excel.Range xlRange = xlWorksheet.UsedRange;
int k = 0;
for (int i = 2; ; i++)
{
for (int j = 1; j <= 2; j++)
{
//write the value to the console
if (xlRange.Cells[i, j] != null && xlRange.Cells[i, j].Value2 != null)
{
if (j % 2 == 1)
{
clist pnn = new clist();
pnn.val = Convert.ToDouble(xlRange.Cells[i, j].Value2);
tot += pnn.val;
pnn.cumulative = tot;
demand.Add(pnn);
dataGridView1.Rows.Add();
dataGridView1.Rows[k].Cells[0].Value = pnn.val;
}
else
{
clist pnn = new clist();
pnn.val = Convert.ToDouble(xlRange.Cells[i, j].Value2);
tot2 += pnn.val;
pnn.cumulative = tot2;
freqency.Add(pnn);
dataGridView1.Rows.Add();
dataGridView1.Rows[k].Cells[1].Value = pnn.val;
dataGridView1.Rows[k].Cells[3].Value = pnn.cumulative;
}
}
else
{
stop = true;
}
//add useful things here!
}
k++;
if (stop)
{
break;
}
}
tot = 0;
for (int i = 0; i < freqency.Count; i++)
{
clist pnn = new clist();
pnn.val = freqency[i].val / freqency[freqency.Count - 1].cumulative;
tot += pnn.val;
pnn.relativecumulative = tot;
relativefreq.Add(pnn);
dataGridView1.Rows[i].Cells[2].Value = pnn.val;
dataGridView1.Rows[i].Cells[4].Value = pnn.relativecumulative;
}
for (int i = 0; i < freqency.Count; i++)
{
if (i == 0)
{
freqency[i].range = 0;
freqency[i].rangeend = freqency[i].cumulative;
dataGridView1.Rows[i].Cells[5].Value = freqency[i].range + "-" + freqency[i].rangeend;
}
else
{
freqency[i].range = (freqency[i - 1].cumulative + 1);
freqency[i].rangeend = freqency[i].cumulative;
dataGridView1.Rows[i].Cells[5].Value = freqency[i].range + "-" + freqency[i].rangeend;
}
}
xlWorkbook.Close(true);
xlApp.Quit();
}
public string getStatusExcel(string fPath)
{
string strval = validation(fPath);
try
{
if (strval == "true")
{
addlog("Getting PR status....");
Excel.Application xlApp = new Excel.Application();
Excel.Workbook xlWorkbook = xlApp.Workbooks.Open(@fPath);
Excel._Worksheet xlWorksheet = xlWorkbook.Sheets[1];
Excel.Range xlRange = xlWorksheet.UsedRange;
VssConnection connection2 = new VssConnection(new Uri($"https://dynamicscrm.visualstudio.com"), new VssAadCredential());
var statsAPI = new GetStatusAPI(connection2);
int rowCount = xlRange.Rows.Count;
int colCount = xlRange.Columns.Count;
int cCnt = 1;
int csCnt = 2;
if (rowCount > 1)
{
for (int rCnt = 1; rCnt <= rowCount; rCnt++)
{
if (rCnt == 1)
{
addlog("PR Number# PR Status");
}
else
{
if (xlRange.Cells[rCnt, cCnt] != null && xlRange.Cells[rCnt, cCnt].Value2 != null)
{
string PrLink = xlRange.Cells[rCnt, cCnt].Value2.ToString();
strStatus = getStatus(PrLink.ToString(), statsAPI).Result.ToString();
xlWorksheet.Cells[rCnt, csCnt] = strStatus;
addlog(PRNum + " " + strStatus);
xlWorkbook.Save();
}
else
{
strStatus = "Invalid PRLink";
xlWorksheet.Cells[rCnt, csCnt] = strStatus;
xlWorkbook.Save();
addlog(strStatus);
}
}
}
xlWorkbook.Close();
strStatus = "The file in the same file path has been updated successfully";
}
else
{
xlWorkbook.Close();
strStatus = "No Data found in the given file";
}
}
else
{
addlog(strval);
}
}
catch (Exception ex)
{
strStatus = ex.Message;
clsStatus.WriteLog("getStatusExcel()-->" + ex.Message);
}
return(strStatus);
}
private void bwrkLoadVFDDefs_DoWork(object sender, DoWorkEventArgs e)
{
xlApp = new XL.Application();
xlWorkbook = xlApp.Workbooks.Open("C:\\Users\\sferry\\source\\repos\\V1000_Drive_Programmer\\V1000_Drive_Programmer\\data\\Parameter Defaults.dat");
//xlWorkbook = xlApp.Workbooks.Open("C:\\Users\\steve\\source\\repos\\V1000_Drive_Programmer\\V1000_Drive_Programmer\\data\\Parameter Defaults.dat");
xlWorksheet = xlWorkbook.Sheets[1];
xlRange = xlWorksheet.UsedRange;
V1000_xlRead_List.Clear();
ProgressArgs.VFDLoad_Total_Units = xlRange.Rows.Count - 1;
for (int i = 2; i <= xlRange.Rows.Count; i++)
{
if (bwrkLoadVFDDefs.CancellationPending)
{
e.Cancel = true;
bwrkLoadVFDDefs.ReportProgress(0);
return;
}
V1000_Param_Data ParamData = new V1000_Param_Data();
if (xlRange.Cells[i, 1] != null && xlRange.Cells[i, 1].Value2 != null)
{
ParamData.RegAddress = Convert.ToUInt16(xlRange.Cells[i, 1].Value2);
}
else
{
ParamData.RegAddress = 0;
}
if (xlRange.Cells[i, 2] != null && xlRange.Cells[i, 2].Value2 != null)
{
ParamData.ParamNum = xlRange.Cells[i, 2].Value2.ToString();
}
else
{
ParamData.ParamNum = "0";
}
if (xlRange.Cells[i, 3] != null && xlRange.Cells[i, 3].Value2 != null)
{
ParamData.ParamName = xlRange.Cells[i, 3].Value2.ToString();
}
else
{
ParamData.ParamName = "0";
}
if (xlRange.Cells[i, 4] != null && xlRange.Cells[i, 4].Value2 != null)
{
ParamData.DefVal = Convert.ToUInt16(xlRange.Cells[i, 4].Value2);
}
else
{
ParamData.DefVal = 0;
}
if (xlRange.Cells[i, 5] != null && xlRange.Cells[i, 5].Value2 != null)
{
ParamData.Multiplier = Convert.ToUInt16(xlRange.Cells[i, 5].Value2);
}
else
{
ParamData.Multiplier = 1;
}
if (xlRange.Cells[i, 6] != null && xlRange.Cells[i, 6].Value2 != null)
{
ParamData.NumBase = Convert.ToByte(xlRange.Cells[i, 6].Value2);
}
else
{
ParamData.NumBase = 10;
}
if (xlRange.Cells[i, 7] != null && xlRange.Cells[i, 7].Value2 != null)
{
ParamData.Units = xlRange.Cells[i, 7].Value2.ToString();
}
else
{
ParamData.Units = "";
}
// Create a string version for display purposes of the actual default paramater value
if (ParamData.NumBase == 16)
{
ParamData.DefValDisp = "0x" + ParamData.DefVal.ToString("X4");
}
else
{
ParamData.DefValDisp = ((float)ParamData.DefVal / ParamData.Multiplier).ToString() + " " + ParamData.Units;
}
VFD_Vals.Add(ParamData);
ProgressArgs.VFDLoad_Unit = i - 2;
ProgressArgs.VFDLoad_Progress = (byte)(((float)ProgressArgs.VFDLoad_Unit / ProgressArgs.VFDLoad_Total_Units) * 100);
bwrkLoadVFDDefs.ReportProgress((int)(((float)i / (xlRange.Rows.Count - 1)) * 100));
}
ProgressArgs.VFDLoad_Unit = ProgressArgs.VFDLoad_Total_Units - 1;
e.Result = 0x02;
bwrkLoadVFDDefs.ReportProgress(100);
}
public IEnumerable <Transaction> ParseFile(IFileService fileService)
{
int year = int.MinValue;
int period = int.MinValue;
using (var frmGetData = new frmRequestData())
{
if (frmGetData.ShowDialog() == DialogResult.Cancel)
{
return(null);
}
year = frmGetData.Year;
period = frmGetData.Period;
}
var transactions = new List <Transaction>();
var excelApp = new Excel.Application();
excelApp.Visible = false;
excelApp.Workbooks.Open(fileService.FileName);
Excel._Worksheet worksheet = excelApp.ActiveSheet;
var account = worksheet.Cells[1, "A"].Value2;
var ibalance = worksheet.Cells[1, "C"].Value2;
int currRow = 3;
do
{
var tdateTemp = worksheet.Cells[currRow, "C"].Value2;
var transactionDate = DateTime.FromOADate(tdateTemp);
var GLCode = worksheet.Cells[currRow, "B"].Value2;
decimal Debit = 0.0m;
decimal Credit = 0.0m;
var vendorName = string.Empty;
var checkNumber = string.Empty;
var sequence = worksheet.Cells[currRow, "F"].Value2;
if (GLCode == "AP-PY")
{
decimal.TryParse(worksheet.Cells[currRow, "G"].Value2.ToString(), out Debit);
decimal.TryParse(worksheet.Cells[currRow, "H"].Value2.ToString(), out Credit);
if (decimal.Equals(Debit, 0.0m))
{
vendorName = worksheet.Cells[currRow, "D"].Value2;
currRow += 1;
string temp = worksheet.Cells[currRow, "A"].Value2.ToString();
checkNumber = temp.Substring(0, temp.LastIndexOf("-")).TrimStart('0');
}
else
{
string temp = worksheet.Cells[currRow, "D"].Value2.ToString();
checkNumber = temp.Substring(0, temp.LastIndexOf("-")).TrimStart('0');
currRow += 1;
vendorName = worksheet.Cells[currRow, "A"].Value2;
}
}
if (GLCode == "GL-JE" || GLCode == "AP-IN")
{
decimal.TryParse(worksheet.Cells[currRow, "G"].Value2.ToString(), out Debit);
decimal.TryParse(worksheet.Cells[currRow, "H"].Value2.ToString(), out Credit);
vendorName = worksheet.Cells[currRow, "D"].Value2;
if (GLCode == "AP-IN")
{
var tempVendorName = worksheet.Cells[currRow + 1, "A"].Value2;
vendorName = $"{(tempVendorName == null ? string.Empty : $"{tempVendorName}:")}{vendorName}";
}
if (worksheet.Cells[currRow + 1, "C"].Value2 == null)
{
currRow += 1;
}
}
transactions.Add(new Transaction()
{
TransactionDate = transactionDate.ToString("M/d/yyyy"),
Period = period.ToString(),
Year = year.ToString(),
Acconut = account.ToString(),
IBalance = ibalance.ToString(),
GLCode = GLCode.ToString(),
VendorName = vendorName == null ? "" : vendorName.ToString(),
CheckNumber = checkNumber,
Credit = Credit.ToString(),
DCredit = Credit,
Debit = Debit.ToString(),
DDebit = Debit,
Sequence = sequence
});
currRow += 1;
} while (!worksheet.Cells[currRow, "A"].Value2.ToString().StartsWith("Net Change"));
public static List <Question> ReadFile(string excelFile)
{
int headersRow = 2;
List <Question> qList = new List <Question>();
// open excel file
Excel.Application excel = new Excel.Application();
Excel.Workbook workbook = excel.Workbooks.Open(excelFile);
Excel._Worksheet worksheet = workbook.Sheets[1];
int rowCount = worksheet.UsedRange.Rows.Count;
int colCount = worksheet.UsedRange.Columns.Count;
// find data indexes
//main.ShowOutput("Finding data indexes....", false);
int questionTextIndex = 0;
int nameIndex = 0;
int categoryIndex = 0;
int answerIndex = 0;
int imageIndex = 0;
int[] optionIndexes = new int[] { 0, 0, 0, 0 };
for (int col = 1; col <= colCount; col++)
{
string v = worksheet.Cells[headersRow, col].Value.ToString();
if (v == "Question Stem")
{
questionTextIndex = col;
}
if (v == "Syllabus")
{
categoryIndex = col;
}
if (v == "Correct Answer")
{
answerIndex = col;
}
if (v == "Question ID")
{
nameIndex = col;
}
if (v == "Annex")
{
imageIndex = col;
}
string[] answerFields = new string[] { "A", "B", "C", "D" };
for (int i = 0; i < 4; i++)
{
if (v == answerFields[i])
{
optionIndexes[i] = col;
}
}
}
// check data indexes
if (questionTextIndex > 0 &&
optionIndexes[0] > 0 &&
optionIndexes[1] > 0 &&
optionIndexes[2] > 0 &&
optionIndexes[3] > 0 &&
answerIndex > 0 &&
imageIndex > 0 &&
categoryIndex > 0 &&
nameIndex > 0)
{
//main.ShowOutput("OK");
}
else
{
Main.myThis.ShowOutput("INDEX ERROR!!");
if (questionTextIndex == 0)
{
Main.myThis.ShowOutput("questionTextIndex not found. Should be\"Question Stem\"");
}
if (optionIndexes[0] == 0)
{
Main.myThis.ShowOutput("optionIndexes(A) not found. Should be\"A\"");
}
if (optionIndexes[0] == 0)
{
Main.myThis.ShowOutput("optionIndexes(B) not found. Should be\"B\"");
}
if (optionIndexes[0] == 0)
{
Main.myThis.ShowOutput("optionIndexes(C) not found. Should be\"C\"");
}
if (optionIndexes[0] == 0)
{
Main.myThis.ShowOutput("optionIndexes(D) not found. Should be\"D\"");
}
if (answerIndex == 0)
{
Main.myThis.ShowOutput("answerIndex not found. Should be\"Correct Answer\"");
}
if (imageIndex == 0)
{
Main.myThis.ShowOutput("imageIndex not found. Should be\"Annex\"");
}
if (categoryIndex == 0)
{
Main.myThis.ShowOutput("categoryIndex not found. Should be\"Syllabus\"");
}
if (nameIndex == 0)
{
Main.myThis.ShowOutput("nameIndex not found. Should be\"Question ID\"");
}
workbook.Close();
excel.Quit();
return(qList);
}
// get question data
for (int row = (headersRow + 1); row <= rowCount; row++)
{
Question q = new Question();
q.name = JwString.Clean(worksheet.Cells[row, nameIndex].Value.ToString());
q.category = CleanCategory(JwString.Clean(worksheet.Cells[row, categoryIndex].Value.ToString()));
q.text = JwString.Clean(worksheet.Cells[row, questionTextIndex].Value.ToString());
if (worksheet.Cells[row, imageIndex].Value != null)
{
string imageName = CleanImage(JwString.Clean(worksheet.Cells[row, imageIndex].Value.ToString()));
q.AddImage(imageName, "");
}
q.type = "multichoice";
string answer = worksheet.Cells[row, answerIndex].Value.Trim();
string[] answerValues = new string[] { "A", "B", "C", "D" };
for (int i = 1; i <= 4; i++)
{
Option o = new Option();
o.text = JwString.Clean(worksheet.Cells[row, optionIndexes[i - 1]].Value.ToString());
o.feedback = "";
o.grade = 0;
if (answer == answerValues[i - 1])
{
o.grade = 100;
}
q.options.Add(o);
}
qList.Add(q);
}
// close excel and return questions
workbook.Close();
excel.Quit();
return(qList);
}
public DataTable getExcelFile(string fileLocation)
{
DataTable dt = new DataTable();
DataColumn clID = new DataColumn("serial", typeof(string));
dt.Columns.Add(clID);
//Create COM Objects. Create a COM object for everything that is referenced
Excel.Application xlApp = new Excel.Application();
Excel.Workbook xlWorkbook = xlApp.Workbooks.Open(fileLocation);
Excel._Worksheet xlWorksheet = xlWorkbook.Sheets[1];
Excel.Range xlRange = xlWorksheet.UsedRange;
int rowCount = xlRange.Rows.Count;
int colCount = xlRange.Columns.Count;
//iterate over the rows and columns and print to the console as it appears in the file
//excel is not zero based!!
for (int i = 1; i <= rowCount; i++)
{
int progree = i * 100 / rowCount;
List <string> lt = new List <string>();
for (int j = 1; j <= colCount; j++)
{
//new line
if (j == 1)
{
if (i != 1)
{
lt.Add((i - 1).ToString());
}
continue;
}
var data = xlRange.Cells[i, j].Value2 == null? "": xlRange.Cells[i, j].Value2.ToString();
if (i == 1)
{
string whiteSapceRemove = Regex.Replace(data, @"[^0-9a-zA-Z]+", "");
DataColumn cl = new DataColumn(whiteSapceRemove, typeof(string));
dt.Columns.Add(cl);
}
else
{
lt.Add(data);
}
}
if (lt.Count > 0)
{
dt.Rows.Add(lt.ToArray());
//dt.Rows.Add("1", "2", "3", "4");
}
toolStripProgressBar.Value = progree;
}
//cleanup
GC.Collect();
GC.WaitForPendingFinalizers();
//rule of thumb for releasing com objects:
// never use two dots, all COM objects must be referenced and released individually
// ex: [somthing].[something].[something] is bad
//release com objects to fully kill excel process from running in the background
Marshal.ReleaseComObject(xlRange);
Marshal.ReleaseComObject(xlWorksheet);
//close and release
xlWorkbook.Close();
Marshal.ReleaseComObject(xlWorkbook);
//quit and release
xlApp.Quit();
Marshal.ReleaseComObject(xlApp);
return(dt);
}
public void ReadData()
{
// Setting the excel application to call it
Excel.Application xlApp = new Excel.Application();
// Which excel the code shell read
Excel.Workbook xlWb = xlApp.Workbooks.Open(@"C:\Users\kiels00j\Desktop\test.xlsx");
// Which sheets the code shall read from (in excel 1 is the first one and not 0 as in coding)
Excel._Worksheet xlWs = xlWb.Sheets[1];
// Getting all the data contain in the predefine setting above
Excel.Range xlR = xlWs.UsedRange;
// Getting how many Rows and Cloumns the excel file contain to the for loop
int rowCount = xlR.Rows.Count;
int colCount = xlR.Columns.Count;
// Making a list to contain all the data from the excel file
List <Data_Used_list> dul = new List <Data_Used_list>();
// Going / loop through every row there is, if there is headers or the data is
// not starting on line 1 set it to the line nr where it is
for (int i = 1; i <= rowCount; i++)
{
// Use this if just want to read every single colums and combine them in string row
//for (int j = 1; j <= colCount; j++)
//{
// Console.Write(xlR.Cells[i, j].Value2.ToString() + "\t");
//}
//Console.WriteLine();
// Here I add data to the list and know which space the headers are in
// notice that it start with 1 and not 0, this is because in excel 1 is the first and not
// 0 as in coding
dul.Add(new Data_Used_list
{
Phone_nr = Convert.ToString(xlR.Cells[i, 1].Value2),
Abo = Convert.ToString(xlR.Cells[i, 2].Value2),
GID = Convert.ToString(xlR.Cells[i, 3].Value2),
DataUsed = Convert.ToString(xlR.Cells[i, 4].Value2)
});
}
// This is only to confirm and write out the list that
foreach (Data_Used_list item in dul)
{
Console.WriteLine("Nr: " + item.Phone_nr + " - GID: " + item.GID + " - Data: " + item.DataUsed);
}
// Closing down the use off the excel file so the code/software do not lock it
GC.Collect();
GC.WaitForPendingFinalizers();
Marshal.ReleaseComObject(xlR);
Marshal.ReleaseComObject(xlWs);
xlWb.Close();
Marshal.ReleaseComObject(xlWb);
xlApp.Quit();
Marshal.ReleaseComObject(xlApp);
}
private void ImportData()
{
string ERPInDirectory = Convert.ToString(ConfigurationManager.AppSettings["ERPIN"].ToString().Trim());
if (!Directory.Exists(ERPInDirectory))
{
log.Info("ERPIN路径不存在。");
return;
}
string ExcelPath = ERPInDirectory;
List <FileInfo> ExcelFiles = getFiles(ExcelPath, ".xls");
if (ExcelFiles.Count == 0)
{
log.Info("没有检测到Excel文件。");
return;
}
foreach (FileInfo fileInfo in ExcelFiles)
{
try
{
string FilePath = fileInfo.FullName;
log.Info("正在处理：" + FilePath);
MSExcel.Application excelApp = new MSExcel.Application
{
Visible = false//是打开可见
};
MSExcel.Workbooks _workbooks = excelApp.Workbooks;
MSExcel._Workbook _workbook = _workbooks.Add(FilePath);
MSExcel._Worksheet whs = _workbook.Sheets[1];//获取第2张工作表 TODO：生产环境改回1
whs.Activate();
JArray ModelArray = new JArray();
JObject ModelJson = new JObject();
JArray FOrderEntrys = new JArray();
List <string> ordenoList = new List <string>();
int excelIndex = 2;
int BillNoLineNumber = 0; //表头单据编号所在行数
bool IsAllSuccess = true; //是否整个文件导入成功
bool HasSuccess = false; //是否整个文件有导入成功的订单
bool HasError = false; //是否整个文件有导入失败的订单
while (true)
{
try
{
MSExcel.Range rang = (MSExcel.Range)whs.Cells[excelIndex, 32];//ERP收料通知单单号
if (rang.Value != null)
{
string ERPBillNo = Convert.ToString(rang.Value);
string PrevFBillNo = Convert.ToString(((MSExcel.Range)whs.Cells[excelIndex - 1, 32]).Value); //上一行的ERP收料通知单单号
string NextFBillNo = Convert.ToString(((MSExcel.Range)whs.Cells[excelIndex + 1, 32]).Value); //下一行的ERP收料通知单单号
string QISBillNo = Convert.ToString(((MSExcel.Range)whs.Cells[excelIndex, 1]).Value); //QIS检验单号
string FDate = Convert.ToString(((MSExcel.Range)whs.Cells[excelIndex, 2]).Value);
string MaterialNumber = Convert.ToString(((MSExcel.Range)whs.Cells[excelIndex, 7]).Value);
string ComputerResult = Convert.ToString(((MSExcel.Range)whs.Cells[excelIndex, 15]).Value); //电脑判定
double CheckQty = Convert.ToDouble(((MSExcel.Range)whs.Cells[excelIndex, 14]).Value); //批量数（报检数量）
double RealQty = Convert.ToDouble(((MSExcel.Range)whs.Cells[excelIndex, 22]).Value); //实收数量（合格数量）
string CheckResult = Convert.ToString(((MSExcel.Range)whs.Cells[excelIndex, 16]).Value); //检验结果
double unqualifiedQty = CheckQty - RealQty; //不合格数量
//ERP收料通知单单号和上一行不同，则是另一张单据
if (!PrevFBillNo.Equals(ERPBillNo))
{
BillNoLineNumber = excelIndex;
log.Info("正在读取：" + ERPBillNo);
}
string NeedPushEntryIds = "";
//excel的一行是相同物料合并数量的，在ERP查源单，查出来可能有多行物料
List <List <object> > Bills = PUR_ReceiveBill.GetAllBill(client, "FBillNo='" + ERPBillNo + "'" + " and FMaterialID.FNumber='" + MaterialNumber + "'");
for (int i = 0; i < Bills.Count; i++)
{
double FQty = 0;
string SrcBillID = Convert.ToString(Bills[i][0]);
string SrcBillNo = Convert.ToString(Bills[i][1]);
string SrcEntryID = Convert.ToString(Bills[i][8]);
double FActReceiveQty = Convert.ToDouble(Bills[i][7]); //交货数量
double FCheckJoinQty = Convert.ToDouble(Bills[i][9]); //检验关联数量
double NoCheckQty = FActReceiveQty - FCheckJoinQty; //剩余未检验数量
string SrcBillTypeID = Convert.ToString(Bills[i][10]);
string SrcSeq = Convert.ToString(Bills[i][11]);
if (NoCheckQty > CheckQty)
{
FQty = CheckQty;
}
else
{
FQty = NoCheckQty;
}
CheckQty = CheckQty - FQty;
if (FQty <= 0)
{
continue;
}
NeedPushEntryIds = NeedPushEntryIds + SrcEntryID + ",";
//一行下推成一单
string NewBillId = PUR_ReceiveBill.PushToInspectBill(client, SrcEntryID);
if (!"".Equals(NewBillId))
{
//修改数量
List <List <object> > InspectBills = QM_InspectBill.GetAllBill(client, "FID=" + NewBillId);
if (InspectBills.Count > 0)
{
string NewBillNo = Convert.ToString(InspectBills[0][1]);
ordenoList.Add(NewBillNo);
JArray MultiLanguageTextArr = new JArray();
JObject MultiLanguageTextJson = new JObject();
MultiLanguageTextJson.Add("Key", 2052);
MultiLanguageTextJson.Add("Value", "QIS导入，单号" + QISBillNo);
JObject Entry = new JObject();
ModelJson.Add("FID", Convert.ToString(InspectBills[0][0]));
ModelJson.Add("FEntity", FOrderEntrys);
ModelJson.Add("FDescription", "QIS导入，单号" + QISBillNo);
FOrderEntrys.Add(Entry);
Entry.Add("FEntryID", Convert.ToString(InspectBills[0][2]));
Entry.Add("FInspectQty", FQty);
Entry.Add("FMemo", "QIS导入，单号" + QISBillNo);
if (!"合格".Equals(ComputerResult))
{
Entry.Add("FInspectResult", "2"); //检验结果=不合格
JArray FPolicyDetailEntrys = new JArray(); //使用决策 子单据体
JObject FPolicyDetailEntry1 = new JObject();
JObject FPolicyDetailEntry2 = new JObject();
switch (CheckResult)
{
case "退货":
break;
case "让步接收":
Entry.Add("FPolicyDetail", FPolicyDetailEntrys);
FPolicyDetailEntrys.Add(FPolicyDetailEntry1);
FPolicyDetailEntry1.Add("FUsePolicy", "A");
FPolicyDetailEntry1.Add("FPolicyQty", RealQty);
FPolicyDetailEntrys.Add(FPolicyDetailEntry2);
FPolicyDetailEntry2.Add("FUsePolicy", "B");
FPolicyDetailEntry2.Add("FPolicyQty", unqualifiedQty);
break;
case "挑选":
Entry.Add("FPolicyDetail", FPolicyDetailEntrys);
FPolicyDetailEntrys.Add(FPolicyDetailEntry1);
FPolicyDetailEntry1.Add("FUsePolicy", "A");
FPolicyDetailEntry1.Add("FPolicyQty", RealQty);
FPolicyDetailEntrys.Add(FPolicyDetailEntry2);
FPolicyDetailEntry2.Add("FUsePolicy", "E");
FPolicyDetailEntry2.Add("FPolicyQty", unqualifiedQty);
break;
}
}
else if ("合格".Equals(ComputerResult) && "内部原因".Equals(CheckResult))
{
JArray FPolicyDetailEntrys = new JArray();//使用决策 子单据体
JObject FPolicyDetailEntry1 = new JObject();
Entry.Add("FPolicyDetail", FPolicyDetailEntrys);
FPolicyDetailEntrys.Add(FPolicyDetailEntry1);
FPolicyDetailEntry1.Add("FDetailID", Convert.ToString(InspectBills[0][3]));
FPolicyDetailEntry1.Add("FMemo1", "内部原因");
}
Dictionary <string, object> ImportResult = QM_InspectBill.SaveBill(client, new StringBuilder(ModelJson.ToString()), new List <string>(ordenoList));
//提交审核检验单
CommonOperate commonOperate = new CommonOperate();
commonOperate.SubmitBill(client, "QM_InspectBill", new StringBuilder("\"" + NewBillNo + "\""));
commonOperate.AuditBill(client, "QM_InspectBill", new StringBuilder("\"" + NewBillNo + "\""));
ModelJson = new JObject();
FOrderEntrys = new JArray();
if (!Convert.ToBoolean(ImportResult["IsSuccess"]))
{
HasError = true;
IsAllSuccess = false;
}
else
{
HasSuccess = true;
}
}
else
{
log.Info("下推的单据已被删除。");
}
}
}
//移除掉最后一个","
if (NeedPushEntryIds.Length > 0)
{
NeedPushEntryIds = NeedPushEntryIds.Remove(NeedPushEntryIds.LastIndexOf(","), 1);
}
}
else
{
break;
}
}
catch (Exception ex)
{
log.Error(ex);
}
finally
{
excelIndex++;
}
}
string ERPBackup = Convert.ToString(ConfigurationManager.AppSettings["ERPBackup"].ToString().Trim());
string ResultDirectory = ERPBackup; //处理完后文件保存到这里
if (!Directory.Exists(ResultDirectory)) //如果不存在就创建文件夹
{
Directory.CreateDirectory(ResultDirectory);
}
whs.SaveAs(Path.Combine(ResultDirectory, fileInfo.Name), 51);
//关闭对象
Marshal.ReleaseComObject(_workbook);
Marshal.ReleaseComObject(whs);
excelApp.Quit();
GC.Collect();
fileInfo.Delete();
}
catch (Exception ex)
{
log.Error(ex);
}
}
Executed = true;
}
private void outPutReciveBill()
{
List <List <object> > Bills = PUR_ReceiveBill.GetAllBill(client, "FDocumentStatus = 'C' and FCheckInComing = 1 and F_PAEZ_Exported=0");//F_PAEZ_Exported=0 未导出
//List<List<object>> Bills = PUR_ReceiveBill.GetAllBill(client, "FBillNo='CGSL000328'");//F_PAEZ_Exported=0 未导出
if (Bills.Count == 0)
{
log.Info("没有可以导出的收料通知单。");
return;
}
MSExcel.Application excelApp = new MSExcel.Application
{
Visible = false//是打开可见
};
MSExcel.Workbooks _workbooks = excelApp.Workbooks;
//MSExcel._Workbook _workbook = _workbooks.Add(AppDomain.CurrentDomain.BaseDirectory+"\\DD_20200901084512--ERP输出模板.XLS");
MSExcel._Workbook _workbook = _workbooks.Add(System.Reflection.Missing.Value);
MSExcel._Worksheet whs = _workbook.Sheets[1];//获取第1张工作表
try
{
whs.Name = "Sheet1";
//headline
whs.Cells[1, 1] = "进货单号";
whs.Cells[1, 2] = "产品编号";
whs.Cells[1, 3] = "物料名称";
whs.Cells[1, 4] = "物料规格";
whs.Cells[1, 5] = "厂商";
whs.Cells[1, 6] = "报检数量";
whs.Cells[1, 7] = "厂商编码";
whs.Cells[1, 8] = "物料分类名称";
whs.Cells[1, 9] = "实收数量";
whs.Cells[1, 10] = "不良数";
whs.Cells[1, 11] = "QIS报检单号";
//同一单据内，相同物料合并数量
Hashtable AllMaterialHT = new Hashtable();
for (int i = 0; i < Bills.Count; i++)
{
string BillNo = Convert.ToString(Bills[i][1]);
string MaterialNumber = Convert.ToString(Bills[i][2]);
double Qty = Convert.ToDouble(Bills[i][7]);
if (AllMaterialHT.ContainsKey(BillNo + MaterialNumber))
{
JObject jObject = AllMaterialHT[BillNo + MaterialNumber] as JObject;
double OldQty = Convert.ToDouble(Convert.ToString(jObject["Qty"]));
jObject["Qty"] = OldQty + Qty;
}
else
{
List <object> OtherInfo = Bills[i];
JObject jObject = new JObject();
jObject.Add("MaterialNumber", MaterialNumber);
jObject.Add("Qty", Qty);
jObject.Add("OtherInfoIndex", i); //原来的List的索引，保存以便在下方获取原来的其他数据
AllMaterialHT.Add(BillNo + MaterialNumber, jObject);
}
}
string DateTimeStr = DateTime.Now.ToString("yyyyMMddHHmmssf");
Dictionary <string, string> BillIDsDic = new Dictionary <string, string>();
StringBuilder sbBillNos = new StringBuilder();
//for (int i = 0; i < Bills.Count; i++)
int ExcelRow = 2;
foreach (string BillAndMaterialNumber in AllMaterialHT.Keys)
{
JObject jObject = AllMaterialHT[BillAndMaterialNumber] as JObject;
string MaterialNumber = Convert.ToString(jObject["MaterialNumber"]);
double Qty = Convert.ToDouble(Convert.ToString(jObject["Qty"]));
int i = Convert.ToInt32(Convert.ToString(jObject["OtherInfoIndex"]));
List <object> BillInfo = Bills[i];
string BillID = Convert.ToString(Bills[i][0]);
string BillNo = Convert.ToString(Bills[i][1]);
string FDetailEntity_FSeq = Convert.ToString(Bills[i][11]);
if (!BillIDsDic.ContainsKey(BillID))
{
BillIDsDic.Add(BillID, BillNo);
sbBillNos.Append("\"").Append(BillNo).Append("\",");
}
whs.Cells[ExcelRow, 1] = BillNo;
whs.Cells[ExcelRow, 2] = Bills[i][2];
whs.Cells[ExcelRow, 3] = Bills[i][3];
whs.Cells[ExcelRow, 4] = Bills[i][4];
whs.Cells[ExcelRow, 5] = Bills[i][6];
whs.Cells[ExcelRow, 6] = Qty;
whs.Cells[ExcelRow, 7] = Bills[i][5];
whs.Cells[ExcelRow, 8] = Bills[i][12];
whs.Cells[ExcelRow, 9] = 0;
whs.Cells[ExcelRow, 10] = 0;
whs.Cells[ExcelRow, 11] = "'" + DateTimeStr + (i + 1).ToString("000");
ExcelRow++;
}
if (sbBillNos.Length > 0)
{
sbBillNos.Remove(sbBillNos.Length - 1, 1);; //移除掉最后一个","
}
string ERPOUTDirectory = Convert.ToString(ConfigurationManager.AppSettings["ERPOUT"].ToString().Trim());
if (!Directory.Exists(ERPOUTDirectory))//如果不存在就创建文件夹
{
Directory.CreateDirectory(ERPOUTDirectory);
}
string fileName = ERPOUTDirectory + "K3Cloud_DD_" + DateTimeStr + ".XLS";
whs.SaveAs(fileName, 51);
log.Info("正在更新该订单的同步状态。");
PUR_ReceiveBill.ExportedToQIS(client, sbBillNos);
//UpdateSyncStatus("PUR_ReceiveBill", BillIDsDic);
}
catch (Exception ex)
{
log.Error(ex);
}
finally {
//关闭对象
Marshal.ReleaseComObject(_workbook);
Marshal.ReleaseComObject(whs);
excelApp.Quit();
GC.Collect();
}
}
static void DisplayInExcelFull(IEnumerable <Account> accounts)
{
var excelApp = new Excel.Application();
// Make the object visible.
excelApp.Visible = true;
// Create a new, empty workbook and add it to the collection returned
// by property Workbooks. The new workbook becomes the active workbook.
// Add has an optional parameter for specifying a particular template.
// Because no argument is sent in this example, Add creates a new workbook.
excelApp.Workbooks.Add();
// This example uses a single workSheet.
Excel._Worksheet workSheet = (Excel.Worksheet)excelApp.ActiveSheet;
//<Snippet5>
// Establish column headings in cells A1 and B1.
workSheet.Cells[1, "A"] = "ID Number";
workSheet.Cells[1, "B"] = "Current Balance";
//</Snippet5>
//<Snippet6>
// Establish column headings. Both parameters must be supplied in
// Visual C# 2008 or earlier versions.
//workSheet.get_Range("A1", Type.Missing).Value2 = "ID Number";
//workSheet.get_Range("B1", Type.Missing).Value2 = "Current Balance";
// Move to the next row.
//workSheet.get_Range("A2", Type.Missing).Select();
//</Snippet6>
//<Snippet7>
var row = 1;
foreach (var acct in accounts)
{
row++;
workSheet.Cells[row, "A"] = acct.ID;
workSheet.Cells[row, "B"] = acct.Balance;
}
//</Snippet7>
// Columns returns a Range object that represents all the columns
// on the active worksheet. AutoFit fits the width of the columns
// to their content.
//ExcelApp.Columns.AutoFit();
//<Snippet13>
workSheet.Columns[1].AutoFit();
workSheet.Columns[2].AutoFit();
//</Snippet13>
//<Snippet14>
((Excel.Range)workSheet.Columns[1]).AutoFit();
((Excel.Range)workSheet.Columns[2]).AutoFit();
//</Snippet14>
//<Snippet15>
// Call to AutoFormat in Visual C# 2010.
workSheet.Range["A1", "B3"].AutoFormat(
Excel.XlRangeAutoFormat.xlRangeAutoFormatClassic2);
//</Snippet15>
//<Snippet16>
// Call to AutoFormat in Visual C# 2010.
workSheet.Range["A1", "B3"].AutoFormat(Format:
Excel.XlRangeAutoFormat.xlRangeAutoFormatClassic2);
//</Snippet16>
//<Snippet12>
// Put the spreadsheet contents on the clipboard. The Copy method has one
// optional parameter for specifying a destination. Because no argument
// is sent, the destination is the Clipboard.
workSheet.Range["A1:B3"].Copy();
//</Snippet12>
//ExcelApp.get_Range("A5:C6").PasteSpecial( Transpose: true);
//dynamic val = Type.Missing;
//ExcelApp.get_Range("A5:C6").PasteSpecial(val, val, val, Transpose: true);
//ExcelApp.get_Range("A5:C6").Copy();
}
private void WriteDataToExcel(IDictionary <string, IList <UserInvestIncomeEntity> > reportData, int deptId, string templateFilePath, string destinyFilePath)
{
if (reportData == null)
{
throw new NullReferenceException(nameof(reportData));
}
Excel.Application excelApp = new Excel.Application();
Excel.Workbook workbook = excelApp.Workbooks.Open(templateFilePath);
try
{
foreach (var item in reportData)
{
var investorName = item.Key;
Excel._Worksheet worksheet = null;
foreach (Excel.Worksheet sheet in workbook.Sheets)
{
if (sheet.Name == investorName)
{
worksheet = sheet;
}
}
if (worksheet == null)
{
continue;
}
WorksheetFormatting(worksheet);
var startRowIndex = 34;
var data = item.Value;
for (int i = 0; i < data.Count; i++)
{
var investInfo = data[i];
if (deptId != (int)EnumLibrary.AccountingDepartment.Day)
{
//周一（万元）
worksheet.Cells[startRowIndex + i, 3] = investInfo.MondayPositionValue / (int)EnumLibrary.NumericUnit.TenThousand;
//净资产（万元）
worksheet.Cells[startRowIndex + i, 4] = investInfo.CurrentAsset / (int)EnumLibrary.NumericUnit.TenThousand;
//持仓市值（万元）
worksheet.Cells[startRowIndex + i, 9] = investInfo.PositionValue / (int)EnumLibrary.NumericUnit.TenThousand;
//持仓仓位
worksheet.Cells[startRowIndex + i, 12] = investInfo.PositionRate;
}
//日期
worksheet.Cells[startRowIndex + i, 2] = investInfo.TradeTime;
//本年累计收益额（万元）
worksheet.Cells[startRowIndex + i, 5] = investInfo.AnnualActualProfit / (int)EnumLibrary.NumericUnit.TenThousand;
//当日收益率
worksheet.Cells[startRowIndex + i, 6] = investInfo.CurrentIncomeRate;
//日收益额（万元）
worksheet.Cells[startRowIndex + i, 7] = investInfo.CurrentActualProfit / (int)EnumLibrary.NumericUnit.TenThousand;
//本年累计收益率
worksheet.Cells[startRowIndex + i, 8] = investInfo.AnnualIncomeRate;
}
}
workbook.SaveAs(destinyFilePath, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Excel.XlSaveAsAccessMode.xlNoChange, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value);
}
catch (Exception ex)
{
throw ex;
}
finally
{
workbook = null;
excelApp.Quit();
excelApp = null;
}
}
public void getExcelFile()
{
while (!comboBoxSelectedIndexChanged)
{
this.Dispatcher.Invoke(() => { tbAction.Text = "Choose a Test Type!"; });
Thread.Sleep(100);
}
this.Dispatcher.Invoke(() => { tbAction.Text = string.Empty; });
Excel.Application xlApp = new Excel.Application();
Excel.Workbook xlWorkbook = xlApp.Workbooks.Open(@"C:\Users\lazlo\Desktop\smokeTest.xlsx");
Excel._Worksheet xlWorksheet = xlWorkbook.Sheets[1];
Microsoft.Office.Interop.Excel.Range xlRange = xlWorksheet.UsedRange;
int rowCount = xlRange.Rows.Count;
int colCount = xlRange.Columns.Count;
int row = 3;
if (xlRange.Cells[2, 1] != null && xlRange.Cells[2, 1].Value2 != null)
{
this.Dispatcher.Invoke(() =>
{
tbAction.Text = (xlRange.Cells[2, 1].Value2.ToString());
});
}
if (xlRange.Cells[2, 2] != null && xlRange.Cells[2, 2].Value2 != null)
{
this.Dispatcher.Invoke(() =>
{
tbExpected.Text = (xlRange.Cells[2, 2].Value2.ToString());
});
}
while (!ButtonPressed)
{
Thread.Sleep(100);
}
ButtonPressed = false;
while (row <= rowCount)
{
for (int col = 1; col <= colCount; col++)
{
switch (col)
{
case 1:
if (xlRange.Cells[row, col] != null && xlRange.Cells[row, col].Value2 != null)
{
this.Dispatcher.Invoke(() =>
{
tbAction.Text = (xlRange.Cells[row, col].Value2.ToString());
});
}
break;
case 2:
if (xlRange.Cells[row, col] != null && xlRange.Cells[row, col].Value2 != null)
{
this.Dispatcher.Invoke(() =>
{
tbExpected.Text = (xlRange.Cells[row, col].Value2.ToString());
});
}
break;
default:
break;
}
}
while (!ButtonPressed)
{
Thread.Sleep(100);
}
ButtonPressed = false;
row++;
}
GC.Collect();
GC.WaitForPendingFinalizers();
Marshal.ReleaseComObject(xlRange);
Marshal.ReleaseComObject(xlWorksheet);
xlWorkbook.Close();
Marshal.ReleaseComObject(xlWorkbook);
xlApp.Quit();
Marshal.ReleaseComObject(xlApp);
}
private void bwrkReadExcelFile_DoWork(object sender, DoWorkEventArgs e)
{
xlApp = new XL.Application();
xlWorkbook = xlApp.Workbooks.Open("C:\\Users\\sferry\\source\\repos\\V1000_Param_Prog\\V1000_Param_Prog\\data\\Parameter List.xlsx");
xlWorksheet = xlWorkbook.Sheets[1];
xlRange = xlWorksheet.UsedRange;
V1000_xlRead_List.Clear();
ProgressArgs.xlRead_Total_Units = xlRange.Rows.Count - 1;
//for (int i = 2; i <= 21; i++)
for (int i = 2; i <= xlRange.Rows.Count; i++)
{
ProgressArgs.xlRead_Unit = i - 1;
ProgressArgs.xlRead_Progress = (byte)(((float)ProgressArgs.xlRead_Unit / ProgressArgs.xlRead_Total_Units) * 100);
bwrkReadExcelFile.ReportProgress((int)i);
if (bwrkReadExcelFile.CancellationPending)
{
e.Cancel = true;
ProgressArgs.xlRead_Stat = 0x03;
bwrkReadExcelFile.ReportProgress(0);
return;
}
V1000_File_Data ParamData = new V1000_File_Data();
if (xlRange.Cells[i, 1] != null && xlRange.Cells[i, 1].Value2 != null)
{
ParamData.RegAddress = xlRange.Cells[i, 1].Value2.ToString();
}
else
{
ParamData.RegAddress = "0";
}
if (xlRange.Cells[i, 2] != null && xlRange.Cells[i, 2].Value2 != null)
{
ParamData.ParamNum = xlRange.Cells[i, 2].Value2.ToString();
}
else
{
ParamData.ParamNum = "0";
}
if (xlRange.Cells[i, 3] != null && xlRange.Cells[i, 3].Value2 != null)
{
ParamData.ParamName = xlRange.Cells[i, 3].Value2.ToString();
}
else
{
ParamData.ParamName = "0";
}
if (xlRange.Cells[i, 4] != null && xlRange.Cells[i, 4].Value2 != null)
{
ParamData.DefVal = xlRange.Cells[i, 4].Value2.ToString();
}
else
{
ParamData.DefVal = "0";
}
if (xlRange.Cells[i, 5] != null && xlRange.Cells[i, 5].Value2 != null)
{
ParamData.Multiplier = xlRange.Cells[i, 5].Value2.ToString();
}
else
{
ParamData.Multiplier = "1";
}
V1000_xlRead_List.Add(ParamData);
}
ProgressArgs.xlRead_Progress = 100;
ProgressArgs.xlRead_Stat = 0x02;
e.Result = ProgressArgs.xlRead_Stat;
bwrkReadExcelFile.ReportProgress(100);
}
public static void getExcelFile()
{
Excel.Application xlApp = new Excel.Application();
Excel.Workbook xlWorkbook = xlApp.Workbooks.Open(@"E:\Json\TEST-DATA.xlsx");
Excel._Worksheet xlWorksheet = xlWorkbook.Sheets[1];
Excel.Range xlRange = xlWorksheet.UsedRange;
int rowCount = xlRange.Rows.Count;
int colCount = xlRange.Columns.Count;
for (int i = 2; i <= rowCount; i++)
{
string fullName = "null";
string address1 = "null";
string address2 = "null";
string city = "null";
string stateCode = "null";
string zipCode = "null";
string countryName = "null";
string productName = "null";
string productType = "null";
string productColor = "null";
string productSize = "null";
string quantity = "null";
string productID = "null";
if (xlRange.Cells[i, 1].Value2 != null)
{
fullName = xlRange.Cells[i, 1].Value2.ToString().Replace("'", @"\'");
}
if (xlRange.Cells[i, 2].Value2 != null)
{
address1 = xlRange.Cells[i, 2].Value2.ToString().Replace("'", @"\'");
}
if (xlRange.Cells[i, 3].Value2 != null)
{
address2 = xlRange.Cells[i, 3].Value2.ToString().Replace("'", @"\'");
}
if (xlRange.Cells[i, 4].Value2 != null)
{
city = xlRange.Cells[i, 4].Value2.ToString().Replace("'", @"\'");
}
if (xlRange.Cells[i, 5].Value2 != null)
{
stateCode = xlRange.Cells[i, 5].Value2.ToString().Replace("'", @"\'");
}
if (xlRange.Cells[i, 6].Value2 != null)
{
zipCode = xlRange.Cells[i, 6].Value2.ToString().Replace("'", @"\'");
}
if (xlRange.Cells[i, 7].Value2 != null)
{
countryName = xlRange.Cells[i, 7].Value2.ToString().Replace("'", @"\'");
}
if (xlRange.Cells[i, 8].Value2 != null)
{
productName = xlRange.Cells[i, 8].Value2.ToString().Replace("'", @"\'");
}
if (xlRange.Cells[i, 9].Value2 != null)
{
productType = xlRange.Cells[i, 9].Value2.ToString().Replace("'", @"\'");
}
if (xlRange.Cells[i, 10].Value2 != null)
{
productColor = xlRange.Cells[i, 10].Value2.ToString().Replace("'", @"\'");
}
if (xlRange.Cells[i, 11].Value2 != null)
{
productSize = xlRange.Cells[i, 11].Value2.ToString().Replace("'", @"\'");
}
if (xlRange.Cells[i, 12].Value2 != null)
{
quantity = xlRange.Cells[i, 12].Value2.ToString().Replace("'", @"\'");
}
if (xlRange.Cells[i, 13].Value2 != null)
{
productID = xlRange.Cells[i, 13].Value2.ToString().Replace("'", @"\'");
}
List <Information> information = new List <Information>();
information.Add(new Information(fullName, address1, address2, city, stateCode, zipCode, countryName, productName, productType, productColor, productSize, quantity, productID));
addData(information, i);
}
GC.Collect();
GC.WaitForPendingFinalizers();
Marshal.ReleaseComObject(xlRange);
Marshal.ReleaseComObject(xlWorksheet);
xlWorkbook.Close();
Marshal.ReleaseComObject(xlWorkbook);
xlApp.Quit();
Marshal.ReleaseComObject(xlApp);
}
/// <summary>
/// 为单元格设置公式
/// </summary>
/// <param name="CurSheet">Worksheet</param>
/// <param name="objCell">单元格</param>
/// <param name="strFormula">公式</param>
public void SetFormula(Excel._Worksheet CurSheet, object objCell, string strFormula)
{
CurSheet.get_Range(objCell, mValue).Formula = strFormula;
}
public List<AccountExcel> readExcel(string sExcelPath)
{
var lReturn = new List<AccountExcel>();
string valueString = string.Empty;
objExcelApp = new Microsoft.Office.Interop.Excel.Application();
objBooks = (Excel.Workbooks)objExcelApp.Workbooks;
//Open the workbook containing the address data.
objBook = objBooks.Open(sExcelPath, Missing.Value, Missing.Value,
Missing.Value, Missing.Value,
Missing.Value, Missing.Value,
Missing.Value, Missing.Value,
Missing.Value, Missing.Value,
Missing.Value, Missing.Value,
Missing.Value, Missing.Value);
//Get a reference to the first sheet of the workbook.
objSheets = objBook.Worksheets;
objSheet = (Excel._Worksheet)objSheets.get_Item(1);
rngLast = objSheet.get_Range("A1").SpecialCells(Microsoft.Office.Interop.Excel.XlCellType.xlCellTypeLastCell);
long lLastRow = rngLast.Row;
long lLastCol = rngLast.Column;
for (long rowCounter = 2; rowCounter <= lLastRow; rowCounter++) //FirstRow Has Headers - start at row 2
{
if (ToStringHandlesNulls(((Excel.Range)objSheet.Cells[rowCounter, 1]).Value) != "")
{
var adAccount = new AccountExcel();
adAccount.sCustomerNumber = ToStringHandlesNulls(((Excel.Range)objSheet.Cells[rowCounter, 1]).Value);
adAccount.sAccountName = ToStringHandlesNulls(((Excel.Range)objSheet.Cells[rowCounter, 40]).Value);
adAccount.sAddressLine1 = ToStringHandlesNulls(((Excel.Range)objSheet.Cells[rowCounter, 2]).Value);
adAccount.sAddressLine2 = ToStringHandlesNulls(((Excel.Range)objSheet.Cells[rowCounter, 5]).Value);
adAccount.sAddressLine3 = ToStringHandlesNulls(((Excel.Range)objSheet.Cells[rowCounter, 9]).Value);
adAccount.sPostCode = ToStringHandlesNulls(((Excel.Range)objSheet.Cells[rowCounter, 15]).Value);
adAccount.sTelephone = ToStringHandlesNulls(((Excel.Range)objSheet.Cells[rowCounter, 17]).Value);
adAccount.sVatNumber = ToStringHandlesNulls(((Excel.Range)objSheet.Cells[rowCounter, 18]).Value);
adAccount.sCountryCode = ToStringHandlesNulls(((Excel.Range)objSheet.Cells[rowCounter, 21]).Value);
adAccount.sEmail = ToStringHandlesNulls(((Excel.Range)objSheet.Cells[rowCounter, 37]).Value);
adAccount.sWeb = "";// ToStringHandlesNulls(((Excel.Range)objSheet.Cells[rowCounter, 38]).Value);
adAccount.sKAM = ToStringHandlesNulls(((Excel.Range)objSheet.Cells[rowCounter, 31]).Value);
adAccount.sRegion = ToStringHandlesNulls(((Excel.Range)objSheet.Cells[rowCounter, 24]).Value);
lReturn.Add(adAccount);
}
}
//Close the Excel Object
objBook.Close(false, System.Reflection.Missing.Value, System.Reflection.Missing.Value);
objBooks.Close();
objExcelApp.Quit();
Marshal.ReleaseComObject(objSheet);
Marshal.ReleaseComObject(objSheets);
Marshal.ReleaseComObject(objBooks);
Marshal.ReleaseComObject(objBook);
Marshal.ReleaseComObject(objExcelApp);
objSheet = null;
objSheets = null;
objBooks = null;
objBook = null;
objExcelApp = null;
GC.GetTotalMemory(false);
GC.Collect();
GC.WaitForPendingFinalizers();
GC.Collect();
GC.GetTotalMemory(true);
return (lReturn);
}
// Program entry point.
static void Main(string[] args)
{
oXL = new Excel.Application();
oXL.Visible = true;
oWB = (Excel._Workbook)(oXL.Workbooks.Add(Type.Missing));
oSheet = (Excel._Worksheet)oWB.ActiveSheet;
Console.WriteLine("Excel worksheet created.");
List<string> phonePages = GetListOfPhonePages();
List<string> specPages = FindPhoneSpecPages(phonePages);
int i = 1;
Stopwatch stopWatch = new Stopwatch();
stopWatch.Start();
foreach (string specPage in specPages)
{
// Specs are extracted on multiple threads to bypass
// the time it takes to make web requests and load the DOM on the page.
new Thread(() =>
{
ExtractSpecs(specPage, i);
}).Start();
Random random = new Random();
Thread.Sleep(random.Next(1000,3000));
i++;
}
for (int j = 0; j < 1000; j++)
{
Thread.Sleep(1000);
TimeSpan ts = stopWatch.Elapsed;
// Format and display the TimeSpan value.
string elapsedTime = String.Format("{0:00}:{1:00}:{2:00}.{3:00}",
ts.Hours, ts.Minutes, ts.Seconds,
ts.Milliseconds / 10);
Console.WriteLine("RunTime " + elapsedTime);
}
}
/// <summary>
/// Метдо установки темплейта для дальнейшего заполнения
/// </summary>
/// <param name="template_filename">Имя и путь файла</param>
/// <param name="background">Создать в бэкграунде</param>
public static void SetTemplate(string template_filename, bool background)
{
if (!background)
excelApp.Visible = true;
else
excelApp.Visible = false;
excelApp.Workbooks.Add(template_filename);
workSheet = excelApp.ActiveSheet;
}
private void button1_Click(object sender, EventArgs e)
{
/// check if user has selected a file - if not then warn the user and return
/// verify a file has been selected by observing the label lbl_1.
if (lbl_1.Text == "Please Select a File")
{
warningLabel.ForeColor = Color.Red;
warningLabel.Text = "Please select a file before continuing";
return;
}
warningLabel.Text = "";
/// check if user has selected a number of files
if (comboBox1.SelectedIndex == -1)
{
warningLabel.ForeColor = Color.Red;
warningLabel.Text = "Please select the number of samples in the file";
return;
}
warningLabel.Text = "";
string[] filePath = lbl_1.Text.Split('\n');
foreach (string sFileName in filePath)
{
//https://coderwall.com/p/app3ya/read-excel-file-in-c
//Create COM Objects. Create a COM object for everything that is referenced
Excel.Application xlApp = new Excel.Application();
xlApp.Visible = false;
Excel.Workbook xlWorkbook = xlApp.Workbooks.Open(sFileName);
Excel._Worksheet xlWorksheet = xlWorkbook.Sheets[1];
Excel.Range xlRange = xlWorksheet.UsedRange;
//create COM objects for copy of original document
Excel.Application xlAppCopy = new Excel.Application();
xlAppCopy.Visible = false;
Excel.Workbook xlWorkbookCopy = xlAppCopy.Workbooks.Add();
Excel._Worksheet xlWorksheetCopy = xlWorkbookCopy.Sheets[1];
//Copy all contents from selected file to the new excel doc
int nRows = xlRange.Rows.Count;
int nEndDestinationCopy = nRows;
string endDestinationCopy = "P" + nEndDestinationCopy.ToString(); // This will only work if all the docs are P columns wide!
Excel.Range xlRangeCopy = xlWorksheetCopy.get_Range("A1", endDestinationCopy);
xlRangeCopy.Value2 = xlRange.Value2;
//create COM objects for output file
Excel.Application xlApp2 = new Excel.Application();
xlApp2.Visible = false;
Excel.Workbook xlWorkbook2 = xlApp2.Workbooks.Add();
Excel._Worksheet xlWorksheet2 = xlWorkbook2.Sheets[1];
//insert HODS codes
xlWorksheet2.Cells[1, 1] = box_0101.Text;
xlWorksheet2.Cells[1, 2] = box_0102.Text;
xlWorksheet2.Cells[1, 3] = box_0103.Text;
xlWorksheet2.Cells[1, 4] = box_0104.Text;
xlWorksheet2.Cells[1, 5] = box_0105.Text;
xlWorksheet2.Cells[1, 6] = box_0106.Text;
xlWorksheet2.Cells[1, 7] = box_0107.Text;
xlWorksheet2.Cells[1, 8] = box_0108.Text;
//LOOP THROUGH SAMPLES
//for loop to repeat for each sample. Can reinstate this later.
for (int sample = 1; sample < comboBox1.SelectedIndex + 2; sample++)
{
//i is the row we're interested in
int i = sample + 2;
//https://docs.microsoft.com/en-us/dotnet/api/microsoft.office.interop.excel.range.autofilter?view=excel-pia
//PUT SAMPLE NAME IN EXCEL
///SORT AND FILTER- i is column number
xlRangeCopy.Sort(xlRangeCopy.Columns[i], Excel.XlSortOrder.xlAscending, Type.Missing, Type.Missing, Excel.XlSortOrder.xlAscending, Type.Missing, Excel.XlSortOrder.xlAscending, Excel.XlYesNoGuess.xlYes);
xlRangeCopy.AutoFilter(i, "<100");
//COUNT FILTERED ROWS
//https://stackoverflow.com/questions/41731714/counting-rows-of-filtered-excel-range-in-c-sharp
//NOTE, this includes the first row in the count, so -1
//this counts visible cells
Excel.Range xlRange3 = xlRangeCopy.SpecialCells(Excel.XlCellType.xlCellTypeVisible);
// -1 because of headers
int nFilteredRows = xlRange3.Rows.Count - 1;
string sampleLetter = ((char)(sample + 64)).ToString();
//MOVE FILTERED ROWS- will need to change the values in get range to fit the sample and number of filtered genes
//-1 becaause to get 4 rows we need A2:A5 and 5-2 is 4-1
int nStartSource = 2;
int nEndSource = nStartSource + nFilteredRows - 1;
int nStartDestination = 2;
int nEndDestination = nStartDestination + nFilteredRows - 1;
string startSource = "A" + nStartSource.ToString();
string startDestination = sampleLetter + nStartDestination.ToString();
string endSource = "A" + nEndSource.ToString();
string endDestination = sampleLetter + nEndDestination.ToString();
Excel.Range xlRange2 = xlWorksheet2.get_Range(startDestination, endDestination);
Excel.Range sourceRng = xlWorksheetCopy.get_Range(startSource, endSource);
xlRange2.Value2 = sourceRng.Value2;
xlRange2.RemoveDuplicates(1, Excel.XlYesNoGuess.xlNo);
//REMOVE FILTER
xlRangeCopy.AutoFilter(i);
Marshal.ReleaseComObject(sourceRng);
Marshal.ReleaseComObject(xlRange2);
Marshal.ReleaseComObject(xlRange3);
}
//TRANSPOSE
//Access range of data in worksheet, and dimensions
Excel.Range xlRange2Used = xlWorksheet2.UsedRange;
int rowsXlRange2Used = xlRange2Used.Rows.Count;
int colsXlRange2Used = xlRange2Used.Columns.Count;
//formulate location for transposed range
string newRangeStart = "A" + (rowsXlRange2Used + 2).ToString();
string newRangeEnd = ((char)(rowsXlRange2Used + 64)).ToString() + (rowsXlRange2Used + colsXlRange2Used + 1).ToString();
Excel.Range xlRange2Replace = xlWorksheet2.get_Range(newRangeStart, newRangeEnd);
//transpose into new location
xlWorksheet2.Range[newRangeStart + ":" + newRangeEnd].Value = xlApp2.WorksheetFunction.Transpose(xlRange2Used);
//delete columns with untransposed information
xlRange2Used.EntireRow.Delete();
//SAVE OUTPUT DOCUMENT
string fileName = Path.GetFileName(sFileName); //retreives the filename from the path
string directoryName = Path.GetDirectoryName(sFileName); //retreives path of the directory of selected file
xlWorkbook2.SaveAs(directoryName + "/" + "output_" + fileName);
label_output.Text = "Output file is complete: " + directoryName + "/" + "output_" + fileName;
//Close documents without displaying any prompt boxes
xlApp2.DisplayAlerts = false;
xlApp2.Quit();
xlAppCopy.DisplayAlerts = false;
xlAppCopy.Quit();
xlApp.DisplayAlerts = false;
xlApp.Quit();
//cleanup
GC.Collect();
GC.WaitForPendingFinalizers();
//rule of thumb for releasing com objects:
// never use two dots, all COM objects must be referenced and released individually
// ex: [somthing].[something].[something] is bad
//release com objects to fully kill excel process from running in the background
Marshal.ReleaseComObject(xlRange);
Marshal.ReleaseComObject(xlRange2Replace);
Marshal.ReleaseComObject(xlRange2Used);
Marshal.ReleaseComObject(xlRangeCopy);
Marshal.ReleaseComObject(xlWorksheet);
Marshal.ReleaseComObject(xlWorksheetCopy);
Marshal.ReleaseComObject(xlWorksheet2);
Marshal.ReleaseComObject(xlWorkbook);
Marshal.ReleaseComObject(xlWorkbook2);
Marshal.ReleaseComObject(xlWorkbookCopy);
Marshal.ReleaseComObject(xlApp);
Marshal.ReleaseComObject(xlApp2);
Marshal.ReleaseComObject(xlAppCopy);
}
}
private void button1_Click(object sender, System.EventArgs e)
{
try
{
// Instantiate Excel and start a new workbook.
objApp = new Microsoft.Office.Interop.Excel.Application();
objBooks = objApp.Workbooks;
objBook = objBooks.Add(Missing.Value);
objSheets = objBook.Worksheets;
objSheet = (Microsoft.Office.Interop.Excel._Worksheet)objSheets.get_Item(1);
//Get the range where the starting cell has the address
//m_sStartingCell and its dimensions are m_iNumRows x m_iNumCols.
range = objSheet.get_Range("A1", Missing.Value);
range = range.get_Resize(5, 5);
//Create an array.
double[,] saRet = new double[5, 5];
//Fill the array.
for (long iRow = 0; iRow < 5; iRow++)
{
for (long iCol = 0; iCol < 5; iCol++)
{
//Put a counter in the cell.
saRet[iRow, iCol] = iRow * iCol * iCol;
}
}
//Set the range value to the array.
range.set_Value(Missing.Value, saRet);
objApp.Visible = true;
objApp.UserControl = true;
}
catch( Exception theException )
{
String errorMessage;
errorMessage = "Error: ";
errorMessage = String.Concat( errorMessage, theException.Message );
errorMessage = String.Concat( errorMessage, " Line: " );
errorMessage = String.Concat( errorMessage, theException.Source );
MessageBox.Show( errorMessage, "Error" );
}
Microsoft.Office.Interop.Excel.Range currentFind = null;
Microsoft.Office.Interop.Excel.Range firstFind = null;
string A = "16";
// You should specify all these parameters every time you call this method,
// since they can be overridden in the user interface.
currentFind = objSheet.Cells.Find(A, Type.Missing,
Microsoft.Office.Interop.Excel.XlFindLookIn.xlValues, Microsoft.Office.Interop.Excel.XlLookAt.xlWhole,
Microsoft.Office.Interop.Excel.XlSearchOrder.xlByRows, Microsoft.Office.Interop.Excel.XlSearchDirection.xlNext, false,
Type.Missing, Type.Missing);
while (currentFind != null)
{
// Keep track of the first range you find.
if (firstFind == null)
{
firstFind = currentFind;
//textBox1.Text = currentFind.get_Address(true, true, Microsoft.Office.Interop.Excel.XlReferenceStyle.xlA1, false, Missing.Value);
}
// If you didn't move to a new range, you are done.
else if (currentFind.get_Address(Type.Missing, Type.Missing, Microsoft.Office.Interop.Excel.XlReferenceStyle.xlA1, Type.Missing, Type.Missing)
== firstFind.get_Address(Type.Missing, Type.Missing, Microsoft.Office.Interop.Excel.XlReferenceStyle.xlA1, Type.Missing, Type.Missing))
{
break;
}
currentFind.Font.Color = System.Drawing.ColorTranslator.ToOl
(System.Drawing.Color.Red);
currentFind.Font.Bold = true;
currentFind = objSheet.Cells.FindNext(currentFind);
}
}
private void BtnXuatExcel_Click(object sender, EventArgs e)
{
//export2Excel(dtgvTonKho, @"D:\", "xuatfileTonKho");
//Tạo đối tượng lưu tệp tin
SaveFileDialog fsave = new SaveFileDialog();
//chỉ ra đuôi
fsave.Filter = "(Tất cả các tệp) | *.*|(Các tệp excel)|*.xlsx";
fsave.ShowDialog();
//xử lý
if (fsave.FileName != "")
{
//tạo excel app
Excel.Application app = new Excel.Application();
//tạo 1 workbook
Excel.Workbook wb = app.Workbooks.Add(Type.Missing);
//tạo sheet
Excel._Worksheet sheet = null;
try
{
//đọc dữ liệu từ Listview export ra file Excel có đinh dạng
sheet = wb.ActiveSheet;
sheet.Name = "Báo cáo tồn kho";
sheet.Columns.ColumnWidth = 25;
sheet.Range[sheet.Cells[1, 1], sheet.Cells[1, dtgvTonKho.Columns.Count]].Merge();
sheet.Cells[1, 1].value = "BÁO CÁO TỒN KHO";
sheet.Cells[1, 1].HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
//sinh tiêu đề
for (int i = 1; i <= dtgvTonKho.Columns.Count; i++)
{
sheet.Cells[2, i] = dtgvTonKho.Columns[i - 1].HeaderText;
}
//sinh du lieu
for (int i = 0; i < dtgvTonKho.Rows.Count; i++)
{
for (int j = 0; j < dtgvTonKho.Columns.Count; j++)
{
if (dtgvTonKho.Rows[i].Cells[j].Value != null)
{
sheet.Cells[i + 3, j + 1] = dtgvTonKho.Rows[i].Cells[j].Value.ToString();
}
}
}
//ghi lại
wb.SaveAs(fsave.FileName);
MessageBox.Show("Xuất thành công", "Thông báo", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, "Thông báo", MessageBoxButtons.OK, MessageBoxIcon.Warning);
}
finally
{
app.Quit();
wb = null;
}
}
else
{
MessageBox.Show("Bạn không chọn tệp tin nào", "Thông báo", MessageBoxButtons.OK, MessageBoxIcon.Warning);
}
}
protected override void Execute(CodeActivityContext context)
{
//Load Input Fields unto local variables
string filePath = FilePath.Get(context);
//Validate that file path is not empty
if (!String.IsNullOrEmpty(filePath))
{
if (File.Exists(filePath))
{
try
{
//Initialize Excel Interop objects;
object m = Type.Missing;
Excel.Application xlApp = new Excel.Application();
Excel.Workbooks xlWorkbooks = xlApp.Workbooks;
Excel.Workbook xlWorkbook = xlWorkbooks.Open(filePath, m, true, m, m, m, m, m, m, m, m, m, m, m, m);
Excel._Worksheet xlWorksheet = xlWorkbook.Sheets[1];
Excel.Range xlRange = xlWorksheet.UsedRange;
// Gather Row and Col counts
Int32 lastRow = xlRange.Rows.Count;
Int32 lCol = xlRange.Columns.Count;
Int32 sCol = lCol + 1;
// Create local variables for processing
int i;
string vLstCol;
//iterate by the existing columns to determine if a current Status column exists
for (i = 1; i <= lCol; i++)
{
vLstCol = xlWorksheet.Cells[1, i].Value;
if (vLstCol == "Status")
{
sCol = i;
lCol = i - 1;
}
}
//Count all rows that Status Column has the value of "Complete"
int countComplete = (int)xlApp.WorksheetFunction.CountIf(xlRange.Columns[sCol, m], "Complete") + 1;
//Count all rows that are not empty on the first column (Item-Index column)
int countNotNull = (int)xlApp.WorksheetFunction.CountA(xlRange.Columns[1, m]);
//Validate that complete rows match the lastRow count
if (countComplete == countNotNull)
{
//Return True for Complete
isComplete.Set(context, true);
}
else
{
//Return False for not complete
isComplete.Set(context, false);
}
//DebugOut Output
DebugOut.Set(context, "CC: " + countComplete.ToString() + " | CA: " + countNotNull.ToString() + " | LR: " + lastRow.ToString());
//Close Workbook no Save
xlWorkbook.Close(false, m, m);
//CLOSE AND GARBAGE COLLECT
Marshal.ReleaseComObject(xlWorksheet);
xlWorksheet = null;
Marshal.ReleaseComObject(xlWorkbooks);
xlWorkbooks = null;
Marshal.ReleaseComObject(xlWorkbook);
xlWorkbook = null;
xlApp.Quit();
Marshal.ReleaseComObject(xlApp);
xlApp = null;
GC.Collect(); //Garbage Collect
GC.WaitForPendingFinalizers(); //Wait until Garbage Collect completes
}
catch
{
throw;
}
}
else
{
throw new FileNotFoundException();
}
}
else
{
throw new ArgumentNullException();
}
}
/// <summary>
/// Метод открытия уже существующего файла
/// </summary>
/// <param name="template_filename">Имя и путь файла</param>
/// <param name="background">Создать в бэкграунде</param>
public static void OpenFile(string filename, bool background)
{
if (!background)
excelApp.Visible = true;
else
excelApp.Visible = false;
excelApp.Workbooks.Open(filename);
workSheet = excelApp.ActiveSheet;
}
/// <summary>
/// 根据Excel表格路径将该表格添加到总表中
/// </summary>
/// <param name="strFileName">原表格路径</param>
public void OpenExcel(string strFileName)
{
object missing = System.Reflection.Missing.Value;
Excel.Application excel = new Excel.Application();//lauch excel application
if (excel == null)
{
textBoxOfResultForCreateExcel.AppendText("Can't access excel" + " " + strFileName);
}
else
{
excel.Visible = false; excel.UserControl = true;
// 以只读的形式打开EXCEL文件
Excel.Workbook wb = excel.Application.Workbooks.Open(strFileName, missing, true, missing, missing, missing,
missing, missing, missing, true, missing, missing, missing, missing, missing);
//取得第一个工作薄
Excel.Worksheet ws = (Excel.Worksheet)wb.Worksheets.get_Item(1);
//取得总记录行数 (包括标题列)
int rowsint = ws.UsedRange.Cells.Rows.Count; //得到行数
//int columnsint = mySheet.UsedRange.Cells.Columns.Count;//得到列数
//取得数据范围区域 (不包括标题列)
Excel.Range rng1 = ws.Cells.get_Range("A2", "A" + rowsint);
Excel.Range rng2 = ws.Cells.get_Range("B2", "B" + rowsint);
Excel.Range rng3 = ws.Cells.get_Range("C2", "C" + rowsint);
Excel.Range rng4 = ws.Cells.get_Range("D2", "D" + rowsint);
object[,] arry1 = (object[, ])rng1.Value2; //get range's value ,学号
object[,] arry2 = (object[, ])rng2.Value2; //专业
object[,] arry3 = (object[, ])rng3.Value2; //get range's value 姓名
object[,] arry4 = (object[, ])rng4.Value2; //性别
//将arry1赋给一个数组
string classId = arry1[2, 1].ToString().Remove(arry1[2, 1].ToString().Length - 2);
//新建Excelapp
Excel.Application excelApp = new Excel.Application();
excelApp.Visible = true;
//设置不显示确认修改提示
excelApp.DisplayAlerts = false;
//得到workbook对象，打开总表
Excel.Workbook excleBook = excelApp.Workbooks.Open(@"D:\总表.xlsx", Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value);
//指定要操作的sheet
Excel._Worksheet excleSheet = (Excel.Worksheet)excelApp.Worksheets.get_Item(1);
//获取总记录行数（包括标题列）
int rowsCount = excleSheet.UsedRange.Cells.Rows.Count;
//将分表的记录写入到总表中去
for (int i = 1; i <= arry1.Length; i++)
{
if (arry1[i, 1] == null)
{
excelApp.Cells[rowsCount + i, 1] = "";
}
else
{
excelApp.Cells[rowsCount + i, 1] = arry1[i, 1].ToString();
}
if (arry2[i, 1] == null)
{
excelApp.Cells[rowsCount + i, 2] = "";
}
else
{
excelApp.Cells[rowsCount + i, 2] = arry2[i, 1].ToString();
}
if (arry3[i, 1] == null)
{
excelApp.Cells[rowsCount + i, 3] = "";
}
else
{
if (arry3[i, 1].ToString().EndsWith("0001"))
{
excelApp.Cells[rowsCount + i, 3] = arry3[i, 1].ToString().Remove(arry3[i, 1].ToString().Length - 4);
}
else
{
excelApp.Cells[rowsCount + i, 3] = arry3[i, 1].ToString();
}
}
if (arry4[i, 1] == null)
{
excelApp.Cells[rowsCount + i, 4] = "";
}
else
{
excelApp.Cells[rowsCount + i, 4] = arry4[i, 1].ToString();
}
excelApp.Cells[rowsCount + i, 5] = classId;
}
//for (int i = 1; i <= rowsint - 1; i++)
// for (int i = 1; i <= rowsint - 2; i++)
// {
// arry[i - 1, 0] = arry1[i, 1].ToString();
// arry[i - 1, 1] = arry2[i, 1].ToString();
// arry[i - 1, 2] = arry3[i, 1].ToString();
// arry[i - 1, 3] = arry4[i, 1].ToString();
// }
// string a = "";
// for (int i = 0; i <= rowsint - 3; i++)
// {
// a += arry[i, 0] + "|" + arry[i, 1] + "|" + arry[i, 2] + "|" + arry[i, 3] + "\n";
// }
// this.label1.Text = a;
//关闭打开的表
wb.Close(false, Missing.Value, Missing.Value);
excleBook.Close(true, Missing.Value, Missing.Value);
//把excel，workbook，worksheet设置为null，防止内存泄漏
wb = null;
excleBook = null;
ws = null;
excleBook = null;
//程序退出
excel.Quit();
excelApp.Quit();
excel = null;
excelApp = null;
GC.Collect();
}
}
private void tmrData_Tick(object sender, EventArgs e)
{
int i;
uint recLength;
recLength = 25;
// Get the Live data, to show in gauge
txBuf[0] = (byte)CMD.GET_LIVE_DATA;
txBuf[1] = 2;
//USB.SendReceivePacket(txBuf, 2, rxBuf, ref recLength);
string port = cbxPorts.SelectedItem.ToString();
if (port == "")
return;
System.IO.Ports.SerialPort myPort = new System.IO.Ports.SerialPort(port);
if (myPort.IsOpen == false) //if not open, open the port
myPort.Open();
//do your work here
if (myPort.IsOpen == false) //if not open, open the port
return;
String data;
data = myPort.ReadLine();
myPort.Close();
string [] dht=data.Split(',');
//s.dataPtr = rxBuf[18];
//i = 19;
//convertData(ref currData, ref i);
//gTemp.Value = currData.temp;
//gPressure.Value = currData.pressure;
//gHumid.Value = currData.humidity;
if (dht.Length != 3)
return;
gHumid.Value = float.Parse(dht[0]);
gTemp.Value = float.Parse(dht[1]);
gPressure.Value = int.Parse(dht[2]);
if (stt == 0)
{
//Start Excel and get Application object.
oXL = new Excel.Application();
oXL.Visible = true;
//Get a new workbook.
oWB = (Excel._Workbook)(oXL.Workbooks.Add(Missing.Value));
oSheet = (Excel._Worksheet)oWB.ActiveSheet;
}
//Add table headers going cell by cell.
oSheet.Cells[stt + 1, 1] = gTemp.Value.ToString();
oSheet.Cells[stt + 1, 2] = gPressure.Value.ToString();
oSheet.Cells[stt + 1, 3] = gHumid.Value.ToString();
//oSheet.Cells[1, 4] = "Salary";
DateTime timeNow = DateTime.Now;
w[stt].humidity = gHumid.Value;
w[stt].temp = gTemp.Value;
w[stt].pressure = gPressure.Value;
w[stt++].time = timeNow;
if (stt == 999)
stt = 0;
Redraw(0);
goLive = false;
if (goLive) // Only load data to w if graph shows live data from PIC
{
recLength = 64;
txBuf[0] = (byte)CMD.GET_FIRST_DATA;
txBuf[1] = 2;
USB.SendReceivePacket(txBuf, 2, rxBuf, ref recLength);
loadData(10);
for (i = 0; i < 5; i++)
{
txBuf[0] = (byte)CMD.GET_NEXT_DATA;
txBuf[1] = 2;
USB.SendReceivePacket(txBuf, 2, rxBuf, ref recLength);
loadData(10);
}
txBuf[0] = (byte)CMD.GET_CURR_DATA;
txBuf[1] = 2;
USB.SendReceivePacket(txBuf, 2, rxBuf, ref recLength);
loadData((int)s.dataPtr);
}
}
public static List <Question> ReadFileGerman(string excelFile)
{
List <Question> qList = new List <Question>();
// open excel file
Excel.Application excel = new Excel.Application();
Excel.Workbook workbook = excel.Workbooks.Open(excelFile);
Excel._Worksheet worksheet = workbook.Sheets[1];
int rowCount = worksheet.UsedRange.Rows.Count;
int colCount = worksheet.UsedRange.Columns.Count;
// find data indexes
Main main = new Main();
main.ShowOutput("Finding data indexes....", false);
int questionTextIndex = 0;
int nameIndex = 0;
int categoryIndex = 0;
int answerIndex = 0;
int[] optionIndexes = new int[] { 0, 0, 0, 0 };
for (int col = 1; col <= colCount; col++)
{
string v = worksheet.Cells[1, col].Value.ToString();
if (v == "Fragetext")
{
questionTextIndex = col;
}
if (v == "Themenname")
{
categoryIndex = col;
}
if (v == "Korrekte Antwort(en)")
{
answerIndex = col;
}
if (v == "ID")
{
nameIndex = col;
}
for (int i = 1; i <= 4; i++)
{
if (v == "Antwort " + i.ToString())
{
optionIndexes[i - 1] = col;
}
}
}
// check data indexes
if (questionTextIndex > 0 &&
optionIndexes[0] > 0 &&
optionIndexes[1] > 0 &&
optionIndexes[2] > 0 &&
optionIndexes[3] > 0 &&
answerIndex > 0 &&
categoryIndex > 0 &&
nameIndex > 0)
{
main.ShowOutput("OK");
}
else
{
main.ShowOutput("Error. Indexes not found.");
workbook.Close();
excel.Quit();
return(qList);
}
// get question data
for (int row = 2; row <= rowCount; row++)
{
Question q = new Question();
q.name = JwString.Clean(worksheet.Cells[row, nameIndex].Value.ToString());
q.category = JwString.Clean(worksheet.Cells[row, categoryIndex].Value.ToString());
q.text = JwString.Clean(worksheet.Cells[row, questionTextIndex].Value.ToString());
q.type = "multichoice";
int answer = Int32.Parse(worksheet.Cells[row, answerIndex].Value);
for (int i = 1; i <= 4; i++)
{
Option o = new Option();
o.text = JwString.Clean(worksheet.Cells[row, optionIndexes[i - 1]].Value.ToString());
o.feedback = "";
o.grade = 0;
if (answer == i)
{
o.grade = 100;
}
q.options.Add(o);
}
qList.Add(q);
}
// close excel and return questions
workbook.Close();
excel.Quit();
return(qList);
}
public void SetSheet( bool bWrite=false )
{
if( bWrite )
{
excelApp.Workbooks.Add();
workSheet = excelApp.ActiveSheet;
excelApp.Visible = true;
}
else
{
Excel.Workbook xlWorkBook = excelApp.Workbooks.Open( FileName, 0, true, 5, "", "", true,
Excel.XlPlatform.xlWindows, "\t", false, false, 0, true, 1, 0);
workSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);
}
}
private void buttonDupCheck_Click(object sender, EventArgs e)
{
fixDataSetNullsToEmptyStrings();
if (xlWorkbook == null)
{
xlApp = new Excel.Application();
OpenFileDialog ofd = new OpenFileDialog();
ofd.Filter = "Excel Documents (*.xlsx)|*.xlsx";
ofd.ShowDialog();
xlWorkbook = xlApp.Workbooks.Open(ofd.FileName, 0, true, 5, "", "", true, Excel.XlPlatform.xlWindows, "\t", false, false, 0, true, 1, 0);
xlWorksheet = (Excel._Worksheet)xlWorkbook.Sheets[1];
}
int checkStart = checkBoxDupeCheckAll.Checked ? 0 : Convert.ToInt32(textDupeCheckRowFrom.Text) - 1;
int checkEnd = checkBoxDupeCheckAll.Checked ? dataCompanyList.Rows.Count - 1 : Convert.ToInt32(textDupeCheckRowTo.Text) - 1;
fixDataSetNullsToEmptyStrings();
finalDuplicateCheck(checkStart, checkEnd);
}
private static string OpenExcel(ExcelInfo excelInfo, bool isOpenSheet = true)
{
Console.WriteLine("Open File:【{0}】", excelInfo.FilePath);
if (!File.Exists(excelInfo.FilePath))
{
return $"文件【{excelInfo.FilePath}】不存在";
}
_objExcel = new Excel.Application { Visible = false, DisplayAlerts = false, AlertBeforeOverwriting = false };
_objBooks = _objExcel.Workbooks;
if (excelInfo.FilePath.Equals(String.Empty) || !File.Exists(excelInfo.FilePath))
{
_objBook = _objBooks.Add(ObjOpt);
}
else
{
_objBook = _objBooks.Open(excelInfo.FilePath, ObjOpt, ObjOpt, ObjOpt, ObjOpt,
ObjOpt, ObjOpt, ObjOpt, ObjOpt, ObjOpt, ObjOpt, ObjOpt, ObjOpt, ObjOpt, ObjOpt);
}
if (isOpenSheet)
{
_objSheet = OpenSheet(excelInfo);
if (_objSheet == null)
{
return "没有指定页签";
}
}
return "";
}
/// <summary>
/// loading values from header of excel into comboboxes
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void menu_open_xlsx_Click(object sender, RoutedEventArgs e)
{
try
{
var excelFile = new OpenFileDialog
{
DefaultExt = ".xlsx",
Filter = "(.xlsx)|*.xlsx"
};
Nullable<bool> result = excelFile.ShowDialog();
if (result != true) return;
RtbProgress.AppendText("Loading Excell file and checking if APIs services are available.\r...\r");
DoEvents();
CbxUlice.Items.Clear();
CbxCp.Items.Clear();
CbxCo.Items.Clear();
CbxObec.Items.Clear();
CbxPsc.Items.Clear();
_excelPath = excelFile.FileName;
_excelApp = new Excel.Application();
_excelWorkBook = _excelApp.Workbooks.Open(_excelPath);
_excelWorkSheet = _excelWorkBook.Sheets[1];
// counting rows and columns
_rowCount = _excelWorkSheet.UsedRange.Rows.Count;
_columnCount = _excelWorkSheet.UsedRange.Columns.Count;
// add max rows into interval
TxtInterval.Text = "[1 - " + _rowCount + "]";
// Adding selections to comboboxes
for (int i = 1; i < _excelWorkSheet.UsedRange.Columns.Count + 1; i++)
{
CbxUlice.Items.Add(_excelWorkSheet.Cells[1, i].Value);
CbxCp.Items.Add(_excelWorkSheet.Cells[1, i].Value);
CbxCo.Items.Add(_excelWorkSheet.Cells[1, i].Value);
CbxObec.Items.Add(_excelWorkSheet.Cells[1, i].Value);
CbxPsc.Items.Add(_excelWorkSheet.Cells[1, i].Value);
}
// adding one null slot to comboboxes
CbxUlice.Items.Add("");
CbxCp.Items.Add("");
CbxCo.Items.Add("");
CbxObec.Items.Add("");
CbxPsc.Items.Add("");
EnableObjects(true);
MenuOpen.IsEnabled = false;
RtbProgress.AppendText(_excelPath + " is loaded!\r");
RtbProgress.AppendText("For new excel file RESTART THE APP please.\r");
RtbProgress.ScrollToEnd();
RtbProgress.AppendText("---------------\r");
CheckIfApisAreOnline();
RtbProgress.AppendText("---------------\r");
}
catch (Exception ex)
{
MessageBox.Show("A handled exception just occurred: " + ex.Message, "Exception", MessageBoxButton.OK, MessageBoxImage.Warning);
}
}
public static InnerResult GetExcelFile(string file)
{
//Create COM Objects. Create a COM object for everything that is referenced
var xlApp = new Excel.Application();
var workbooks = xlApp.Workbooks;
Excel.Workbook xlWorkbook;
try
{
xlWorkbook = workbooks.Open(file, null, ReadOnly: true);
}
catch (Exception e)
{
MessageBox.Show("Exception Message: " + e.Message);
if (e.InnerException != null)
{
MessageBox.Show("InnerException Message: " + e.InnerException.Message);
}
MessageBox.Show("Exception Trace : " + e.StackTrace);
return(new InnerResult {
Success = false, Message = "Error on open file."
});
}
var sheets = xlWorkbook.Sheets;
Console.WriteLine(sheets.Count);
Excel._Worksheet xlWorksheet = null;
if (sheets.Count == 1)
{
xlWorksheet = xlWorkbook.Sheets[1];
}
else
{
for (int i = 1; i <= sheets.Count; i++)
{
Excel._Worksheet tmp = sheets[i];
if (tmp.Name != "PO Upload Download Template-APP")
{
continue;
}
xlWorksheet = tmp;
break;
}
}
//check if sheet null
if (xlWorksheet == null && sheets.Count > 0)
{
//try first one
xlWorksheet = xlWorkbook.Sheets[1];
//forgot quit
//xlWorkbook.Close(false);
//xlApp.Quit();
//return new InnerResult { Success = false, Message = Wording.CantFindRightColumns };
}
Excel.Range xlRange = xlWorksheet.UsedRange;
int rowCount = xlRange.Rows.Count;
int colCount = xlRange.Columns.Count;
Debug.WriteLine($"row={rowCount} col={colCount}");
#region get skip
var target = xlWorksheet.UsedRange.Find("Issue Date:");
if (target == null)
{
xlWorkbook.Close(false);
xlApp.Quit();
return(new InnerResult {
Success = false, Message = Wording.CantFindRightColumns
});
}
var skip = target.Row;
#endregion
#region check header
//Check header
var heads = xlWorksheet.Range[target, xlWorksheet.Cells[skip, Models.AdidasHeader.Length]];
var check = CheckHeader(heads, Models.AdidasHeader);
if (!check.Success)
{
xlWorkbook.Close(false);
xlApp.Quit();
return(new InnerResult {
Success = false, Message = check.Message
});
}
#endregion
#region toObject
object[,] data = xlRange.Value2;
DataTable dt = new DataTable();
// Create new Column in DataTable
for (int cCnt = 1; cCnt <= colCount; cCnt++)
{
var column = new DataColumn
{
DataType = Type.GetType("System.String"),
ColumnName = cCnt.ToString()
};
dt.Columns.Add(column);
// Create row for Data Table
for (int rCnt = skip + 1; rCnt <= rowCount; rCnt++)
{
string cellVal;
if (Models.DateTimeColume.Contains(cCnt))
{
try
{
cellVal = data[rCnt, cCnt] != null
? DateTime.FromOADate(Convert.ToDouble(data[rCnt, cCnt])).ToShortDateString()
: null;
}
catch (Exception)
{
cellVal = data[rCnt, cCnt]?.ToString();
}
}
else
{
cellVal = data[rCnt, cCnt]?.ToString();
}
DataRow row;
// Add to the DataTable
if (cCnt == 1)
{
row = dt.NewRow();
row[cCnt.ToString()] = cellVal;
dt.Rows.Add(row);
}
else
{
row = dt.Rows[rCnt - 1 - skip];
row[cCnt.ToString()] = cellVal;
}
}
}
#endregion
//close and release
xlWorkbook.Close(false);
workbooks.Close();
//quit and release
xlApp.Quit();
Marshal.ReleaseComObject(xlApp);
Console.WriteLine(@"Load Success");
return(new InnerResult {
Success = true, Data = dt
});
}
private string GetExcelSheetName(string pPath)
{
//打开一个Excel应用
_excelApp = new Excel.Application();
if (_excelApp == null)
{
throw new Exception("打开Excel应用时发生错误！");
}
_books = _excelApp.Workbooks;
//打开一个现有的工作薄
_book = _books.Add(pPath);
_sheets = _book.Sheets;
//选择第一个Sheet页
_sheet = (Excel._Worksheet)_sheets.get_Item(1);
string sheetName = _sheet.Name;
ReleaseCOM(_sheet);
ReleaseCOM(_sheets);
ReleaseCOM(_book);
ReleaseCOM(_books);
_excelApp.Quit();
ReleaseCOM(_excelApp);
return sheetName;
}
static void Main(string[] args)
{
Excel.Application xlApp = new Excel.Application();
Excel.Workbook xlWorkBook = xlApp.Workbooks.Open(@"D:\Github\SSENSE_Price_checker\data.xlsx");
Excel._Worksheet xlWorksheet = xlWorkBook.Sheets[1];
Excel.Range xlRange = xlWorksheet.UsedRange;
int rowCount = xlRange.Rows.Count;
int colCount = xlRange.Columns.Count;
List <bontique> buylist = new List <bontique>();
for (int i = 1; i <= rowCount; i++)
{
bontique tmp = new bontique()
{
row = i, url = xlRange.Cells[i, 1].Text, lowest = Convert.ToDouble(xlRange.Cells[i, 2].Text), highest = Convert.ToDouble(xlRange.Cells[i, 3].Text), last = Convert.ToDouble(xlRange.Cells[i, 4].Text), size = xlRange.Cells[i, 5].Text
};
buylist.Add(tmp);
}
IWebDriver driver = new FirefoxDriver(@"D:\Github\SSENSE_Price_checker");
foreach (bontique tmp1 in buylist)
{
/*
* using (WebClient client = new WebClient()) // WebClient class inherits IDisposable
* {
* System.Net.ServicePointManager.SecurityProtocol = System.Net.SecurityProtocolType.Tls12;
* string htmlCode = client.DownloadString(tmp1.url);
* int position = htmlCode.IndexOf("class=\"price\">");
* }
*/
driver.Url = tmp1.url;
IWebElement pricetag = driver.FindElement(By.XPath("//span[@class='price']"));
string ooo = pricetag.GetAttribute("innerHTML").ToString();
double price = Convert.ToDouble(ooo.Replace(" CAD", "").Replace("$", ""));
//check if size exist
if (driver.FindElements(By.XPath("//select[@id='size']")).Count() != 0)
{
IWebElement sizeelement = driver.FindElement(By.XPath("//select[@id='size']"));
string sizeString = sizeelement.Text;
string[] sizeList = sizeString.Split(
new[] { "\r\n", "\r", "\n" },
StringSplitOptions.None
);
//only xx left, sold out, normal
foreach (string tmpsize in sizeList)
{
if (tmpsize.Contains(tmp1.size))
{
if (tmpsize.Contains("Out"))
{
//sold out send notification
email(tmp1, 3);
}
else if (tmpsize.Contains("left"))
{
//only serveral left buybuybuy!
email(tmp1, 2);
}
else
{
//do nothing
}
}
}
}
if (driver.FindElements(By.XPath("//span[@class='text-no-transform']")).Count() != 0)
{
email(tmp1, 2);
}
xlRange.Cells[tmp1.row, 4].Value = price;
if (price < tmp1.lowest)
{
xlRange.Cells[tmp1.row, 2].Value = price;
tmp1.lowest = price;
email(tmp1, 1);
}
if (price > tmp1.highest)
{
xlRange.Cells[tmp1.row, 3].Value = price;
}
}
driver.Close();
driver.Quit();
xlWorkBook.Save();
xlWorkBook.Close(0);
xlApp.Quit();
}
public void ConvertToCsv()
{
string cellValu = "";
string tempValu = "";
Excel.Application xlApp = new Excel.Application();
Excel.Workbook xlWorkbook = xlApp.Workbooks.Open(filePath + fileName); // + "VizientImportTest");//"Terumo.xls");
Excel._Worksheet xlWorksheet = xlWorkbook.Sheets[1];
Excel.Range xlRange = xlWorksheet.UsedRange;
rowCount = xlRange.Rows.Count;
colCount = xlRange.Columns.Count;
if (trace)
{
lm.Write("Convert.ConvertToCsv");
}
try
{
for (int r = 1; r <= rowCount; r++)
{
for (int c = 1; c <= colCount; c++)
{
//new line
if (c == 1)
{
Console.Write("\r\n");
if (cellValu.Length > 0)
{
cellValu += Environment.NewLine;
}
}
//write the value to the console
if (xlRange.Cells[r, c] != null && xlRange.Cells[r, c].Value2 != null)
{
tempValu = RemoveCommas(xlRange.Cells[r, c].Value2.ToString());
//Console.WriteLine(xlRange.Cells[r, c].Value2.ToString() + "\t");
//tempValu = RemoveCommas(xlRange.Cells[r, c].Value2.ToString());
//if (tempValu.Length == 0)
// tempValu = "x";
//cellValu += tempValu + "|";
}
else
{
tempValu = ".";
}
Console.WriteLine(tempValu + "\t");
tempValu = RemoveCommas(tempValu);
cellValu += tempValu + "||";
}
}
fileName = "ImportFile.csv";
File.WriteAllText(filePath + fileName, cellValu);
xlApp.Workbooks.Close();
GC.Collect();
//GC.WaitForPendingFinalizers();
}
catch (Exception ex)
{
lm.Write("Convert.ConvertToCsv: " + ex.Message);
}
}
/**
* if no log file exists this method creates new spreadsheet
**/
private bool CreateWorkBook()
{
m_WorkBook = (Excel._Workbook)(m_XLApp.Workbooks.Add(Missing.Value));
m_Sheet = (Excel._Worksheet) m_WorkBook.ActiveSheet;
if (m_WorkBook == null || m_Sheet == null)
{
return false;
}
m_Sheet.Cells[1, 1] = "Date";
m_columnsMap = new Dictionary<string, int>();
foreach (KeyValuePair<string, ProjectData> entry in m_currProjectWarnings)
{
AddNewProjectColumn(entry.Key);
}
//addColumn("Total");
ApplyStyle();
return true;
}
public static bool DBDataToXls(string sqlstr, string path)
{
// Excel object references.
Excel.Application m_objExcel = null;
Excel.Workbooks m_objBooks = null;
Excel._Workbook m_objBook = null;
Excel.Sheets m_objSheets = null;
Excel._Worksheet m_objSheet = null;
Excel.Range m_objRange = null;
Excel.Font m_objFont = null;
//Excel.QueryTables m_objQryTables = null;
//Excel._QueryTable m_objQryTable = null;
// Frequenty-used variable for optional arguments.
object m_objOpt = System.Reflection.Missing.Value;
// Paths used by the sample code for accessing and storing data.
object m_strSampleFolder = "C:\\ExcelData\\";
m_objExcel = new Excel.Application();
m_objBooks = (Excel.Workbooks)m_objExcel.Workbooks;
m_objBook = (Excel._Workbook)(m_objBooks.Add(m_objOpt));
m_objSheets = (Excel.Sheets)m_objBook.Worksheets;
m_objSheet = (Excel._Worksheet)(m_objSheets.get_Item(1));
OleDbConnection conn = new OleDbConnection(DBHelper.OleConnectionString);
OleDbCommand cmd = new OleDbCommand(sqlstr, conn);
OleDbDataAdapter da = new OleDbDataAdapter(cmd);
DataSet ds = new DataSet();
DataTable dt = null;
int fieldCount_;
int rowsCount_;
object[] objHeader;
object[,] objData;
da.Fill(ds);
dt = ds.Tables[0];
fieldCount_ = dt.Columns.Count;
rowsCount_ = dt.Rows.Count;
if (rowsCount_ == 0)
{
return(false);
}
m_objRange = m_objSheet.get_Range(m_objSheet.Cells[1, 1], m_objSheet.Cells[1, fieldCount_]);
objHeader = new string[fieldCount_];
for (int i = 0; i < fieldCount_; i++)
{
objHeader[i] = dt.Columns[i].ColumnName;
}
m_objRange.set_Value(m_objOpt, objHeader);
m_objFont = m_objRange.Font;
m_objFont.Bold = true;
m_objFont.Size = 9;
m_objFont.Name = "MS Sans Serif";
//object[rowscount,columnscount]
objData = new object[rowsCount_, fieldCount_];
for (int i = 0; i < rowsCount_; i++)
{
for (int j = 0; j < fieldCount_; j++)
{
objData[i, j] = dt.Rows[i][j];
}
}
m_objRange = m_objSheet.get_Range("A2", m_objOpt);
m_objRange = m_objRange.get_Resize(rowsCount_, fieldCount_);
m_objRange.NumberFormatLocal = "@";
m_objRange.set_Value(m_objOpt, objData);
m_objFont = m_objRange.Font;
m_objFont.Size = 9;
m_objFont.Name = "MS Sans Serif";
m_objBook.SaveAs(path, m_objOpt, m_objOpt,
m_objOpt, m_objOpt, m_objOpt, Excel.XlSaveAsAccessMode.xlNoChange,
m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt);
m_objBook.Close(false, m_objOpt, m_objOpt);
m_objExcel.Quit();
return(true);
}
private void buttonSearch_Click(object sender, EventArgs e)
{
/* initiate duplicate checker with excel file */
if (xlWorkbook == null)
{
xlApp = new Excel.Application();
OpenFileDialog ofd = new OpenFileDialog();
ofd.Filter = "Excel Documents (*.xlsx)|*.xlsx";
ofd.ShowDialog();
xlWorkbook = xlApp.Workbooks.Open(ofd.FileName, 0, true, 5, "", "", true, Excel.XlPlatform.xlWindows, "\t", false, false, 0, true, 1, 0);
xlWorksheet = (Excel._Worksheet)xlWorkbook.Sheets[1];
}
/* Save settings */
SaveSettings();
checkListCategories.Items.Clear();
statusBotStrip.Text = "Status: Searching";
string[] countriesArray = new string[]{"United States", "Singapore", "Australia", "New Zealand"};
if (searchCity.Lines.Length == 0)
searchCity.Text = countriesArray[searchCountry.SelectedIndex];
for (int locationline = 0; locationline < searchCity.Lines.Length; locationline++)
{
for (int categoryline = 0; categoryline < searchCategory.Lines.Length; categoryline++)
{
if (searchGoogle.Checked)
{
/* Conduct first page search */
string searchCritera = generateSearchQuery(categoryline, locationline);
statusBotStrip.Text = "Status: Searching " + searchCritera + " on Google";
searchCritera = searchCritera.Replace(" ", "+");
string json = getMapList(searchCritera);
var jarray = JsonConvert.DeserializeObject<RootObjectPlaces>(json);
if (jarray.status == "OK")
{
/* Populate the company list */
populateGoogleCompanies(jarray, xlWorksheet);
/* Conduct next page search until reached end of search */
string nextPageToken = jarray.next_page_token;
while (nextPageToken != null)
{
json = getMapList(searchCritera + "&pagetoken=" + nextPageToken);
jarray = JsonConvert.DeserializeObject<RootObjectPlaces>(json);
/* Populate again */
populateGoogleCompanies(jarray, xlWorksheet);
nextPageToken = jarray.next_page_token;
}
}
else if (jarray.status == "ZERO_RESULTS")
textDebugger.AppendText("Google found no results searching:" + searchCritera + Environment.NewLine);
else
textDebugger.AppendText("Google Search Error: " + jarray.error_message + " when searching: "
+ searchCritera + Environment.NewLine);
}
saveDataSet();
if (searchYelp.Checked)
{
string searchCriteria = searchCategory.Lines[categoryline];
string search_Country = searchCountry.Text;
string searchArea = "";
searchArea = generateSearchQueryArea(locationline);
statusBotStrip.Text = "Status: Searching " + searchArea + " " + searchCriteria + " on Yelp";
var yelp = new Yelp(Config.Options);
var searchOpt = new SearchOptions();
searchOpt.GeneralOptions = new GeneralOptions() { term = searchCriteria, radius_filter = 50000, category_filter = "homeservices", sort = 1 };
searchOpt.LocationOptions = new LocationOptions() { location = searchArea };
searchOpt.LocaleOptions = new LocaleOptions() { cc = search_Country };
var task = yelp.Search(searchOpt);
int pages = (int)Math.Ceiling(task.Result.total / 20.0);
for (int p = 1; p <= pages; p++)
{
for (int i = 0; i <= 19; i++)
{
try
{
Business business = task.Result.businesses[i];
string companyName = business.name;
string companyAddress = String.Join(" ", business.location.address);
string companyCity = business.location.city == null ? "" : business.location.city;
string companyState = business.location.state_code == null ? "" : business.location.state_code;
string companyZip = business.location.postal_code == null ? "" : business.location.postal_code;
string companyCountry = business.location.country_code == null ? "" : business.location.country_code;
string companyPhone = business.phone == null ? "" : fixPhoneNumberFormat(business.phone);
if (!checkIfExistInCurrentList(companyName, companyPhone, "No website yet", "No email on 1st check"))
{
string companyWebsite = findYelpCompanyWebsite(task.Result.businesses[i].url);
bool companyHasPicturesOrPersonalWebsite = true;
if (companyWebsite == "No company website")
{
companyHasPicturesOrPersonalWebsite = doesYelpCompanyHavePictures();
companyWebsite = task.Result.businesses[i].url;
}
if (companyHasPicturesOrPersonalWebsite)
{
List<string> companyCategories = new List<string>();
foreach (string[] category in task.Result.businesses[i].categories)
companyCategories.Add(category[0]);
string companyHouzzSearch = generateHouzzSearchLink(replaceCompanyNamePTE(companyName),
searchArea.Remove(0, searchArea.LastIndexOfAny(new char[] { ',', ' ' }) + 1).Replace(" ", ""), true);
AddtoList(companyName, companyAddress, companyCity, companyState, companyZip, companyCountry,
companyPhone, "No email", "", companyWebsite, companyHouzzSearch, companyCategories);
}
}
}
catch { }
}
var searchOptions = new SearchOptions();
searchOptions.GeneralOptions = new GeneralOptions()
{
term = searchCriteria,
offset = (p * 20),
radius_filter = 50000,
category_filter = "homeservices",
sort = 1 //distance
};
searchOptions.LocationOptions = new LocationOptions() { location = searchArea };
searchOptions.LocaleOptions = new LocaleOptions() { cc = search_Country };
task = yelp.Search(searchOptions);
}
}
saveDataSet();
if (searchFacebook.Checked)
{
string searchCriteria = searchCategory.Lines[categoryline];
string searchArea = generateSearchQueryArea(locationline);
statusBotStrip.Text = "Status: Searching " + searchArea + " " + searchCriteria + " on Facebook";
string[] jsonFacebookSearchResults = new string[5];
jsonFacebookSearchResults[1] = getFacebookSearchList(searchCriteria, searchArea, "page", false);
jsonFacebookSearchResults[2] = getFacebookSearchList(searchCriteria, searchArea, "page", true);
jsonFacebookSearchResults[3] = getFacebookSearchList(searchCriteria, searchArea, "place", false);
jsonFacebookSearchResults[4] = getFacebookSearchList(searchCriteria, searchArea, "place", true);
for (int i = 1; i <= 4; i++)
{
var jarray = JsonConvert.DeserializeObject<RootObjectFacebook>(jsonFacebookSearchResults[i]);
if( jarray.data != null & jarray.data.Count > 0 )
populateFacebookCompanies(jarray);
else if( jarray.error != null)
textDebugger.AppendText("Facebook search error: " + jarray.error.message + Environment.NewLine);
}
}
saveDataSet();
if (searchYellowPages.Checked)
{
/* Conduct first page search */
string searchCriteria = searchCategory.Lines[categoryline].Replace(" ", "+");
string search_Country = searchCountry.Text;
string searchArea = generateSearchQueryArea(locationline);
statusBotStrip.Text = "Status: Searching " + searchArea + " " + searchCriteria + " on YellowPages";
for (int i = 1; i <= 30; i++) /* 30 pages even if they do not reach 30 */
{
string json = getYPList(searchCriteria, searchArea, i);
var jarray = JsonConvert.DeserializeObject<RootObjectYP>(json);
if (jarray.searchResult.metaProperties.errorCode == "")
{
// Populate the company list
if (jarray.searchResult.metaProperties.listingCount > 0)
{
populateYPCompanies(jarray, xlWorksheet);
}
else
{
textDebugger.AppendText("Error on YP page " + i.ToString() + ": No results found searching: "
+ searchArea + " " + searchCriteria + Environment.NewLine);
break;
}
}
else
textDebugger.AppendText("Error on YP page " + i.ToString() + ": " + jarray.searchResult.metaProperties.message
+ Environment.NewLine);
}
}
saveDataSet();
if( searchFactual.Checked )
{
string searchCriteria = searchCategory.Lines[categoryline];
string search_Country = searchCountry.Text;
string search_City = "", search_State = "";
Factual factual = new Factual(apiFactualKey, apiFactualSecret);
Query q = new Query().SearchExact(searchCriteria);
if (search_Country != "US")
{
q.And(q.Field("country").Equal(search_Country), q.Limit(50));
statusBotStrip.Text = "Status: Searching" + search_Country + " " + searchCriteria + " on Factual";
}
if (search_Country == "US")
{
q.And(q.Field("country").Equal(search_Country), q.Field("region").Equal(search_State), q.Field("locality").Equal(search_City), q.Limit(50));
search_City = searchCity.Lines[locationline];
search_State = searchState.Text;
statusBotStrip.Text = "Status: Searching " + search_Country + " " + search_City
+ " " + searchCriteria + " on Factual";
}
/* if (searchCategoryFilters != "")
q.Field("category_labels").Includes(searchCategoryFilters); */
int page = 1;
try
{
var json = factual.Fetch("places", q);
var jarray = JsonConvert.DeserializeObject<RootObjectFactual>(json);
while (jarray.status == "ok" & jarray.response.data.Count > 0)
{
/* Populate the company list */
foreach (Datum item in jarray.response.data)
{
string companyName = item.name;
string companyAddress = item.address == null ? "" : item.address;
string companyCity = item.locality == null ? "" : item.locality;
string companyState = item.region == null ? "" : item.region;
string companyCountry = item.country == null ? "" : item.country;
string companyZip = item.postcode == null ? "" : item.postcode;
string companyPhone = item.tel == null ? "" : fixPhoneNumberFormat(item.tel);
string companyEmail = item.email == null ? "no email" : item.email;
string companyWebsite = item.website;
string companyHouzzSearch = generateHouzzSearchLink(replaceCompanyNamePTE(companyName), companyState, true);
List<string> companyCategories = item.category_labels == null ? new List<string>() : item.category_labels[0];
//textDebugger.AppendText(companyName + Environment.NewLine);
AddtoList(companyName, companyAddress, companyCity, companyState, companyZip, companyCountry,
companyPhone, companyEmail, "", companyWebsite, companyHouzzSearch, companyCategories);
}
page++;
int pageOffset = (page - 1) * 50;
Query qnew = new Query().SearchExact(searchCriteria);
if ( searchCountry.Text == "US" || searchCountry.Text == "AU")
{
json = factual.Fetch("places", new Query()
.SearchExact(searchCriteria)
.Field("country").Equal(search_Country)
.Field("region").Equal(search_State)
.Field("locality").Equal(search_City)
.Limit(50)
.Offset(pageOffset));
}
else if (searchCountry.Text != "US" & searchCountry.Text != "AU")
{
json = factual.Fetch("places", new Query()
.SearchExact(searchCriteria)
.Field("country").Equal(search_Country)
.Limit(50)
.Offset(pageOffset));
}
jarray = JsonConvert.DeserializeObject<RootObjectFactual>(json);
}
}
catch (FactualApiException ex)
{
textDebugger.AppendText("Factual Requested URL: " + ex.Url + Environment.NewLine);
textDebugger.AppendText("Factual Error Status Code: " + ex.StatusCode + Environment.NewLine); ;
textDebugger.AppendText("Factual Error Response Message: " + ex.Response + Environment.NewLine); ;
if (ex.StatusCode.ToString().Contains("RequestedRangeNotSatisfiable"))
textDebugger.AppendText("Factual reached end of results on page " + (page - 1).ToString() + Environment.NewLine);
}
}
saveDataSet();
if( searchSensis.Checked )
{
var searcher = new SsapiSearcher(searchEndPoint, apiSensisKey);
// Perform a search and check the response
var searchResponse = searcher.SearchFor(searchCategory.Text, searchCity.Text, searchState.Text, 1); /* page 1 */
if (searchResponse.code < 200 || searchResponse.code > 299)
textDebugger.AppendText("Search failed - Error " + searchResponse.code + ": " + searchResponse.message + Environment.NewLine);
else
{
textDebugger.AppendText("Total results found: " + searchResponse.totalResults.ToString() + Environment.NewLine);
textDebugger.AppendText("Total pages: " + searchResponse.totalPages.ToString() + Environment.NewLine);
for (int page = 1; page < searchResponse.totalPages; page++)
{
// Display the results
foreach (var result in searchResponse.results)
{
string companyWebsite = getSensisContactComponents(result.primaryContacts, "URL");
if (companyWebsite != "")
{
string companyName = result.name;
string companyAddress = "", companyCity = "", companyState = "", companyZip = "";
if (result.primaryAddress != null)
{
companyAddress = result.primaryAddress.addressLine == null ? "" : result.primaryAddress.addressLine;
companyCity = result.primaryAddress.suburb == null ? "" : result.primaryAddress.suburb;
companyState = result.primaryAddress.state == null ? "" : result.primaryAddress.state;
companyZip = result.primaryAddress.postcode == null ? "" : result.primaryAddress.postcode;
}
string companyCountry = "AU";
string companyEmail = getSensisContactComponents(result.primaryContacts, "EMAIL");
string companyPhone = fixPhoneNumberFormat(getSensisContactComponents(result.primaryContacts, "PHONE"));
if (companyPhone == "") companyPhone = fixPhoneNumberFormat(getSensisContactComponents(result.primaryContacts, "MOBILE"));
string companyContactUs = companyEmail.Contains("@") ? "" : getSensisExternalLinksComponents(result.externalLinks, "Contact Us");
string companyHouzzSearch = generateHouzzSearchLink(replaceCompanyNamePTE(companyName), companyState, true);
List<string> companyCategories = result.categories == null ? new List<string>() : new List<string>(new string[] { result.categories[0].name });
//textDebugger.AppendText(companyName + companyAddress + companyCity + companyState + companyZip + companyEmail + companyPhone + companyWebsite + Environment.NewLine);
AddtoList(companyName, companyAddress, companyCity, companyState, companyZip, companyCountry, companyPhone,
companyEmail, companyContactUs, companyWebsite, companyHouzzSearch, companyCategories);
}
}
searchResponse = searcher.SearchFor(searchCategory.Text, searchCity.Text, searchState.Text, page);
}
}
}
/* At the end of each category, we save the data found into an xml file*/
saveDataSet();
}
}
statusBotStrip.Text = "Status: Done";
/* Save settings */
SaveSettings();
}
private void exportexcel()
{
DateTime dt = DateTime.Now;
string Name = cbthongke.Text + " (" + dt.Hour + "h" + dt.Minute + " " + dt.Day + "." + dt.Month + "." + dt.Year + ")";
string LinkSave = System.Windows.Forms.Application.StartupPath + @"\..\Reports\";
// tạo đối tượng
SaveFileDialog fsave = new SaveFileDialog();
fsave.InitialDirectory = LinkSave;
fsave.Filter = "(tất cả các tệp)|*.*|(các tệp tin)|*.xlsx";
fsave.FileName = Name + ".xlsx";
if (fsave.ShowDialog() == System.Windows.Forms.DialogResult.Cancel)
{
}
else
{
if (fsave.FileName != "")
{
//khởi tạo excel
Microsoft.Office.Interop.Excel._Application app = new Microsoft.Office.Interop.Excel.Application();
//Khởi tại WorkBook
Microsoft.Office.Interop.Excel._Workbook workbook = app.Workbooks.Add(Type.Missing);
//Khởi tạo Worksheet
Microsoft.Office.Interop.Excel._Worksheet worksheet = null;
worksheet = workbook.Sheets["Sheet1"];
worksheet = workbook.ActiveSheet;
app.Visible = false;
DateTime time1 = datetimebd.Value;
DateTime time2 = datetimekt.Value;
string dt1 = time1.ToString("dd/MM/yyyy");
string dt2 = time2.ToString("dd/MM/yyyy");
//Đổ dữ liệu vào sheet
if (cbthongke.Text == "Báo cáo khách hàng")
{
//worksheet.Range["E4", "E100"].NumberFormat("Text");
worksheet.Cells[1, 1] = "BÁO CÁO KHÁCH HÀNG";
worksheet.Cells[2, 1] = "Nhân Viên: " + frmQLCHG.Ten;
worksheet.Cells[3, 1] = "MãKH";
worksheet.Cells[3, 2] = "Họ Và Tên";
worksheet.Cells[3, 3] = "Năm Sinh";
worksheet.Cells[3, 4] = "Giới Tính";
worksheet.Cells[3, 5] = "Số Điện Thoại";
worksheet.Cells[3, 6] = "Địa Chỉ";
worksheet.Cells[3, 7] = "Loại KH";
worksheet.Cells[3, 8] = "Tiền Tích Lũy";
for (int i = 0; i < dataviewthongke.RowCount; i++)
{
for (int j = 0; j < 8; j++)
{
worksheet.Cells[i + 4, j + 1] = dataviewthongke.Rows[i].Cells[j].Value;
worksheet.Cells[i + 5, 2] = "VIP 0 = " + txtvip0.Text + "";
worksheet.Cells[i + 6, 2] = "VIP 1 = " + txtvip1.Text + "";
worksheet.Cells[i + 7, 2] = "VIP 2 = " + txtvip2.Text + "";
worksheet.Cells[i + 8, 2] = "VIP 3 = " + txtvip3.Text + "";
worksheet.Cells[i + 9, 2] = "Tổng KH = " + txttongkh.Text + "";
//Kẻ Bảng
worksheet.Range["A3", "H" + (i + 4)].Borders.LineStyle = 1;
worksheet.Range["A4", "H" + (i + 4)].Borders.LineStyle = 1;
//Định dạng các dòng text;
worksheet.Range["A4", "A" + (i + 4)].HorizontalAlignment = 3;
worksheet.Range["B4", "B" + (i + 4)].HorizontalAlignment = 3;
worksheet.Range["E4", "E" + (i + 4)].HorizontalAlignment = 3;
worksheet.Range["F4", "F" + (i + 4)].HorizontalAlignment = 3;
worksheet.Range["G4", "G" + (i + 4)].HorizontalAlignment = 3;
worksheet.Range["H4", "H" + (i + 4)].HorizontalAlignment = 3;
}
}
//Định dạng trang:
worksheet.PageSetup.Orientation = Microsoft.Office.Interop.Excel.XlPageOrientation.xlPortrait;
worksheet.PageSetup.PaperSize = Microsoft.Office.Interop.Excel.XlPaperSize.xlPaperA4;
worksheet.PageSetup.LeftMargin = 0;
worksheet.PageSetup.RightMargin = 0;
worksheet.PageSetup.TopMargin = 0;
worksheet.PageSetup.BottomMargin = 0;
//Định dạng cột
worksheet.Range["A1"].ColumnWidth = 8.57;
worksheet.Range["B1"].ColumnWidth = 30;
worksheet.Range["C1"].ColumnWidth = 12;
worksheet.Range["D1"].ColumnWidth = 11.86;
worksheet.Range["E1"].ColumnWidth = 17.57;
worksheet.Range["F1"].ColumnWidth = 15;
worksheet.Range["G1"].ColumnWidth = 10.57;
worksheet.Range["H1"].ColumnWidth = 18;
//Định dạng font
worksheet.Range["A1", "H100"].Font.Name = "Times New Roman";
worksheet.Range["A1", "H1"].Font.Size = 20;
worksheet.Range["A2", "H100"].Font.Size = 14;
worksheet.Range["A1", "H1"].MergeCells = true;
worksheet.Range["A1", "H1"].Font.Bold = true;
worksheet.Range["A3", "H3"].Font.Bold = true;
//Định dạng các dòng text:
int abc = dataviewthongke.RowCount + 2;
string def = "H" + abc;
worksheet.Range["A1", "H1"].HorizontalAlignment = 3;
worksheet.Range["A3", def].HorizontalAlignment = 3;
}
if (cbthongke.Text == "Báo cáo doanh thu, lợi nhuận")
{
worksheet.Cells[1, 1] = "BÁO CÁO DOANH THU, LỢI NHUẬN";
worksheet.Cells[2, 1] = "Nhân Viên: " + frmQLCHG.Ten;
worksheet.Cells[2, 6] = "Ngày: " + dt1 + " - " + dt2;
worksheet.Cells[3, 1] = "MãHĐ";
worksheet.Cells[3, 2] = "MãTK";
worksheet.Cells[3, 3] = "MãKH";
worksheet.Cells[3, 4] = "Ngày lập";
worksheet.Cells[3, 5] = "Tổng Tiền";
worksheet.Cells[3, 6] = "Chiết Khấu";
worksheet.Cells[3, 7] = "Thuế";
worksheet.Cells[3, 8] = "Tổng Tiền Gốc";
for (int i = 0; i < dataviewthongke.RowCount; i++)
{
for (int j = 0; j < 8; j++)
{
//worksheet.Cells[i + 8, 1] = i ;
worksheet.Cells[i + 4, j + 1] = dataviewthongke.Rows[i].Cells[j].Value;
worksheet.Cells[i + 5, 1] = "";
worksheet.Cells[i + 6, 1] = "Tổng Doanh Thu : " + txtdoanhthu.Text + " VND";
worksheet.Cells[i + 7, 1] = "Lợi Nhuận : " + txtloinhuan.Text + " VND";
//Kẻ Bảng
worksheet.Range["A3", "H" + (i + 4)].Borders.LineStyle = 1;
worksheet.Range["A4", "H" + (i + 4)].Borders.LineStyle = 1;
//Định dạng các dòng text;
worksheet.Range["A4", "A" + (i + 4)].HorizontalAlignment = 3;
worksheet.Range["B4", "B" + (i + 4)].HorizontalAlignment = 3;
worksheet.Range["E4", "E" + (i + 4)].HorizontalAlignment = 3;
worksheet.Range["F4", "F" + (i + 4)].HorizontalAlignment = 3;
worksheet.Range["G4", "G" + (i + 4)].HorizontalAlignment = 3;
worksheet.Range["H4", "H" + (i + 4)].HorizontalAlignment = 3;
}
}
//Định dạng trang:
worksheet.PageSetup.Orientation = Microsoft.Office.Interop.Excel.XlPageOrientation.xlPortrait;
worksheet.PageSetup.PaperSize = Microsoft.Office.Interop.Excel.XlPaperSize.xlPaperA4;
worksheet.PageSetup.LeftMargin = 0;
worksheet.PageSetup.RightMargin = 0;
worksheet.PageSetup.TopMargin = 0;
worksheet.PageSetup.BottomMargin = 0;
//Định dạng cột
worksheet.Range["A1"].ColumnWidth = 8.57;
worksheet.Range["B1"].ColumnWidth = 8.57;
worksheet.Range["C1"].ColumnWidth = 12;
worksheet.Range["D1"].ColumnWidth = 21.29;
worksheet.Range["E1"].ColumnWidth = 17.57;
worksheet.Range["F1"].ColumnWidth = 15;
worksheet.Range["G1"].ColumnWidth = 10.57;
worksheet.Range["H1"].ColumnWidth = 18.86;
//Định dạng font
worksheet.Range["A1", "H100"].Font.Name = "Times New Roman";
worksheet.Range["A1", "H1"].Font.Size = 20;
worksheet.Range["A2", "H100"].Font.Size = 14;
worksheet.Range["A1", "H1"].MergeCells = true;
worksheet.Range["A1", "H1"].Font.Bold = true;
worksheet.Range["A3", "H3"].Font.Bold = true;
//Định dạng các dòng text:
int abc = dataviewthongke.RowCount + 3;
string def = "H" + abc;
worksheet.Range["A1", "G1"].HorizontalAlignment = 3;
worksheet.Range["A3", def].HorizontalAlignment = 3;
}
if (cbthongke.Text == "Báo cáo số lượng tồn")
{
worksheet.Cells[1, 1] = "BÁO CÁO SỐ LƯỢNG TỒN";
worksheet.Cells[2, 1] = "Nhân Viên: " + frmQLCHG.Ten;
worksheet.Cells[3, 1] = "MãG";
worksheet.Cells[3, 2] = "TênG";
worksheet.Cells[3, 3] = "LoạiG";
worksheet.Cells[3, 4] = "TLGao";
worksheet.Cells[3, 5] = "Giá Nhập";
worksheet.Cells[3, 6] = "Giá Bán";
worksheet.Cells[3, 7] = "Xuất Xứ";
for (int i = 0; i < dataviewthongke.RowCount; i++)
{
for (int j = 0; j < 7; j++)
{
//worksheet.Cells[i + 8, 1] = i ;
worksheet.Cells[i + 4, j + 1] = dataviewthongke.Rows[i].Cells[j].Value;
worksheet.Cells[i + 5, 1] = "";
//Kẻ Bảng
worksheet.Range["A3", "G" + (i + 4)].Borders.LineStyle = 1;
worksheet.Range["A4", "G" + (i + 4)].Borders.LineStyle = 1;
//Định dạng các dòng text:
worksheet.Range["A3", "A" + (i + 4)].HorizontalAlignment = 3;
worksheet.Range["B3", "B" + (i + 4)].HorizontalAlignment = 3;
worksheet.Range["E3", "E" + (i + 4)].HorizontalAlignment = 3;
worksheet.Range["F3", "F" + (i + 4)].HorizontalAlignment = 3;
worksheet.Range["G3", "G" + (i + 4)].HorizontalAlignment = 3;
}
}
//Định dạng trang:
worksheet.PageSetup.Orientation = Microsoft.Office.Interop.Excel.XlPageOrientation.xlPortrait;
worksheet.PageSetup.PaperSize = Microsoft.Office.Interop.Excel.XlPaperSize.xlPaperA4;
worksheet.PageSetup.LeftMargin = 0;
worksheet.PageSetup.RightMargin = 0;
worksheet.PageSetup.TopMargin = 0;
worksheet.PageSetup.BottomMargin = 0;
//Định dạng cột
worksheet.Range["A1"].ColumnWidth = 5.55;
worksheet.Range["B1"].ColumnWidth = 10;
worksheet.Range["C1"].ColumnWidth = 22;
worksheet.Range["D1"].ColumnWidth = 15;
worksheet.Range["E1"].ColumnWidth = 16;
worksheet.Range["F1"].ColumnWidth = 13.35;
worksheet.Range["G1"].ColumnWidth = 15;
//Định dạng font
worksheet.Range["A1", "G100"].Font.Name = "Times New Roman";
worksheet.Range["A1", "G1"].Font.Size = 20;
worksheet.Range["A2", "G100"].Font.Size = 14;
worksheet.Range["A1", "G1"].MergeCells = true;
worksheet.Range["A1", "G1"].Font.Bold = true;
worksheet.Range["A3", "G3"].Font.Bold = true;
//Định dạng các dòng text:
int abc = dataviewthongke.RowCount + 3;
string def = "G" + abc;
worksheet.Range["A1", "G1"].HorizontalAlignment = 3;
worksheet.Range["A3", def].HorizontalAlignment = 3;
}
if (cbthongke.Text == "Báo cáo gạo sắp hết")
{
worksheet.Cells[1, 1] = "BÁO CÁO SỐ GẠO SẮP HẾT";
worksheet.Cells[2, 1] = "Nhân Viên: " + frmQLCHG.Ten;
worksheet.Cells[3, 1] = "MãG";
worksheet.Cells[3, 2] = "TênG";
worksheet.Cells[3, 3] = "LoạiG";
worksheet.Cells[3, 4] = "TLGạo";
worksheet.Cells[3, 5] = "Giá Nhập";
worksheet.Cells[3, 6] = "Giá Bán";
worksheet.Cells[3, 7] = "Xuất Xứ";
for (int i = 0; i < dataviewthongke.RowCount; i++)
{
for (int j = 0; j < 7; j++)
{
//worksheet.Cells[i + 8, 1] = i ;
worksheet.Cells[i + 4, j + 1] = dataviewthongke.Rows[i].Cells[j].Value;
worksheet.Cells[i + 5, 1] = "";
//Kẻ Bảng
worksheet.Range["A3", "G" + (i + 4)].Borders.LineStyle = 1;
worksheet.Range["A4", "G" + (i + 4)].Borders.LineStyle = 1;
//Định dạng các dòng text:
worksheet.Range["A3", "A" + (i + 4)].HorizontalAlignment = 3;
worksheet.Range["B3", "B" + (i + 4)].HorizontalAlignment = 3;
worksheet.Range["E3", "E" + (i + 4)].HorizontalAlignment = 3;
worksheet.Range["F3", "F" + (i + 4)].HorizontalAlignment = 3;
worksheet.Range["G3", "G" + (i + 4)].HorizontalAlignment = 3;
}
}
//Định dạng trang:
worksheet.PageSetup.Orientation = Microsoft.Office.Interop.Excel.XlPageOrientation.xlPortrait;
worksheet.PageSetup.PaperSize = Microsoft.Office.Interop.Excel.XlPaperSize.xlPaperA4;
worksheet.PageSetup.LeftMargin = 0;
worksheet.PageSetup.RightMargin = 0;
worksheet.PageSetup.TopMargin = 0;
worksheet.PageSetup.BottomMargin = 0;
//Định dạng cột
worksheet.Range["A1"].ColumnWidth = 5.55;
worksheet.Range["B1"].ColumnWidth = 10;
worksheet.Range["C1"].ColumnWidth = 22;
worksheet.Range["D1"].ColumnWidth = 15;
worksheet.Range["E1"].ColumnWidth = 16;
worksheet.Range["F1"].ColumnWidth = 13.35;
worksheet.Range["G1"].ColumnWidth = 15;
//Định dạng font
worksheet.Range["A1", "G100"].Font.Name = "Times New Roman";
worksheet.Range["A1", "G1"].Font.Size = 20;
worksheet.Range["A2", "G100"].Font.Size = 14;
worksheet.Range["A1", "G1"].MergeCells = true;
worksheet.Range["A1", "G1"].Font.Bold = true;
worksheet.Range["A3", "G3"].Font.Bold = true;
//Định dạng các dòng text:
int abc = dataviewthongke.RowCount + 3;
string def = "G" + abc;
worksheet.Range["A1", "G1"].HorizontalAlignment = 3;
worksheet.Range["A3", def].HorizontalAlignment = 3;
}
worksheet.SaveAs(fsave.FileName);
String Message = "Xuất " + cbthongke.Text + " thành công";
MessageBox.Show(Message, "THÔNG BÁO", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
}
}
private void button10_Click(object sender, EventArgs e)
{
if (xlWorkbook == null)
{
xlApp = new Excel.Application();
OpenFileDialog ofd = new OpenFileDialog();
ofd.Filter = "Excel Documents (*.xlsx)|*.xlsx";
ofd.ShowDialog();
xlWorkbook = xlApp.Workbooks.Open(ofd.FileName, 0, true, 5, "", "", true, Excel.XlPlatform.xlWindows, "\t", false, false, 0, true, 1, 0);
xlWorksheet = (Excel._Worksheet)xlWorkbook.Sheets[1];
}
finalDuplicateCheck(Convert.ToInt32(textDupeCheckRowFrom.Text)-1, Convert.ToInt32(textDupeCheckRowFrom.Text)-1);
}
/// <summary>
/// 设置连续区域的字体名称
/// </summary>
/// <param name="CurSheet">Worksheet</param>
/// <param name="objStartCell">开始单元格</param>
/// <param name="objEndCell">结束单元格</param>
/// <param name="fontname">字体名称 隶书、仿宋_GB2312等</param>
public void SetFontName(Excel._Worksheet CurSheet, object objStartCell, object objEndCell, string fontname)
{
CurSheet.get_Range(objStartCell, objEndCell).Font.Name = fontname;
}
/// <summary>
/// 创建一个Excel程序实例
/// </summary>
private void CreateExcelRef()
{
_excelApp = new Excel.Application();
_books = (Excel.Workbooks)_excelApp.Workbooks;
_book = (Excel._Workbook)(_books.Add(_optionalValue));
_sheets = (Excel.Sheets)_book.Worksheets;
_sheet = (Excel._Worksheet)(_sheets.get_Item(1));
}
/// <summary>
/// 设置连续区域的字体为黑体
/// </summary>
/// <param name="CurSheet">Worksheet</param>
/// <param name="objStartCell">开始单元格</param>
/// <param name="objEndCell">结束单元格</param>
public void SetBold(Excel._Worksheet CurSheet, object objStartCell, object objEndCell)
{
CurSheet.get_Range(objStartCell, objEndCell).Font.Bold = true;
}
//Method to initialize opening Excel
private void ExcelInit(String path, out List<Product> idinfo)
{
xlApp = new Excel.Application();
string dsSheet = "datasheet";
string idinfoSheet = "idinfo";
idinfo = null;
if (System.IO.File.Exists(path)) {
// then go and load this into excel
xlWorkBook = xlApp.Workbooks.Open(path,
0, true, 5, "", "", true, Excel.XlPlatform.xlWindows, "\t",
false, false, 0, true, 1, 0);
//read info datasheet first to generate product specs list
info_xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(idinfoSheet);
idinfo = ReadIdInfoIntoDataTable(info_xlWorkSheet);
//read datasheet and load the data into product specs list
//ds_xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(dsSheet);
//ReadDatasheetIntoDataTable(ds_xlWorkSheet);
} else {
MessageBox.Show("Unable to open excel file!");
System.Runtime.InteropServices.Marshal.ReleaseComObject(xlApp);
xlApp = null;
System.Windows.Forms.Application.Exit();
}
}
/// <summary>
/// 设置连续区域水平居右
/// </summary>
/// <param name="CurSheet">Worksheet</param>
/// <param name="objStartCell">开始单元格</param>
/// <param name="objEndCell">结束单元格</param>
public void SetHAlignRight(Excel._Worksheet CurSheet, object objStartCell, object objEndCell)
{
CurSheet.get_Range(objStartCell, objEndCell).HorizontalAlignment = Excel.XlHAlign.xlHAlignRight;
}
