datatable<-->excle相互转换
代码：private void Button1_Click(object sender, RoutedEventArgs e)
{
OpenFileDialog file = new OpenFileDialog();
if (file.ShowDialog() == DialogResult.OK)
{
string Path = file.FileName.ToString();//文件完整路径
//文件名
string Name = Path.Substring(Path.LastIndexOf("\\") + 1);
txtfileName.Text = Path; //将文件路径放入路径显示文本框
}
}
/// <summary>
/// datatable->databox
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
//调用方法解析excle
private void Button2_Click(object sender, RoutedEventArgs e)
{
string fileName = txtfileName.Text;
DataTable table = ExcelToDataTable(fileName);
if (table != null)
{
databox.InitDataBox();
databox.BindDataTable(table);
for (int i = 0; i < table.Columns.Count; i++)
{
databox.SetColumnsHeader(i, table.Columns.ColumnName);
}
}else{
this.ShowErrorMessageBox("文件不存在或者文件为空");
}
}
/// <summary>
/// excle->datatable
/// </summary>
/// <param name="fileName"></param>
/// <returns></returns>
public DataTable ExcelToDataTable(string fileName)
{
try
{
//整个Excel表格叫做工作表：WorkBook（工作薄），包含的叫页（工作表）：Sheet；行：Row；单元格Cell。
FileStream fs = new FileStream(fileName, FileMode.OpenOrCreate); //读取文件流
if (fileName.IndexOf(".xlsx") > 0) // 2007版本
{
workbook = new XSSFWorkbook(fs);
}
else if (fileName.IndexOf(".xls") > 0) // 2003版本
{
workbook = new HSSFWorkbook(fs);
}
var sheet1 = workbook.GetSheetAt(0); //获取第一个sheet
DataTable table = new DataTable();//
var row1 = sheet1.GetRow(0);//获取第一行即标头
int cellCount = row1.LastCellNum; //第一行的列数
//把第一行的数据添加到datatable的列名
for (int i = row1.FirstCellNum; i < cellCount; i++)
{
DataColumn column = new DataColumn(row1.GetCell(i).StringCellValue);
table.Columns.Add(column);
}
int rowCount = sheet1.LastRowNum; //总行数
//把每行数据添加到datatable中
for (int i = (sheet1.FirstRowNum + 1); i < sheet1.LastRowNum + 1; i++)
{
IRow row = sheet1.GetRow(i);
DataRow dataRow = table.NewRow();
for (int j = row.FirstCellNum; j < cellCount; j++)
{
if (row.GetCell(j) != null)
dataRow = row.GetCell(j).ToString();
}
table.Rows.Add(dataRow);
}
return table;
}
catch (NullReferenceException e)
{
Platform.Common.LogSystem.PlatformLogger.TradeErrorInfo("excle文件不存在或者为空", e);
DataTable dt = new DataTable();
return dt;
}
}
///////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
databox转为excle保存
private void OutToExcel(System.Data.DataTable dt)
{
#region 验证可操作性
//定义表格内数据的行数和列数
int rowscount = dt.Rows.Count;
int colscount = dt.Columns.Count;
//行数必须大于0
if (rowscount <= 0)
{
System.Windows.Forms.MessageBox.Show("没有数据可供保存 ", "提示 ", MessageBoxButtons.OK, MessageBoxIcon.Information);
return;
}
//列数必须大于0
if (colscount <= 0)
{
System.Windows.Forms.MessageBox.Show("没有数据可供保存 ", "提示 ", MessageBoxButtons.OK, MessageBoxIcon.Information);
return;
}
//行数不可以大于65536
if (rowscount > 65536)
{
System.Windows.Forms.MessageBox.Show("数据记录数太多(最多不能超过65536条)，不能保存 ", "提示 ", MessageBoxButtons.OK, MessageBoxIcon.Information);
return;
}
//列数不可以大于255
if (colscount > 255)
{
System.Windows.Forms.MessageBox.Show("数据记录行数太多，不能保存 ", "提示 ", MessageBoxButtons.OK, MessageBoxIcon.Information);
return;
}
#endregion
SaveFileDialog saveFileDialog = new SaveFileDialog();
saveFileDialog.Filter = "Execl files (*.xls)|*.xls";
saveFileDialog.FilterIndex = 0;
saveFileDialog.RestoreDirectory = true;
saveFileDialog.CreatePrompt = true;
saveFileDialog.Title = "保存为Excel文件";
saveFileDialog.ShowDialog();
if (saveFileDialog.FileName.IndexOf(":") < 0) return; //被点了"取消"
Stream myStream;
myStream = saveFileDialog.OpenFile();
StreamWriter sw = new StreamWriter(myStream, System.Text.Encoding.GetEncoding(-0));
string columnTitle = "";
try
{
//写入列标题
for (int i = 0; i < colscount; i++)
{
if (i > 0)
{
columnTitle += "\t";
}
columnTitle += dt.Columns.ColumnName;
}
sw.WriteLine(columnTitle);
//写入列内容
for (int j = 0; j < rowscount; j++)
{
string columnValue = "";
for (int k = 0; k < colscount; k++)
{
if (k > 0)
{
columnValue += "\t";
}
if (dt.Rows == null)
columnValue += "";
else
{
if (dt.Rows.GetType() == typeof(string) && dt.Rows.ToString().StartsWith("0"))
{
columnValue += "'" + dt.Rows.ToString();
}
else
columnValue += dt.Rows.ToString();
}
}
sw.WriteLine(columnValue);
}
sw.Close();
myStream.Close();
}
catch (Exception ex)
{
System.Windows.Forms.MessageBox.Show(ex.ToString());
}
finally
{
sw.Close();
myStream.Close();
System.Windows.Forms.MessageBox.Show("数据导出成功,共导出" + dt.Rows.Count.ToString() + "条记录");
}
}
}
提示：
导入excle用的npoi 需要添加引用包：
ICSharpCode.SharpZipLib.dll
NPOI.dll
NPOI.OOXML.dll
NPOI.OpenXml4Net.dll
NPOI.OpenXmlFormats.dll
......
页:
[1]