对于用com组件写excel，笔者表示那个太慢了。而且很耗资源，还要装excel。
今天我们就用写文本文件的方式来写excel。
步骤1，用excel写好一个设计一个我们想要的模板。
步骤2，我们把做好的excel模板，另存为。 这时会出现另存为的对话框。在保存类型中选择“XML 电子表格 2003 （*.xml）”。然后名字随便写。
步骤3，把我们刚保存的xml文件用文本打开。我们就会看到，其实excel是以xml方式存放的数据。如果这时你用excel打开那个xml文件，会发现excel出现的就是我们刚才的设计的模板。而且格式和我们设计的一样。
步骤4，所以我们可以用写xml，或者文本的方式写excel，只要符合我们刚才保存的那个xml格式。然后文件的后缀名改成xls就OK了。 而这个写的速度超快。
说完了上面的方法我们用代码把它实现吧。笔者主要编程语言为c#。所以就用c#封装了个写excel的类。代码如下
public class ExcelHelper { private Excel.Application oExcel = null; private Excel.Workbook oBook = null; private Excel.Worksheet oSheet = null; private Excel.Range oRange = null; public ExcelHelper() { oExcel = new Excel.Application(); oBook = oExcel.Application.Workbooks.Add(true); oSheet = (Excel.Worksheet)oBook.ActiveSheet; } /// <summary> /// 向excel写入文本（如向“A1”写入文字：InsertText("A1",要填入的文字)） /// </summary> /// <param name="val_range">单元格</param> /// <param name="val_text">文本</param> public void InsertText(string val_range, string val_text) { oRange = oSheet.Range[val_range]; oRange.Value = val_text; } /// <summary> /// 向excel写入文本 /// </summary> /// <param name="val_range">单元格</param> /// <param name="val_text">文本</param> /// <param name="val_fontSize">字体大小</param> public void InsertText(string val_range, string val_text, float val_fontSize) { oRange = oSheet.Range[val_range]; oRange.Value = val_text; oRange.Font.Size = val_fontSize; } /// <summary> /// 向excel写入文本（如向“B1”写入文字：InsertText(1,2,要填入的文字)） /// </summary> /// <param name="val_i">行号</param> /// <param name="val_j">列号</param> /// <param name="val_text">文本</param> public void InsertText(int val_i, int val_j, string val_text) { oRange = (Excel.Range)oSheet.Cells[val_i, val_j]; oRange.Value = val_text; } /// <summary> /// 向excel写入文本（如向“B1”写入文字：InsertText(1,2,要填入的文字,字体大小)） /// </summary> /// <param name="val_i">行号</param> /// <param name="val_j">列号</param> /// <param name="val_text">文本</param> /// <param name="val_fontSize">字体大小</param> public void InsertText(int val_i, int val_j, string val_text, float val_fontSize) { oRange = oSheet.Cells[val_i, val_j]; oRange.Value = val_text; oRange.Font.Size = val_fontSize; } /// <summary> /// 读取excel文本项（如读取“B1”的文字：ReadText("B1")） /// </summary> /// <param name="val_range">单元格</param> /// <returns></returns> public string ReadText(string val_range) { oRange = oSheet.Range[val_range]; return oRange.Text.ToString(); } /// <summary> /// 读取excel文本项（如读取“A1”的文字：ReadText(1,1)） /// </summary> /// <param name="val_i">行号</param> /// <param name="val_j">列号</param> /// <returns></returns> public string ReadText(int val_i, int val_j) { oRange = oSheet.Cells[val_i, val_j]; return oRange.Text.ToString(); } /// <summary> /// 合并单元格 /// </summary> /// <param name="x1">行号</param> /// <param name="y1">列号</param> /// <param name="x2">行号</param> /// <param name="y2">列号</param> public void MerMergeCells(int x1, int y1, int x2, int y2) { oSheet.Range[oSheet.Cells[x1, y1], oSheet.Cells[x2, y2]].Merge(); } /// <summary> /// 设置excel列的默认样式 /// </summary> public void SetColumnDefaultStyle() { oSheet.Columns.EntireColumn.AutoFit(); oSheet.Columns.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter; } /// <summary> /// 保存excel文件（如要将excel保存到D:\test.xls：SaveAs） /// </summary> /// <param name="val_saveAsFilePath">文件路径</param> /// <returns></returns> public bool SaveAs(string val_saveAsFilePath) { try { oSheet.SaveAs(val_saveAsFilePath); return true; } catch { return false; } } public bool Print() { try { oSheet.PrintOut(); return true; } catch { return false; } } /// <summary> /// 自动释放excel资源 /// </summary> public void Dispose() { if (oExcel != null) { oExcel.Workbooks.Close(); oExcel.Quit(); KillAllExcel(); oExcel = null; } if (oBook != null) { oBook = null; } if (oSheet != null) { oSheet = null; } if (oRange != null) { oRange = null; } GC.Collect(); } /// <summary> /// 获取所有excel进程 /// </summary> /// <returns></returns> private static List<Process> GetExcelProcesses() { Process[] processes = Process.GetProcesses(); List<Process> excelProcesses = new List<Process>(); for (int i = 0; i < processes.Length; i++) { if (processes[i].ProcessName.ToUpper() == "EXCEL") excelProcesses.Add(processes[i]); } return excelProcesses; } /// <summary> /// 杀死所有Excel进程 /// </summary> private static void KillAllExcel() { List<Process> excelProcesses = GetExcelProcesses(); for (int i = 0; i < excelProcesses.Count; i++) { excelProcesses[i].Kill(); } } /// <summary> /// DataTable导出Excel /// </summary> /// <param name="sFileName"></param> /// <param name="dt"></param> public void DataTableToExcel(string fileName, DataTable dt) { int CurrentCol = 0;//当前列 int RowCount = dt.Rows.Count + 1;//总行数 int ColCount = dt.Columns.Count;//总列数 StreamWriter sw = new StreamWriter(fileName, false);//文件如果存在，则自动覆盖 try { #region XML头部 sw.WriteLine("<?xml version=\"1.0\"?>"); sw.WriteLine("<?mso-application progid=\"Excel.Sheet\"?>"); sw.WriteLine("<Workbook"); sw.WriteLine("xmlns=\"urn:schemas-microsoft-com:office:spreadsheet\""); sw.WriteLine("xmlns:ss=\"urn:schemas-microsoft-com:office:spreadsheet\">"); sw.WriteLine("\t<Styles>"); sw.WriteLine("\t\t<Style ss:ID=\"Default\" ss:Name=\"Normal\"><Alignment ss:Vertical=\"Center\"/><Font ss:FontName=\"宋体\" ss:Size=\"12\"/></Style>"); sw.WriteLine("\t\t<Style ss:ID=\"s47\"><Font ss:FontName=\"宋体\" ss:Size=\"11\" ss:Color=\"#000000\"/><Interior ss:Color=\"#EBF1DE\" ss:Pattern=\"Solid\"/></Style>"); sw.WriteLine("\t\t<Style ss:ID=\"s33\"><Borders><Border ss:Position=\"Bottom\" ss:LineStyle=\"Double\" ss:Weight=\"3\" ss:Color=\"#3F3F3F\"/><Border ss:Position=\"Left\" ss:LineStyle=\"Double\" ss:Weight=\"3\" ss:Color=\"#3F3F3F\"/><Border ss:Position=\"Right\" ss:LineStyle=\"Double\" ss:Weight=\"3\" ss:Color=\"#3F3F3F\"/><Border ss:Position=\"Top\" ss:LineStyle=\"Double\" ss:Weight=\"3\" ss:Color=\"#3F3F3F\"/></Borders><Font ss:FontName=\"宋体\" ss:Size=\"11\" ss:Color=\"#FFFFFF\" ss:Bold=\"1\"/><Interior ss:Color=\"#A5A5A5\" ss:Pattern=\"Solid\"/></Style>"); sw.WriteLine("\t\t<Style ss:ID=\"s68\" ss:Parent=\"s33\"><Alignment ss:Horizontal=\"Center\" ss:Vertical=\"Center\"/></Style>"); sw.WriteLine("\t\t<Style ss:ID=\"s93\" ss:Parent=\"s47\"><Borders><Border ss:Position=\"Bottom\" ss:LineStyle=\"Continuous\" ss:Weight=\"1\" ss:Color=\"#3F3F3F\"/><Border ss:Position=\"Left\" ss:LineStyle=\"Continuous\" ss:Weight=\"1\" ss:Color=\"#3F3F3F\"/><Border ss:Position=\"Right\" ss:LineStyle=\"Continuous\" ss:Weight=\"1\" ss:Color=\"#3F3F3F\"/><Border ss:Position=\"Top\" ss:LineStyle=\"Continuous\" ss:Weight=\"1\" ss:Color=\"#3F3F3F\"/></Borders></Style>"); sw.WriteLine("\t</Styles>"); sw.WriteLine("\t<Worksheet ss:Name=\"Sheet1\">"); sw.WriteLine("\t\t<Table ss:DefaultColumnWidth=\"150\" ss:DefaultRowHeight=\"20\">"); #endregion #region excel标题 sw.WriteLine("\t\t\t<Row>"); sw.WriteLine("\t\t\t\t<Cell ss:MergeAcross=\"{0}\" ss:StyleID=\"s68\">",ColCount-1); sw.WriteLine("\t\t\t\t\t<Data ss:Type=\"String\">{0}</Data>",dt.TableName); sw.WriteLine("\t\t\t\t</Cell>"); sw.WriteLine("\t\t\t</Row>"); #endregion #region excel表头信息 sw.WriteLine("\t\t\t<Row ss:AutoFitHeight=\"0\" ss:Height=\"15\">"); for (CurrentCol = 0; CurrentCol < ColCount; CurrentCol++) { sw.Write("\t\t\t\t<Cell ss:StyleID=\"s93\"><Data ss:Type=\"String\">{0}</Data></Cell>", dt.Columns[CurrentCol].ColumnName.ToString().Trim()); } sw.WriteLine("\t\t\t</Row>"); #endregion #region excel表格内容 foreach (DataRow row in dt.Rows) { sw.WriteLine("\t\t\t<Row ss:AutoFitHeight=\"0\" ss:Height=\"15\">"); for (CurrentCol = 0; CurrentCol < ColCount; CurrentCol++) { sw.Write("\t\t\t\t<Cell ss:StyleID=\"s93\"><Data ss:Type=\"String\">"); if (row[CurrentCol] != null) { sw.Write(row[CurrentCol].ToString().Trim()); } else { sw.Write(""); } sw.Write("</Data></Cell>"); } sw.WriteLine("\t\t\t</Row>"); } #endregion #region XML尾部 sw.WriteLine("\t\t</Table>"); sw.WriteLine("\t</Worksheet>"); sw.WriteLine("</Workbook>"); #endregion } catch { } finally { sw.Close(); sw = null; } } }
| 作者：Bonker 出处：http://www.cnblogs.com/Bonker QQ：519841366 |
|
