GoogleCodeExporter
commented
9 years ago 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
Original issue reported on code.google.com by
yan.uniko.102@gmail.comon 7 Nov 2013 at 7:30