private void newNToolStripMenuItem_Click(object sender, EventArgs e)
{
//test create xls file
string file = "C:\\newdoc.xls";
Workbook workbook = new Workbook();
Worksheet worksheet = new Worksheet("First Sheet");
worksheet.Cells[0, 1] = new Cell((short)1);
worksheet.Cells[2, 0] = new Cell(2.8);
worksheet.Cells[3, 3] = new Cell((decimal)3.45);
worksheet.Cells[2, 2] = new Cell("Text string");
worksheet.Cells[2, 4] = new Cell("Second string");
worksheet.Cells[4, 0] = new Cell(32764.5, "#,##0.00");
worksheet.Cells[5, 1] = new Cell(DateTime.Now, @"YYYY\-MM\-DD");
worksheet.Cells.ColumnWidth[0, 1] = 3000;
//Picture pic = new Picture();
//pic.Image = Excel.Image.FromFile("C:\\DelBreakpoint.png");
//pic.TopLeftCorner = new CellAnchor(5, 1, 0, 0);
//pic.BottomRightCorner = new CellAnchor(12, 5, 592, 243);
//worksheet.AddPicture(pic);
workbook.Worksheets.Add(worksheet);
workbook.Save(file);
//open created file
doc = CompoundDocument.Open(file);
IsOpened = true;
PopulateTreeview(file);
}
public void WriteLongTextTest()
{
int longTextLength = 50000;
StringBuilder builder = new StringBuilder(longTextLength);
for (int i = 0; i < longTextLength; i++)
builder.Append('A');
string longText = builder.ToString();
string tempFilePath = Path.GetTempFileName();
{
Workbook workbook = new Workbook();
Worksheet worksheet = new Worksheet("Test");
worksheet.Cells[0, 0] = new Cell(longText);
workbook.Worksheets.Add(worksheet);
workbook.Save(tempFilePath);
}
{
Workbook workbook = Workbook.Load(tempFilePath);
Assert.AreEqual(longText, workbook.Worksheets[0].Cells[0, 0].Value);
}
}
public static void ExportToExcelFile(
DataSet dataSet,
string filePath)
{
if (!filePath.EndsWith(@".xls", StringComparison.InvariantCultureIgnoreCase))
{
throw new Exception("Currently only XLS (i.e. not XLSX) is supported when writing Excel files.");
}
var wb = new Workbook();
wb.Worksheets.Clear();
foreach (DataTable table in dataSet.Tables)
{
var ws = new Worksheet(table.TableName);
wb.Worksheets.Add(ws);
processSheet(table, ws);
}
SafeFileOperations.SafeDeleteFile(filePath);
wb.Save(filePath);
}
private void exportAsXLSToolStripMenuItem_Click(object sender, EventArgs e)
{
saveFileDialog1.InitialDirectory = Environment.GetFolderPath(Environment.SpecialFolder.Desktop);
saveFileDialog1.Title = "Save the Excel file!";
saveFileDialog1.FileName = selectedItem;
saveFileDialog1.Filter = "Excel Document(2003)|*.xls|Excel Document(2007)|*.xlsx";
if (saveFileDialog1.ShowDialog() != DialogResult.Cancel)
{
if (dataGridView1.Rows.Count == 0)
{
MessageBox.Show("The DataGridView is empty. Please fill it!", "Error!", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
else
{
try
{
Workbook excelWorkbook = new Workbook();
Worksheet worksheet = new Worksheet("DataGridView");
for (int i = 0; i < dataGridView1.Columns.Count; i++)
{
worksheet.Cells[0, i] = new Cell(dataGridView1.Columns[i].HeaderText);
}
for (int i = 1; i < dataGridView1.Rows.Count; i++)
{
for (int j = 0; j < dataGridView1.Columns.Count; j++)
{
worksheet.Cells[i, j] = new Cell(dataGridView1.Rows[i - 1].Cells[j].Value.ToString());
}
}
//MessageBox.Show(dataGridView1.Rows[0].Cells[0].Value.ToString());
excelWorkbook.Worksheets.Add(worksheet);
excelWorkbook.Save(saveFileDialog1.FileName.ToString());
MessageBox.Show("File exported succesfully!", "Succes!", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
catch (Exception ex)
{
MessageBox.Show("Can't export the file!", "Error!", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
}
}
internal XMLConverter(XmlType type, string excel_file) {
xml_type = type;
e_fname = excel_file;
dir =(excel_file==null || excel_file=="")? "":Directory.GetParent(e_fname).FullName+"\\";
switch (type) {
case XmlType.Index:
xml_fname = "index.xml";
break;
case XmlType.TaskList:
xml_fname = "task.xml";
break;
case XmlType.Master:
xml_fname = "master.xml";
break;
case XmlType.Dpr:
xml_fname = Path.GetFileNameWithoutExtension(e_fname) + ".dprx";
break;
}
data = new DataSet();
if (e_fname == null || e_fname == "") return;
data.DataSetName = "dockmaker.net";
try {
book = Workbook.Open(e_fname);
} catch (Exception ex) {
throw ex;
}
}
public void SimpleMultipleSheetTest()
{
int worksheetToCreate = 10;
string tempFilePath = Path.GetTempFileName();
{
Workbook workbook = new Workbook();
for (int i = 0; i < worksheetToCreate; i++)
{
Worksheet worksheetWrite2 = new Worksheet(String.Format("Sheet {0}", i));
workbook.Worksheets.Add(worksheetWrite2);
}
workbook.Save(tempFilePath);
}
{
Workbook workbook = Workbook.Load(tempFilePath);
Assert.AreEqual(worksheetToCreate, workbook.Worksheets.Count);
for (int i = 0; i < worksheetToCreate; i++)
{
Assert.AreEqual(String.Format("Sheet {0}", i), workbook.Worksheets[i].Name);
}
}
}
private void writeHeader(string filename)
{
Workbook workbook;
if (File.Exists(filename))
workbook = Workbook.Load(filename);
else
workbook = new Workbook();
Worksheet akWorksheet = null;
foreach (Worksheet worksheet in workbook.Worksheets)
{
if (worksheet.Name == TableName)
akWorksheet = worksheet;
}
if (akWorksheet == null)
{
akWorksheet = new Worksheet(TableName);
workbook.Worksheets.Add(akWorksheet);
}
int n = 0;
foreach (DatasetConfigRow myFeld in datasetConfig.DatasetConfigRows)
{
akWorksheet.Cells[0, n] = new Cell(myFeld.DatabaseField);
n++;
}
workbook.Save(filename);
}
public ExportVehicleInfo(VehicleCache vehicles, string fileName)
{
_vehicleCache = vehicles;
//bucketSortVehicles(vehicles);
//string file = Resources.Settings.TempFolder + DateTime.Now.ToFileTimeUtc().ToString() + ".xls";
string currFileName = fileName;
Workbook wb = new Workbook();
createExcelFile(wb, currFileName);
}
public static Worksheet Decode(Workbook book, Stream stream, SharedResource sharedResource)
{
Worksheet sheet = new Worksheet();
sheet.Book = book;
List<Record> records = ReadRecords(stream, out sheet.Drawing);
sheet.Cells = PopulateCells(records, sharedResource);
sheet.Book.Records.AddRange(records);
return sheet;
}
public void RelatedTest()
{
Workbook workbook = new Workbook();
Worksheet worksheet = new Worksheet("Test");
workbook.Worksheets.Add(worksheet);
DateTime date = new DateTime(2009, 2, 13, 11, 30, 45);
worksheet.Cells[0, 0] = new Cell(date);
Assert.IsNotNull(worksheet.Cells[0, 0].Format);
}
public static void Encode(Workbook workbook, Stream stream)
{
List<Record> records = EncodeWorkbook(workbook);
BinaryWriter writer = new BinaryWriter(stream);
foreach (Record record in records)
{
record.Write(writer);
}
writer.Close();
}
public static void ExportXLSX(List<IParticipant> participants, List<string> formFields)
{
string eventName = (participants.Count > 0) ? ((participants[0] != null && participants[0].EventPage != null && participants[0].EventPage != PageReference.EmptyReference) ? EPiServer.DataFactory.Instance.Get<PageData>(participants[0].EventPage).URLSegment : "NA" ) : "NA";
string fileName = EPiServer.Framework.Localization.LocalizationService.Current.GetString("/attend/edit/participants") + " - " + eventName;
HttpContext.Current.Response.Clear();
HttpContext.Current.Response.ClearHeaders();
HttpContext.Current.Response.ClearContent();
HttpContext.Current.Response.ContentType = "application/vnd.ms-excel";
HttpContext.Current.Response.AddHeader("content-disposition", "attachment;filename=" + fileName + ".xls");
HttpContext.Current.Response.Charset = Encoding.UTF8.WebName;
HttpContext.Current.Response.ContentEncoding = Encoding.UTF8;
HttpContext.Current.Response.BinaryWrite(Encoding.UTF8.GetPreamble());
HttpContext.Current.Response.ContentType = "application/vnd.ms-excel";
HttpContext.Current.Response.Clear();
HttpContext.Current.Response.BufferOutput = true;
Workbook workbook = new Workbook();
Worksheet worksheet = new Worksheet("Participants");
if(formFields != null)
for(int i = 0; i < formFields.Count; i++)
worksheet.Cells[0, i]= new Cell(formFields[i]);
else if(participants.Count > 0)
{
string[] headers = AttendRegistrationEngine.GetFormData(participants[0]).AllKeys;
worksheet.Cells[0, 0] = new Cell("Status");
worksheet.Cells[0, 1] = new Cell("E-mail");
worksheet.Cells[0, 2] = new Cell("Code");
for (int i = 0; i < headers.Length; i++)
{
worksheet.Cells[0, i+3] = new Cell(headers[i]);
}
}
for (int i = 0; i < participants.Count; i++)
{
AddParticipantToWorksheet(worksheet, i+1, participants[i]);
}
// Some Excel versions requires more than 100 rows - adding empty ones
for (int i = participants.Count; i < 150; i++)
worksheet.Cells[i, 0] = new Cell(string.Empty);
workbook.Worksheets.Add(worksheet);
MemoryStream ms = new MemoryStream();
workbook.SaveToStream(ms);
ms.WriteTo(HttpContext.Current.Response.OutputStream);
HttpContext.Current.Response.End();
}
public void Test()
{
Workbook workbook = new Workbook();
Worksheet worksheet = new Worksheet("Test");
workbook.Worksheets.Add(worksheet);
DateTime date = new DateTime(2009, 2, 13, 11, 30, 45);
worksheet.Cells[0, 0] = new Cell(date);
worksheet.Cells[0, 0].DateTimeValue = date;
Assert.AreEqual(date, worksheet.Cells[0, 0].DateTimeValue);
}
void ExtractImages(Workbook book, string path)
{
if (book.DrawingGroup == null) return;
if (!Directory.Exists(path))
{
Directory.CreateDirectory(path);
}
Worksheet sheet1 = book.Worksheets[0];
foreach (KeyValuePair<Pair<int, int>, Picture> pic in sheet1.Pictures)
{
string filename = String.Format("image{0}-{1}", pic.Key.Left, pic.Key.Right)
+ pic.Value.Image.FileExtension;
string file = Path.Combine(path, filename);
File.WriteAllBytes(file, pic.Value.Image.Data);
}
}
/// <summary>
/// test excel library
/// </summary>
public static void ExcelLibraryTest()
{
//create new xls file
string file = "C:\\temp\\newdoc.xls";
ExcelLibrary.SpreadSheet.Workbook workbook = new ExcelLibrary.SpreadSheet.Workbook();
ExcelLibrary.SpreadSheet.Worksheet worksheet = new ExcelLibrary.SpreadSheet.Worksheet("First Sheet");
worksheet.Cells[0, 1] = new Cell((short)1);
worksheet.Cells[2, 0] = new Cell(9999999);
worksheet.Cells[3, 3] = new Cell((decimal)3.45);
worksheet.Cells[2, 2] = new Cell("Text string");
worksheet.Cells[2, 4] = new Cell("Second string");
worksheet.Cells[4, 0] = new Cell(32764.5, "#,##0.00");
//worksheet.Cells[5, 1] = new Cell(DateTime.Now, @"YYYY-MM-DD");
worksheet.Cells.ColumnWidth[0, 1] = 3000;
workbook.Worksheets.Add(worksheet);
workbook.Save(file);
}
public static Workbook Decode(Stream stream)
{
Workbook book = new Workbook();
SharedResource sharedResource;
List<Record> records = ReadRecords(stream, out book.DrawingGroup);
book.Records = records;
List<BOUNDSHEET> boundSheets = DecodeRecords(records, out sharedResource);
foreach (BOUNDSHEET boundSheet in boundSheets)
{
stream.Position = boundSheet.StreamPosition;
Worksheet sheet = WorksheetDecoder.Decode(book, stream, sharedResource);
sheet.Book = book;
sheet.Name = boundSheet.SheetName;
sheet.SheetType = (SheetType)boundSheet.SheetType;
book.Worksheets.Add(sheet);
}
return book;
}
static void Main(string[] args)
{
string file = "Test.xls";
m_pWorkBook = Workbook.Load(file);
InitializeDictionaryListFromFirstRow();
ReadAllStringsIntoDictionaries();
WriteStringsToBinary();
ReadBinaryIntoDictionary();
WriteStringsToXML();
XmlWriterSettings pSettings = new XmlWriterSettings();
pSettings.Indent = true;
ReadFontDeclaration();
WriteAllBmFontConfigFiles();
}
public static void ExportXLSX(List<IParticipant> participants, List<string> formFields)
{
HttpContext.Current.Response.Clear();
HttpContext.Current.Response.ClearHeaders();
HttpContext.Current.Response.ClearContent();
HttpContext.Current.Response.ContentType = "application/vnd.ms-excel";
HttpContext.Current.Response.AddHeader("content-disposition", "attachment;filename=participants-" + participants[0].EventPage.ID + ".xls");
HttpContext.Current.Response.Charset = Encoding.UTF8.WebName;
HttpContext.Current.Response.ContentEncoding = Encoding.UTF8;
HttpContext.Current.Response.BinaryWrite(Encoding.UTF8.GetPreamble());
HttpContext.Current.Response.ContentType = "application/vnd.ms-excel";
HttpContext.Current.Response.Clear();
HttpContext.Current.Response.BufferOutput = true;
Workbook workbook = new Workbook();
Worksheet worksheet = new Worksheet("Participants");
if(formFields != null)
for(int i = 0; i < formFields.Count; i++)
worksheet.Cells[0, i]= new Cell(formFields[i]);
else
{
string[] headers = AttendRegistrationEngine.GetFormData(participants[0]).AllKeys;
for (int i = 0; i < headers.Length; i++)
{
worksheet.Cells[0, i] = new Cell(headers[i]);
}
}
for (int i = 0; i < participants.Count; i++)
{
AddParticipantToWorksheet(worksheet, i+1, participants[i]);
}
workbook.Worksheets.Add(worksheet);
MemoryStream ms = new MemoryStream();
workbook.SaveToStream(ms);
ms.WriteTo(HttpContext.Current.Response.OutputStream);
HttpContext.Current.Response.End();
}
public void ExtraireDATA(string nomFichier, string CodeSoc, string NomSoc, ProgressBar prog, string Date1, string Date2, int idSoc, int MoisPe, int AnneePe)
{
prog.Visible = true;
prog.Minimum = 0;
//create new xls file
Workbook workbook = new Workbook();
Worksheet worksheet = new Worksheet("SVE");
worksheet.Cells.ColumnWidth[0, 1] = 3000;
worksheet = entete.EnteteSVRLigne1(worksheet);
worksheet = entete.EnteteSVRLigne2(worksheet);
worksheet = entete.EnteteSVRLigne3(worksheet);
worksheet = entete.EnteteSVRLigne4(worksheet, NomSoc);
worksheet = entete.EnteteSVRLigne5(worksheet, CodeSoc);
worksheet = entete.EnteteSVRLigne6(worksheet, NomSoc);
worksheet = entete.EnteteSVRLigne7(worksheet);
worksheet = entete.EnteteSVRLigne8(worksheet, Date1, Date2);
worksheet = entete.EnteteSVRLigne9(worksheet);
worksheet = entete.EnteteSVRLigne10(worksheet);
worksheet = entete.EnteteSVRLigne11(worksheet);
worksheet = entete.EnteteSVRLigne12(worksheet);
worksheet = entete.EnteteSVRLigne13(worksheet, idSoc, MoisPe);
worksheet = entete.EnteteSVRLigne14(worksheet, idSoc, MoisPe, AnneePe);
workbook.Worksheets.Add(worksheet);
workbook.Save(nomFichier);
prog.Visible = false;
prog.Value = 0;
Workbook book = Workbook.Load(nomFichier);
Worksheet sheet = book.Worksheets[0];
// traverse rows by Index
for (int rowIndex = sheet.Cells.FirstRowIndex;
rowIndex <= sheet.Cells.LastRowIndex; rowIndex++)
{
Row row = sheet.Cells.GetRow(rowIndex);
for (int colIndex = row.FirstColIndex;
colIndex <= row.LastColIndex; colIndex++)
{
Cell cell = row.GetCell(colIndex);
}
}
MessageBox.Show("File Created with Success");
}
public void SimpleReadWriteTest()
{
string tempFilePath = Path.GetTempFileName();
{
Workbook workbook = new Workbook();
Worksheet worksheet = new Worksheet("Test1");
worksheet.Cells[0, 1] = new Cell(100);
worksheet.Cells[2, 0] = new Cell("Test String");
workbook.Worksheets.Add(worksheet);
workbook.Save(tempFilePath);
}
{
Workbook workbook = Workbook.Load(tempFilePath);
Assert.AreEqual(1, workbook.Worksheets.Count);
Worksheet worksheet = workbook.Worksheets[0];
Assert.AreEqual("Test1", worksheet.Name);
Assert.AreEqual(100, worksheet.Cells[0, 1].Value);
Assert.AreEqual("Test String", worksheet.Cells[2, 0].Value);
}
}
public void WriteCellTest1()
{
string tempFilePath = Path.GetTempFileName();
{
Workbook workbook = new Workbook();
Worksheet worksheet1 = new Worksheet("Test 1");
Worksheet worksheet2 = new Worksheet("Test 2");
Worksheet worksheet3 = new Worksheet("Test 3");
worksheet1.Cells[0, 1] = new Cell("Test");
worksheet1.Cells[1, 0] = new Cell("Test");
workbook.Worksheets.Add(worksheet1);
workbook.Worksheets.Add(worksheet2);
workbook.Worksheets.Add(worksheet3);
workbook.Save(tempFilePath);
}
{
Workbook workbook = Workbook.Load(tempFilePath);
Assert.AreEqual(3, workbook.Worksheets.Count);
}
}
public void ExportBook()
{
try {
string file = "LMS_ExportBook.xls";
string query = "select * from library.book_database ;";
MySqlConnection myConn = DB.GetDB ();
MySqlCommand cmdDataBase = new MySqlCommand (query, myConn);
MySqlDataReader myReader;
myConn.Open ();
myReader = cmdDataBase.ExecuteReader ();
Workbook workbook = new Workbook ();
Worksheet worksheet = new Worksheet ("Book Sheet");
worksheet.Cells [0, 0] = new Cell ("Book ID");
worksheet.Cells [0, 1] = new Cell ("Book Title");
worksheet.Cells [0, 2] = new Cell ("Book Author");
worksheet.Cells [0, 3] = new Cell ("Book Genre");
worksheet.Cells [0, 4] = new Cell ("Book Copies");
worksheet.Cells [0, 5] = new Cell (DateTime.Now, @"YYYY\-MM\-DD");
line = 1;
while (myReader.Read ()) {
worksheet.Cells [line, 0] = new Cell (myReader.GetString ("book_id"));
worksheet.Cells [line, 1] = new Cell (myReader.GetString ("title"));
worksheet.Cells [line, 2] = new Cell (myReader.GetString ("author"));
worksheet.Cells [line, 3] = new Cell (myReader.GetString ("genre"));
worksheet.Cells [line, 4] = new Cell (myReader.GetString ("no_of_copies"));
line++;
}
myConn.Close ();
worksheet.Cells.ColumnWidth [0, 1] = 3000;
workbook.Worksheets.Add (worksheet);
workbook.Save (file);
MessageBox.Show ("Export Success\n" + (line - 1) + " data is exported");
} catch (Exception ex) {
MessageBox.Show (ex.Message);
}
}
private static void BuildSpreadsheet(IEnumerable<TimesheetEntry> timesheetEntries, string totalDuration)
{
string file = ConfigurationManager.AppSettings["ExportPath"];
var workbook = new Workbook();
var worksheet = new Worksheet("TimeSheet");
var index = 1;
foreach (var timesheetEntry in timesheetEntries)
{
worksheet.Cells[index, 0] = new Cell(timesheetEntry.Date);
worksheet.Cells[index, 1] = new Cell(timesheetEntry.DurationText);
worksheet.Cells[index, 2] = new Cell(timesheetEntry.Description);
index++;
}
worksheet.Cells[index + 5, 1] = new Cell(totalDuration);
AddWhitespace(index, worksheet);
workbook.Worksheets.Add(worksheet);
workbook.Save(file);
}
private void saveAsToolStripMenuItem_Click(object sender, EventArgs e)
{
if (doc == null) return;
string file = FileSelector.BrowseFileForSave(FileType.All);
if (file == null) return;
using (CompoundDocument newDoc = CompoundDocument.Create(file))
{
foreach (string streamName in doc.RootStorage.Members.Keys)
{
newDoc.WriteStreamData(new string[] { streamName }, doc.GetStreamData(streamName));
}
byte[] bookdata = doc.GetStreamData("Workbook");
if (bookdata != null)
{
if (workbook == null)
{
workbook = WorkbookDecoder.Decode(new MemoryStream(bookdata));
}
MemoryStream stream = new MemoryStream();
//WorkbookEncoder.Encode(workbook, stream);
BinaryWriter writer = new BinaryWriter(stream);
foreach (Record record in workbook.Records)
{
record.Write(writer);
}
writer.Close();
newDoc.WriteStreamData(new string[] { "Workbook" }, stream.ToArray());
}
newDoc.Save();
}
}
public void WriteFormulaTest()
{
string tempFilePath = Path.GetTempFileName();
{
Workbook workbook = new Workbook();
Worksheet worksheet = new Worksheet("Test");
worksheet.Cells[0, 0] = new Cell(10);
worksheet.Cells[0, 1] = new Cell(20);
worksheet.Cells[0, 2] = new Cell("=A1+B1");
workbook.Worksheets.Add(worksheet);
workbook.Save(tempFilePath);
}
{
Workbook workbook = Workbook.Load(tempFilePath);
Assert.AreEqual(10, workbook.Worksheets[0].Cells[0, 0].Value);
Assert.AreEqual(20, workbook.Worksheets[0].Cells[0, 1].Value);
Assert.AreEqual("=A1+B1", workbook.Worksheets[0].Cells[0, 2].Value);
}
}
public void WriteMultipleCellTest()
{
int worksheetToWrite = 30;
int rowToWrite = 200;
int columnToWrite = 15;
string tempFilePath = Path.GetTempFileName();
{
int start = Environment.TickCount;
Workbook workbook = new Workbook();
for (int sheet = 0; sheet < worksheetToWrite; sheet++)
{
Worksheet worksheet = new Worksheet(String.Format("Sheet {0}", sheet));
for (int row = 0; row < rowToWrite; row++)
for (int column = 0; column < columnToWrite; column++)
worksheet.Cells[row, column] = new Cell(String.Format("{0}{1}", row, column));
workbook.Worksheets.Add(worksheet);
}
workbook.Save(tempFilePath);
int end = Environment.TickCount;
Console.WriteLine(String.Format("Write tick count: {0}", end - start));
}
{
int start = Environment.TickCount;
Workbook workbook = Workbook.Load(tempFilePath);
for (int sheet = 0; sheet < worksheetToWrite; sheet++)
for (int row = 0; row < rowToWrite; row++)
for (int column = 0; column < columnToWrite; column++)
{
Assert.AreEqual(String.Format("{0}{1}", row, column), workbook.Worksheets[sheet].Cells[row, column].Value);
}
int end = Environment.TickCount;
Console.WriteLine(String.Format("Read tick count: {0}", end - start));
}
}
public static void saveRevenue(string range, List<string[]> l)
{
try
{
Workbook wb = new Workbook();
Worksheet ws = new Worksheet("revenue");
SaveFileDialog save = new SaveFileDialog();
save.Title = "Save file dialog";
save.Filter = "Microsoft Office Excel (*.xls)|.xls";
save.FileName = "revenue_" + range + ".xls";
bool resul = (bool)save.ShowDialog();
if (resul)
{
string file = save.FileName;
// Handle office 2010 neds more than 200 rows for properly open
for (int i = 0; i < 201; i++)
{
ws.Cells[i, 1] = new Cell(" ");
}
// END handle
ws.Cells[0, 0] = new Cell("Revenue range:");
ws.Cells[0, 1] = new Cell(range);
ws.Cells[1, 0] = new Cell("bottle");
ws.Cells[1, 1] = new Cell("small pieces");
ws.Cells[1, 2] = new Cell("small sum");
ws.Cells[1, 3] = new Cell("medium pieces");
ws.Cells[1, 4] = new Cell("medium sum");
ws.Cells[1, 5] = new Cell("large pieces");
ws.Cells[1, 6] = new Cell("large sum");
ws.Cells[1, 7] = new Cell("VAT");
ws.Cells[1, 8] = new Cell("total");
int cell = 0;
int row = 2;
foreach (string[] s in l)
{
ws.Cells[row, cell++] = new Cell("bottle " + s[0]);
ws.Cells[row, cell++] = new Cell(s[1]);
ws.Cells[row, cell++] = new Cell(s[2], "0.00");
ws.Cells[row, cell++] = new Cell(s[3]);
ws.Cells[row, cell++] = new Cell(s[4], "0.00");
ws.Cells[row, cell++] = new Cell(s[5]);
ws.Cells[row, cell++] = new Cell(s[6], "0.00");
ws.Cells[row, cell++] = new Cell(s[7], "0.00");
ws.Cells[row, cell++] = new Cell(s[8], "0.00");
row++;
cell = 0;
}
ws.Cells[--row, 9] = new Cell(settings.Currency);
ws.Cells.ColumnWidth[0, 8] = 4000;
wb.Worksheets.Add(ws);
wb.Save(file);
}
}
catch (Exception ex)
{
log.write("app", ex.Message + "\r\n " + ex.TargetSite);
}
}
/// <summary>
/// save data back to file
/// </summary>
public void Save(string worksheetName = null)
{
try
{
Workbook workbook = new Workbook();
Worksheet worksheet = new Worksheet(worksheetName == null ? m_WorksheetName : worksheetName);
for (int i = 0; i < Lines.Count; i++)
{
for (int j = 0; j < Lines[i].ColumnCount; j++)
worksheet.Cells[i, j] = new Cell(Lines[i].Columns[j]);
}
int workaround = 100 - Lines.Count;
for (int i = 0; i < workaround; i++)
worksheet.Cells[Lines.Count + i, 0] = new Cell(string.Empty);
workbook.Worksheets.Add(worksheet);
workbook.Save(WorkingDir + FileName + FileExtension);
}
catch
{
throw;
}
}
public static void saveLog(string range, List<statistic> l)
{
try
{
Workbook wb = new Workbook();
Worksheet ws = new Worksheet("log");
SaveFileDialog save = new SaveFileDialog();
save.Title = "Save file dialog";
save.Filter = "Microsoft Office Excel (*.xls)|.xls";
save.FileName = "LOG_" + range + ".xls";
bool resul = (bool)save.ShowDialog();
if (resul)
{
string file = save.FileName;
// Handle office 2010 neds more than 200 rows for properly open
for (int i = 0; i < 201; i++)
{
ws.Cells[i, 1] = new Cell(" ");
}
// END handle
ws.Cells[0, 0] = new Cell("Log range:");
ws.Cells[0, 1] = new Cell(range);
ws.Cells[1, 0] = new Cell("index");
ws.Cells[1, 1] = new Cell("date");
ws.Cells[1, 2] = new Cell("card");
ws.Cells[1, 3] = new Cell("event");
ws.Cells[1, 4] = new Cell("val1");
ws.Cells[1, 5] = new Cell("val2");
ws.Cells[1, 6] = new Cell("val3");
ws.Cells[1, 7] = new Cell("val4");
ws.Cells[1, 8] = new Cell("val5");
ws.Cells[1, 9] = new Cell("val6");
int cell = 0;
int row = 2;
foreach (statistic s in l)
{
ws.Cells[row, cell++] = new Cell(s.id);
ws.Cells[row, cell++] = new Cell(s.time.ToString());
ws.Cells[row, cell++] = new Cell(s.card);
ws.Cells[row, cell++] = new Cell(s.ev);
ws.Cells[row, cell++] = new Cell(s.val1);
ws.Cells[row, cell++] = new Cell(s.val2);
ws.Cells[row, cell++] = new Cell(s.val3);
ws.Cells[row, cell++] = new Cell(s.val4);
ws.Cells[row, cell++] = new Cell(s.val5);
ws.Cells[row, cell++] = new Cell(s.val6);
row++;
cell = 0;
}
ws.Cells.ColumnWidth[0, 8] = 4000;
wb.Worksheets.Add(ws);
wb.Save(file);
}
}
catch (Exception ex)
{
log.write("app", ex.Message + "\r\n " + ex.TargetSite);
}
}
public static void Main()
{
List<Student> students = GetInput();
List<Student> onlineStudentsSortedByResult = students
.Where(student => student.StudentType == "Online")
.OrderByDescending(student => student.CalculateResult())
.ToList();
// creating new workbook and sheet
const string pathToOutputFile = "../../output/outputFile.xls";
Workbook workbook = new Workbook();
Worksheet worksheet = new Worksheet("Online Students");
// adding all the students
for (int i = 0; i < onlineStudentsSortedByResult.Count; i++)
{
worksheet.Cells[i, 0] = new Cell(onlineStudentsSortedByResult[i].ID);
worksheet.Cells[i, 1] = new Cell(onlineStudentsSortedByResult[i].FirstName);
worksheet.Cells[i, 2] = new Cell(onlineStudentsSortedByResult[i].LastName);
worksheet.Cells[i, 3] = new Cell(onlineStudentsSortedByResult[i].Email);
worksheet.Cells[i, 4] = new Cell(onlineStudentsSortedByResult[i].Gender);
worksheet.Cells[i, 5] = new Cell(onlineStudentsSortedByResult[i].StudentType);
worksheet.Cells[i, 6] = new Cell(onlineStudentsSortedByResult[i].ExamResult);
worksheet.Cells[i, 7] = new Cell(onlineStudentsSortedByResult[i].HomeworkSent);
worksheet.Cells[i, 8] = new Cell(onlineStudentsSortedByResult[i].HomeworkEvaluated);
worksheet.Cells[i, 9] = new Cell(onlineStudentsSortedByResult[i].TeamworkScore);
worksheet.Cells[i, 10] = new Cell(onlineStudentsSortedByResult[i].AttendacesCount);
worksheet.Cells[i, 11] = new Cell(onlineStudentsSortedByResult[i].Bonus);
worksheet.Cells[i, 12] = new Cell(onlineStudentsSortedByResult[i].CalculateResult());
}
// saving the workbook
workbook.Worksheets.Add(worksheet);
workbook.Save(pathToOutputFile);
}
static void Main(string[] args)
{
var underTest = new Workbook();
var sheet = new Worksheet("Sheet 1");
var styleA = new CellFormat();
styleA.SetBackgroundColor(ExcelColor.Red);
styleA.Border.DiagonalUp = true;
styleA.Border.DiagonalDown = true;
styleA.Border.DiagonalStyle = CellBorderStyle.Thin;
var styleB = new CellFormat
{
Pattern =
{
Style = PatternStyle.LightDown,
ForegroundColor = ExcelColor.Blue
}
};
var styleC = new CellFormat();
styleC.SetBackgroundColor(ExcelColor.Silver);
styleC.Border = CellBorder.MediumBox;
styleC.Font.Bold = true;
for (var i = 0; i < 100; i++)
{
var cellA = new Cell("Abcde");
var cellB = new Cell(1234);
var cellC = new Cell(string.Format("This is row {0:000}", i));
if (i%2 == 0)
{
cellA.CellFormat = styleA;
cellB.CellFormat = styleB;
cellC.CellFormat = styleC;
}
else
{
cellB.CellFormat.TextControl.RotationStyle = RotationStyle.CounterClockwise;
cellB.CellFormat.TextControl.TextRotation = 45;
cellC.VerticalAlignment = VerticalAlignStyle.Centered;
cellC.CellFormat.Font.Name = "Times New Roman";
cellC.CellFormat.Font.Family = FontFamilyType.Roman;
cellC.CellFormat.Font.Height = 240;
}
sheet.Cells[i, 0] = cellA;
sheet.Cells[i, 1] = cellB;
sheet.Cells[i, 2] = cellC;
sheet.Cells.ColumnWidth[2] = 256 * 15;
}
underTest.Worksheets.Add(sheet);
var filename = Path.Combine(Environment.GetFolderPath(Environment.SpecialFolder.DesktopDirectory), "test.xls");
if (File.Exists(filename))
{
File.Delete(filename);
}
underTest.Save(filename);
}
