使用ClosedXML读写excel

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

 

使用ClosedXML读写excel

上一篇:深入理解Tornado——一个异步web服务器


下一篇:bitcoin PoW原理及区块创建过程