/// <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)
{
// 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");
}
public static void MergeCells(ref C1.C1Excel.XLSheet sheet, C1.C1Excel.XLStyle style, int rowFrom, int rowTo, int colForm, int colTo, object value)
{
if (style == null)
{
throw new ArgumentNullException("style", "style is null.");
}
if (value == null)
{
throw new ArgumentNullException("value", "value is null.");
}
for (int x = rowFrom; x <= rowTo; x++)
{
for (int y = colForm; y <= colTo; y++)
{
sheet[x, y].Style = style;
}
}
sheet.MergedCells.Add(new C1.C1Excel.XLCellRange(rowFrom, rowTo, colForm, colTo));
sheet[rowFrom, colForm].Value = value;
}