private void button1_Click(object sender, System.EventArgs e)
{
// clear book
c1XLBook1.Clear();
// add some styles
XLStyle s1 = new XLStyle(c1XLBook1);
XLStyle s2 = new XLStyle(c1XLBook1);
XLStyle s3 = new XLStyle(c1XLBook1);
s1.Format = "#,##0.00000";
s2.Format = "#,##0.00000";
s2.Font = new Font("Courier New", 14);
s3.Format = "dd-MMM-yy";
// populate sheet with some random values
C1.C1Excel.XLSheet sheet = c1XLBook1.Sheets[0];
Random r = new Random();
for (int i = 0; i < 100; i++)
{
sheet[i, 0].Value = r.NextDouble() * 100000;
sheet[i, 0].Style = (i % 13 == 0)? s2 : s1;
}
for (int i = 0; i < 100; i++)
{
sheet[i, 1].Value = new DateTime(2005, r.Next(1, 12), r.Next(1, 28));
sheet[i, 1].Style = s3;
}
string tempdir = Application.ExecutablePath.Substring(0,
Application.ExecutablePath.LastIndexOf("\\") + 1);
// save with default column widths
c1XLBook1.Save(tempdir + @"defaultWidth.xls");
// autosize columns
AutoSizeColumns(sheet);
// save again after autosizing
c1XLBook1.Save(tempdir + @"autoSized.xls");
// show the autosized version
System.Diagnostics.Process.Start(tempdir + @"autoSized.xls");
}
private void button1_Click(object sender, System.EventArgs e)
{
// copy the book
string xlsFileName = "..\\..\\XlsxTestBin.xlsm";
c1XLBook1.Load(xlsFileName);
c1XLBook1.Sheets[0][0, 0].Value = "It is copy!";
xlsFileName = xlsFileName.Replace(".xlsm", "Modified.xlsm");
c1XLBook1.Save(xlsFileName);
// run Excel file
System.Diagnostics.Process.Start(xlsFileName);
}
private void button2_Click(object sender, System.EventArgs e)
{
// apply print settings
ApplyPrintSettings();
// save book
string tempdir = Application.ExecutablePath.Substring(0,
Application.ExecutablePath.LastIndexOf("\\") + 1);
string xlsFile = tempdir + @"psOut.xls";
c1XLBook1.Save(xlsFile);
System.Diagnostics.Process.Start(xlsFile);
}
/// <summary>
/// 导出EXCEL
/// </summary>
/// <param name="fileName"></param>
/// <param name="captain"></param>
/// <param name="dt"></param>
private void ExportTo(string fileName, string captain, DataTable dt)
{
//是否存在Excel文件
bool isExist = System.IO.File.Exists(fileName);
//将当前水标状态保存到临时变量中后将光标置为忙状态
Cursor currentCursor = Cursor.Current;
Cursor.Current = Cursors.WaitCursor;
// populate sheet with some random values
C1.C1Excel.XLSheet sheet = c1XLBook1.Sheets[0];
//获取列名
for (int i = 0; i < dt.Columns.Count; i++)
{
sheet[1, i].Value = dt.Columns[i].Caption;
}
//获取dt内的数据
for (int i = 0; i < dt.Rows.Count; i++)
{
DataRow dr = dt.Rows[i];
for (int j = 0; j < dt.Columns.Count; j++)
{
sheet[i + 2, j].Value = dr[j];
}
}
//如果存在Excel文件,将生成的表加入该文件
if (isExist)
{
// load Excel file
C1XLBook book = new C1XLBook();
book.Load(fileName);
// clone and rename first sheet (sheet names must be unique)
XLSheet clone = book.Sheets[0].Clone();
clone.Name = Path.GetFileNameWithoutExtension(fileName);
// add cloned sheet to main book
c1XLBook1.Sheets.Add(clone);
}
string tempdir = Application.ExecutablePath.Substring(0,
Application.ExecutablePath.LastIndexOf("\\") + 1);
// autosize columns
AutoSizeColumns(sheet);
// save with default column widths
c1XLBook1.Save(tempdir + fileName + ".xls");
System.Diagnostics.Process.Start(tempdir + fileName + ".xls");
}
private void button1_Click(object sender, System.EventArgs e)
{
string path = Application.StartupPath;
int pos = path.IndexOf(@"\bin");
path = path.Substring(0, pos);
string tempdir = Application.ExecutablePath.Substring(0,
Application.ExecutablePath.LastIndexOf("\\") + 1);
string xlsFileName = tempdir + @"combineSheets.xls";
if (File.Exists(xlsFileName))
{
File.Delete(xlsFileName);
}
// clear the book
c1XLBook1.Clear();
c1XLBook1.Sheets.Clear();
foreach (string fileName in Directory.GetFiles(path, "*.xls"))
{
// load Excel file
C1XLBook book = new C1XLBook();
book.Load(fileName);
// clone and rename first sheet (sheet names must be unique)
XLSheet clone = book.Sheets[0].Clone();
clone.Name = Path.GetFileNameWithoutExtension(fileName);
// add cloned sheet to main book
c1XLBook1.Sheets.Add(clone);
}
c1XLBook1.Save(xlsFileName);
System.Diagnostics.Process.Start(xlsFileName);
}
// handle toolbar
private void toolBar1_ButtonClick(object sender, System.Windows.Forms.ToolBarButtonClickEventArgs e)
{
// apply border to current selection
if (e.Button == tbBorderOn)
{
// assign border style to selection
CellRange rg = _flex.Selection;
rg.Style = _flex.Styles["Border"];
}
// remove border from current selection
if (e.Button == tbBorderOff)
{
// remove border style from selection
CellRange rg = _flex.Selection;
rg.Style = null;
}
// change border color
if (e.Button == tbBorderColor)
{
// show color picker dialog
ColorDialog dlg = new ColorDialog();
dlg.Color = _bdrBrush.Color;
// if the user clicked OK, set new border color
if (dlg.ShowDialog() == DialogResult.OK)
{
_bdrBrush.Color = dlg.Color;
}
}
// thicker/thinner outer border
if (e.Button == tbIncreaseOut)
{
if (_bdrOutside < 10)
{
_bdrOutside++;
}
}
if (e.Button == tbDecreaseOut)
{
if (_bdrOutside > 0)
{
_bdrOutside--;
}
}
// thicker/thinner inner border
if (e.Button == tbIncreaseIn)
{
if (_bdrInside < 10)
{
_bdrInside++;
}
}
if (e.Button == tbDecreaseIn)
{
if (_bdrInside > 0)
{
_bdrInside--;
}
}
// save current sheet (with borders) into an Excel file
if (e.Button == tbExcel)
{
_c1xl.Clear();
_c1xl.DefaultFont = _flex.Font;
XLSheet sheet = _c1xl.Sheets[0];
for (int c = 0; c < _flex.Cols.Count; c++)
{
// save column width (twips)
sheet.Columns[c].Width = (int)(_flex.Cols[c].WidthDisplay / 96f * 1440);
// save cells on this column
for (int r = 0; r < _flex.Rows.Count; r++)
{
// save cell value
sheet[r, c].Value = _flex[r, c];
// we only want cells with style set to "Border"
CellStyle s = _flex.GetCellStyle(r, c);
if (s == null || s.Name != "Border")
{
continue;
}
// get custom border widths for this cell
// (depends on neighbor cells)
Margins m = GetBorderMargins(r, c);
// create stytle for this cell
XLStyle xs = new XLStyle(_c1xl);
if (m.Top > 0)
{
xs.BorderTop = GetLineStyle(m.Top);
xs.BorderColorTop = _bdrBrush.Color;
}
if (m.Left > 0)
{
xs.BorderLeft = GetLineStyle(m.Left);
xs.BorderColorLeft = _bdrBrush.Color;
}
if (m.Right > 0)
{
xs.BorderRight = GetLineStyle(m.Right);
xs.BorderColorRight = _bdrBrush.Color;
}
if (m.Bottom > 0)
{
xs.BorderBottom = GetLineStyle(m.Bottom);
xs.BorderColorBottom = _bdrBrush.Color;
}
sheet[r, c].Style = xs;
}
}
// save book
string fileName = Application.StartupPath + @"\borders.xls";
_c1xl.Save(fileName);
System.Diagnostics.Process.Start(fileName);
}
// repaint control to show changes
_flex.Invalidate();
}