excel表已写好表头,怎么将c#中的数据直接导入到这个模板表里去 5C

excel表已写好表头,怎么将c#中的数据直接导入到这个模板表里去

5个回答

 用npoi这个库,可以直接写excel文件的单元格。
HSSFWorkbook hssfworkbook = new HSSFWorkbook(你的excel文件);
ISheet sheet1 = hssfworkbook.GetSheet("sheet1");
sheet1.CreateRow(行);
sheet1.GetRow(行).CreateCell(列);
sheet1.GetRow(行).GetCell(列).SetCellValue(写入值);

/// 将DataGridView 写入ExceL中去
///

/// 保存的文件名称

public void DataGridViewToExcel(DataGridView dgv, string name)
{

//总可见列数,总可见行数
int colCount = dgv.Columns.GetColumnCount(DataGridViewElementStates.Visible);
int rowCount = dgv.Rows.GetRowCount(DataGridViewElementStates.Visible);
//dataGridView 没有数据提示
if (dgv.Rows.Count == 0 || rowCount == 0) { MessageBox.Show("表中没有数据", "提示"); }
else
{ //选择创建文件的路径

SaveFileDialog save = new SaveFileDialog();
save.Filter = "excel files(*.xls)|*.xls";
save.Title = "请选择要导出数据的位置";
save.FileName = name + DateTime.Now.ToLongDateString();
if (save.ShowDialog() == DialogResult.OK)
{
string fileName = save.FileName; //MessageBox.Show(save.FileName);
// 创建Excel对象

Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.Application();//new Microsoft.Office.Interop.Excel.ApplicationClass();

if (excel == null) { MessageBox.Show("Excel无法启动", "提示"); return; }
//创建Excel工作薄

Microsoft.Office.Interop.Excel.Workbook excelBook = excel.Workbooks.Add(true);
Microsoft.Office.Interop.Excel.Worksheet excelSheet = (Microsoft.Office.Interop.Excel.Worksheet)excelBook.Worksheets[1];
//excel.Application.Workbooks.Add(true);
//生成字段名称

int k = 0; for (int i = 0; i < dgv.ColumnCount; i++)
{
if (dgv.Columns[i].Visible) //不导出隐藏的列

{ excel.Cells[1, k + 1] = dgv.Columns[i].HeaderText; k++; }
}
//填充数据
for (int i = 0; i < dgv.RowCount; i++)
{
k = 0; for (int j = 0; j < dgv.ColumnCount; j++)
{
if (dgv.Columns[j].Visible)
//不导出隐藏的列

{
if (dgv[j, i].ValueType == typeof(string))
{ excel.Cells[i + 2, k + 1] = "" + dgv[j, i].Value.ToString(); }
else { excel.Cells[i + 2, k + 1] = dgv[j, i].Value.ToString(); }
} k++;
}
} try { excelBook.Saved = true; excelBook.SaveCopyAs(fileName); }
catch { MessageBox.Show("导出失败,文件可能正在使用中", "提示"); }
}
}
}

