---运行代码
public partial class Form1 : Form
{
public Form1()
{
InitializeComponent();
//DataGridView中最后空白的一行
//this.dataGridView1.AllowUserToAddRows = true;
去掉左侧前面 选择行
//this.dataGridView1.RowHeadersVisible = false;
}
BindingList<GongshiModel> model = new BindingList<GongshiModel>();
private Point mPoint;
private void btnLiulan_Click(object sender, EventArgs e)
{
OpenFileDialog dialog = new OpenFileDialog();
dialog.Multiselect = false;//该值确定是否可以选择多个文件
dialog.Title = "请选择工时表文件";
dialog.Filter = "Excel文件|*.xls;*.xlsx";
if (dialog.ShowDialog() == System.Windows.Forms.DialogResult.OK)
{
txtLujing.Text = dialog.FileName;
ExcelReder excelHelper = new ExcelReder();
DataTable dt = excelHelper.ReadExcelFile(dialog.FileName, "select * from [Sheet1$]");
if (dt != null && dt.Rows.Count > 0)
{
dataGridView1.DataSource = dt;
}
}
}
/// <summary>
/// 计算
/// </summary>
public void JiSuanMoney()
{
try
{
if (txtJine.Text != "")
{
//计算总数
double columnSum = 0;
for (int i = 0; i < 22; i++)
{
columnSum += double.Parse(this.dataGridView1.Rows[i].Cells[4].Value.ToString());
}
this.dataGridView1.Rows[22].Cells[4].Value = columnSum;
//计算每一个的平均数
double columnSum1 = 0;
for (int i = 0; i < 22; i++)
{
columnSum1 = double.Parse(this.dataGridView1.Rows[i].Cells[4].Value.ToString()) / columnSum * Int32.Parse(txtJine.Text);
this.dataGridView1.Rows[i].Cells[6].Value = columnSum1.ToString("0.00");
}
}
else
{
MessageBox.Show("计算金额不能为空!请输入金额!");
}
#region 根据输入的行数计算总数和总和
//if (txtRow.Text != "" && txtRows.Text != "")
//{
// int r = Int32.Parse(txtRow.Text);
// //计算总数
// double columnSum = 0;
// for (int i = 0; i < r; i++)
// {
// columnSum += double.Parse(this.dataGridView1.Rows[i].Cells[4].Value.ToString());
// }
// this.dataGridView1.Rows[r].Cells[4].Value = columnSum;
// if (txtJine.Text != "")
// {
// int rs = Int32.Parse(txtRows.Text);
// //计算每一个的平均数
// double columnSum1 = 0;
// for (int i = 0; i < rs; i++)
// {
// columnSum1 = double.Parse(this.dataGridView1.Rows[i].Cells[4].Value.ToString()) / columnSum * Int32.Parse(txtJine.Text);
// this.dataGridView1.Rows[i].Cells[6].Value = columnSum1.ToString("0.00");
// }
// }
// else
// {
// MessageBox.Show("计算金额不能为空!");
// }
//}
//else
//{
// MessageBox.Show("总项目号工时行数和总和项目号工时行数不能为空");
//}
#endregion
}
catch (Exception e)
{
throw e;
}
}
/// <summary>
/// 计算按钮
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void btnJiSuan_Click(object sender, EventArgs e)
{
JiSuanMoney();
}
/// <summary>
/// 导出算出数据的Excel文件
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void btnDaoRu_Click(object sender, EventArgs e)
{
ExcelDaoChu e2e = new ExcelDaoChu();
e2e.ExportExcel(DateTime.Now.ToString("yyyyMMddHHmmssfff") + ".xlsx", dataGridView1, "微软雅黑", 12);
}
#region 按钮点击事件
private void Form1_MouseDown(object sender, MouseEventArgs e)
{
mPoint = new Point(e.X, e.Y);
}
private void Form1_MouseMove(object sender, MouseEventArgs e)
{
if (e.Button == MouseButtons.Left)
{
this.Location = new Point(this.Location.X + e.X - mPoint.X, this.Location.Y + e.Y - mPoint.Y);
}
}
#endregion
/// <summary>
/// 退出
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void btnTuiChu_Click(object sender, EventArgs e)
{
Application.Exit();
}
}
---类
public class GongshiModel
{
/// <summary>
/// 项目号
/// </summary>
public string Xiangmuhao { get; set; }
/// <summary>
/// 项目号工时
/// </summary>
public float XiangmuhaoH { get; set; }
/ <summary>
/ 项目合计工时
/ </summary>
//public float XiangmuheH { get; set; }
/// <summary>
/// 项目号总金额
/// </summary>
public float Xiangmujine { get; set; }
}
---EXECL类
public class ExcelReder
{
public DataTable ReadExcelFile(string FileLoad, string sql)
{
string FlieType = Path.GetExtension(FileLoad);
string strConn = string.Empty;
if (FlieType == ".xls")
{
strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + FileLoad + ";" + "Extended Properties=Excel 8.0;;";
}
else if (FlieType == ".xlsx")
{
strConn = "Provider=Microsoft.Ace.OLEDB.12.0;" + "Data Source=" + FileLoad + ";" + "Extended Properties=Excel 12.0;";
}
else
{
MessageBox.Show("文件格式错误,请重新选择!!!");
return null;
}
try
{
using (OleDbConnection conn = new OleDbConnection(strConn))
{
conn.Open();
OleDbDataAdapter da = new OleDbDataAdapter(sql, strConn);
DataSet ds = new DataSet();
da.Fill(ds);
return ds.Tables[0];
}
}
catch (Exception ex)
{
MessageBox.Show("读取excle出错:" + ex.Message);
return null;
}
}
}
--- EXECL类
public class ExcelDaoChu
{
#region NPOI DataGridView 导出 EXCEL
///
/// NPOI DataGridView 导出 EXCEL
/// /// 03版Excel-xls最大行数是65536行,最大列数是256列
/// /// 07版Excel-xlsx最大行数是1048576行,最大列数是16384列
/// // 默认保存文件名 /// DataGridView /// 字体名称 /// 字体大小
public void ExportExcel(string fileName, DataGridView dgv, string fontname, short fontsize)
{
IWorkbook workbook;
ISheet sheet;
Stopwatch sw = null;
//判断datagridview中内容是否为空
if (dgv.Rows.Count == 0)
{
MessageBox.Show("DataGridView中内容为空,请先导入数据!", "提示", MessageBoxButtons.OK, MessageBoxIcon.Warning);
return;
}
//保存文件
string saveFileName = "";
SaveFileDialog saveFileDialog = new SaveFileDialog();
saveFileDialog.DefaultExt = "xls";
saveFileDialog.Filter = "Excel文件(*.xls)|*.xls|Excel文件(*.xlsx)|*.xlsx";
saveFileDialog.RestoreDirectory = true; saveFileDialog.Title = "Excel文件保存路径";
saveFileDialog.FileName = fileName;
MemoryStream ms = new MemoryStream();
//MemoryStream
if (saveFileDialog.ShowDialog() == DialogResult.OK)
{
//**程序开始计时**//
sw = new Stopwatch();
sw.Start();
saveFileName = saveFileDialog.FileName;
//检测文件是否被占用
if (!CheckFiles(saveFileName))
{
MessageBox.Show("文件被占用,请关闭文件" + saveFileName);
workbook = null; ms.Close(); ms.Dispose(); return;
}
}
else
{
workbook = null;
ms.Close();
ms.Dispose();
}
//*** 根据扩展名xls和xlsx来创建对象
string fileExt = Path.GetExtension(saveFileName).ToLower();
if (fileExt == ".xlsx")
{
workbook = new XSSFWorkbook();
}
else if (fileExt == ".xls")
{
workbook = new HSSFWorkbook();
}
else
{
workbook = null;
}
//*** //创建Sheet
if (workbook != null)
{
sheet = workbook.CreateSheet("Sheet1");
//Sheet的名称
}
else
{
return;
}
//设置单元格样式
ICellStyle cellStyle = workbook.CreateCellStyle();
//水平居中对齐和垂直居中对齐
cellStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
cellStyle.VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment.Center;
//设置字体
IFont font = workbook.CreateFont();
font.FontName = fontname; //字体名称
font.FontHeightInPoints = fontsize;//字号
font.Color = NPOI.HSSF.Util.HSSFColor.Black.Index;//字体颜色
cellStyle.SetFont(font); //添加列名
IRow headRow = sheet.CreateRow(1);
for (int i = 0; i < dgv.Columns.Count; i++)
{ //隐藏行列不导出
if (dgv.Columns[i].Visible == true)
{
headRow.CreateCell(i+1).SetCellValue(dgv.Columns[i].HeaderText);
headRow.GetCell(i+1).CellStyle = cellStyle;
}
}
//根据类型写入内容
for (int rowNum = 0; rowNum < dgv.Rows.Count; rowNum++)
{ ///跳过第一行,第一行为列名
IRow dataRow = sheet.CreateRow(rowNum + 2);
for (int columnNum = 0; columnNum < dgv.Columns.Count; columnNum++)
{
int columnWidth = sheet.GetColumnWidth(columnNum+1) / 256; //列宽
//隐藏行列不导出
if (dgv.Rows[rowNum].Visible == true && dgv.Columns[columnNum].Visible == true)
{ //防止行列超出Excel限制
if (fileExt == ".xls")
{
//03版Excel最大行数是65536行,最大列数是256列
if (rowNum > 65536)
{
MessageBox.Show("行数超过Excel限制!");
return;
}
if (columnNum > 256)
{
MessageBox.Show("列数超过Excel限制!");
return;
}
}
else if (fileExt == ".xlsx")
{ //07版Excel最大行数是1048576行,最大列数是16384列
if (rowNum > 1048576)
{
MessageBox.Show("行数超过Excel限制!");
return;
}
if (columnNum > 16384)
{
MessageBox.Show("列数超过Excel限制!");
return;
}
}
ICell cell = dataRow.CreateCell(columnNum+1);
cell.SetCellValue(dgv.Rows[rowNum].Cells[columnNum].Value == null ? "" : dgv.Rows[rowNum].Cells[columnNum].Value.ToString());
//if (dgv.Rows[rowNum].Cells[columnNum].Value == null)
//{
// cell.SetCellType(CellType.Blank);
//}
//else
//{
// if (dgv.Rows[rowNum].Cells[columnNum].ValueType.FullName.Contains("System.Int32"))
// {
// cell.SetCellValue(Convert.ToInt32(dgv.Rows[rowNum].Cells[columnNum].Value));
// }
// else if (dgv.Rows[rowNum].Cells[columnNum].ValueType.FullName.Contains("System.String"))
// {
// cell.SetCellValue(dgv.Rows[rowNum].Cells[columnNum].Value.ToString());
// }
// else if (dgv.Rows[rowNum].Cells[columnNum].ValueType.FullName.Contains("System.Single"))
// {
// cell.SetCellValue(Convert.ToSingle(dgv.Rows[rowNum].Cells[columnNum].Value));
// }
// else if (dgv.Rows[rowNum].Cells[columnNum].ValueType.FullName.Contains("System.Double"))
// {
// cell.SetCellValue(Convert.ToDouble(dgv.Rows[rowNum].Cells[columnNum].Value));
// }
// else if (dgv.Rows[rowNum].Cells[columnNum].ValueType.FullName.Contains("System.Decimal"))
// {
// cell.SetCellValue(Convert.ToDouble(dgv.Rows[rowNum].Cells[columnNum].Value));
// }
// else if (dgv.Rows[rowNum].Cells[columnNum].ValueType.FullName.Contains("System.DateTime"))
// {
// cell.SetCellValue(Convert.ToDateTime(dgv.Rows[rowNum].Cells[columnNum].Value).ToString("yyyy-MM-dd"));
// }
// else if (dgv.Rows[rowNum].Cells[columnNum].ValueType.FullName.Contains("System.DBNull"))
// {
// cell.SetCellValue("");
// }
//}
//设置列宽
IRow currentRow;
if (sheet.GetRow(rowNum) == null)
{
currentRow = sheet.CreateRow(rowNum);
}
else
{
currentRow = sheet.GetRow(rowNum);
}
if (currentRow.GetCell(columnNum+1) != null)
{
ICell currentCell = currentRow.GetCell(columnNum+1);
int length = Encoding.Default.GetBytes(currentCell.ToString()).Length;
if (columnWidth < length)
{
columnWidth = length + 10; //设置列宽数值
}
}
if (columnWidth > 255) columnWidth = 255;
sheet.SetColumnWidth(columnNum+1, columnWidth * 256); //单元格样式
dataRow.GetCell(columnNum+1).CellStyle = cellStyle;
}
}
}
//保存为Excel文件
workbook.Write(ms);
FileStream file = new FileStream(saveFileName, FileMode.Create);
workbook.Write(file);
file.Close();
workbook = null;
ms.Close();
ms.Dispose();
//**程序结束计时**//
sw.Stop();
double totalTime = sw.ElapsedMilliseconds / 1000.0; MessageBox.Show(fileName + " 导出成功\n耗时" + totalTime + "s", "提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
#endregion
#region 检测文件是否被占用 ///
/// 判定文件是否打开 ///
[DllImport("kernel32.dll")] public static extern IntPtr _lopen(string lpPathName, int iReadWrite);
[DllImport("kernel32.dll")] public static extern bool CloseHandle(IntPtr hObject);
public const int OF_READWRITE = 2; public const int OF_SHARE_DENY_NONE = 0x40;
public readonly IntPtr HFILE_ERROR = new IntPtr(-1); ///
/// 检测文件被占用 // 要检测的文件路径 ///
public bool CheckFiles(string FileNames)
{
if (!File.Exists(FileNames))
{ //文件不存在
return true;
}
IntPtr vHandle = _lopen(FileNames, OF_READWRITE | OF_SHARE_DENY_NONE); if (vHandle == HFILE_ERROR)
{ //文件被占用
return false;
} //文件没被占用
CloseHandle(vHandle); return true;
}
#endregion
}
---实现
链接:https://pan.baidu.com/s/17qMXTrV7e4neVCvD15nsGQ
提取码:yyds