功能
- 读取Excel文档(2003或2007)
- 在表格中列出数据
- 编辑、输入、删除数据
- 导出成json、xml、csv格式
界面
过程
新建工程
Visual Studio中新建一个Windows窗体,基于C#。
选择文件
一种方法是点击按钮触发事件
private void BtnSelect_Click(object sender, EventArgs e) { OpenFileDialog fileDialog = new OpenFileDialog(); fileDialog.Multiselect = true; fileDialog.Title = "请选择文件"; fileDialog.Filter = "Excel|*.xls; *.xlsx"; fileDialog.ValidateNames = true; //文件有效性验证ValidateNames 验证用户输入是否是一个有效的Windows文件名 fileDialog.CheckFileExists = true; //验证路径有效性 fileDialog.CheckPathExists = true; //验证文件有效性 if (fileDialog.ShowDialog() == DialogResult.OK) { ReadExcelFile(fileDialog.FileName); } }
或另一种操作是直接拖拽文件到窗口上
首先把form的AllowDrop设置为true,然后添加下以两个事件:
private void form1_DragEnter(object sender, DragEventArgs e) { if (e.Data.GetDataPresent(DataFormats.FileDrop)) { e.Effect = DragDropEffects.Link; } else { e.Effect = DragDropEffects.None; } } private void form1_DragDrop(object sender, DragEventArgs e) { string path = ((Array)e.Data.GetData(DataFormats.FileDrop)).GetValue(0).ToString(); ReadExcelFile(path); }
读取文件
引用NPOI库
最新库可以读03和07的文档
/// <summary> /// 将Excel文件中的数据读出到DataTable中(xls) /// </summary> /// <param name="file"></param> /// <returns></returns> public static DataTable ExcelToTableForXLS(string file) { DataTable dt = new DataTable(); try { using (FileStream fs = new FileStream(file, FileMode.Open, FileAccess.Read)) { HSSFWorkbook hssfworkbook = new HSSFWorkbook(fs); ISheet sheet = hssfworkbook.GetSheetAt(0); //表头 IRow header = sheet.GetRow(sheet.FirstRowNum); List<int> columns = new List<int>(); for (int i = 0; i < header.LastCellNum; i++) { object obj = GetValueTypeForXLS(header.GetCell(i) as HSSFCell); if (obj == null || obj.ToString() == string.Empty) { dt.Columns.Add(new DataColumn("Columns" + i.ToString())); //continue; } else dt.Columns.Add(new DataColumn(obj.ToString())); columns.Add(i); } //数据 for (int i = sheet.FirstRowNum + 1; i <= sheet.LastRowNum; i++) { DataRow dr = dt.NewRow(); bool hasValue = false; foreach (int j in columns) { dr[j] = GetValueTypeForXLS(sheet.GetRow(i).GetCell(j) as HSSFCell); if (dr[j] != null && dr[j].ToString() != string.Empty) { hasValue = true; } } if (hasValue) { dt.Rows.Add(dr); } } } } catch (Exception ex) { MessageBox.Show(ex.Message, "Error"); } return dt; }
/// <summary> /// 将Excel文件中的数据读出到DataTable中(xlsx) /// </summary> /// <param name="file"></param> /// <returns></returns> public static DataTable ExcelToTableForXLSX(string file) { DataTable dt = new DataTable(); try { using (FileStream fs = new FileStream(file, FileMode.Open, FileAccess.Read)) { XSSFWorkbook xssfworkbook = new XSSFWorkbook(fs); ISheet sheet = xssfworkbook.GetSheetAt(0); //表头 IRow header = sheet.GetRow(sheet.FirstRowNum); List<int> columns = new List<int>(); for (int i = 0; i < header.LastCellNum; i++) { object obj = GetValueTypeForXLSX(header.GetCell(i) as XSSFCell); if (obj == null || obj.ToString() == string.Empty) { dt.Columns.Add(new DataColumn("Columns" + i.ToString())); //continue; } else { if (dt.Columns.IndexOf(obj.ToString()) >= 0) { throw new Exception("列表名重复: " + obj.ToString()); } dt.Columns.Add(new DataColumn(obj.ToString())); } columns.Add(i); } //数据 for (int i = sheet.FirstRowNum + 1; i <= sheet.LastRowNum; i++) { DataRow dr = dt.NewRow(); bool hasValue = false; foreach (int j in columns) { dr[j] = GetValueTypeForXLSX(sheet.GetRow(i).GetCell(j) as XSSFCell); if (dr[j] != null && dr[j].ToString() != string.Empty) { hasValue = true; } } if (hasValue) { dt.Rows.Add(dr); } } } } catch (Exception ex) { MessageBox.Show(ex.Message); } return dt; }
显示数据
把读取出来的Excel数据保存到DataTable中,在form中增加一个DataGridView,绑定DataTable到DataGridView的DataSource
导出数据
void ConvertToJson(string path, DataTable data, Encoding encoding) { try { if (File.Exists(path)) { File.Delete(path); } StringBuilder jsonBuilder = new StringBuilder(); jsonBuilder.Append("{\""); jsonBuilder.Append(data.TableName); jsonBuilder.Append("\":["); //jsonBuilder.Append("["); for (int i = 0; i < data.Rows.Count; i++) { jsonBuilder.Append("{"); for (int j = 0; j < data.Columns.Count; j++) { jsonBuilder.Append("\""); jsonBuilder.Append(data.Columns[j].ColumnName); jsonBuilder.Append("\":\""); jsonBuilder.Append(data.Rows[i][j].ToString()); jsonBuilder.Append("\","); } jsonBuilder.Remove(jsonBuilder.Length - 1, 1); jsonBuilder.Append("},"); } jsonBuilder.Remove(jsonBuilder.Length - 1, 1); jsonBuilder.Append("]"); jsonBuilder.Append("}"); //生成Json字符串 string json = jsonBuilder.ToString(); //写入文件 using (FileStream fileStream = new FileStream(path, FileMode.Create, FileAccess.Write)) { using (TextWriter textWriter = new StreamWriter(fileStream, encoding)) { textWriter.Write(json); } } } catch (Exception ex) { MessageBox.Show(ex.Message, "Error"); } } void ConvertToXml(string path, DataTable data, Encoding encoding) { try { //如果文件DataTable.xml存在则直接删除 if (File.Exists(path)) { File.Delete(path); } XmlTextWriter writer = new XmlTextWriter(path, encoding); writer.Formatting = System.Xml.Formatting.Indented; //XML文档创建开始 writer.WriteStartDocument(); writer.WriteStartElement(data.TableName); //DataTable开始 //按行各行 for (int j = 0; j < data.Rows.Count; j++) { writer.WriteStartElement("item"); //打印各列 for (int k = 0; k < data.Columns.Count; k++) { writer.WriteAttributeString( data.Columns[k].ToString(), data.Rows[j][k].ToString()); } writer.WriteEndElement(); } writer.WriteEndElement(); //DataTable结束 writer.WriteEndDocument(); writer.Close(); //XML文档创建结束 } catch (Exception ex) { MessageBox.Show(ex.Message, "Error"); } } void ConvertToCsv(string path, DataTable data, Encoding encoding) { if (File.Exists(path)) { File.Delete(path); } //读取数据表行数和列数 int rowCount = data.Rows.Count; int colCount = data.Columns.Count; //创建一个StringBuilder存储数据 StringBuilder stringBuilder = new StringBuilder(); //读取数据 for (int i = -1; i < rowCount; i++) { for (int j = 0; j < colCount; j++) { if (i == -1)//-1为列名 stringBuilder.Append(data.Columns[j] + ","); else stringBuilder.Append(data.Rows[i][j] + ",");//使用","分割每一个数值 } //使用换行符分割每一行 stringBuilder.Append("\r\n"); } //写入文件 using (FileStream fileStream = new FileStream(path, FileMode.Create, FileAccess.Write)) { using (TextWriter textWriter = new StreamWriter(fileStream, encoding)) { textWriter.Write(stringBuilder.ToString()); } } }