///
/// 保存数据到excel表格
///
///
///
///
public static bool DataTableToExcel(DataTable datatable, string excelPath)
{
try
{
if (datatable == null || datatable.Rows.Count == 0 || datatable.Rows.Count > _maxRecord)
{
throw new Exception(string.Format("导出的数据小于0或大于{0}", _maxRecord));
}
//wpf创建excel对象
CreateExcelRef();
//将数据填充到内存Excel的工作表
string startRange = "A1";//数据从A1开始插入
int rowCount = datatable.Rows.Count;
int colCount = datatable.Columns.Count;

            string[,] objData = new string[datatable.Rows.Count,datatable.Columns.Count];
            int [,] colorIndex=new int [datatable.Rows.Count,2];
            objData = DataTableToArr(datatable, colorIndex);

            _range = _sheet.get_Range(startRange, _optionalValue);
            _range = _range.get_Resize(rowCount+1,colCount);
            _range.set_Value(_optionalValue,objData);
            #region 格式设置
            //设置单元格大小               
            _range = _range.get_Resize(rowCount+1, colCount);
            _range.Columns.AutoFit();
            _range.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;//水平对其设置
            _range.VerticalAlignment = Excel.XlHAlign.xlHAlignCenter;//垂直对齐设置
            _range.Borders.LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;//加边框
            _range.NumberFormatLocal = "@";//设置文本格式;
            //判定标色

            _log.Debug("【数据导出】标色开始");
            for (int i = 0; i < colorIndex.GetLength(0); i++)
            {
                if (colorIndex[i, 1] == 0) break;                   
                _range = _sheet.Range[_sheet.Cells[colorIndex[i, 0] + 2, colorIndex[i, 1] + 1], _sheet.Cells[colorIndex[i, 0] + 2, colorIndex[i, 1] + 1]];
                _range.Cells.Interior.Color = Color.Red;

            }                      
            _log.Debug("【数据导出】标色结束");
            //列名设置格式
            int iCnt = datatable.Columns.Count / 26;
            int iNum = datatable.Columns.Count % 26;
            string endColSignal = (iCnt == 0 || (iCnt == 1 && iNum == 0)) ? "" : iNum == 0 ? ((char)('A' + (iCnt - 2))).ToString() : ((char)('A' + (iCnt - 1))).ToString();
            string endCol = endColSignal + (iNum == 0 ? 'Z'.ToString() : ((char)('A' + datatable.Columns.Count - iCnt * 26 - 1)).ToString());
            //列名
            _range=_sheet.get_Range(startRange, endCol + "1");//Excel文件列名 字体设定为Bold
            _range.Cells.Interior.Color = Color.AliceBlue;//单元格颜色
            _range.Font.Bold = 1;//加黑
            _range.Font.Size = 12;//大小
            #endregion
            //保存
            _excelApp.Visible = false;//
            _excelApp.DisplayAlerts = false;//保存excel的时候,不弹出是否保存的窗口直接保存
            //保存为Office2003和Office2007都兼容的格式
            _book.SaveAs(excelPath, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
            _log.InfoFormat("成功|导出 " + (datatable.Rows.Count).ToString() + " 条记录!");
            MessageBox.Show("成功|导出 " + (datatable.Rows.Count).ToString() + " 条记录!");

            return true;
        }
        catch(Exception ex)
        {
            string ms = string.Format("导出失败!【{0}】", ex.Message);
            _log.FatalFormat(ms);
            MessageBox.Show(ms);                
        }
        finally
        {
            //if (_excelApp != null)
            //{
                _excelApp.Quit();
                ReleaseCOM(_sheet);
                ReleaseCOM(_sheets);
                ReleaseCOM(_book);
                ReleaseCOM(_books);
                ReleaseCOM(_excelApp);
            //}
        }
        return false;
    }
    /// <summary>
    /// 释放指定资源
    /// </summary>
    /// <param name="pObj"></param>
    private static void ReleaseCOM(object pObj)
    {
        try
        {
            System.Runtime.InteropServices.Marshal.ReleaseComObject(pObj);
        }
        catch
        {
            throw new Exception("释放资源时发生错误!");
        }
        finally
        {
            pObj = null;
        }
    }
              /// <summary>
    /// 创建一个Excel程序实例
    /// </summary>
    private static void CreateExcelRef()
    {
        _excelApp = new Excel.Application();
        _books = (Excel.Workbooks)_excelApp.Workbooks;
        _book = (Excel._Workbook)(_books.Add(_optionalValue));
        _sheets = (Excel.Sheets)_book.Worksheets;
        _sheet = (Excel._Worksheet)(_sheets.get_Item(1));
    }        

推荐使用Aspose.Cells这个dll,如果楼主要源码的话我有,记得私聊我,我发给你,做了数据处理,相信你能很快看懂。

我也推荐aspose.cells这个dll 里面有很简单的方法可以导入数据

Csdn user default icon
上传中...
上传图片
插入图片
抄袭、复制答案,以达到刷声望分或其他目的的行为,在CSDN问答是严格禁止的,一经发现立刻封号。是时候展现真正的技术了!