/// <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; }