c#如何将TXT转换成excel格式文件
用c#生0成了txt文件如下：
1：abc
2：ddg
3：hello boy
4：what？
……
public class ExcelIO
{
private int _ReturnStatus;
private string _ReturnMessage;
/// <summary>
/// Execute return status
/// </summary>
public int ReturnStatus
{
get { return _ReturnStatus; }
}
/// <summary>
/// Execute return info
/// </summary>
public string ReturnMessage
{
get { return _ReturnMessage; }
}
public ExcelIO()
{
}
/// <summary>
/// Import excel to dataset
/// </summary>
/// <param name="fileName">Excel full path file name</param>
/// <returns>The dataset data</returns>
public DataSet ImportExcel(string fileName)
{
Microsoft.Office.Interop.Excel.Application xlApp = new Microsoft.Office.Interop.Excel.Application();
if (xlApp == null)
{
_ReturnStatus = -1;
_ReturnMessage = "Could not create excel object , possibly your computer cann't install excel";
return null;
}
Microsoft.Office.Interop.Excel.Workbook workbook;
try
{
workbook = xlApp.Workbooks.Open(fileName, 0, false, 5, "", "", false, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "", true, false, 0, true, 1, 0);
}
catch
{
_ReturnStatus = -1;
_ReturnMessage = "Excel file is opening now , please save and exit";
return null;
}
int n = workbook.Worksheets.Count;
string[] SheetSet = new string[n];
System.Collections.ArrayList al = new System.Collections.ArrayList();
for (int i = 1; i <= n; i++)
{
SheetSet[i - 1] = ((Microsoft.Office.Interop.Excel.Worksheet)workbook.Worksheets[i]).Name;
}
workbook.Close(null, null, null);
xlApp.Quit();
if (workbook != null)
{
System.Runtime.InteropServices.Marshal.ReleaseComObject(workbook);
workbook = null;
}
if (xlApp != null)
{
System.Runtime.InteropServices.Marshal.ReleaseComObject(xlApp);
xlApp = null;
}
GC.Collect();
DataSet ds = new DataSet();
string connStr = " Provider = Microsoft.Jet.OLEDB.4.0 ; Data Source = " + fileName + ";Extended Properties=Excel 8.0";
using (OleDbConnection conn = new OleDbConnection(connStr))
{
conn.Open();
OleDbDataAdapter da;
for (int i = 1; i <= n; i++)
{
string sql = "select * from [" + SheetSet[i - 1] + "$] ";
da = new OleDbDataAdapter(sql, conn);
da.Fill(ds, SheetSet[i - 1]);
da.Dispose();
}
conn.Close();
conn.Dispose();
}
return ds;
}
/// <summary>
/// Export datatable to excel
/// </summary>
/// <param name="reportName">The report name</param>
/// <param name="dt">The source datatable</param>
/// <param name="saveFileName">Excel full path file name</param>
/// <returns>True if export success , otherwise false</returns>
public bool ExportExcel(string reportName, System.Data.DataTable dt, string saveFileName)
{
if (dt == null)
{
_ReturnStatus = -1;
_ReturnMessage = "DataSet is empty";
return false;
}
bool fileSaved = false;
Microsoft.Office.Interop.Excel.Application xlApp = new Microsoft.Office.Interop.Excel.Application();
if (xlApp == null)
{
_ReturnStatus = -1;
_ReturnMessage = "Could not create excel object , possibly your computer cann't install excel";
return false;
}
Microsoft.Office.Interop.Excel.Workbooks workbooks = xlApp.Workbooks;
Microsoft.Office.Interop.Excel.Workbook workbook = workbooks.Add(Microsoft.Office.Interop.Excel.XlWBATemplate.xlWBATWorksheet);
Microsoft.Office.Interop.Excel.Worksheet worksheet = (Microsoft.Office.Interop.Excel.Worksheet)workbook.Worksheets[1];//取得sheet1
worksheet.Cells.Font.Size = 10;
Microsoft.Office.Interop.Excel.Range range;
long totalCount = dt.Rows.Count;
long rowRead = 0;
float percent = 0;
worksheet.Cells[1, 1] = reportName;
((Microsoft.Office.Interop.Excel.Range)worksheet.Cells[1, 1]).Font.Size = 12;
((Microsoft.Office.Interop.Excel.Range)worksheet.Cells[1, 1]).Font.Bold = true;
for (int i = 0; i < dt.Columns.Count; i++)
{
worksheet.Cells[2, i + 1] = dt.Columns[i].ColumnName;
range = (Microsoft.Office.Interop.Excel.Range)worksheet.Cells[2, i + 1];
//range.Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.FromArgb(79, 129, 189));
range.Font.Bold = true;
}
for (int r = 0; r < dt.Rows.Count; r++)
{
for (int i = 0; i < dt.Columns.Count; i++)
{
worksheet.Cells[r + 3, i + 1] = dt.Rows[r][i].ToString();
range = (Microsoft.Office.Interop.Excel.Range)worksheet.Cells[r + 3, i + 1];
if (r % 2 == 0)
{
//range.Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.FromArgb(211, 223, 238));
}
else
{
//range.Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.FromArgb(255, 255, 255));
}
}
rowRead++;
percent = ((float)(100 * rowRead)) / totalCount;
}
range = worksheet.Range[worksheet.Cells[2, 1], worksheet.Cells[dt.Rows.Count + 2, dt.Columns.Count]];
range.BorderAround(Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous, Microsoft.Office.Interop.Excel.XlBorderWeight.xlThin, Microsoft.Office.Interop.Excel.XlColorIndex.xlColorIndexNone, System.Drawing.Color.FromArgb(123, 160, 205));
if (dt.Rows.Count > 0)
{
range.Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlInsideHorizontal].Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.FromArgb(123, 160, 205));
range.Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlInsideHorizontal].LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;
range.Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlInsideHorizontal].Weight = Microsoft.Office.Interop.Excel.XlBorderWeight.xlThin;
}
if (saveFileName != "")
{
try
{
workbook.Saved = true;
workbook.SaveCopyAs(saveFileName);
fileSaved = true;
}
catch (Exception ex)
{
fileSaved = false;
_ReturnStatus = -1;
_ReturnMessage = "Export file error , possibly this file is opening now \n" + ex.Message;
}
}
else
{
fileSaved = false;
}
if (range != null)
{
System.Runtime.InteropServices.Marshal.ReleaseComObject(range);
range = null;
}
if (worksheet != null)
{
System.Runtime.InteropServices.Marshal.ReleaseComObject(worksheet);
worksheet = null;
}
if (workbook != null)
{
System.Runtime.InteropServices.Marshal.ReleaseComObject(workbook);
workbook = null;
}
if (workbooks != null)
{
System.Runtime.InteropServices.Marshal.ReleaseComObject(workbooks);
workbooks = null;
}
xlApp.Application.Workbooks.Close();
xlApp.Quit();
if (xlApp != null)
{
System.Runtime.InteropServices.Marshal.ReleaseComObject(xlApp);
xlApp = null;
}
GC.Collect();
return fileSaved;
}
}
Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.Application();
Microsoft.Office.Interop.Excel.Workbook wb = null;
Microsoft.Office.Interop.Excel.Sheets sh = null;
excel.Application.Workbooks.Add(true);
excel.Cells[1, 1] = "1";
excel.Cells[1, 2] = "abc";
excel.Cells[2, 1] = "2";
excel.Cells[2, 2] = "ddg";
excel.Cells[3, 1] = "3";
excel.Cells[3, 2] = "hello boy";
excel.Cells[4, 1] = "3";
excel.Cells[4, 2] = "what";
excel.Visible = false;
excel.DisplayAlerts = false;
excel.AlertBeforeOverwriting = false;
excel.ActiveWorkbook.SaveCopyAs(@"d:\123.xls");
excel.Quit();
