C#导入EXCEL丢数据

[tytyh970410]

C#导入EXCEL丢数据,如0000为0,002100为2100,前加"'"可解决,不加可不可解决,建立EXCEL对象时,可不可把整个工作薄单元格设为文本

[livode]

把连接字符串改成这样就可以了。excel文件不用设置。
"Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + Path + ";" + "Extended Properties='Excel 8.0;HDR=Yes;IMEX=1;'";

[fsdy2000]

ADO方式就注意一下连接字符串的IMEX=1

[tytyh970410]

二位说的我没听明白,我做了一个类保存EXCEL,代码如下,还有一个问题ViewSaveExcel方法EXCEL.EXE进程不能及时退出

using System;
using Excel;
using System.Reflection;
using System.Windows.Forms;

namespace 管理系统主程序
{
/// <summary>
/// SaveExcel 的摘要说明。
/// </summary>
public class SaveExcel
{
public SaveExcel()
{
//
// TODO: 在此处添加构造函数逻辑
//
}
public void TableSaveExcel(System.Data.DataTable tempTableName,string filename)
{
try
{
Excel.Application exc = new Excel.Application();
if (exc == null)
{
MessageBox.Show("不能建立EXCEL对象，请在机器上安装EXCEL","提示",MessageBoxButtons.OK,MessageBoxIcon.Error);
return;
}

// exc.set_Visible(0, true);
// exc.Visible=true;
object MissValue = System.Reflection.Missing.Value;
Workbooks workbooks = exc.Workbooks;
_Workbook workbook = workbooks.Add(MissValue);
// _Workbook workbook = workbooks.Add(XlWBATemplate.xlWBATWorksheet);
Sheets sheets = workbook.Worksheets;

_Worksheet worksheet = (_Worksheet) sheets.get_Item(1);
// worksheet.Cells.
int i,numCols=tempTableName.Rows.Count,j;
int lnumCols = tempTableName.Columns.Count;
for (i = 0;i< lnumCols;i++) {
worksheet.Cells[1,i+1]=tempTableName.Columns[i].ColumnName;
}
string ttyy;
for (i = 0;i< numCols;i++) {
for (j = 0;j< lnumCols;j++) {
ttyy="'"+tempTableName.Rows[i][j].ToString();
worksheet.Cells[i+2,j+1]=ttyy;
}
}
worksheet.SaveAs(filename, MissValue, MissValue, MissValue, MissValue, MissValue, Excel.XlSaveAsAccessMode.xlNoChange , MissValue, MissValue);
workbooks.Close();
exc.Quit();
worksheet=null;workbooks=null;exc=null;GC.Collect();
}
catch
{
MessageBox.Show("导出至EXCEL错误！！！！","提示",MessageBoxButtons.OK,MessageBoxIcon.Error);
return ;
}
}
public void ViewSaveExcel(System.Data.DataView tempTableName,string filename)
{
try
{
// System.Globalization.CultureInfo CurrentCI = System.Threading.Thread.CurrentThread.CurrentCulture;
// System.Threading.Thread.CurrentThread.CurrentCulture = new System.Globalization.CultureInfo("en-US");
Excel.Application exc = new Excel.Application();
if (exc == null)
{
MessageBox.Show("不能建立EXCEL对象，请在机器上安装EXCEL","提示",MessageBoxButtons.OK,MessageBoxIcon.Error);
return;
}

// exc.set_Visible(0, true);
// exc.Visible=true;
object MissValue = System.Reflection.Missing.Value;
Workbooks workbooks = exc.Workbooks;
// _Workbook workbook = workbooks.Add(MissValue);
_Workbook workbook = workbooks.Add(XlWBATemplate.xlWBATWorksheet);
Sheets sheets = workbook.Worksheets;

_Worksheet worksheet = (_Worksheet) sheets.get_Item(1);
// worksheet.Cells.
int iRowCount=tempTableName.Count;
int lnumCols = tempTableName.Table.Columns.Count;
for (int i = 0;i< lnumCols;i++) {
worksheet.Cells[1,i+1]=tempTableName.Table.Columns[i].ColumnName;
}

Range rangedata;


// rangedata = worksheet.get_Range("A2", Missing.Value);

Range xlRang = null;
int iParstedRow = 0, iCurrSize = 0;
int iEachSize = 2000; // each time you
object[,] objVal = new object[iEachSize, lnumCols];
try
{
iCurrSize = iEachSize;
while (iParstedRow < iRowCount)
{
if ((iRowCount - iParstedRow) < iEachSize)
iCurrSize = iRowCount - iParstedRow;
for (int i = 0; i < iCurrSize; i++)
{
for (int j = 0; j < lnumCols; j++)
objVal[i, j] ="'" +tempTableName[i + iParstedRow][j].ToString();
}
// Get Save Range from Excel WorkSheet
// such as A1 H10, means From A to H Columns, and 1 to 10 rows
xlRang = worksheet.get_Range("A" + ((int)(iParstedRow + 2)).ToString(), ((char)('A' + lnumCols - 1)).ToString() + ((int)(iParstedRow + iCurrSize + 1)).ToString());

xlRang.Value2 = objVal;

iParstedRow = iParstedRow + iCurrSize;
}

// change number format of the data column

System.Runtime.InteropServices.Marshal.ReleaseComObject(xlRang);
// System.Runtime.InteropServices.Marshal.ReleaseComObject(rangedata);
// xlRang = null;
// rangedata=null;

}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
return ;
}
// exc.Visible=true;

worksheet.SaveAs(filename, MissValue, MissValue, MissValue, MissValue, MissValue, Excel.XlSaveAsAccessMode.xlNoChange , MissValue, MissValue);
System.Runtime.InteropServices.Marshal.ReleaseComObject(xlRang);
workbooks.Close();
exc.Quit();
exc.Quit();
// System.Threading.Thread.CurrentThread.CurrentCulture = CurrentCI;
worksheet=null;workbooks=null;exc=null;xlRang = null;rangedata=null;GC.Collect();
// System.Threading.Thread.CurrentThread.CurrentCulture = CurrentCI;
}
catch
{
MessageBox.Show("导出至EXCEL错误！！！！","提示",MessageBoxButtons.OK,MessageBoxIcon.Error);
return ;
}
}
}
}

[Jinwmmail]

参考:
http://community.csdn.net/Expert/topic/5581/5581277.xml?temp=.3087885

[bote_china]

通过Range.Style来设置数字样式