Копировать данные столбца с нескольких листов на один лист в одном и том же столбце, без перезаписи

Вы можете обернуть поток ввода с помощью PushbackInputStream. PushbackInputStream позволяет непрочитанным записать назад ») байты, которые уже были прочитаны, поэтому вы можете сделать это следующим образом:

public class StreamTest {
  public static void main(String[] args) throws IOException {
    byte[] bytes = new byte[] { 1, 2, 3, 4, 5, 6, 7, 8, 9 };

    InputStream originalStream = new ByteArrayInputStream(bytes);

    byte[] readBytes = getBytes(originalStream, 3);
    printBytes(readBytes); // prints: 1 2 3

    readBytes = getBytes(originalStream, 3);
    printBytes(readBytes); // prints: 4 5 6

    // now let's wrap it with PushBackInputStream

    originalStream = new ByteArrayInputStream(bytes);

    InputStream wrappedStream = new PushbackInputStream(originalStream, 10); // 10 means that maximnum 10 characters can be "written back" to the stream

    readBytes = getBytes(wrappedStream, 3);
    printBytes(readBytes); // prints 1 2 3

    ((PushbackInputStream) wrappedStream).unread(readBytes, 0, readBytes.length);

    readBytes = getBytes(wrappedStream, 3);
    printBytes(readBytes); // prints 1 2 3


  }

  private static byte[] getBytes(InputStream is, int howManyBytes) throws IOException {
    System.out.print("Reading stream: ");

    byte[] buf = new byte[howManyBytes];

    int next = 0;
    for (int i = 0; i < howManyBytes; i++) {
      next = is.read();
      if (next > 0) {
        buf[i] = (byte) next;
      }
    }
    return buf;
  }

  private static void printBytes(byte[] buffer) throws IOException {
    System.out.print("Reading stream: ");

    for (int i = 0; i < buffer.length; i++) {
      System.out.print(buffer[i] + " ");
    }
    System.out.println();
  }


}

Обратите внимание, что PushbackInputStream хранит внутренний буфер байтов, поэтому он действительно создает буфер в памяти, который содержит байты «записаны».

Зная этот подход, мы можем пойти дальше и объединить его с FilterInputStream. FilterInputStream хранит исходный входной поток в качестве делегата. Это позволяет создать новое определение класса, которое автоматически позволяет « непрочитанных » исходных данных. Определение этого класса следующее:

public class TryReadInputStream extends FilterInputStream {
  private final int maxPushbackBufferSize;

  /**
  * Creates a <code>FilterInputStream</code>
  * by assigning the  argument <code>in</code>
  * to the field <code>this.in</code> so as
  * to remember it for later use.
  *
  * @param in the underlying input stream, or <code>null</code> if
  *           this instance is to be created without an underlying stream.
  */
  public TryReadInputStream(InputStream in, int maxPushbackBufferSize) {
    super(new PushbackInputStream(in, maxPushbackBufferSize));
    this.maxPushbackBufferSize = maxPushbackBufferSize;
  }

  /**
   * Reads from input stream the <code>length</code> of bytes to given buffer. The read bytes are still avilable
   * in the stream
   *
   * @param buffer the destination buffer to which read the data
   * @param offset  the start offset in the destination <code>buffer</code>
   * @aram length how many bytes to read from the stream to buff. Length needs to be less than
   *        <code>maxPushbackBufferSize</code> or IOException will be thrown
   *
   * @return number of bytes read
   * @throws java.io.IOException in case length is
   */
  public int tryRead(byte[] buffer, int offset, int length) throws IOException {
    validateMaxLength(length);

    // NOTE: below reading byte by byte instead of "int bytesRead = is.read(firstBytes, 0, maxBytesOfResponseToLog);"
    // because read() guarantees to read a byte

    int bytesRead = 0;

    int nextByte = 0;

    for (int i = 0; (i < length) && (nextByte >= 0); i++) {
      nextByte = read();
      if (nextByte >= 0) {
        buffer[offset + bytesRead++] = (byte) nextByte;
      }
    }

    if (bytesRead > 0) {
      ((PushbackInputStream) in).unread(buffer, offset, bytesRead);
    }

    return bytesRead;

  }

  public byte[] tryRead(int maxBytesToRead) throws IOException {
    validateMaxLength(maxBytesToRead);

    ByteArrayOutputStream baos = new ByteArrayOutputStream(); // as ByteArrayOutputStream to dynamically allocate internal bytes array instead of allocating possibly large buffer (if maxBytesToRead is large)

    // NOTE: below reading byte by byte instead of "int bytesRead = is.read(firstBytes, 0, maxBytesOfResponseToLog);"
    // because read() guarantees to read a byte

    int nextByte = 0;

    for (int i = 0; (i < maxBytesToRead) && (nextByte >= 0); i++) {
      nextByte = read();
      if (nextByte >= 0) {
        baos.write((byte) nextByte);
      }
    }

    byte[] buffer = baos.toByteArray();

    if (buffer.length > 0) {
      ((PushbackInputStream) in).unread(buffer, 0, buffer.length);
    }

    return buffer;

  }

