|
public Message(String text, String title) {
MessageBox.Show(text, title, MessageBoxButton.OK, MessageBoxImage.Information);
}
// global variable
List myarray; // save the values, List, ArrayList, Array or ObservableCollection
// this solution export one cell per action
// very slow
private void bnExport2Excel1_Click(object sender, RoutedEventArgs e) {
Stopwatch stopWatch1 = new Stopwatch();
stopWatch1.Start();
var xl = new Excel.Application();
var wb = xl.Workbooks.Add();
xl.Cells[1, 1] = "Nr"; //Row,Column
xl.Cells[1, 1].Select();
xl.ActiveCell.Font.Size = 12;
xl.ActiveCell.Font.Bold = true;
xl.Cells[1, 2] = "Firstname";
xl.Cells[1, 2].Select();
xl.ActiveCell.Font.Size = 12;
xl.ActiveCell.Font.Bold = true;
xl.Cells[1, 3] = "Lastname";
xl.Cells[1, 3].Select();
xl.ActiveCell.Font.Size = 12;
xl.ActiveCell.Font.Bold = true;
var ws = (Microsoft.Office.Interop.Excel.Worksheet)wb.ActiveSheet;
int row = 0;
// if you export String with points, one must shure to export a string
// so one must define the format for the columns to string
// the string-format is the character "@", what a surprise !
ws.Range["A2:B" + (empl_items.Count + 1)].Select();
xl.Selection.NumberFormat = "@"; // String
foreach (MyObject obj in myarray) {
row++;
Title = row + " / " + myarray.Count; // is thread save
n++;
xl.Cells[row, 1] = (row - 1).ToString(); //Row,Column
xl.Cells[row, 2] = obj.Firstname;
xl.Cells[row, 3] = obj.Lastname;
}
stopWatch1.Stop();
TimeSpan ts = stopWatch1.Elapsed;
Message(String.Format("{0:00}:{1:00}:{2:00}.{3:00}", ts.Hours, ts.Minutes, ts.Seconds, ts.Milliseconds / 10), "Länge der Zeit");
xl.Visible = true;
} // bnExport2Excel1_Click
// this solution export one row per action
// better
private void bnExport2Excel2_Click(object sender, RoutedEventArgs e) {
Stopwatch stopWatch1 = new Stopwatch();
stopWatch1.Start();
var xl = new Excel.Application();
var wb = xl.Workbooks.Add();
int anzCols = MyObject.CaptionItemArray.Length;
for (int i = 0; i < anzCols; i++) {
xl.Cells[1, i + 1] = MyObject.CaptionItemArray[i]; //Row,Column
xl.Cells[1, i + 1].Select();
xl.ActiveCell.Font.Size = 12;
xl.ActiveCell.Font.Bold = true;
}
var ws = (Microsoft.Office.Interop.Excel.Worksheet)wb.ActiveSheet;
int n = 0;
int row = 0;
int iIndex = 0;
ws.Range["A2:B" + (myarray.Count + 1)].Select();
xl.Selection.NumberFormat = "@"; // String
//Object[] items = new Object[anzCols];
foreach (MyObject obj in myarray) {
iIndex++;
Title = iIndex + " / " + myarray.Count;
n++;
//emp.insertinItemArray(ref items);
ws.Range["A2"].Offset[row].Resize[1, anzCols].Value = obj.ItemArray;
n = 0;
row++;
}
stopWatch1.Stop();
TimeSpan ts = stopWatch1.Elapsed;
Message(String.Format("{0:00}:{1:00}:{2:00}.{3:00}", ts.Hours, ts.Minutes, ts.Seconds, ts.Milliseconds / 10), "Länge der Zeit");
xl.Visible = true;
} // bnExport2Excel2_Click
// this solution export one block of MAX-rows
// very fast
private void bnExport2Excel3_Click(object sender, RoutedEventArgs e) {
Stopwatch stopWatch1 = new Stopwatch();
stopWatch1.Start();
var xl = new Excel.Application();
var wb = xl.Workbooks.Add();
int anzCols = MyObject.CaptionItemArray.Length;
for (int i = 0; i < anzCols; i++) {
xl.Cells[1, i + 1] = MyObject.CaptionItemArray[i]; //Row,Column
xl.Cells[1, i + 1].Select();
xl.ActiveCell.Font.Size = 12;
xl.ActiveCell.Font.Bold = true;
}
var ws = (Microsoft.Office.Interop.Excel.Worksheet)wb.ActiveSheet;
int n = 0;
int rowOffset = 0;
int row = 0;
int iIndex = 0;
int MAX = 10;
ws.Range["A2:B" + (myarray.Count + 1)].Select();
xl.Selection.NumberFormat = "@"; // String
Object[,] items = new Object[MAX, anzCols];
foreach (MyObject obj in myarray) {
iIndex++;
Title = iIndex + " / " + empl_items.Count;
n++;
obj.insertinItemArray(rowOffset, ref items);
rowOffset++;
if (n == MAX) {
ws.Range["A2"].Offset[row].Resize[MAX, anzCols].Value = items;
n = 0;
rowOffset = 0;
row += MAX;
}
}
if (n > 0) {
ws.Range["A2"].Offset[row].Resize[rowOffset, anzCols].Value = items;
}
stopWatch1.Stop();
TimeSpan ts = stopWatch1.Elapsed;
Message(String.Format("{0:00}:{1:00}:{2:00}.{3:00}", ts.Hours, ts.Minutes, ts.Seconds, ts.Milliseconds / 10), "Länge der Zeit");
xl.Visible = true;
} // bnExport2Excel3_Click
public class MyObject {
public MyObject(String firstname, String lastname) {
Firstname = firstname;
Lastname = lastname;
}
private String firstname;
public String Firstname {
get {
return firstname;
}
set {
if (value != firstname) {
firstname = value;
}
}
}
private String lastname;
public String Lastname {
get {
if (!isDataTranfered) dataTranfered();
return lastname;
}
set {
if (value != lastname) {
lastname = value;
}
}
}
// Excel Export, solution 2 and 3
public static Object[] CaptionItemArray {
get {
Object[] items = new Object[2];
items[0] = "Vorname";
items[1] = "Nachname";
return items;
}
}
// Excel Export, solution 2
public Object[] ItemArray {
get {
Object[] items = new Object[2];
items[0] = firstname;
items[1] = lastname;
return items;
}
}
// Excel Export, solution 3
public void insertinItemArray(int row, ref Object[,] items) {
items[row, 0] = firstname;
items[row, 1] = lastname;
}
|
|