Prapop (Note & Knowledge): 2020

Wednesday, September 2, 2020

EXCEL EXPERT !!!!!!!!!!!! Function AGGREGATE

AGGREGATE(function,option,Array,k)

AGGREGATE(function,option,ref1,ref2...)

AGGREGATE(function,option,Area*Area=เงื่อนไข,ref)

=AGGREGATE(14,7,I:I*((J:J=2)),1)

14 Large

7 no error ...

I:I พื้นที่ค้นหา

J:J=2 เงื่อนไข

1 not know

Wednesday, August 26, 2020

Clear Pivot cache in excel : paste it on thisworkbook and refresh connection

Private Sub Workbook_Open()

Dim xPt As PivotTable

Dim xWs As Worksheet

Dim xPc As PivotCache

Application.ScreenUpdating = False

ThisWorkbook.RefreshAll

For Each xWs In ActiveWorkbook.Worksheets

For Each xPt In xWs.PivotTables

xPt.PivotCache.MissingItemsLimit = xlMissingItemsNone

Next xPt

Next xWs

For Each xPc In ActiveWorkbook.PivotCaches

On Error Resume Next

xPc.Refresh

Next xPc

Application.ScreenUpdating = True

End Sub

Thursday, July 30, 2020

Java cutter v2020 07 07

/*
* To change this license header, choose License Headers in Project Properties.
* To change this template file, choose Tools | Templates
* and open the template in the editor.
*/
package com.matco.pdfcutter;

/**
*
* @author prapop
*/

import com.itextpdf.text.Document;
import com.itextpdf.text.DocumentException;
import com.itextpdf.text.Image;
import com.itextpdf.text.Rectangle;
import com.itextpdf.text.pdf.BarcodeQRCode;
import com.itextpdf.text.pdf.PdfContentByte;
import com.itextpdf.text.pdf.PdfImportedPage;
import com.itextpdf.text.pdf.PdfReader;
import com.itextpdf.text.pdf.PdfWriter;
import com.itextpdf.text.pdf.parser.PdfReaderContentParser;
import com.itextpdf.text.pdf.parser.SimpleTextExtractionStrategy;
import com.itextpdf.text.pdf.parser.TextExtractionStrategy;
import java.io.BufferedReader;
import java.io.File;
import java.io.FileOutputStream;
import java.io.FileReader;
import java.io.IOException;
import java.io.PrintStream;
import java.util.ArrayList;

public class Pdfcutter
{
public static void main(String[] args)
throws IOException, DocumentException
{
String way = args[0];
if (way.equals("0"))
{
System.out.println("splite " + way);
new Pdfcutter().splite(args);
}
if (way.equals("1"))
{
/*remove blank page and add barcode TMT*/
System.out.println("remove blank " + way);
new Pdfcutter().manipulatePdf(args);
}
if (way.equals("2"))
{
/*remove blank page and add barcode TMT Special*/
System.out.println("remove blank " + way);
new Pdfcutter().manipulatePdf2(args);
}
if (way.equals("3"))
{
/*remove blank page and add barcode Nissan*/
System.out.println("remove blank " + way);
new Pdfcutter().manipulatePdf(args);
}
if (way.equals("4"))
{
/*TPCAP R1,R2*/
System.out.println("splite TPCAP " + way);
new Pdfcutter().splite2(args);
}
}

public void manipulatePdf(String[] args)
throws DocumentException, IOException
{
String SRC = args[1];
String DEST = args[2];
String textFile = args[3];
String textFileoutput;

Rectangle pagesize = new Rectangle(595.0F, 280.0F);
Document document = new Document(pagesize, 36.0F, 72.0F, 108.0F, 180.0F);
PdfWriter writer = PdfWriter.getInstance(document, new FileOutputStream(DEST));
document.open();
PdfReader reader = new PdfReader(SRC);
int n = reader.getNumberOfPages();

PdfReaderContentParser parser = new PdfReaderContentParser(reader);

File file = new File(textFile);
int y = 0;
int xmax=0;
int boxmax =0;

textFileoutput=textFile.substring(0,textFile.length()-10)+"log.txt";
System.out.println("filename "+textFileoutput);
File fileoutput = new File(textFileoutput);
FileOutputStream fos = new FileOutputStream(fileoutput);

// Create new print stream for file.
PrintStream ps = new PrintStream(fos);

// Set file print stream.
System.setOut(ps);

try
{
BufferedReader br = new BufferedReader(new FileReader(file));

int x = 0;
int[][][] datax = new int[100][50][180];
String line;
int k = 0;
String orderlist="1234";
int zz=1;

//for packing plant
String MM=""; //P1
int mark;

String order1;
while ((line = br.readLine()) != null)
{
y+=1;
while (y > 0)
{
x += 1;
TextExtractionStrategy strategy = (TextExtractionStrategy)parser.processContent(x, new SimpleTextExtractionStrategy());
String str = strategy.getResultantText();
str = str.toUpperCase();
//System.out.println("readpage "+str);
ArrayList order = new ArrayList();
ArrayList skid = new ArrayList();
ArrayList box = new ArrayList();
String[] name = str.split("\\\r?\\\n");
mark=0;
for(int i = 0; i < name.length; i++) {
int c;
System.out.println("xxx "+name[i]+" | "+name.length);
c = name[i].indexOf("ORDER!NO.");
c = c+1+name[i].indexOf("ORDER NO.");
//System.out.println(c+" order "+name[i+1]);
if (c==0)
{
order.add(name[i+1].trim()+name[i+3].trim()+name[i-10].trim());
order1=name[i+1].trim()+name[i+3].trim()+name[i-10].trim();
System.out.println(c+" order "+name[i+1]);
if(orderlist.equals(order1))
{
zz=zz;
}
else
{
//System.out.println("order1 "+order1+" orderlist "+orderlist);
zz=1;
k++;
orderlist=order1;
//System.out.println("order1 "+order1+" orderlist "+orderlist);
}
}
c = name[i].indexOf("D-LANE");
if (c==0)
{
mark=1;
MM=name[i-10].trim();
}
c = name[i].indexOf("SKID");
if (c==0)
{
skid.add(name[i+1].trim());
//System.out.println(c+" skid "+name[i+1]);
if(mark==1)
{
System.out.println("yyy "+i+" | "+name.length);
order.add(name[i+5].trim()+MM);
order1=name[i+5].trim()+MM;
System.out.println(c+" xxxxxxxxxxorder "+name[i+1]);
if(orderlist.equals(order1))
{
zz=zz;
}
else
{
System.out.println("order1 "+order1+" orderlist "+orderlist);
zz=1;
k++;
orderlist=order1;
System.out.println("order1 "+order1+" orderlist "+orderlist);
}
}
}
c = name[i].indexOf("BOX");
if (c==0)
{
if (name[i+1].trim().equals("N.A."))
{
box.add(Integer.toString(zz));
}
else
{
box.add(name[i+1].trim());
}
//System.out.println(c+" box "+name[i+1]);
}
}
//System.out.println("end "+index1+"|"+index2+"|"+index3);
//System.out.println("end "+index4+"|"+index5+"|"+index6);
//System.out.println("end "+skidb.trim()+"|"+skidb.trim()+"|"+skidc.trim());
int c = str.split("SKID").length;
//System.out.println("linex " + x + " liney " + y);
//System.out.println("Count " + c);
//System.out.println("QR " + line);
int jj=3;
if (c==4)
{
if (x % 3 == 1)
{
//System.out.println("order "+order.get(0)+" skid "+skid.get(0)+" box "+box.get(0));
jj=0;
}
else
{
if (x % 3 == 2)
{
//System.out.println("order "+order.get(1)+" skid "+skid.get(1)+" box "+box.get(1));
jj=1;
}
else
{
//System.out.println("order "+order.get(2)+" skid "+skid.get(2)+" box "+box.get(2));
jj=2;
}
}
//System.out.println("4");
//break;
}
else
{
if (c==2)
{
if (x % 3 == 1)
{
jj=0;
//System.out.println("order "+order.get(0)+" skid "+skid.get(0)+" box "+box.get(0));
//System.out.println("2");
//break;
}
else
{
//System.out.println("HY");
}
}
else
{
if (c==3)
{
if (x % 3 == 1)
{
jj=0;
//System.out.println("order "+order.get(0)+" skid "+skid.get(0)+" box "+box.get(0));
//System.out.println("3");
//break;
}
else
{
if (x % 3 == 2)
{
jj=1;
//System.out.println("order "+order.get(1)+" skid "+skid.get(1)+" box "+box.get(1));
//System.out.println("3");
//break;
}
else
{
//System.out.println("HY");
}
}
}
}
}
if (jj!=3)
{
if(orderlist.equals(order.get(jj)))
{
zz++;
}
System.out.println("Seq "+x+" PDS "+k+" order "+order.get(jj)+" skid "+skid.get(jj)+" box "+box.get(jj));
datax[k][Integer.parseInt(skid.get(jj))][Integer.parseInt(box.get(jj))]=x;
//System.out.println("datax "+datax[k]+" "+Integer.parseInt(skid.get(jj))+" "+Integer.parseInt(box.get(jj)));
if (Integer.parseInt(skid.get(jj))>xmax)
xmax=Integer.parseInt(skid.get(jj));
if (Integer.parseInt(box.get(jj))>boxmax)
boxmax=Integer.parseInt(box.get(jj));
break;
}
}
/*PdfImportedPage template = writer.getImportedPage(reader, x);
Image tmpImage = Image.getInstance(template);
tmpImage.setAbsolutePosition(0.0F, 0.0F);
document.add(Image.getInstance(tmpImage));

BarcodeQRCode barcodeQRCode = new BarcodeQRCode(line, 40, 40, null);
tmpImage = barcodeQRCode.getImage();
//tmpImage.setAbsolutePosition(25.0F, 90.0F);
tmpImage.setAbsolutePosition(20.0F, 70.0F);
document.add(tmpImage);
document.newPage();
*/
}
br.close();
br = new BufferedReader(new FileReader(file));
System.out.println("k "+k);
int z=0;
//line item
for (int i=1;i<=k;i++){
//skid
//for (int j=1;j<=20;j++){
for (int j=1;j<=xmax;j++){
//box
//for (y=1;y<=100;y++){
for (y=1;y<=boxmax;y++){
if(datax[i][j][y]!=0)
{
System.out.println("PDS "+i+" skid "+j+" boxno "+y+" seq "+datax[i][j][y]);
line = br.readLine();
PdfImportedPage template = writer.getImportedPage(reader, datax[i][j][y]);
Image tmpImage = Image.getInstance(template);
tmpImage.setAbsolutePosition(0.0F, 0.0F);
document.add(Image.getInstance(tmpImage));

BarcodeQRCode barcodeQRCode = new BarcodeQRCode(line, 40, 40, null);
tmpImage = barcodeQRCode.getImage();
//tmpImage.setAbsolutePosition(25.0F, 90.0F);
tmpImage.setAbsolutePosition(20.0F, 70.0F);
document.add(tmpImage);
document.newPage();

z++;
}
}
}
}
br.close();
System.out.println("z "+z);

}
catch (IOException e)
{
e.printStackTrace();
}
//System.out.println("liney " + y);
document.close();
}

public void splite(String[] args)
throws IOException, DocumentException
{
String SRC = args[1];
String DEST = args[2];

PdfReader reader = new PdfReader(SRC);

Rectangle pagesize = new Rectangle(595.0F, 280.0F);

Document document = new Document(pagesize);

PdfWriter writer = PdfWriter.getInstance(document, new FileOutputStream(DEST));
document.open();
PdfContentByte content = writer.getDirectContent();
int n = reader.getNumberOfPages();
for (int j = 1; j <= n; j++)
{
PdfImportedPage page = writer.getImportedPage(reader, j);
float x = 0.0F;
for (int i = 0; i < 3; i++)
{
int month = (5 - i) % 3;
switch (month)
{
case 2:
x = -5.0F;
break;
case 1:
x = 0.0F;
break;
case 0:
x = 4.0F;
break;
}
float y = -(pagesize.getHeight() * ((5 - i) % 3) + x);
content.addTemplate(page, 1.0F, 0.0F, 0.0F, 1.0F, 0.0F, y);
document.newPage();
}
}
document.close();
}
public void splite2(String[] args)
throws IOException, DocumentException
{
String SRC = args[1];
String DEST = args[2];

PdfReader reader = new PdfReader(SRC);
PdfReaderContentParser parser = new PdfReaderContentParser(reader);

Rectangle pagesize = new Rectangle(595.0F, 280.0F);

Document document = new Document(pagesize);

PdfWriter writer = PdfWriter.getInstance(document, new FileOutputStream(DEST));
document.open();
PdfContentByte content = writer.getDirectContent();
int n = reader.getNumberOfPages();
int c;
int b;
int z=0;
for (int j = 1; j <= n; j++)
{
//writer.addPageDictEntry(PdfName.ROTATE, PdfPage.SEASCAPE);
PdfImportedPage page = writer.getImportedPage(reader, j);
TextExtractionStrategy strategy = (TextExtractionStrategy)parser.processContent(j, new SimpleTextExtractionStrategy());
String str = strategy.getResultantText();
String[] name = str.split("SD KANBAN");
float x = 0.0F;
for (int i = 1; i < name.length; i++)
{

c = name[i].split("Code R2").length-1;
b = name[i].split("Code R1").length-1;
if (c+b>0)
{
int month=0;
switch (i)
{
case 1:
month=0;
x = -2.0F; //-5
break;
case 2:
month=1;
x = 0.0F;
break;
case 3:
month=2;
x = 2.0F; //4
break;
}
System.out.println("page "+j+" i "+i+" month "+month+" c "+c+" b "+b);
//System.out.println("/////////////");
//System.out.println(name[i]);
float y = -(pagesize.getHeight() * month + x);
content.addTemplate(page, 1.0F, 0.0F, 0.0F, 1.0F, 0.0F, y);
document.newPage();
z++;
}
}
}
document.close();
System.out.println("Total Kanban "+z);
}
public void manipulatePdf2(String[] args)
throws DocumentException, IOException
{
String SRC = args[1];
String DEST = args[2];
String textFile = args[3];

Rectangle pagesize = new Rectangle(595.0F, 280.0F);
Document document = new Document(pagesize, 36.0F, 72.0F, 108.0F, 180.0F);
PdfWriter writer = PdfWriter.getInstance(document, new FileOutputStream(DEST));
document.open();
PdfReader reader = new PdfReader(SRC);
int n = reader.getNumberOfPages();

PdfReaderContentParser parser = new PdfReaderContentParser(reader);

File file = new File(textFile);
int y = 0;
try
{
BufferedReader br = new BufferedReader(new FileReader(file));

int x = 0;

String line;
while ((line = br.readLine()) != null)
{
y+=1;
while (y > 0)
{
x += 1;

TextExtractionStrategy strategy = (TextExtractionStrategy)parser.processContent(x, new SimpleTextExtractionStrategy());
String str = strategy.getResultantText();
str = str.toUpperCase();
int c = str.split("ECI TAG").length - 1;
if (c != 3)
{
System.out.println("page " + x);
System.out.println("C " + c);
System.out.println("line " + line);
}
if ((x % 3 == 1) && (c == 1)) {
System.out.println("1page " + x);
System.out.println("1C " + c);
System.out.println("1line " + line);
break;
}
if ((x % 3 > 0) && (c == 2)) {
System.out.println("2page " + x);
System.out.println("2C " + c);
System.out.println("2line " + line);
break;
}
if (c == 3) {
break;
}
}
PdfImportedPage template = writer.getImportedPage(reader, x);
Image tmpImage = Image.getInstance(template);
tmpImage.setAbsolutePosition(0.0F, 0.0F);
document.add(Image.getInstance(tmpImage));

BarcodeQRCode barcodeQRCode = new BarcodeQRCode(line, 40, 40, null);
tmpImage = barcodeQRCode.getImage();
tmpImage.setAbsolutePosition(100.0F, 225.0F);
document.add(tmpImage);
document.newPage();
}
br.close();
}
catch (IOException e)
{
e.printStackTrace();
}
System.out.println("liney " + y);
document.close();
}
}

