What is dgvCells? - Githubissues

using System;
using System.Collections.Generic;
using System.IO;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using ExcelLibrary.SpreadSheet;
using System.IO;
using System.Data;

namespace Collections
{
    public class excel
    {
        public void exptoxls(string filename, DataGridView dgv)
        {
            if (filename == string.Empty)
            {
                SaveFileDialog file = new SaveFileDialog();
                file.DefaultExt = "xls";
                file.Filter = "Excel文件|*.xls";
                file.Title = "导出的EXCEL文件保存到哪里?:";
                if (file.ShowDialog() == DialogResult.OK) { filename = file.FileName; }
                else { return; }
            }
            Workbook workbook = new Workbook();
            Worksheet worksheet = new Worksheet("sheet1");
            /*worksheet.Cells[0, 1] = new Cell((short)1);
            worksheet.Cells[2, 0] = new Cell(9999999);
            worksheet.Cells[3, 3] = new Cell((decimal)3.45);
            worksheet.Cells[2, 2] = new Cell("Text string");
            worksheet.Cells[4, 0] = new Cell(32764.5, "#,##0.00");
            worksheet.Cells[5, 1] = new Cell(DateTime.Now, @"YYYY\-MM\-DD");
            worksheet.Cells.ColumnWidth[0, 1] = 3000;
            */
            //输出列名
            foreach (DataGridViewColumn column in dgv.Columns)
            {
                worksheet.Cells[0, column.Index] = new Cell(column.HeaderText);
            }

            foreach (DataGridViewRow row in dgv.Rows)
            {
                foreach (DataGridViewColumn column in dgv.Columns)
                {
                    //处理日期,数字类型字段
                    if (column.HeaderText.IndexOf("日期") >= 0 && row.Cells[column.Index].Value != null) { worksheet.Cells[row.Index + 1, column.Index] = new Cell(row.Cells[column.Index].Value, @"YYYY\-MM\-DD HH:mm"); }
                    else if (column.HeaderText.IndexOf("时间") >= 0 && row.Cells[column.Index].Value.ToString() != string.Empty) { worksheet.Cells[row.Index + 1, column.Index] = new Cell(row.Cells[column.Index].Value, @"YYYY\-MM\-DD HH:mm"); }
                    else if (column.HeaderText.IndexOf("重量") >= 0 && row.Cells[column.Index].Value.ToString() != string.Empty) { worksheet.Cells[row.Index + 1, column.Index] = new Cell(row.Cells[column.Index].Value, "#,##0.00"); }
                    else if (column.HeaderText.IndexOf("运费") >= 0 && row.Cells[column.Index].Value.ToString() != string.Empty) { worksheet.Cells[row.Index + 1, column.Index] = new Cell(row.Cells[column.Index].Value, "#,##0.00"); }
                    else if (column.HeaderText.IndexOf("货款") >= 0 && row.Cells[column.Index].Value.ToString() != string.Empty) { worksheet.Cells[row.Index + 1, column.Index] = new Cell(row.Cells[column.Index].Value, "#,##0.00"); }
                    else if (column.HeaderText.IndexOf("体积") >= 0 && row.Cells[column.Index].Value.ToString() != string.Empty) { worksheet.Cells[row.Index + 1, column.Index] = new Cell(row.Cells[column.Index].Value, "#,##0.00"); }
                    else if (column.HeaderText.IndexOf("附加费") >= 0 && row.Cells[column.Index].Value.ToString() != string.Empty) { worksheet.Cells[row.Index + 1, column.Index] = new Cell(row.Cells[column.Index].Value, "#,##0.00"); }
                    else if (column.HeaderText.IndexOf("到付") >= 0 && row.Cells[column.Index].Value.ToString() != string.Empty) { worksheet.Cells[row.Index + 1, column.Index] = new Cell(row.Cells[column.Index].Value, "#,##0.00"); }
                    else if (column.HeaderText.IndexOf("款") >= 0 && row.Cells[column.Index].Value.ToString() != string.Empty) { worksheet.Cells[row.Index + 1, column.Index] = new Cell(row.Cells[column.Index].Value, "#,##0.00"); }
                    else if (column.HeaderText.IndexOf("件数") >= 0 && row.Cells[column.Index].Value.ToString() != string.Empty) { worksheet.Cells[row.Index + 1, column.Index] = new Cell(row.Cells[column.Index].Value, "#"); }

                    else { worksheet.Cells[row.Index + 1, column.Index] = new Cell(row.Cells[column.Index].Value.ToString()); }
                }
            }

            workbook.Worksheets.Add(worksheet);
            workbook.Save(filename);
            MessageBox.Show("文件已导出", "系统提示");
        }
        public void imtfromxls(string filename, DataGridView dgv)
        {
            if (filename == string.Empty)
            {
                OpenFileDialog file = new OpenFileDialog();
                file.DefaultExt = "xls";
                file.Filter = "Excel文件|*.xls";
                file.Title = "您导入哪个EXCEL文件呢?";
                if (file.ShowDialog() == DialogResult.OK) { filename = file.FileName; }
                else { return; }
            }
            if (!File.Exists(filename)) { MessageBox.Show("文件不存在,无法导入", "系统提示"); }
            else
            {
                Workbook book = Workbook.Load(filename);
                Worksheet sheet = book.Worksheets[0];
                if (sheet.Cells.Rows.Count > 0)
                {
                    DataTable tab = new DataTable();
                    Row HeadRow = sheet.Cells.GetRow(0);
                    DataColumn dc;

                    //把第一行当成列标题
                    for (int a = 0; a < HeadRow.LastColIndex; a++)
                    {
                        dc = new DataColumn(HeadRow.GetCell(a).StringValue);
                        tab.Columns.Add(dc);
                    }
                    for (int rowIndex = sheet.Cells.FirstRowIndex + 1; rowIndex <= sheet.Cells.LastRowIndex; rowIndex++)
                    {
                        DataRow dr = tab.NewRow();
                        Row row = sheet.Cells.GetRow(rowIndex);

                        for (int colIndex = row.FirstColIndex; colIndex <= row.LastColIndex; colIndex++)
                        {
                            if (HeadRow.GetCell(colIndex).StringValue.IndexOf("日期") >= 0 || HeadRow.GetCell(colIndex).StringValue.IndexOf("时间") >= 0)
                            {
                                Double df = double.Parse(row.GetCell(colIndex).StringValue);
                                DateTime dt= DateTime.FromOADate(df);
                                dr[colIndex] = dt.ToString("yyyy-MM-dd HH:mm:ss");
                            }
                            else { dr[colIndex] = row.GetCell(colIndex).StringValue; }
                        }
                        tab.Rows.Add(dr);
                    }
                    book = null;
                    sheet = null;
                    dgv.DataSource = tab;
                }
                else { MessageBox.Show("指定的EXCEL文件没有数据", "系统提示"); }
            }
        }
    }
}
Original comment by mingiqu...@gmail.com on 15 Nov 2013 at 8:50
realgz / excellibrary

What is dgvCells? #154
