首页 > 代码库 > NPOI导出excel表格应用
NPOI导出excel表格应用
最近接到一个需求，在原有系统上做二次开发 ，要求导出DataGridView数据到Excel表格中。要求如下：
- 兼容所有excel版本；
- 导出后excel各列的样式，字段类型不变。
成型如下：
具体代码实现如下：
- girdview数据绑定
public Form1() { InitializeComponent(); Load += Form1_Load; } /// <summary> /// 给DataGridView绑定数据 /// </summary> /// <param name="sender"></param> /// <param name="e"></param> void Form1_Load(object sender, EventArgs e) { List<Jacket> lst = new List<Jacket>(); for (int i = 0; i < 3; i++) { Jacket j = new Jacket(); j.Type = "男款"; j.Color = "black"; j.Size = "S"; j.price = 55.4M + i; lst.Add(j); } for (int i = 0; i < 2; i++) { Jacket j = new Jacket(); j.Type = "男款"; j.Color = "blue"; j.Size = "L"; j.price = 60.4M + i; lst.Add(j); } for (int i = 0; i < 4; i++) { Jacket j = new Jacket(); j.Type = "女款"; j.Color = "red"; j.Size = "S"; j.price = 60.4M + i; lst.Add(j); } dataGridView1.DataSource = lst; }
2.导出excel表格
/// <summary> /// 导出操作 /// </summary> /// <param name="sender"></param> /// <param name="e"></param> private void button1_Click(object sender, EventArgs e) { ExportExcel("d:\\1.xls", dataGridView1, "宋体", 8); } /// <summary> /// 导出excel /// </summary> /// <param name="fileName">导出路径</param> /// <param name="dgv">数据grilview</param> /// <param name="fontName">字体</param> /// <param name="fontSize">大小</param> void ExportExcel(string fileName, DataGridView dgv, string fontName, short fontSize) { //检测是否有数据 //if (dgv.SelectedRows.Count == 0) return; //创建主要对象 HSSFWorkbook workbook = new HSSFWorkbook(); HSSFSheet sheet = (HSSFSheet)workbook.CreateSheet("Weight"); //设置字体，大小，对齐方式 HSSFCellStyle style = (HSSFCellStyle)workbook.CreateCellStyle(); HSSFFont font = (HSSFFont)workbook.CreateFont(); font.FontName = fontName; font.FontHeightInPoints = fontSize; style.SetFont(font); style.Alignment = NPOI.SS.UserModel.HorizontalAlignment.CENTER; //居中对齐 //添加表头 HSSFRow dataRow = (HSSFRow)sheet.CreateRow(0); for (int i = 0; i < dgv.Columns.Count; i++) { dataRow.CreateCell(i).SetCellValue(dgv.Columns[i].HeaderText); dataRow.GetCell(i).CellStyle = style; } //添加列及内容 for (int i = 0; i < dgv.Rows.Count; i++) { dataRow = (HSSFRow)sheet.CreateRow(i + 1); for (int j = 0; j < dgv.Columns.Count; j++) { string ValueType = dgv.Rows[i].Cells[j].Value.GetType().ToString(); string Value =http://www.mamicode.com/ dgv.Rows[i].Cells[j].Value.ToString(); switch (ValueType) { case "System.String"://字符串类型 dataRow.CreateCell(j).SetCellValue(Value); break; case "System.DateTime"://日期类型 System.DateTime dateV; System.DateTime.TryParse(Value, out dateV); dataRow.CreateCell(j).SetCellValue(dateV); break; case "System.Boolean"://布尔型 bool boolV = false; bool.TryParse(Value, out boolV); dataRow.CreateCell(j).SetCellValue(boolV); break; case "System.Int16"://整型 case "System.Int32": case "System.Int64": case "System.Byte": int intV = 0; int.TryParse(Value, out intV); dataRow.CreateCell(j).SetCellValue(intV); break; case "System.Decimal"://浮点型 case "System.Double": double doubV = 0; double.TryParse(Value, out doubV); dataRow.CreateCell(j).SetCellValue(doubV); break; case "System.DBNull"://空值处理 dataRow.CreateCell(j).SetCellValue(""); break; default: dataRow.CreateCell(j).SetCellValue(""); break; } dataRow.GetCell(j).CellStyle = style; //设置宽度 sheet.SetColumnWidth(j, (Value.Length + 10) * 256); } } //保存文件 string saveFileName = ""; SaveFileDialog saveDialog = new SaveFileDialog(); saveDialog.DefaultExt = "xls"; saveDialog.Filter = "Excel文件|*.xls"; saveDialog.FileName = fileName; MemoryStream ms = new MemoryStream(); if (saveDialog.ShowDialog() == DialogResult.OK) { saveFileName = saveDialog.FileName; if (!CheckFiles(saveFileName)) { MessageBox.Show("文件被站用，请关闭文件 " + saveFileName); workbook = null; ms.Close(); ms.Dispose(); return; } FileStream file = new FileStream(saveFileName, FileMode.Create); workbook.Write(file); file.Close(); MessageBox.Show(fileName + " 保存成功", "提示", MessageBoxButtons.OK); } else { workbook = null; ms.Close(); ms.Dispose(); } } /// <summary> /// 检测文件被占用 /// </summary> /// <param name="FileNames">要检测的文件路径</param> /// <returns></returns> public bool CheckFiles(string FileNames) { if (!File.Exists(FileNames)) { //文件不存在 return false; } else { IntPtr vHandle = _lopen(FileNames, OF_READWRITE | OF_SHARE_DENY_NONE); if (vHandle == HFILE_ERROR) { return false; } CloseHandle(vHandle); } return true; }
3.定义jacket类
public class Jacket { public string Type { get; set; } public string Color { get; set; } public string Size { get; set; } public decimal price { get; set; } }
4.判定文件是否打开
[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);
NPOI导出excel表格应用
声明：以上内容来自用户投稿及互联网公开渠道收集整理发布，本网站不拥有所有权，未作人工编辑处理，也不承担相关法律责任，若内容有误或涉及侵权可进行投诉： 投诉/举报 工作人员会在5个工作日内联系你，一经查实，本站将立刻删除涉嫌侵权内容。