modify GDIGRAB (FFMPEG) to select windows by HWND id

if (!strncmp(filename, "title=", 6)) {
name = filename + 6;
hwnd = FindWindow(NULL, name);
if (!hwnd) {
av_log(s1, AV_LOG_ERROR,
"Can't find window '%s', aborting.\n", name);
ret = AVERROR(EIO);
goto error;
}
if (gdigrab->show_region) {
av_log(s1, AV_LOG_WARNING,
"Can't show region when grabbing a window.\n");
gdigrab->show_region = 0;
}
} else if (!strncmp(filename, "hwnd=", 5)) {
name = filename + 5;
hwnd = strtol(name, NULL, 16);
//hwnd = 0x21230;

if (!hwnd) {
av_log(s1, AV_LOG_ERROR,
"Can't find HWND window '%s', aborting.\n", name);
ret = AVERROR(EIO);
goto error;
}
if (gdigrab->show_region) {
av_log(s1, AV_LOG_WARNING,
"Can't show region when grabbing a window.\n");
gdigrab->show_region = 0;
}
} else if (!strcmp(filename, "desktop")) {
hwnd = NULL;
} else {
av_log(s1, AV_LOG_ERROR,
"Please use \"desktop\" or \"title=\" to specify your target.\n");
ret = AVERROR(EIO);
goto error;
}

Tuesday, June 9, 2020

AHK for fix mouse roll

#InstallMouseHook
Gui,+AlwaysOnTop
Gui, Add, Edit, w50 r2 ReadOnly vmyEdit
Gui, Show ,x1800 y120
sendwheel()
{
GuiControl,,myEdit, %A_TimeSincePriorHotkey%
x:=A_PriorHotKey
if(A_TimeSincePriorHotkey>400)
x:=A_ThisHotKey
if (x=A_ThisHotKey)
send {%A_ThisHotKey%}
if (x<>A_ThisHotKey and A_TimeSincePriorHotkey<50 p=""> send {%x%}
}
wheelup::sendwheel()
wheeldown::sendwheel()

Prapop (Note & Knowledge)

Wednesday, September 2, 2020

EXCEL EXPERT !!!!!!!!!!!! Function AGGREGATE

Wednesday, August 26, 2020

Clear Pivot cache in excel : paste it on thisworkbook and refresh connection

Thursday, July 30, 2020

Java cutter v2020 07 07

modify GDIGRAB (FFMPEG) to select windows by HWND id

Tuesday, June 9, 2020

AHK for fix mouse roll

Thursday, January 30, 2020

JAVA PDF CUTTER

Vmware

Blog Archive