  private void validateMaxLength(int length) throws IOException {
    if (length > maxPushbackBufferSize) {
      throw new IOException(
        "Trying to read more bytes than maxBytesToRead. Max bytes: " + maxPushbackBufferSize + ". Trying to read: " +
        length);
    }
  }

}

Этот класс имеет два метода. Один для чтения в существующий буфер (defintion аналогичен вызову public int read(byte b[], int off, int len) класса InputStream). Второй, который возвращает новый буфер (это может быть более эффективным, если размер буфера для чтения неизвестен).

Теперь посмотрим наш класс в действии:

public class StreamTest2 {
  public static void main(String[] args) throws IOException {
    byte[] bytes = new byte[] { 1, 2, 3, 4, 5, 6, 7, 8, 9 };

    InputStream originalStream = new ByteArrayInputStream(bytes);

    byte[] readBytes = getBytes(originalStream, 3);
    printBytes(readBytes); // prints: 1 2 3

    readBytes = getBytes(originalStream, 3);
    printBytes(readBytes); // prints: 4 5 6

    // now let's use our TryReadInputStream

    originalStream = new ByteArrayInputStream(bytes);

    InputStream wrappedStream = new TryReadInputStream(originalStream, 10);

    readBytes = ((TryReadInputStream) wrappedStream).tryRead(3); // NOTE: no manual call to "unread"(!) because TryReadInputStream handles this internally
    printBytes(readBytes); // prints 1 2 3

    readBytes = ((TryReadInputStream) wrappedStream).tryRead(3); 
    printBytes(readBytes); // prints 1 2 3

    readBytes = ((TryReadInputStream) wrappedStream).tryRead(3);
    printBytes(readBytes); // prints 1 2 3

    // we can also call normal read which will actually read the bytes without "writing them back"
    readBytes = getBytes(wrappedStream, 3);
    printBytes(readBytes); // prints 1 2 3

    readBytes = getBytes(wrappedStream, 3);
    printBytes(readBytes); // prints 4 5 6

    readBytes = ((TryReadInputStream) wrappedStream).tryRead(3); // now we can try read next bytes
    printBytes(readBytes); // prints 7 8 9

    readBytes = ((TryReadInputStream) wrappedStream).tryRead(3); 
    printBytes(readBytes); // prints 7 8 9


  }



}
0
задан Pᴇʜ 18 March 2019 в 07:50
поделиться

1 ответ

Колонна консолидации

Sub ConsolidateColumn()

    ' Source Worksheet List
    Const cSheets As String = "Invoices,CreditNotes,Journals"
    Const cTarget As String = "Sheet1"  ' Target Worksheet Name
    Const cFur As Long = 2              ' Source First Used Row
    Const cColS As Variant = 11         ' Source Column Letter/Number ("K")
    Const cColT As Variant = 1          ' Target Column Letter/Number ("A")

    Dim rng As Range      ' Source Column Range (in current Source Worksheet)
    Dim vntW As Variant   ' Worksheet Array
    Dim LurS As Long      ' Source Last Used Row Number
    Dim FerT As Long      ' Target First Empty Row Number
    Dim i As Long         ' Worksheet Array Element Counter

    ' Split Source Worksheet List to Worksheet Array.
    vntW = Split(cSheets, ",")

    ' In This Workbook (The Workbook Containing This Code)
    With ThisWorkbook.Worksheets(cTarget)
        ' Loop through elements of Worksheet Array.
        For i = 0 To UBound(vntW)
            ' Calculate Target First Empty Row Number.
            FerT = .Cells(.Rows.Count, cColT).End(xlUp).Row + 1
            ' In Current Source Worksheet
            With ThisWorkbook.Worksheets(Trim(vntW(i)))
                ' Calculate Source Last Used Row Number.
                LurS = .Cells(.Rows.Count, cColS).End(xlUp).Row
                ' Calculate Source Column Range.
                ' Create a reference to Source Column Range.
                Set rng = .Cells(cFur, cColS).Resize(LurS - cFur + 1)
            End With
            ' Calculate Target Column Range.
            ' Copy values from current Source Column Range to Target Column
            ' Range.
            .Cells(FerT, cColT).Resize(rng.Rows.Count) = rng.Value
        Next
    End With
 End Sub
0
ответ дан VBasic2008 18 March 2019 в 07:50
поделиться
Другие вопросы по тегам:

Похожие вопросы: