private void ExportToExcel(DataTable dt) { using (SaveFileDialog sfd = new SaveFileDialog() { Filter = "xlsx files|*.xlsx", DefaultExt = "xlsx" }) { if (sfd.ShowDialog() != DialogResult.OK) { return; } using (var workbook = new XLWorkbook()) { if (dt.TableName == "") dt.TableName = "sheet1"; var worksheet = workbook.Worksheets.Add(dt.TableName); var header = worksheet.FirstRow(); for (int i = 0; i < dt.Columns.Count; ++i) { worksheet.Cell(1, i + 1).Value = dt.Columns[i].ColumnName; worksheet.Cell(1, i + 1).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center; worksheet.Cell(1, i + 1).Style.Alignment.Vertical = XLAlignmentVerticalValues.Center; } for (int i = 0; i < dt.Rows.Count; ++i) { for (int j = 0; j < dt.Columns.Count; ++j) { worksheet.Cell(i + 2, j + 1).Value = dt.Rows[i][j].ToString(); worksheet.Cell(i + 2, j + 1).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center; worksheet.Cell(i + 2, j + 1).Style.Alignment.Vertical = XLAlignmentVerticalValues.Center; } } workbook.SaveAs(sfd.FileName); } } }
private DataTable ImportExcelToDataTable() { DataTable dt = new DataTable(); using (OpenFileDialog ofd = new OpenFileDialog()) { if (ofd.ShowDialog() != DialogResult.OK) { return dt; } using (XLWorkbook workBook = new XLWorkbook(ofd.FileName)) { IXLWorksheet workSheet = workBook.Worksheet(1); bool firstRow = true; foreach (var row in workSheet.Rows()) { if (firstRow) { foreach (IXLCell cell in row.Cells()) { dt.Columns.Add(cell.Value.ToString()); } firstRow = false; } else { dt.Rows.Add(); int i = 0; foreach (IXLCell cell in row.Cells()) { dt.Rows[dt.Rows.Count - 1][i] = cell.Value.ToString(); i++; } } } } return dt; } }